Use Dynamic SQL to Improve Query Performance

Question

Use Dynamic SQL to Improve Query Performance

Viewing 15 posts - 31 through 45 (of 106 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

shaytoder (5/20/2010)
it's great,
because if you have indexes on name or colour, it will use them,
and it will NOT check every column you have in the query, just the ones with not-null parameters.
so you can have covered indexes which not contains every column Shay

But I like index seeks! 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

shaytoder (5/20/2010)
Paul White NZ (5/20/2010)
shaytoder (5/20/2010)
another option to solve the issue with optional parameters, without using dynamic SQL, is to use CASE, it will NOT check fields that he doesn't need !
How good is that at using indexes Shay? 😉
it's great,
because if you have indexes on name or colour, it will use them,
and it will NOT check every column you have in the query, just the ones with not-null parameters.
so you can have covered indexes which not contains every column
Shay

How will it use it? Seek or scan? I have just tested on both SQL Server 2005 SP3 and SQL Server 2008 SP1, and all I get is scans.

matt-895911 SSC Enthusiast Points: 103 More actions · Answer 3

Isn't this article worthless without some actual exection plans and timings to backup the point?

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

The author did a fine job discussing the different approaches. The article shows once again that never and always are both almost always never the correct answer.

Paul White SSC Guru Points: 150467 More actions · Answer 5

matt-895911 (5/20/2010)
Isn't this article worthless without some actual exection plans and timings to backup the point?

No. It's a great article - and well worth the 4 stars I gave it.

I agree that test results, execution plans, and a downloadable test script would have been useful. It would have received 5 stars in that case.

Certainly a long, long way from 'worthless'.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

shaytoder SSC Enthusiast Points: 127 More actions · Answer 6

Nils Gustav Stråbø (5/20/2010)
shaytoder (5/20/2010)
Paul White NZ (5/20/2010)
shaytoder (5/20/2010)
another option to solve the issue with optional parameters, without using dynamic SQL, is to use CASE, it will NOT check fields that he doesn't need !
How good is that at using indexes Shay? 😉
it's great,
because if you have indexes on name or colour, it will use them,
and it will NOT check every column you have in the query, just the ones with not-null parameters.
so you can have covered indexes which not contains every column
Shay
How will it use it? Seek or scan? I have just tested on both SQL Server 2005 SP3 and SQL Server 2008 SP1, and all I get is scans.

my mistake,

for some reason i thought this approach will use seeks and not scans,

but now i tried it again, and i see scans.

sorry...:blush:

matt-895911 SSC Enthusiast Points: 103 More actions · Answer 7

But surely without actual data to back it up its nothing but hyperbole?

I mean, sure most of us have a gut feeling about how things ought to go speed-wise in a situation like this. However, its very easy to make a statement that is seemingly true, but can have the reverse effect on performance.

Of course there is always the universal caveat that you should test things on your own system and data sets.

So yes, worthless was an overstatement. How about, not appropriatley comprehensive given the subject matter?

Paul White SSC Guru Points: 150467 More actions · Answer 8

Jon Saltzman (5/20/2010)
I second the sommarskog.se site (which gretchkosiy 2550 refers to above) - that is an excellent writeup which includes gotchas for SQL 2008 SP1 and dynamic SQL if I recall correctly.

It is the definitive work - everyone decrying the use of dynamic SQL absolutely must read it.

...suffering because of the complete failure of the SQL query engine to short-circuit effectively...

This is not an optimiser failure, it is a design choice. The optimiser's goal is to quickly produce a good plan that can be reused by future queries. It does include some contradiction detection, and the CASE statement can be used where evaluation-order guarantees are required.

In general, the benefits of allowing the optimiser freedom to reorder expressions and logical operations greatly outweighs the perceived drawbacks of not implementing short-circuiting.

Again, as of 2008 SP1 CU5, OPTION (RECOMPILE) can be used to provide some of the aims of short-circuiting, at the cost of not caching a plan and incurring a full recompilation on every execution.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

matt-895911 (5/20/2010)
But surely without actual data to back it up its nothing but hyperbole?

I do take your point (hence the 4 stars) but that is still way too harsh.

I mean, sure most of us have a gut feeling about how things ought to go speed-wise in a situation like this. However, its very easy to make a statement that is seemingly true, but can have the reverse effect on performance.
Of course there is always the universal caveat that you should test things on your own system and data sets.

Quite so.

So yes, worthless was an overstatement. How about, not appropriatley comprehensive given the subject matter?

There was scope for an extra star, yes 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

Brett Robson (5/20/2010)
I don't see why you guys are promotiing the use of dynamic SQL. I'm often asked in job interviews when it's appropriate to use dynamic SQL - the correct answer is never.
I have never (and will never) see dynamic SQL out perform a properly coded & compiled stored procedure. It's impossible - NOTHING gets cached.

I'm sorry to disagree with you here, but dynamic queries do get cached. And if you build them using the parameterized approach, they can get reused too. I would say that using dynamic SQL should be an edge case, but to suggest that it's "never" a possible solution is taking away a useful tool.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

kwiggans Ten Centuries Points: 1196 More actions · Answer 11

Excellent article. I must admit that I was extreemly skeptical at first as I am used to writing catch all phrases in my code which I thought was so clever before.

Example: SELECT number FROM dbo.tbl WHERE name=ISNULL(@name,name);

Now I realize after running a test that this kind of "catch-all" coding as described by Gail can be a performance killer. I had to share my test findings because the test is so simple, but the results are dramatic.

Setup

I'm querying a table in our test environment with over 700,000 rows. The two columns I'm querying consist of the submission number qte and policy number pol. The submission number is the primary key on a clustered index while the policy is on a nonclustered index and could be a NULL value. Searching for NULL values in pol returns roughly half the rows in the table. A valid pol value will return at most 2 rows.

Step 1

Try the old "catch-all" method.

DECLARE @p varchar(100);SET @p='p12345';

SELECT qte FROM dbo.tbl WHERE pol=ISNULL(@p,pol);

The actual execution plan shows an index scan is performed on tbl with an estimated subtree cost of 6.19. The exact same query plan is used when I assign NULL to the @p parameter.

Step 2

Try using dynamic sql.

DECLARE @sql nvarchar(1000),@where nvarchar(1000),@params nvarchar(1000),@p varchar(100);

SET @p='p12345';

SET @sql=N'SELECT qte FROM dbo.tbl ';

SET @params=N'@pol varchar(100)';

IF @p IS NOT NULL

SET @where='WHERE pol=@pol;'

ELSE

SET @where='WHERE pol IS NULL;';

SET @sql=@sql+@where;

EXEC sp_executesql @sql,@params,@pol=@p;

The actual execution plan uses an index seek on both the clustered index and the nonclustered index for a total subtree cost of... wait for it... .00657 :w00t:. When I pass a NULL value for the parameter it switches back to an index scan with the same cost as seen in Step 1.

What an eye opener. Almost a 1000% increase in performance for non-NULL parameters. Previously, I looked at dynamic sql as something to be feared and shunned, but it definitely has its place.

I think the argument about disallowing a NULL value from being passed in the first place is a valid one, but we are still all at the mercy of app developers and end users requesting reports with wide-ranging functionality.

Keith Wiggans

gretchkosiy 2550 Valued Member Points: 62 More actions · Answer 12

kwiggans (5/20/2010)
The actual execution plan shows an index scan is performed on tbl with an estimated subtree cost of 6.19.
...
The actual execution plan uses an index seek on both the clustered index and the nonclustered index for a total subtree cost of... wait for it... .00657 :w00t:. When I pass a NULL value for the parameter it switches back to an index scan with the same cost as seen in Step 1.
What an eye opener. Almost a 1000% increase in performance for non-NULL parameters.

This VERY depends on statistics of data in the field.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 13

gretchkosiy 2550 (5/20/2010)This VERY depends on statistics of data in the field.

But the point is that when you use any of the mentioned methods (COALESCE, ISNULL, CASE etc), SQL Server will not be able to seek because of the non-deterministic nature of the expression.

gideon.kahl SSC-Addicted Points: 489 More actions · Answer 14

This article came at exactly the right time. I've been struggling to improve the performance of a rather complex search query, but without success. The query was using index seeks all over the place with no scans in sight.

When I saw the heading of the article in my e-mail this morning I wondered why I haven't thought of dynamic TSQL before. I rewrote the search routine in dynamic TSQL and it now takes 25% of the time of the original proc to execute.

Thanks!

Tim Cullen Ten Centuries Points: 1394 More actions · Answer 15

One of the things I do during development of a stored procedure that includes dynamic SQL is instead of executing the statement I PRINT (http://msdn.microsoft.com/en-us/library/ms176047.aspx) the statement. From there you can review the structure to make sure it's what you actually, as well as perform other tasks like setting IO and TIME statistics on and read the estimated execution plan.