dcsimg
Results 1 to 7 of 7

Thread: [RESOLVED] search contition opinions

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,380

    Resolved [RESOLVED] search contition opinions

    Hello.

    I want to write a query that will have 2 conditions, '' and membershipid.
    if '' is found then a select * will be done, else it will select the membership id.

    So I'm doing something like this:

    Code:
    select * from blah 
    where something 
    ----
    and  MembershipID  like
    Case when @memberid = '' then '%'
    else @memberid
    end
    ---
    Firstly I think this will not be SQL injection prone as I'm using a parameter but just to be on the safe side I'm asking.
    Secondly, is this sargable? Using like, and if condition is met, then using '%' . I'm thinking this is like a select all and is sargable and not a 'like '%%' (which seems basically the same but not sure how the SQL engine understands that) but not 100% sure.
    Although testing with and without the like condition results to the same amount of time.
    Thirdly, is this a valid way? If this is ugly then I'm open to suggestions.

    Also another thought to use:
    and MembershipID like '' + @memberid + '%'
    Seems more simple but you are endangering to get more memberships, although not possible right now but a slim chance in the future if we expand the membership text length.

    BTW i don't see significant difference in execution plans, whatever I use. I'm not going to post as it's huge but, well it looks kinda the same.
    All index scan and stuff :P (not my database, cannot change the database indexing unfortunately)

    Thanks.
    Last edited by sapator; Oct 16th, 2018 at 03:33 AM.
    Slow as hell.

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,996

    Re: search contition opinions

    First the direct answers, then a bit more discussion

    1. No, this is not prone to injection. As long as you're not concatenating strings to produce the sql, injection is not an issue.
    2. No, it's not Sargable. As soon as a Where clause contains an OR (and a CASE is really just an alternative syntax for an OR) the query becomes non sargable because the query can't jump to a single block of records on the index. There's not much you can do about that with static sql though.
    3. Yes it's valid, depending on your main need - is it performance or maintainability. I generally aim for maintainability first then optimise for performance if I think it's necessary but the decisions always a bit more wooly than that.

    I would argue the simplest form for expressing optional search filters like this is:-
    Code:
    Where (@Parm1 = '' or Field1 like @Parm1 + '%')
    And (@Parm2 is null or Field2 = @Parm2)
    And (@Parm3 = 0 or Field3 = @Parms3)
    …
    This form is nice because it's very readable and can easily be applied across all your optional filters. However, it's not sargable and can perform quite badly in the wrong circumstances.

    A more performant approach is to have different code blocks depending on the parms that are passed in:-
    Code:
    If @Parm1 is not null
    And @Parm2 is null
    And @Parm3 is null
    	Select *
    	From MyTable
    	Where Field1 = @Parm1
    Else If @Parm1 is not null
    and @Parm2 is not null
    and @Parm3 is null
    	Select *
    	From MyTable
    	Where Field1 = @Parm1
    	And Field2 = @Parm2
    Else If …
    This is the safest and most performant approach but can be a maintenance nightmare as you need to maintain a separate block for every permutation. That 2^n blocks where n = the number of parms. For 3 parms it's 8 blocks. For 4 parms it's 16. That grows FAST.

    The third option is dynamic SQL:-
    Declare @SQL varchar(max)
    Declare @ParamList Varchar(max)

    Set @SQL = 'Select *
    From MyTable
    Where 1 = 1'

    If @Parm1 is not null
    Set @SQL = @SQL + ' AND Field1 = @QueryParm1'
    If @Parm2 is not null
    Set @SQL = @SQL + ' AND Field2 = @QueryParm2'
    If @Parm3 is not null
    Set @SQL = @SQL + ' AND Field3 = @QueryParm3'

    Set @ParamList = '@QueryParm1 varchar(50), @QueryParm2 Int, @QueryParm3 Date'

    Exec sp_executesql @SQL, @ParamList, @Parm1, @Parm2, @Parm3
    This will give you the same optimal performance as the separate code blocks but you need to get your head around how sp_executesql works with parameter lists. If you're going to use dynamic sql it's really important to follow this best practice because the "easy way out" or concatenating strings will leave you prone to injection. Using sp_executesql with a param list sanitises the inputs for you.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,380

    Re: search contition opinions

    Thanks Funky.
    The dynamic SQL and IF solution is currently out of the question as I'm involved in lot of urgent projects and I can't rewrite this and test.
    I was going for a simple add in so I can test the SQL before it goes live (with my membership id code) .
    This code is running through a .net solution so what I would currently do is either use the "like" or just hard code the Sproc for a single run and then turn it back to normal.
    But of course nice to know for future usage.

    A question here. You said that as soon as an or (case) is used it became non sargable.Seems logical.
    So when I use this: "and MembershipID like '' + @memberid + '%' " this does not use an OR operator, so am I to assume that it is sargable?

    Thanks.

    (can't rep so I owe you a coffee on your next visit here. It's 25 Celsius today btw )
    Slow as hell.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,996

    Re: search contition opinions

    Yes, that would be sargable… probably. See below

    Be careful with Likes and sargability:-
    1. They're only sargable if the wildcard is at the end of the literal string being compared. If it's in the middle or at the start it becomes non-sargable.
    2. If a user enters a wildcard in the string themselves it will be consumed as a wildcard. Worse, sp_executeSQL won't sanitise out wildcards (with good reason, it would make genuine wildcard searches impossible to code)

    So this: "and MembershipID like '' + @memberid + '%' " might look sargable because the wildcard is at the end. But if a user were to enter "ABC%DEF" the resultant SQL would be "and MembershipID like 'ABC%DEF%" and that's not sargable.

    So if you're going to allow "Like" searches you need to think about user input.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,380

    Re: search contition opinions

    Good to know.
    Does not apply in the aforementioned data but good to know nevertheless.

    Thanks.
    Slow as hell.

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,996

    Re: [RESOLVED] search contition opinions

    It's 25 Celsius today btw
    Over here it's raining
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,380

    Re: [RESOLVED] search contition opinions

    Quote Originally Posted by FunkyDexter View Post
    Over here it's raining
    Well, sorry about that.
    But you are rich we are poor, so

    This summer the weather was smooth to a nice 30-35 Degree and now, with the exception of one week, we are at 20 - 25 .
    Don't want to make you jealous, just luring you into vacation next summer , luuuuurre luuuurreee
    Slow as hell.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width