Forum Discussion

joshAU's avatar
joshAU
Copper Contributor
Mar 29, 2023

SQL queries have become extremely slow compared to nearly identical queries on the same tables

Hi all.
I've been drafting a SQL query from within MS SSMS, as a .SQL file, and then running it from within SSMS, on the same system that is running the 2019 developer edition server.

The query file contains around 100 or so queries, virtually all UPDATE queries, which populate rows in a SUMMARYTABLE with results taken from the WORKTABLE.

I've noticed that recently, the last 10% or so of these queries have become exceptionally slow - around 500 times slower than comparable queries.
Here is an example of one of the fast ones, and one of the slow ones.
The first takes less than a second:

UPDATE database1.dbo.SUMMARYTABLE SET database1.dbo.SUMMARYTABLE.SalesTierCount = (select count(*) from database1.dbo.WORKTABLE as T1

WHERE T1.Manager = database1.dbo.SUMMARYTABLE.Manager and T1.SalesTier =1 and T1.Zdate < database1.dbo.SUMMARYTABLE.Zdate)

the second one takes about seven minutes:

UPDATE database1.dbo.SUMMARYTABLE SET database1.dbo.SUMMARYTABLE.LifetimeCount  = (select count(*) from database1.dbo.WORKTABLE as T1

WHERE T1.retailer = database1.dbo.SUMMARYTABLE.retailer and T1.SalesTier =1 and T1.Zdate < database1.dbo.SUMMARYTABLE.Zdate)

 Does anyone have any suggestions for rectifying this extremely slow issue?
2 of the 3 WHERE/AND clauses are identical.
The third clause differs, however both queries query the same type of data for that clause - both are varchar(50), in both tables.
This is a link to the execution plan for both queries:
Execution plan 
The final part of both plans shows an INDEX SCAN (non-clustered).
The first, fast query, has the "Number of rows read" of around 200,000, the second, around 436 million.
The total time to run the entire .SQL query has  blown out from a minute or so, to nearly 4 hours!
Any suggestions greatly appreciated.


MS SQL
Performance Issues
Query
SQL 2019
    • joshAU's avatar
      joshAU
      Copper Contributor
      Mar 29, 2023

      Hiolafhelper 
      Thanks for your advice, and sorry if some of my question sounded vague.

      I did not actually say "similar queries" or say "may be", that you are quoting me as saying.

      I did say the "query file contains around 100 or so queries", and "the last 10% or so".
      I did not feel the exact number of queries was relevant to the question, as I was not concerned about the duration of ALL queries, but rather the duration of a few specific queries.

      I did actually include both execution plans in the original question, via the link I posted to the brentozar webpage here: Execution plan 

      I have just realised that the slowness only occurs with the final AND clause:

      and T1.Zdate < database1.dbo.SUMMARYTABLE.Zdate


      If I remove that, or manually type in a static date, eg. '2023-12-01', instead of the SUMMARYTABLE.Zdate, the query finishes in a second or so.

Resources