Today I wrote a monster query to give me the abandon rate for each step of our checkout process at Sewell Direct. We’ve been using good old Google Analytics, but we really want to see the data up to the minute, hour, etc. Anyway, running it in SQL Server Management Studio took about 4 seconds for a date range of one week. After I was satisfied, I plopped it in a stored procedure and ran it again for good measure–39 seconds. What?!! I thought (as many others do) that stored procedures are supposed to beat dynamic queries, especially on the first run before the dynamic query’s execution plan has been cached.
After a little digging, I discovered a few things. First, when running a dynamic query, SQL Server Management Studio (or whatever client you’re using like ADO.NET) actually prepares the execution plan. Apparently, SSMS was doing a better job than SQL Server proper was when the stored procedure was compiled. I found this forum post that explains why. Basically, the parameter types play a huge role in the preparation of the execution plan. My stored procedure was using two DATETIME parameters (e.g. @StartTime, @EndTime) whereas my dynamic query just CAST two string-based representations of the dates into DATETIME variables for use in the query. When I changed the parameters to VARCHAR(23) and passed in string-based dates, the stored procedure ran in 4 seconds, just like the dynamic query!
Before:
CREATE PROCEDURE CheckoutProcessAbandonRateReport
@StartTime datetime,
@EndTime datetime
AS
BEGIN
...
After:
CREATE PROCEDURE CheckoutProcessAbandonRateReport
@StartTimeStr varchar(23),
@EndTimeStr varchar(23)
AS
BEGIN
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SET @StartTime = (CAST(@StartTimeStr as datetime))
SET @EndTime = (CAST(@EndTimeStr as datetime))
...
I’m going back though a few of our other hefty queries to see if this tweak can speed those up as well.