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.
| Author |
Topic |
|
nmg196
Yak Posting Veteran
70 Posts |
 Posted - 2009-06-03 : 13:40:52
|
Hi,I was analysing why one of our queries seemed a little slow and have got a bit stuck. The behaviour seems rather weird!The search has a lot of "optional" parameters, and by that, I mean that if I pass in null, they effectively have no effect:SELECT VenueNameFROM dbo.VenueWHERE (@VenueName IS NULL OR CONTAINS(Venue.*, @VenueName))AND (@VenueID IS NULL OR Venue.VenueID = @VenueID) -- etc However I noticed that if I remove the bit in bold, the query speed improves dramatically (from 120ms down to 30ms). Why would this be? The checking of @Venuename is designed to be an optimisation which should speed it up rather than slowing it down. The example above is simplified and there are actually 20 or so parameters which work this way. I have noticed though, that only the first line seems to cause this problem, and using that method in further clauses does not cause any additional slowdown. Perhaps it's because the first line uses the CONTAINS keyword? Was I correct in presuming that SQL Server will not bother evaluating the right hand side of an OR if the left hand side is already true?Any clues as to why this happening would be appreciated.Nick... |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-06-03 : 14:01:24
|
| compare the query plan for both versions and I'm sure you will see that the execution plan changes. Some boolean logic, especially ORs, can result in an index not being used. I'd suggest a construct like this:...AND venue.Venueid = isNull(@VenueID, venue.Venueid)...EDIT:I just noticed the CONTAINS part. That is probably what is doing it. I don't have experience using that key word but I see that you're using the "*" argument. That is searching every column in the table registered for full text searching. Are there many columns registered? Be One with the OptimizerTG |
 |
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2009-06-03 : 14:39:31
|
| The actual CONTAINS keyword specifies the two columns to be searched. I only changed it to * to keep the example simple. Regarding your example:AND venue.Venueid = isNull(@VenueID, venue.Venueid)I'm not sure this has the same effect. In my example it is comparing the parameter to null, to decide what to use it for. If I'm reading it correctly, your example will check the actual data to see if it's null which is not my aim. What I was trying to do was to avoid using dymamic SQL by making parts of the where clause have no effect when you passed in null, so effectively they become optional.I'll check the exec plan when I'm back at the office and see if I can spot any weirdness, but to be honest I rarely understand execution plans enough for them to help me fix any problems :) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-06-03 : 15:12:46
|
| >>If I'm reading it correctly, your example will check the actual data to see if it's null which is not my aim.my example is logically the same as yours. If the variable is null then the data value is just compared to itself. If the variable is not null then the data value is compared to the variable. In earlier versions of sql this helped the optimizer continue to use any appropriate index. That deficiency may have been resolved in more recent versions. I just still use that technique because it still works :)Even with 2 columns in your CONTAINS clause that is still equivalent to:(col1 like '%' + @v + '%' OR col2 like '%' + @v + '%') I'm not too familiar with full text searching but that probably still pretty expensive.Be One with the OptimizerTG |
|
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2009-06-03 : 15:25:48
|
| I'm not too worried about the expense of the CONTAINS bit as I really doubt that's the problem here. There are only 7000 rows in the table and searching it using CONTAINS on it's own takes only about 10ms, so it's the combining the CONTAINS with the NULL check that seems to be the cause of the prob. I think you're probably right on the execution plan varying so I'll check that tomorrow and try your suggestion. I'm also having to use WITH RECOMPILE on the stored proc as if I leave that off, my query will randomly start taking 20 seconds (yes SECONDS) to run every now and then unless I go in and save it out again (no changes). Since I put WITH RECOMPILE in, I haven't seen any 20 second queries, but I like all my main website queries to run in less than 100ms :) |
|
|
|
nmg196
Yak Posting Veteran
70 Posts |
Posted - 2009-06-04 : 04:21:24
|
| > AND venue.Venueid = isNull(@VenueID, venue.Venueid)That seems to work OK on int fields, but on varchar's it's even slower :( |
|
|
|
|
|
|
|
|
Really odd query speed problem!