-7

I just received a question from one of the developers and the question is, he ran a SQL statement with a where clause and when he puts 004 the SQL finished very fast, and on the same SQL when he puts 015 in a where clause it took very long time. any idea? Here is the part of the SQL with the where clause:

tax_type_code='015' (very slow)

tax_type_code='004' (very fast)

Improve this question
4
  • 4
    We need more information. Complete SQL for example. Also table description and indexes. – Marco Sep 10 '15 at 13:17
  • 2
    And why these are typed into the query instead of using proper parameters... – Aaron Bertrand Sep 10 '15 at 13:20
  • 2
    Well, just think of the hypothetical case where you have a table indexed on tax_type_code with a billion records, of which just one has tax_type_code of 004 while all the rest have 015. – Colin 't Hart Sep 10 '15 at 13:25
  • 2
    Or another hypothetical case where the column is not indexed, the query has TOP (1) and there are a lot of rows with 004 and none with 015. Both executions will scan the table. the 004 case will find a row, rather quickly, the 015 case will have to go through the whole table. So without the whole query and many other things (execution plans, tables structure, what DBMS you are using), it's not possible to answer the question. – ypercubeᵀᴹ Sep 10 '15 at 13:32
1

You have pointed out something that is pretty random in where clauses and is based on a query optimization problem when you use non-parameter predicates. This result can be cause by the following:

1) the query where the where clause is tax_type_code='004' may already have been parsed in the query cache or pool and therefore runs much faster because it's hash value matches the the same query but is different with a '015'

2) A scan of the index may return '004' faster because the optimizer more information and has decided to use the index instead of scanning the object itself. Typically, scanning the index is faster depending on the cardinality of the data.

3) The reasons may be as basic as a change in the execution plan.

So the point I'm making is, you will not know the reasons why unless you look at the query execution plan, make sure that the latest statistics are collected on the objects and check for matching hash values in the plan cache/shared pool for that query.

AND....

Make sure you alway...

1) Use bind variable/parameterization 2) Make sure your indexes/tables have the latest statistics collected. 3) Check the execution plan to make sure the most optimal plan is being use. 4) Use predicates that have columns in unique and key indexes for normalized tables.

Improve this answer
2
  • Even though it's hard to tell for sure without any details about the system in general and the query in particular , it appears to me that (1) is very unlikely ; parsing time itself won't be noticeable by end user if he issues just couple statements ; – a1ex07 Sep 10 '15 at 15:10
  • Agreed. Good point. Add in a bunch more statements running simultaneously with more complexities and having to reload them all with different values each time with no cursor sharing, you may take a performance hit. – Data Flux Sep 10 '15 at 18:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.