2

I have the following SQL query:

SELECT top 100 Id
FROM TestTable
where 
    (Code like 'a000' + '%' or contains(Desc , 'a000*' )  )
    AND  (Active!='N' or Active is null)         
    AND  substring(Code,1,1) in ('1','2','3','4','5','6','7','8','9','0')
    ORDER BY Code

enter image description here

It takes almost 70 ms to run but I have set the active to 'N' when the first character of code is not numeric, so the last where clause is unnecessary but after removing that the following query takes 6 seconds to run!

SELECT top 100 Id
FROM TestTable
where 
    (Code like 'a000' + '%' or contains(Desc , 'a000*' )  )
    AND  (Active!='N' or Active is null)         
    ORDER BY Code

I also run this query with database engine tuning advisor but it doesn't have any recomandation. enter image description here

rezord
  • 53
  • 6
  • 1
    I could see both queries are same..am i missing some thing – TheGameiswar Apr 24 '18 at 17:53
  • Try adding execution plans as xml for two queries(slow and fast) as xml – TheGameiswar Apr 24 '18 at 17:53
  • Questions seeking performance help should include DDL,DML Of the tables involved along with test data..if your test data is large,try scripting out schema and stats for the table(right click database->generate scripts->select specific database objects->in next screen select advanced and choose Script statistics) and paste it in question..With this info any one repro the same issue you are facing.Otherwise it becomes very difficult to answer your question .Pasting server version also helps – TheGameiswar Apr 24 '18 at 17:55
  • Look at the plan (click the "show estimated plan" button in SQL Server Management Studio). Once for each query (each in a separate window). Compare the plans. It should be pretty obvious what the difference is. – pmbAustin Apr 24 '18 at 17:56
  • Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor) – rezord Apr 24 '18 at 17:57
  • It shows Index Scan (NonClustered) Cost: 86% in slow query but in fast one it is 12% – rezord Apr 24 '18 at 18:08
  • Have you tried flipping around the order of the where clauses? It may seem silly, but it's possible that the order of execution is coming into play here, especially if there's a large number of records. – user2366842 Apr 24 '18 at 19:03
  • 1
    Wild guess `substring(Code,1,1) in ('1','2','3','4','5','6','7','8','9','0')` causes full scan (nonSARGable condition) + **parallel execution**, while second query is single threaded. Please post actual execution plan for both. – Lukasz Szozda Apr 24 '18 at 19:03
  • I have added the execution plan – rezord Apr 24 '18 at 19:55
  • @lad2025 I have posted the execution plan, Have you had a chance to take look? Thank you – rezord Apr 25 '18 at 17:47
  • 1
    @rezord Ok, I see that `SORT` is expensive. Could you remove `ORDER BY` from both queries and compare performance of both? – Lukasz Szozda Apr 25 '18 at 17:51
  • @lad2025 I did, the same story! – rezord Apr 25 '18 at 17:52

1 Answers1

0

Hmm,serious database design issue if you have to query by substring of a field. However, if the total number of records which meet the first criteria is not too large, you could use a temporary table:
SELECT ID,code into #Testemp FROM TestTable where (Code like 'a000' + '%' or contains(Desc , 'a000*' ) ) AND (Active!='N' or Active is null) ORDER BY Code --Then:
SELECT top 100 ID from #testemp where substring(Code,1,1) in ('1','2','3','4','5','6','7','8','9','0') order by Code

Clark Vera
  • 183
  • 1
  • 7
  • Both generate the same size result and I want to remove the extra unnecessary condition from my where clause – rezord Apr 24 '18 at 18:22
  • I think that's kinda what he was trying to say, he shouldn't HAVE to query by a substring of a field. It's not in fact a db design flaw. – user2366842 Apr 24 '18 at 18:42
  • But with substring, it is faster and this is the strange thing! – rezord Apr 24 '18 at 19:01