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 |
|
JamesT
Yak Posting Veteran
97 Posts |
 Posted - 2002-12-13 : 11:04:34
|
| First, sorry for the lengthy overview. I work at a multi-level marketing company where people purchase products, sign others to purchase products, get credit/money for their purchases and those that they sign up. I have some rather complex queries that conduct calculations to determine various numbers that determine how much each individual will get paid among other things. The calculations often need to take into account multiple reporting periods (e.g., months) and so have to hit large (relatively) tables for historical data. In the calculations we may or may not be calculating for an individual (for re-calculation purposes) or for an entire country, or globally. Because of that we have several instances where we check to see if a value passed into the calculation stored procedure is null and if so replace it with a table reference. For example: IsNull(@Customer_ID, customer_table.customer_id). As mentioned above, this is done for country, customer, etc. Here is the problem: In the where clause if we leave the IsNull(@customer_id, customer_table.customer_id) clause in and run it for ONE customer it takes an average of 15 to 20 minutes. If we hard code in the variable and take out the IsNull it takes sub-second. Of the joins made there are just four tables that have what would be considered "large" tables and they have about 2, 5, 7, and 8 million rows. Nothing major. Of course the joins and where clause all have indexes and in the execution path there are no table scans and so on. When the IsNull is left in the execution path shows a clustered index scan. When it is taken out it is an index seek. What I did to resolve this was to set a variable equal to the SQL statement and evaluate the variables with IsNull prior to executing it statement thereby replacing the variables with actual values. I know this will remove any optimization capabilities from SQL Server but I have, in a sense, done the optimization myself before hand. Our DBA informed us that this is not, as Martha Soon-to-be-jail-bird Stewart would say is "A Good Thing." Question: Is this behavior characteristic of IsNull? Second, is it safe to say that NO functions should be used in the where clause or joins? And third, is using the dynamic SQL a good solution for something like this with all things being constant in the SQL statement such as table size, where criteria, etc? Is that vague?Again, sorry for the length but I don't believe that I can convey the issue without first going James Joyce on this and doing a brain dump.Thanks!Rev. James Tow |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-12-13 : 12:41:40
|
| I read through your post and one thing that caught my eye was, how you are testing the where condition.I don't think that the "ISNULL" is the right one for this use. I would suggest that you try "expression IS NULL". ISNULL is very specific and lots of VB programmers often use it like the same function in VB, although it has a completely different meaning (functionality).Lookup ISNULL and NULL VALUES in books online... |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-12-13 : 14:39:00
|
| Using a function like ISNULL() around a field in a WHERE clause means SQL needs to get all of the values in the fields needed to evaluate the expression for ALL records before it can evaluate the WHERE to filter records.Ex:WHERE Right(Customer,4) = '1234'SQL has to get ALL of the records, evaluate Right(Customer,4) and then filter. It cannot use an index because it can only look up values by how they are sorted, which has nothing to do with the right 4 characters in the field.Using ISNULL(a,b) causes the same problem.To make a long story short, instead of:SELECT * FROMCustomersWHERE ISNULL(@CustomerID,CustomerID) = blah blahyou are better off doing:SELECT * FROMCustomersWHERE CustomerID = @CustomerID OR@CustomerID is nullor something along those lines (not sure of exactly what you criteria needs to be).If you can post you SELECT statement and some information, I'd be happy to optimize it.Basically, yes, try to avoid expressions in WHERE clauses UNLESS you can filter the records down quite a bit first using indexes.- Jeff |
|
|
|
|
|
|
|
|
Is IsNull the culprit or am I just a buffoon?