SQL Server Performance

Conditional Where???

Discussion in 'T-SQL Performance Tuning for Developers' started by cbtoolkit, Dec 22, 2004.

  1. cbtoolkit New Member

    I have been playing around with a few queries (based on my post a few days ago).

    However, how can I include or exclude - based on variables based to my stored proc, portions of a where clause...

    For instance:


    Dim @strvalue varchar(10)
    Set @strvalue = 'A'

    Select * from Table
    Where field1 > 0
    if Len(@strvalue) > 0
    and field2 = @strvalue


    Or something similar? I am searching various sites (but only posting here).

    Any help would be greatly appreciated.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  2. mmarovic Active Member

    Not sure what you want to accomplish, maybe:
    Dim @strvalue varchar(10)

    Set @strvalue = 'A'

    Select *
    from Table
    Where field1 > 0
    and (Len(@strValue) = 0 or field2 = @strvalue)
  3. cbtoolkit New Member

    I have several fields that I want to include or not include in my where clause.

    For instance:
    I have a field that determines if a record is active or inactive.
    I want to query for inactive records if one value is passed, active records if another value is passed, and not query on the field at all if another value is passed - in which case I get inactive and active records.

    This is true for multiple fields in my query.

    Another example:


    Declare var1 varchar(10), var2 money

    Set @var1 = 'GC'
    Set @var2 = 300

    Select * from vwCallList
    Where iscomplete <> -1
    --now I need to add additional where clause statements based on whether those variables have a value
    if Len(@var1) > 0
    CHARINDEX(@var1,'STATUSCODES') > 0

    if @var2 > 0
    And amount >= @var2


    I don't need to query on the status codes field unless they are looking for those codes. I don't need to check the dollar amount unless they are looking for the dollar amount.

    How can I do this?

    [/code]


    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  4. Twan New Member

    Hi Matthew,

    yeah you can use the AND and OR operators to have multiple where clauses. You do want to be careful about performance though since procedures have their query execution plan worked out based on the first call to that proc.

    BUT you can do something like



    Declare
    var1 varchar(10),
    var2 money

    Set @var1 = 'GC'
    Set @var2 = 300

    Select *
    from vwCallList
    Where iscomplete <> -1
    and ( Len(@var1) = 0 OR CHARINDEX(@var1,'STATUSCODES') > 0 )
    and ( @var2 = 0 OR amount >= @var2 )


    Cheers
    Twan
  5. cbtoolkit New Member

    I'm confused by the syntax you show???

    How is that being read..
    and ( Len(@var1) > 0 OR CHARINDEX(@var1,'STATUSCODES') > 0 )

    For instance, if my @var1 has not value or is zero length '' versus being set to 'GC'??

    Thanks.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  6. mmarovic Active Member

    I guess you mean:
    Declare var1 varchar(10),
    var2 money
    --
    Set @var1 = 'GC'
    Set @var2 = 300
    --
    Select * from vwCallList
    Where iscomplete <> -1
    and ( Len(@var1) = 0 OR CHARINDEX(@var1,'STATUSCODES') > 0 )
    and ( @var2 <= 0 OR amount >= @var2 )
  7. RGKN New Member

    You could also use a case statement

    e.g.


    Declare var1 varchar(10), var2 money

    Set @var1 = 'GC'
    Set @var2 = 300

    Select * from vwCallList
    Where iscomplete <> -1
    AND amount >= (CASE when @var2 is null
    then amount
    else @var2
    end)

    The variable @var2 can be set to default to NULL and it will only be compared if it is not null.

    Regards,

    Robert.

    PS Haven't used it before and don't know the performance overheads involved.

  8. cbtoolkit New Member

    Mondo scratches his head???

    Uh, does that mean my where clause basically includes an and clause with a true statement, not related to any field, or if that statement is not true, uses the field evaluation statement in the OR part of the field?

    Something like that?

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  9. mmarovic Active Member

    Short answer: Yes.

    But, as Twan mentioned, you could have performance problems. If your variables are parameters you may have frequent (re)compilations. Otherwise your query may use sub optimal execution plan.
  10. RGKN New Member

    You'll also need to deal with NULL values of amount as they will be excluded if you don't

    something like ISNULL(amount, 0) instead of amount will fix it
    e.g.

    Declare var1 varchar(10), var2 money

    Set @var1 = 'GC'
    Set @var2 = 300

    Select * from vwCallList
    Where iscomplete <> -1
    AND ISNULL(amount, 0) >= (CASE when @var2 is null
    then ISNULL(amount, 0)
    else @var2
    end)


    Regards,

    Robert.
  11. cbtoolkit New Member

    What is the preferred method to get around performance issues in this case? Or to be more precise, short of a stored proc for each iteration of possible variables from the client, is there something I am missing?

    Also, re: nulls. I will be ensuring values passed to the stored procedure are not null. I will have to look at DB field values..

    Thanks.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  12. Twan New Member

    Hi Matthew,

    it depends on a lot of things like the type of data in the table, the frequency of queries, the difference between optimal index plan and the one used by a generic all encompassing statement.

    what is often done is to start with the generic procedure encompassing all of the possible rules and then for those cases that clients use a lot (or which are terrible for performance) have a more optimal statement and then use an if...else within the proc to specifically check for the case you're trying to optimise

    Cheers
    Twan

  13. cbtoolkit New Member

    Thanks....

    I'll give everyone an idea of the applications function and this particular query...

    About 50 users make calls to delinquent accounts. The users are placed in call groups that determine which type of account they call on. Data like, status codes, dollar amounts, days delinquent, specific lines of business, whether the account is active or inactive, etc. are specified in a call group table.

    The users open the application immediately to a call queue. The call queue pulls a record based on the data in the user's call group. This is where it gets dynamic.

    If one user has a call group that does not specify any line of business (a text field), that field should not be used to limit the query. If there is a line of business specified, it will limit the results based on that field - hence the need to add it to the query.

    Or...
    one call group may specify that they call inactive accounts with a dollar amount that is 120 days past due. Another group may specify that they call any account with a dollar amount that is 90 days past due (but this would need to exclude any accounts with a 120 balance).

    Each user is likely to sit on a given record for 2+ minutes, so the system is not transaction heavy. I don't believe performance of the query should be an issue but want to know if I am going about this the right way.

    I have to do this in a stored proc transaction to ensure that each user gets a unique record/account to call - this is working - and must now pass the other paramters to filter the list by call group.

    The administrator has the ability to add/edit call group paramters and the user will requery their call group parameters every time they move to a new record. This is needed to allow the admin to focus users to a specific line of business or regional area (another field I capture).

    I hope this wasn't a muddy explanation as to why I am looking to do what I am doing.

    Thanks for everyone's help.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  14. cbtoolkit New Member

    I just tried the following:



    Declare @st1 varchar(3)
    Set @st1 = ''
    Select * from vwCallList
    Where
    iscomplete <> -1
    and (Len(@st1)>0 or CharIndex(@st1,STAALL)>0)


    If I run the query as is, zero records returned (not what I want).
    If I run the query setting the @st1 variable to 'GC' - a valid code in the field STAALL, I get the appropriate records.

    When @st1 is '' a zero length string, I need it to not use the @st1 variable or STAALL fields at all. In affect, if @st1 is '' I should get all the records where iscomplete <> -1

    How is this done.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  15. Twan New Member

    ah oops yes it should be


    and (Lan(@st1) = 0 OR CharIndex( @st1, STAALL ) > 0 )


    sorry I mutalated your code without then checking back that it still does what you set out to do... I'll correct my original post now

    in terms of the general guideline, it really depends on performance versus maintainability. For my own guidlines if there are few distinct cases of various parameter combinations being passed then I'd create a proc for each parameter combination. If there are lots then I'd start with a generic statement and optimise the often used cases/worst performers.

    It is really more a trial and error approach rather than hard and fast rules... From the front-end app I'd try to still have a single calling interface so that if I change my mind later on then I can make changes to the procs without changing the calling app

    Cheers
    Twan
  16. cbtoolkit New Member

    Oops. In fact, the above does not work when I set @st1 to 'GC'. When I do so, the above returns records where any data is in the STAALL field, not just those with 'GC'

    It should be:
    and (Len(@st1)=0 or CharIndex(@st1,STAALL)>0)

    In this case, if @st1 is zero length the CharIndex statement becomes irrelevent. If @st1 is greater than zero length, the CharIndex statement is evaluated properly...

    ...testing...testing - report back shortly.

    Matthew Moran
    The IT Career Builder's Toolkit
    http://www.cbtoolkit.com
  17. cbtoolkit New Member

    Okay, we get more complicated....<br /><br />I have several status codes that could be used in the query. They are setup as:<br />@st1-@st4<br /><br />And so I have created the following..and it works...<br />and (Len(@st1)=0 or (CharIndex(@st1,STAALL)+CharIndex(@st2,STAALL)+CharIndex(@st3,STAALL)+CharIndex(@st4,STAALL)&gt;0))<br /><br />I ensure on the form where they setup call group parameters that if any codes are specified (they have four fields), the the first field is populated. This is the paramter passed for @st1 and so evaluating the length of that parameters is all I need. Then adding the CharIndex of the others works to ensure that any of the four is then included in the resulting data.<br /><br />Thanks everyone. I think that does it until it doesn't. <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Matthew Moran<br />The IT Career Builder's Toolkit<br /<a target="_blank" href=http://www.cbtoolkit.com>http://www.cbtoolkit.com</a>
  18. mmarovic Active Member

    quote:Originally posted by Twan

    ah oops yes it should be


    and (Lan(@st1) = 0 OR CharIndex( @st1, STAALL ) > 0 )
    I see I have to keep quoting to avoid confusion. I posted my query just to fix this part of yours.
  19. Hartmut5 New Member

    If this is all in a stored procedure and you want to keep it simple and easily maintainable, you could do conditional execution of separate select statements. But the extent that this works and is simple depends on the number of parameters that are optional. Conditional queries could also result in much better performance if you can avoid a CHARINDEX operation where it is irrelevant. The tradeoffs here are between compile time, execution time, and maintenance time. An example is:

    if @st1 is null or len(@st1) = 0
    begin
    -- Ignore @st1 parameter
    Select * from vwCallList
    Where iscomplete <> -1
    end
    else
    begin
    -- Use @st1 parameter
    Select * from vwCallList
    Where iscomplete <> -1
    and (CharIndex(@st1,STAALL)+CharIndex(@st2,STAALL)+CharIndex(@st3,STAALL)+CharIndex(@st4,STAALL)>0)
    end


    -Hartmut5

Share This Page