SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Filter and Order By


Dynamic Filter and Order By

Author
Message
PiMané
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2698 Visits: 1351
Hi,

I'm doing some testing with dynamic filtering and order by clause.
I'm comparing dynamic SQL vs using the CASE statement...

DECLARE @FilterName NVARCHAR(100) = 'M%'
DECLARE @FilterSalary FLOAT = 3000
DECLARE @OrderField INT = 0 --0 empname, 1 salary, 2 - empid
DECLARE @Query NVARCHAR(500)
DECLARE @Params NVARCHAR(500)

SET @Query = 'SELECT empid, empname, salary FROM dbo.Employees WHERE 1 = 1'
SET @Params = '@FilterNameIn NVARCHAR(100), @FilterSalaryIn FLOAT'

IF LEN(@FilterName) > 0
SET @Query = @Query + ' AND empname LIKE @FilterNameIn'
IF LEN(@FilterSalary) > 0
SET @Query = @Query + ' AND salary > @FilterSalaryIn'
IF @OrderField = 0
SET @Query = @Query + ' ORDER BY empname'
IF @OrderField = 1
SET @Query = @Query + ' ORDER BY salary'
IF @OrderField = 2
SET @Query = @Query + ' ORDER BY empid'

EXEC sp_executesql @query, @Params, @FilterNameIn = @FilterName, @FilterSalaryIn = @FilterSalary

SELECT empid, empname, salary FROM dbo.Employees WHERE
empname LIKE CASE WHEN LEN(@FilterName) > 0 THEN @FilterName ELSE empname END
AND
salary >= ISNULL(@FilterSalary, 0)
ORDER BY
CASE WHEN @OrderField = 0 THEN empname END,
CASE WHEN @OrderField = 1 THEN salary END,
CASE WHEN @OrderField = 2 THEN empid END


Initially I tried

ORDER BY
CASE
WHEN @OrderField = 0 THEN empname
WHEN @OrderField = 1 THEN salary
WHEN @OrderField = 2 THEN empid
END


To order by only one column but since the fields have different data types SQL gave an error. I could do a CAST but salary as NVARCHAR doesn't give good results... So I used the 3 columns sort.....
In both cases the execution plan is very similar, the CASE statement has a Compute Scalar with 0% (from the CASE statement)...
But if I order by the 3rd column ( = 2) to non dynamic statement, according to execution plan comparing both, is much "heavier" since it orders by 2 NULL columns and only then by the desired column....
Is this a case where Dynamic SQL can be used, where it's better than "regular" SQL statements?

Thanks,
Pedro



If you need to work better, try working less...
Sean Lange
Sean Lange
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58841 Visits: 17935
You should read this article from Gail about catch all queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
PiMané
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2698 Visits: 1351
Sean Lange (10/2/2012)
You should read this article from Gail about catch all queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Thanks, I've read it and it answers my question...
But is there a way of having a dynamic ORDER BY with CASE clause without having N order columns?!

ORDER BY CASE WHEN @ordfld = 1 THEN MoneyField WHEN @ordfld = 2 THEN NameField END


this raises an error.. it has to be written

ORDER BY CASE WHEN @ordfld = 1 THEN MoneyField END, CASE WHEN @ordfld = 2 THEN NameField END


which makes 3 order by clauses when I only want one, even if they can be NULL...

Thanks,
Pedro



If you need to work better, try working less...
davidandrews13
davidandrews13
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1805 Visits: 4633
if you know where the column that you want to Order By, sits, you can do

select
col1
,col2
,col3
from tablename
order by 1



this will order by the first column.
change it to 'order by 2' to order by the second column
PiMané
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2698 Visits: 1351
Thanks... using the column number isn't as much readable friendly as is the column name.... but it works fine :-)
Is there any problem using the column number besides risking adding a column to the query and messing the order and not being so friendly?!

Thanks,
Pedro



If you need to work better, try working less...
Sean Lange
Sean Lange
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58841 Visits: 17935
PiMané (10/2/2012)
Thanks... using the column number isn't as much readable friendly as is the column name.... but it works fine :-)
Is there any problem using the column number besides risking adding a column to the query and messing the order and not being so friendly?!

Thanks,
Pedro


It will work but referring to columns by ordinal position is fraught with maintenance issues. You have already defined the problem. The query changes and the person making the change doesn't know or even think about the order by variable stuff going on.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
PiMané
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2698 Visits: 1351
Thanks to all for the help..
Pedro



If you need to work better, try working less...
PiMané
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2698 Visits: 1351
Hi,

I just made a simple test... I build a dynamic SQL to execute and executed the same query directly in SMSS...
Can anyone explain why the dynamic SQL is 46% of total time and the same query but "fixed" is 54%, as shown in the attachment..?!?!


CREATE NONCLUSTERED INDEX [idx_Employees_003] ON [dbo].[Employees]
(
[empname] ASC,
[salary] ASC
);


This is the index being used...

If I use the "fixed" SQL but "guessing" the parameters, like this:

SELECT empid, empname, salary FROM dbo.Employees WHERE empname >= CASE WHEN LEN(@FilterName) > 0 THEN @FilterName ELSE empname END AND salary >= ISNULL(@FilterSalary, 0)


SQL suggests an index on salary including empid and empname..

If instead of empname >= ... I use empname LIKE ..., on the dynamic SQL, it also suggests the index on salary and include empid and empname... Isn't LIKE as good as >= for index seeks?!?

Thanks,
Pedro



If you need to work better, try working less...
Attachments
execution_plan.jpg (9 views, 152.00 KB)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58841 Visits: 17935

... Isn't LIKE as good as >= for index seeks?!?


NO LIKE is not SARGable so you will get scans. It has to examine every row to determine if it is a match or not.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
PiMané
PiMané
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2698 Visits: 1351
Sean Lange (10/3/2012)
NO LIKE is not SARGable so you will get scans. It has to examine every row to determine if it is a match or not.


So a LIKE 'M%' is better replaced with a >= 'M' AND < 'N'..



If you need to work better, try working less...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search