Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mridula
Starting Member
2 Posts |
 Posted - 2010-01-26 : 21:04:03
|
| If we have a query select count(1) from tblA where col1 ='aaa'versusdeclare @colx varchar(10)set @colx = 'aaa'select count(1) from tblA where col1 = @colxthey have very different query plans. The optimizer seems to be thrown off in case 2 and only a with recompile option seems to work. THis happens more with tables having uneven distribution on index columns etc. Anyone have insights into how the query optimizer views these 2 cases?thanksmridula |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 03:15:57
|
| The second is happening because of "parameter sniffing" - you might want to Google for that? |
 |
|
|
mridula
Starting Member
2 Posts |
Posted - 2010-01-27 : 13:05:21
|
| Thank you, that makes sense. but if this is a parameterized query via jdbc, sounds like my only workaround is to make this a sp? any other options to handle this? this is a dynamically generated query and we would like to avoid passing the query as an input (to avoid SQL injection issues.)Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 14:14:12
|
| Using sp_ExecuteSQL might avoid the issue - but still have the benefits of allowing you to have a parametrised query, and caching the Query Plan |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-27 : 14:16:52
|
| Kristen,Does using sp_ExecuteSQL always help in query times? I've got a couple very long queries that use a few date parameters, I am googling it right now, but didn't know if you had a more direct answer. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 14:28:20
|
| I think it would be fair to say that most of the time sp_ExecuteSQL is as good as using an SProc.If you have multiple statements, conditional logic, and so on then an SProc will be better. But for a single, parametrised, SELECT I think sp_ExecuteSQL is just fine.In particular, sp_ExecuteSQL is excellent for dynamic SQL - e.g. where you make the WHERE clause in your application (i.e. using string concatenation), depending on the criteria values that the user has provided. This tends to generate several variations-on-a-theme for the WHERE clause, each of them cached as a separate query plan, but if any of them are reused the Query plan will already be cached - so it favours the frequently used queries, and also the query that is immediately re-run with slightly different parameters (which I think is common amongst users - "That is not what I need, I'll change the Date Range and try again ...") |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-27 : 14:38:12
|
| Ok, thanks for the info, if I may ask one more followup (ok maybe 2):1. Will there still be a benefit to sp_ExecuteSQL if the statement contains a UNION ALL?2. I was reading just now on msdn that executing SQL via EXEC() also has plan reuse capabilities, are both viable? (again with a UNION as well)Thanks! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 14:55:12
|
1. Yes, I don't see why not. sp_ExecuteSQL is used in preference to just firing dynamic SQL (i.e. unparametrised) at the server because the generic query plans are cached. Make the SQL as complex as you like!2. I read that too. I don't know how well it works. SQL has to guess that "WHERE Name = 'Kristen'" could be a criteria that will change in subsequent reuses. It also has to spend CPU working out that "WHERE Name = 'DP978'" is the same as the earlier query , but with a different parameter - some fancy string comparison there! whereas "WHERE Name = @NameParameter" is unambiguous in that regard  and just requires an EXACT MATCH comparison with the cached query plans. (be aware of that when using sp_ExecuteSQL, even different capitalisation could mean SQL caching a different query plan)The problem is that 99% of SQL is written by DEVs with no advanced knowledge of SQL and then just build strings of SQL commands, like "WHERE Name = 'Kristen'", and EXEC them.Then they call up people like me and say "Our database has got really slow", I charge them Mega bucks and explain how they should use sp_ExecuteSQL and they get anything up to 100 fold [honestly!] improvement in performance.So I think, given that you are comfortable with sp_ExecuteSQL, that will give you unambiguous control over what is a parameter, and what is a Constant, in the query and thus ought to perform better.But there is a development cost, of course, to using the more complicate sp_ExecuteSQL syntax and maybe SQL is now smart enough to just be given "WHERE Name = 'Kristen'" all the time, and I can no longer make Mega Bucks from consultancy  !!Note that sp_ExecuteSQL does not care if you give it parameters that are not in the query itself, so you don't have to carefully control those.e.g. maybe your WHERE clause can have @Name and @Age, you can always provide both, even if sometimes the query is only "WHERE Name = @Name".Obviously if you can avoid providing anything that is superfluous that would be better, but sometimes it is easier to pass all possible parameters, but only the "skinny" WHERE clause. |
|
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2010-01-27 : 15:29:58
|
| Awesome, really niec write up. I appreciate your time in writing that. I will put my code into sp_ExecuteSQL and see if there are any improvements upon running.http://technet.microsoft.com/en-us/library/cc966425.aspx^ is where I spotted it saying exec() can take advantage of Plan Cache, I have not finished the entire paper though. Some decent info in there.Thanks again! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 16:18:23
|
| Well, you can always try both ways and check the Query Plan for each.It is also possible to see whether the query found a cached Query Plan, or not. So you could compare A with B.One point to note:When you use sp_ExecuteSQL the Query Planner has to check if the SQL statement is in the cache (i.e. look for the whole piece of text, which might be quite long).If you execute an SProc the Query Planner just has to look if that Sproc's query plan is in the cache.That test is just "Look for the SProc name / Sproc ID in the Cache" - so may be demonstrably faster. ("Internally, SQL Server converts the name of the stored procedure to an ID, and subsequent plan reuse happens based on the value of that ID.")Also, with an Sproc you just say "EXEC MySProc @Param1=123, ..." which is MUCH skinnier than sending the whole query each time ... so there are additional benefits to Sprocs compared to the other dynamic SQL methods.However, lets not get this out of proportion. If you give SQL Query Planner a COMPLEX query - e.g. where there are lots of JOINs and lots of indexes on the Tables for Query Planner to choose which ones to use - it is entirely possible that Query Planner will take longer to DECIDE which indexes to use than it ACTUALLY takes to get the data - hence the importance of getting a Cached Query Plan!! That time will outweigh the slight benefit of SProcs over sp_ExecuteSQL for general queries.From the article you linked to (interesting reading, thanks!)"In general, SQL Server 2005 auto-parameterizes those queries whose query plans do no depend on particular values of the constant literals. ... When values of constant literals appearing in a query can influence a query plan, the query is not-autoparameterized"This suggests to me that a Constant value, in your sp_ExecuteSQL query, will be cached correctly, but in an auto-parametrized query that might cause the query to NOT be treated as auto-parametrized - and then all benefit is lost."When using sp_executesql, a user or an application explicitly identifies the parameters"So, as I hypothesized earlier, sp_ExecuteSQL will give you control over what is parametrized than just letting SQL 2005 do it for you. |
|
|
|
|
|
|
|
|
Dynamic versus parameterized query