Large dynamic Query. - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Large dynamic Query.

Author

Topic

martind1
Starting Member

28 Posts

Posted - 2012-02-06 : 09:06:16

Hi,

My first post here. I am a web developer and I work for a large web travel company.

I currently have an SQL query where one part (the where clause) has to be dynamic, based on the value of a parameter passed to the stored procedure.

For example:

If @value = 2

WHERE dbo.Prices.Price2 > 0

But if @value = 3

WHERE dbo.Proces.Price3 > 0

I have tried setting a varchar and storing the query in that and using a CASE statement, but it doesnt like to print or execute that for some reason, and I want the query to be as clean as possible.

Any suggestions?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-06 : 09:20:08

Take a look at this blog: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Your query is slightly different, in that, you are changing the where clause based on the value of a single parameter. But, the same principles should apply. Two interesting things about what she is demonstrating there are: a) it is done without the risk of SQL injection, and b) it is done to avoid poor execution plans which some of the other techniques can generate.

martind1
Starting Member

28 Posts

Posted - 2012-02-06 : 09:20:40

Thanks sunitabeck, I have eventually solved it.

For anybody who ever needs this.

I simply did:


WHERE ( (@value=2 AND Price2 > 0) OR (@value=3 AND Price3 > 0) )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 09:30:18

two options are there
one is to use dynamic sql and append where part as a string based on @value parameter

other way is to use CASE statement like


WHERE CASE @value 
      WHEN 2 THEN dbo.Prices.Price2 
      WHEN 3 THEN dbo.Prices.Price3
       ...
      END >0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

martind1
Starting Member

28 Posts

Posted - 2012-02-06 : 09:36:07

Cool,

Quick question.

In terms of performance (query speed) what is better to use.

Build a query up in a variable, then execute it. Or execute it as a normal query?

Example:


declare @sql nvarchar(max) = 'SELECT * FROM table';
sp_executesql @sql


SELECT * FROM table

But the queries would have around 15 JOINS on them and many filters.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 09:51:32

i would say dynamic would be better especially if data contained in table is huge. You can use sp_executesql as you posted. That would mimise risk of sql injection attack too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

martind1
Starting Member

28 Posts

Posted - 2012-02-06 : 09:57:54

Hmm Okay,
I just hate the look of dynamic queries lol.
Yeah the table data will be huge eventually, Around the region of 15million rows in the main table plus joins.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 10:00:36

quote:
Originally posted by martind1

Hmm Okay,
I just hate the look of dynamic queries lol.
Yeah the table data will be huge eventually, Around the region of 15million rows in the main table plus joins.

the problem with CASE logic is it will not result in an optimal plan so better to do it dynamic way especially if number of conditions are large

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-02-06 : 10:12:23

You need your call to sp_executesql to be parametrised - that way the query plan will be cached (assuming that the same query re-occurs).

Lets assume you ALSO have a variable @ClientID which may be 0 (match anything) or a specific ID to match a single Client.


declare @sql nvarchar(max) = 'SELECT * FROM table WHERE 1=1'
IF @value=2 SELECT @sql = @sql + ' AND Price2 > 0'
IF @value=3 SELECT @sql = @sql + ' AND Price3 > 0'
...
IF @ClientID <> 0 SELECT @sql = @sql + ' AND MyClientID = @ClientID'

EXEC sp_executesql @sql
                   N'@ClientID int',
                   @ClientID

Points to note:

Whether @SQL actually contains the WHERE clause phrase " AND MyClientID = @ClientID", or not, doesn't matter. You can still pass the @ClientID parameter to sp_ExecuteSQL without it actually needing to be used.

You COULD do this instead:


IF @ClientID <> 0 SELECT @sql = @sql + ' AND MyClientID = ' + CONVERT(varchar(20), @ClientID)

i.e. your WHERE clause would be something like


WHERE 1=1 AND MyClientID = 1234

but a) this type of string concatenation is at risk from "SQL Injection" and b) SQL is caching the query plan for ClientID=1234, if next time you ask for ClientID=5678 then that won't match the cached query, so a new query plan will be created (and cached!). Chances of re-use are very low.

Whereas if, in effect, every time you ask for


WHERE 1=1 AND MyClientID = @ClientID

then that exactly matches the cached query ... so the plan is reused (but the place-marker "@ClientID" is substituted for the current parameter's value - either "1234" or "5678" before the query is actually run)

Of course, using dynamic SQL, you may have several cached query plans:


SELECT * FROM table WHERE 1=1 AND Price2 > 0
SELECT * FROM table WHERE 1=1 AND Price3 > 0
SELECT * FROM table WHERE 1=1 AND Price2 > 0 AND MyClientID = @ClientID
SELECT * FROM table WHERE 1=1 AND Price3 > 0 AND MyClientID = @ClientID

but the commonly used ones will be retained in cache, and the infrequent ones purged. So long as you don't have an infinite number of combinations this will be more efficient than having a single "generic" query with a WHERE clause like this:


SELECT * FROM table
WHERE 1=1 
      AND (@value <> 2 OR Price2 > 0)
      AND (@value <> 3 OR Price3 > 0)
      AND (@ClientID = 0 OR MyClientID = @ClientID)

because all the OR's in that WHERE clause will probably mean that Indexes are not used efficiently, or at all, and there is risk that the very first instance that is run, which SQL will use to make & cache the query plan, is not representative of the "Norm", and then all subsequent queries will retrieve the Query Plan FROM CACHE, but that Query plan will be inappropriate and inefficient for the actual combination of parameter values.

(SQL is a bit smarter than that in practice

but that's the gist of it)

martind1
Starting Member

28 Posts

Posted - 2012-02-06 : 10:31:22

Thats some very helpful information.

However, the query is hardly ever the same. The query is passed with up to 25 different filters which can all be different combinations.
So would this have an effect on which ones is best to use?

Kristen
Test

22859 Posts

Posted - 2012-02-06 : 11:01:47

Couple of possible routes:

1) Do all the WHERE clause parameters dynamically. Yes you will have lots of cached query plans, and only the popular ones will be retained.

2) Only include the most effective columns in the dynamic part (those with highly selective indexes) and all the other Bits & Bobs include always using:


AND (@MyFlagParameter IS NULL OR MyFlagColumn = @MyFlagParameter)

type logic, so all queries have those columns, in identical format (so the query plan cache is HIT)

of I suppose 3) you could have a pre-pass where you gather the PK's into a temporary table, based on which parameters are used and which not (again, 80:20 using parameters that map onto the most selective indexes) and then JOIN that temporary table to the Main Tables with a complete WHERE clause with all parameters specified )(using the ... IS NULL OR ... logic above)

As with all such things some experimentation, with a stop-watch, which give you relevant performance stats. for your particular data set

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources