OK, I am impressed now…

I was working on a set of scripts for updating some data in a test database here at work…. (had not saved…)

(yet…)

My computer dies for some reason… I think I have a bad mother board… Anyway…

After turning my computer back on, I opened SQL Server Management Studio… the application then prompted me that it had found a file that had not been saved and asked me if I would like to restore it… Uhhhhhh Yeah……

Nothing was lost. Thanks Microsoft for that nice addition to the SQL Server tools…

You’re blessed when you’re content with just who you are—no more, no less. That’s the moment you find yourselves proud owners of everything that can’t be bought.

Matthew 5:5 (The Message)

My oldest child and only daughter turned SIX today.

Happy Birthday Emilee!!!

I have two messenger beta invites if anyone wants them. Please let me know.

Michal Ferber was showing me the Full Screen functionality of IE7 (F11 Key). This presented a Bug…

I am running Version 7.0.5112.0 of Internet Explorer Beta.

I use two monitors on my computer. If IE7 is on the non-primary monitor, hit F11 (full Screen), Internet Explorer will disappear.

This works fine on the primary monitor.

I got a call the other day from a client with an error in one of our applications. This application has been running bug free (knock on some wood), for a solid year. She explained when running one of her monthly reports; it would run for 30 seconds and then give an error.

This is an ASP.NET application written in C# with Microsoft SQL 2000 as the database.

I started by running the stored procedure using SQL Management Studio. It took 59 seconds to return all the data. Found my problem right away; the application timeout was 30 seconds… Now… Why in the world was it taking soooooooo long to run?

When I opened the stored procedure… I noticed two function calls in the SQL code. The function call executed a query to retrieve a specimen count based on certain parameters. This was not a basic query…

Anyway, I modified the query in the stored procedure to eliminate the function calls and it reduced the 59 seconds to 1 second. YES… 1 second. I simply replaced the function call with a subquery of the exact same query as the function.

Ok, now the reason. Functions are not compiled in the same execution plan of stored procedures. SQL Server cannot determine the best indexes and optimum execution plans. It is very import to keep this in mind when writing queries. Functions are GREAT for doing a calculations or string/ date alterations… but not too good for queries.

Modified example of what found…

SELECT
‘z’ = sv.Site,
‘y’ = u.FirstName + ‘ ‘ + u.LastName,
‘AvgVisitLength’ = AVG(DATEDIFF(s, SV.StartTime, SV.DepartTime)),
‘SiteVisitCount’ = COUNT(*),
‘Count’ = dbo.func_GetCount(@A, @Date1, @Date2, @B, sv.Site),
‘AvgPerVisit’ = CONVERT(money, dbo.func_GetCount(@A, @Date1, @Date2, @B, sv.Site))  / COUNT(*)

FROM table1 SV
INNER JOIN table2 s ON sv.SiteID = s.SiteId
INNER JOIN table3 u ON sv.UserID = u.UserID

WHERE
sv.Field1 = @A AND
SV.StartTime BETWEEN @Date1 AND DATEADD(d, 1, @Date2) AND
SV.UserID = CASE WHEN @B IS NULL THEN SV.UserID ELSE @B END

GROUP BY sv.Site, u.FirstName, u.LastName, s.Site

ORDER BY s.Site, u.FirstName, u.LastName>