Use Dynamic SQL to Improve Query Performance

Question

Use Dynamic SQL to Improve Query Performance

Viewing 15 posts - 16 through 30 (of 106 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Nils Gustav Stråbø (5/20/2010)
Atif Sheikh (5/20/2010)
Dynamic SQL is the last thing to do
Dymamic SQL is the only efficient way (in my knowledge) to solve the problem with multiple optional parameters. Whether you use CASE, COALESCE, ISNULL or OR to try to avoid dynamic SQL, you will most likely (if not always) end up with index scans. I have never seen the methods mentioned above work in a performance perspective. Look at your execution plan the next time you try a MyCol=case when @val is null then MyCol else @val end. You will see a index scan, not a seek (if the column is indexed).

Ditto. See what Gail Shaw writes about this topic: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/.

-- Gianluca Sartori

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

Gianluca Sartori (5/20/2010)Ditto. See what Gail Shaw writes about this topic: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/.

My experience is that developers (who doesn't understand how a DBMS executes a query and have never heard of execution plans) write the queries on tables with small number of test rows. When they see that it works, and quite fast because of the small numbers of rows, they are happy with it and puts it to production. A year or two later, when the amount of data has grown large, the start to get complaints from the users because things are slow.

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 3

[font="Verdana"]

mohd.nizamuddin (5/20/2010)
Vladimir Basanovic (5/20/2010)
Maybe you should consider rewriting original function with coalesce on every parameter. That's how you would avoid table scan if the parameter is null.
BR,
Vladimir
Is it Vladmir... 😎
Thanks for sharing the idea. I know it is very simple thing but not aware.
Although I use to write WHERE clause using COALESCE, but do not know the performance improvement.
Really appriciate.

After traversing here and there

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

nothing but more controversies ! :crying:

[/font]

Brett Robson SSC Rookie Points: 49 More actions · Answer 4

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.

Regards,

Brett Robson

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

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.

"Never" and "Always" hardly apply to the database world. "It depends" fits better.

You have two possibilities:

1) Keep your idea that dynamic sql is evil and should never be used

2) Read the excellent articles (especially Erland Sommarskog's and Gail Shaw's blogs) that were linked in this thread and try to learn something

The coiche is yours.

-- Gianluca Sartori

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 6

I have executed the queries posted in the article. The table product.tblProduct have an index on Name Column. This column is used with LIKE operator in the query. If the parameter @Name is supplied with % signs, it will always go through Index Scan.

@Name Like '%A%' .

But It will go through Index Seek if it is used with = sign.

Plz correct me if I am wrong...

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 7

When dealing with sql injection, here is a derived table approach that can avoid it

http://beyondrelational.com/blogs/madhivanan/archive/2010/05/14/derived-table-new-approach-to-avoid-sql-injection.aspx

Madhivanan

Failing to plan is Planning to fail

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

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.

There is no correct answer to the question. As already mentioned, it always depends.

And the statements executed by sp_executesql DOES get cached. The combination of the SQL statement and the parameter definition gets cached. I suggest you take a look at the SP:Cache* events in Profiler when you use sp_executesql. Don't believe in myths, even not the one presented to you by interviewers.

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

Brett Robson (5/20/2010)
I don't see why you guys are promotiing the use of dynamic SQL.

Because it is very often the most efficient way to solve problems like those shown in the article.

I'm often asked in job interviews when it's appropriate to use dynamic SQL - the correct answer is never.

You can cut down the number of interviews you attend by changing your answer.

I have never (and will never) see dynamic SQL out perform a properly coded & compiled stored procedure. It's impossible - NOTHING gets cached.

Exactly wrong.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Atif Sheikh (5/20/2010)
I have executed the queries posted in the article. The table product.tblProduct have an index on Name Column. This column is used with LIKE operator in the query. If the parameter @Name is supplied with % signs, it will always go through Index Scan. @Name Like '%A%' .
But It will go through Index Seek if it is used with = sign.
Plz correct me if I am wrong...

LIKE is SARGable when the expression does not begin with a wildcard. Try @Name LIKE 'A%' for example.

There are tricks to make '%A%' SARGable:

CREATE TABLE #Test (string VARCHAR(10) NOT NULL, pos AS PATINDEX('%A%', string));

INSERT #Test VALUES ('Egg');

INSERT #Test VALUES ('Banana');

INSERT #Test VALUES ('Cherry');

INSERT #Test VALUES ('Onion');

INSERT #Test VALUES ('Aardvark');

CREATE INDEX nc1 ON #Test (pos) INCLUDE (string);

-- Index seek

SELECT string

FROM #Test

WHERE PATINDEX('%A%', string) = 0;

-- Index seek

SELECT string

FROM #Test

WHERE PATINDEX('%A%', string) <> 0;

DROP TABLE #Test;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Nils Gustav Stråbø (5/20/2010)
Atif Sheikh (5/20/2010)
Dynamic SQL is the last thing to do
Dymamic SQL is the only efficient way (in my knowledge) to solve the problem with multiple optional parameters. Whether you use CASE, COALESCE, ISNULL or OR to try to avoid dynamic SQL, you will most likely (if not always) end up with index scans. I have never seen the methods mentioned above work in a performance perspective. Look at your execution plan the next time you try a MyCol=case when @val is null then MyCol else @val end. You will see a index scan, not a seek (if the column is indexed).

True - or at least it is prior to SP1 CU5 SQL Server 2008. After that, a query plan very smiliar to that produced by dynamic SQL can be obtained (but not cached, sadly) at the cost of a recompile. See Erland's fine work on this subject.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

shaytoder SSC Enthusiast Points: 127 More actions · Answer 12

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 !

for example -

select ...

from ...

where 1 = (case when @Name is null then 1

when @Name is not null AND Name=@Name then 1

else 0

end)

and 1 = (case when @Color is null then 1

when @Color is not null AND Colour=@Color then 1

else 0

end)

and ...

Shay

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

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? 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

shaytoder SSC Enthusiast Points: 127 More actions · Answer 14

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

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

Paul White NZ (5/20/2010)True - or at least it is prior to SP1 CU5 SQL Server 2008. After that, a query plan very smiliar to that produced by dynamic SQL can be obtained (but not cached, sadly) at the cost of a recompile. See Erland's fine work on this subject.

Interesting. I'll have to install CU5 and try on one of my test servers.