Here is another tip for those who need to explore big SQL databases with a lot of stored procedures and quite a bit of logic. This will gives you a list of all the procedures that has a certain string in them:
CREATE PROCEDURE dev_FindInSP
@str nvarchar(max)
AS
select distinct name from sys.procedures procs join syscomments comments
on procs.object_id = comments.id where [text] like '%' + @str + '%'
Can someone explain why VB.Net has a keyword called "AddessOf" ? I would expect such a thing from a low level language, and even C# could be excused if it had this. But AdressOf seems to me like a throwback to ancient times where passing function pointers around was the norm.
My legendary skills in breaking code have now reached a new level, in which I managed to break the C# Compiler. Check out this code:
public delegate void Proc();
public class CrashAndBurnAttribute : System.Attribute
{
public CrashAndBurnAttribute(Proc p)
{ }
}
public class CrashAndBurnClient
{
[CrashAndBurn(delegate { return; })]
public void Foo()
{
}
}
If you try to run compile it from VS.Net you’ll get a nice crash dialog, and the result of running the compilation from the command line is:
Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.42
for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727
Copyright (C) Microsoft Corporation 2001-2005. All rights reserved.
crash.cs(11,16): error CS1706: Expression cannot contain anonymous methods
error CS0583: Internal Compiler Error (0xc0000005 at address 5A10A8E0): likely culprit is 'COMPILE'.
An internal error has occurred in the compiler. To work around this problem, try simplifying or changing the program near the locations listed below. Locations at the top of the list are closer to the point at which the internal error occurred. Errors such as this can be reported to Microsoft by using the /errorreport option.
crash.cs(11,25): error CS0585: Internal Compiler Error: stage 'COMPILE'
crash.cs(12,14): error CS0584: Internal Compiler Error: stage 'COMPILE' symbol 'CrashAndBurnClient.Foo()'
crash.cs(12,14): error CS0584: Internal Compiler Error: stage 'COMPILE' symbol 'CrashAndBurnClient.Foo()'
crash.cs(12,14): error CS0584: Internal Compiler Error: stage 'COMPILE' symbol 'CrashAndBurnClient.Foo()'
crash.cs(9,14): error CS0584: Internal Compiler Error: stage 'COMPILE' symbol 'CrashAndBurnClient'
crash.cs: error CS0584: Internal Compiler Error: stage 'COMPILE' symbol '<global namespace>'
crash.cs: error CS0586: Internal Compiler Error: stage 'COMPILE'
error CS0587: Internal Compiler Error: stage 'COMPILE'
error CS0587: Internal Compiler Error: stage 'BEGIN'
This is actually sad, since I really wanted to be able to do pass delegates to attributes.
I recently had to write a lazy loaded property that picked what its value will be based on the environment it run on. Basically, I had the same old problem of keeping data for the current context only. This usually means the current thread, but in ASP.Net, it is the current request.
My solution for it was simple:
.
So I can store/retrieve data without caring in what environment I’m running on by simply using:
CurrentData.Items["current-db-connection"] = new SqlConnection();
I need this ability to store data that is important for the current logical execution thread (be that an ASP.Net request or a real thread). The usual method of implemention the Items property on the CurrentData class would be something like:
public static ICurrentDataStore Items
{
get
{
if (items == null)
items = CreateCurrentDataStoreForEnviornment();
return items;
}
}
But I thought that I could get rid of the null check, this should allow the JIT to view the property as a simple accessor, so it would be just as fast as accessing a field directly. I could’ve simply instasitated the items field to the proper value based on the environment, but I wanted to mimic a real lazy loaded property. I ended up with this class:
public class DataStoreSwitcher : ICurrentDataStore
{
private Action<ICurrentDataStore> set;
public DataStoreSwitcher(Action<ICurrentDataStore> set)
{
this.set = set;
}
#region ICurrentDataStore Members
public object this[object key]
{
get
{
ICurrentDataStore real = EnsureCorrectDataStoreForEnvironment();
return real[key];
}
set
{
ICurrentDataStore real = EnsureCorrectDataStoreForEnvironment();
real[key] = value;
}
}
private ICurrentDataStore EnsureCorrectDataStoreForEnvironment()
{
ICurrentDataStore real;
if (HttpContext.Current == null)
real = new CurrentThreadDataStore();
else
real = new CurrentRequestDataStore();
set(real);
return real;
}
#endregion
}
I initialize the variable like this:
private static ICurrentDataStore items =
new DataStoreSwitcher(
delegate(ICurrentDataStore real)
{
items = real;
});
In this particular point, there is very little to be gain from this technqiue, and if the case was a hefty resource (choosing database connection, for instance), it can certainly be useful. Beside, I like using delegate J, and it’s a cool thing to do.
I got a stored procedure that was taking an undue amount of time to run, in was about half an hour, and that was really bad. I tried looking at the execution plan and add an index to make it faster, but it wasn't that effective.
The query was an update from a nested select (which contained a rather complex condition and a couple of joins), and I couldn't really figure out how to make it better. I tried running the pieces individually, and they run reasonably fast, so it was the interactions between them that killed the performance. The execution plan showed that the joins were costing very much in terms of performance.
During my attempts to figure it out, I moved the select into a temporary table, and then updated from it, like this:
SELECT To, SUM(p.Amount) TotalAmount
INTO #TempPaymetsAmounts
FROM Payments p, Payments p2, Orders o
WHERE ComplexCondition
GROUP BY [To]
UPDATE PaymentsTotal
SET Total = TotalAmount
FROM #TempPaymetsAmounts
WHERE SimplerCondition
That query run in less than 10 seconds.
The only thing that I did was move the select into a temporary table, but I'm not sure why this should have such a profound effect on the performance of the query.
I recently discovered that a certain class of programmers has a really bad case of Not Invented here. It got so bad that they decided to implement JOIN and GROUP BY by using cursors. Of course, due to the nature of the problem, they were so excited by their new discovery that they immediately rushed to propogate it thorughout the code base, using none else than the tried and true method of copy-paste-change-one-charater-and-then-move-on-never-looking-back.
I won't speak of the performance of this method, but I think that you can guess that there are orders of magnitude difference between this new way of dealing with the database and the standard method of operation in the industry.
I knew that it was bound to happen.
I started running into highly mysterious errors in SQL Server. The strangest among them is the Missing Columns Mystery.
When I run a stored procedure that calls to other SPs, and so on, I get an error about invalid columns somewhere deep in the stack. But when I run the SP directly, it passes perfectly.
Of course that when I check it, the table has the supposedly missing columns. It drives me crazy, and I can't really get the error consistently, if I run it for a couple of time, it seems to fix itself, but I've no idea how.
[Via Mitch Denny's Blog ] [Via Sql Down Under]
It's usualy hard to efficently get hierarchical data using SQL. But apperantely SQL Server can do that easily. The key to this is to create common table expression that recursively points to itself, like this:
WITH AllEmployeesOf([EmployeeId], [Name], [ManagerName])
AS
(
SELECT m.[EmployeeId], m.[Name], convert(nvarchar(50),null) as ManagerName
FROM [Employees] m WHERE @employee_id = m.[EmployeeId]
UNION ALL
SELECT e.[EmployeeId], e.[Name], AllEmployeesOf.[Name] as ManagerName
FROM [Employees] e JOIN AllEmployeesOf ON
e.[ManagerId] = AllEmployeesOf.[EmployeeId]
)
SELECT * FROM AllEmployeesOf
Before this I would resort to either getting all of the rows or issuing several queries. Nice.
No future posts left, oh my!
| Main feed | |
| Comments feed |
+972 52-548-6969
