SQL Server Performance Forum – Threads Archive
Prepared statements vs Static Statements
Hi all, I am optimizing an application of mine and have come across a strange phenomenon. I’ve always heard/known that Prepared statements are better than static statements because SQL Server is more likely to use a previous execution plan (no recompile). But I’ve run some tests and noticed the following annoying behaviour. Prepared statement: exec sp_executesql N’SELECT TOP 1 SubscriptionID, SubscriptionNotes FROM Subscriptions WHERE SubscriptionUserName = @par0 AND SubscriptionGroupID = @par1 ‘, N’@par0 nvarchar(4000),@par1 bigint’, @par0 = N’Glim’, @par1 = 1 Static version: exec sp_executesql N’SELECT TOP 1 SubscriptionID, SubscriptionNotes FROM Subscriptions WHERE SubscriptionUserName = ”Glim” AND SubscriptionGroupID = @par1 ‘, N’@par0 nvarchar(4000),@par1 bigint’, @par0 = N’Glim’, @par1 = 1 So far I’ve been using the previous type for all my queries, but I just noticed that this version is causing a table scan. I couldn’t figure it out until I ran the same query with the literal embedded in the string (bottom version). As soon as I made this change, SQL Server decided to use the index (subscriptionusername,subscriptiongroupid) as expected. Am I missing something here? Should I go back and use static queries everywhere? Any help would be appreciated. H.Note: The top query takes 5 logical reads while the bottom one requires 2. The table is still small though (under 1k records). The table scan will get less efficient as the table grows, presumably.
Sorry, but what is the difference between both statements?
And why do you use dynamic sql where a s_proc would do better?
Frank
http://www.insidesql.de
http://www.familienzirkus.de
I’m not sure if ‘dynamic SQL’ is the correct term in this case. It’s a a prepared statement vs a statement where the parameters are hardcoded. Look closely at the two queries, one has ‘Glim’ embedded, the other has that variable in a parameter. I’m not using stored procedures because I prefer having all my SQL code within my application. Thanks
Ooh, me bad !!! I see the difference. I would suggest readinghttp://www.sommarskog.se/dynamic_sql.html . Erland provides there several very good reasons not to use embedded SQL.
Frank
http://www.insidesql.de
http://www.familienzirkus.de
Thanks for the link. That was a very interesting article. Unfortunately, none of the pitfalls that are mentionned in that article are used in my query. The pitfalls noted usually concern using dynamic SQL in places like table names or column names, etc. I’m just using parameters in the search criteria, which according to the article, is exactly where dynamic SQL is supposed to be used. Seehttp://www.sommarskog.se/dyn-search.html from same person. The only thing I can think of is that SQL server knows that the query (with the current table size) is very easy to execute and is using a cached plan instead of wasting time creating a better one. I will try to force a recompile and update statistics and see if that changes anything.
also when you pass a parameter into a dynamic SQL statement its value will not be used to determine which index to use, whereas with a literal SQL will check how many rows actually have that value by checking statistics for that column/for indexes Cheers
Twan
Is that true? I don’t see why SQL Server would do that. It renders prepared statements completely useless in my opinion. I’m still not 100% satisfied with this, if anyone has any articles/links that goes into more detail, I’d be grateful. Clarification:
The reason I’m not satisfied is because every article that I can find on prepared statements praises their performance. If SQL Server is using the wrong index half the time because the statement is parameterized then I don’t see how it’s gonna help performance.
Thanks.
Well, let me report my findings for future reference. The above statement is not actually a prepared statement. All I had to do was call Command.Prepare() (ADO.NET) to make the provider use prepared statements. The trace now looks like this: declare @P1 int
set @P1=-1
exec sp_prepexec @P1 output, N’@par0 varchar(13),@par1 bigint’, N’SELECT TOP 1 SubscriptionID, SubscriptionNotes FROM Subscriptions WHERE SubscriptionMSISDN = @par0 AND SubscriptionServiceID = @par1 ‘, @par0 = ‘+447751234567’, @par1 = 10
select @P1 The only problem is now that every single query (sp_prepexec) is followed by a sp_unprepare, which may indicate that the cached plan is being thrown away. I will end this thread and open a new one on this topic.
Ok, I’ll talk to myself one last time. For those using .NET and SQL 2000. Here is the dumb reason why SQL Server is using the wrong indexes. It has nothing to do with prepared/non-prepared statements. The reason is that unless you specificy otherwise the SQL provider in .NET declares string characters as NVARCHAR. If you take a look closely at the original query you’ll see: nvarchar(4000). Anyway, all you have to do is tell it to use varchar explicitely. Enough of my rambling. H.
]]>