Microsoft SQL Server: wrong query execution plan taking too long

Question

This is on Windows SQL Server Cluster.

Query is coming from 3rd party application so I can not modify the query permanently.

Query is:

DECLARE @FromBrCode INT = 1001
DECLARE @ToBrCode INT = 1637
DECLARE @Cdate DATE = '31-mar-2017'

SELECT 
    a.PrdCd, a.Name, SUM(b.Balance4) as Balance
FROM 
    D009021 a, D010014 b
WHERE 
    a.PrdCd = LTRIM(RTRIM(SUBSTRING(b.PrdAcctId, 1, 8)))
    AND substring(b.PrdAcctId, 9, 24) = '000000000000000000000000'
    AND a.LBrCode = b.LBrCode
    AND a.LBrCode BETWEEN @FromBrCode AND @ToBrCode
    AND b.CblDate = (SELECT MAX(c.CblDate) 
                     FROM D010014 c
                     WHERE c.PrdAcctId = b.PrdAcctId
                       AND c.LBrCode = b.LBrCode
                       AND c.CblDate <= @Cdate)
GROUP BY 
    a.PrdCd, a.Name
HAVING 
    SUM(b.Balance4) <> 0
ORDER BY 
    a.PrdCd

This particular query is taking too much time to complete execution. The same problem happens on a different SQL Server.

No table lock was found, processor and memory usage is normal while the query is running.

Normal "select top 1000" working and showing output instantly in both tables (D009021, D010014)

Reindex and rebuild / update stats done in both tables but problem did not resolve (D009021, D010014)

The same query is working if we reduce number of branch but slowly

  (
    DECLARE  @FromBrCode INT =1001
    DECLARE @ToBrCode INT =1001
    )

The same query is working faster giving output within 2 mins if we replace any one variable and use the value directly

 AND a.LBrCode BETWEEN @FromBrCode AND @ToBrCode

changed to

AND a.LBrCode BETWEEN 1001 AND @ToBrCode

The same query is working faster and giving output within 2 mins if we add "OPTION (RECOMPILE)" at end

I tried to clean cache query execution plan and optimized new one but problem still exists

Found that the query estimate plan and actual execution plan are different (see screenshots)

Why don't you just make `OPTION (RECOMPILE)` permanent? There's nothing wrong with that for queries that might see parameter values with widely varying selectivity. — Joel Coehoorn, Apr 07 '17 at 01:53
Also, when was the last time you rebuilt statistics? Or is that what you mean by the "rebuild" in step 5? — Joel Coehoorn, Apr 07 '17 at 02:03
that query is coming from another application i cant modify that . rebuilding / updating statistics did not solved the issue — Sumanta, Apr 07 '17 at 02:22
Could it be a parameter sniffing problem? see this question. http://stackoverflow.com/questions/30061405/parameter-sniffing — AlwaysData, Apr 07 '17 at 02:47
Tell your third-party that sells that software: [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**25 years** ago) and its use is discouraged — marc_s, Apr 07 '17 at 05:32

score 0 · Answer 1 · answered Apr 07 '17 at 16:23

Table D010014 is aliased twice once as b and once as c the they are joined to the same table. Try toto remove the sub query below and create a temp table to store the values you need. I added * to the fields you self join

SELECT MAX(c.CblDate) FROM D010014 c WHERE c.PrdAcctId = b.PrdAcctId AND c.LBrCode = b.LBrCode AND c.CblDate <= @Cdate

if you cant do that then try

SELECT TOP 1 c.CblDate 
                     FROM D010014 c
                     WHERE c.PrdAcctId = b.PrdAcctId
                       AND c.LBrCode = b.LBrCode
                       AND c.CblDate <= @Cdate
                       ORDER BY c.CblDate DESC

thanks but as i said earlier "Query is coming from 3rd party application so I can not modify the query permanently." — Sumanta, Apr 09 '17 at 01:19
Are you allowed to partition the indexes on the table, or to create a new index? If not playing with hints like KEEP PLAN or KEEPFIXED PLAN might at least get to estimated and actual plan closer. — Cooker, Apr 09 '17 at 10:54

Microsoft SQL Server: wrong query execution plan taking too long

1 Answers1