January 7, 2020 at 9:28 am
Hi,
I have a query that takes too much time to execute. Query and plan can be seen here: https://www.brentozar.com/pastetheplan/?id=SJWSzileL
I believe problem is in this part of the query:
CROSS APPLY (SELECT MAX(DATO) AS dato
FROM dbo.STDORD ST
WHERE DATO <= DS.CLARION_DATE AND ST.KUNDE = sto.KUNDE AND ST.ANN <> 1) CA
Any suggestions are welcome because I'm a bit stuck here.
Thanks in advance.
January 7, 2020 at 2:36 pm
It's estimating 1.9 million rows and moving 500k. That's my first concern, not the spool. The spool is in place so that it doesn't have to do that scan over and over. In theory, it's helping performance. The fact is, you're moving 46k rows with an estimate of 170k, all filtered at the end of the process. So you're scanning all your tables, moving all the data, then filtering at the end.
Let's see the predicate for the CALENDAR table:
datepart(weekday,[DBMCS_BK].[dbo].[CALENDAR].[SQL_DATE])=(2) OR datepart(weekday,[DBMCS_BK].[dbo].[CALENDAR].[SQL_DATE])=(4) OR datepart(weekday,[DBMCS_BK].[dbo].[CALENDAR].[SQL_DATE])=(5) OR datepart(weekday,[DBMCS_BK].[dbo].[CALENDAR].[SQL_DATE])=(6) OR datepart(weekday,[DBMCS_BK].[dbo].[CALENDAR].[SQL_DATE])=(7) OR datepart(weekday,[DBMCS_BK].[dbo].[CALENDAR].[SQL_DATE])=(1) OR datepart(weekday,[DBMCS_BK].[dbo].[CALENDAR].[SQL_DATE])=(3)
See all those functions on the columns? That's killing performance right there. You need to completely reassess what this query is doing and how it's doing it. Whatever function or view you're calling in the base query for the plan, it's got problems.
Let's take a look at another predicate:
[DBMCS_BK].[dbo].[STDORD].[TYPE] as [sto].[TYPE]=(1)
This results in a scan of 1.8k rows. How many rows in the table have a type of 1? How many types are there? Is there any other additional filtering criteria that can be applied so that we're not simply moving ALL the data?
Another:
[DBMCS_BK].[dbo].[DBVARE].[TYPE] as [var].[TYPE]<(5)
18k rows scanned. Again, how and what are you filtering things by. I suspect this pattern is running throughout. This is the root of the problem.
46k rows returned from an estimated 1.9 million row table is about 2%. That's somewhat high for index use, but not out of the realm of possibility. So, do you have indexes in support of these queries? Also, how up to date are your statistics? The misestimates are fairly high.
The issue isn't the index spool. This is one of those places where the estimated cost is misleading as to the root of the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2020 at 2:53 pm
Thanks for your advices, Grant. I will review the query and let you know.
January 7, 2020 at 7:20 pm
Well, I've modified CALENDAR table, adding a column for day of the week so I don't need to use the function. It improved the query but still I'm not happy with it. This is the new execution plan: https://www.brentozar.com/pastetheplan/?id=HJIeYLGxI
Statistics show this:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 55 ms, elapsed time = 55 ms.
Table 'DBVARE'. Scan count 9, logical reads 109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'STDORD'. Scan count 10, logical reads 195, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'STDORDRE'. Scan count 9, logical reads 664, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 568602, logical reads 1341468, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CALENDAR'. Scan count 8, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(46891 rows affected)
(1 row affected)
SQL Server Execution Times:
CPU time = 21300 ms, elapsed time = 5312 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
I believe problem is with this:
Table 'Worktable'. Scan count 568602, logical reads 1341468, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
How can I improve it?
Thanks again.
January 7, 2020 at 7:59 pm
This query seems to be spending most of its work trying to resolve the self join of dbo.stdord sto to dbo.stord st in ca subquery to do the filter. Is there really a need to hit that table twice? Maybe we need a better explanation of what this ca subquery is doing, and the business rules behind it.
January 7, 2020 at 8:05 pm
You're still looking at 2.4 million rows estimated and 46.8k actual. That's an enormous disparity. What other functions do you have in there? You also have filters that aren't doing any work. A seek that's returning 18k rows. Another returning an equally high number. Are the stats up to date? Are these indexes really working or are you just scanning the tables? The late filtering on STDORD.DAT0 is certainly an issue. Can you get a better index in place there? What's with all the WHERE not equal to an empty string stuff? That's also doubtless killing performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2020 at 8:22 pm
I didn't even notice it earlier, but the older version of the plan had a missing join predicate. The structure of this query is in dire straights.
How big are these tables. I'm trying to understand why the optimizer is choosing nested loops for 2 million rows. You would normally only see that if it's dealing with 200 million rows or more.
Are these two the same query? One plan has parameters. The other does not. I think you're changing more than one thing at a time.
According to the actual stats on the plans, only about 12 seconds are being spent down in the table spool. The first four operators account for over 20 seconds. 5.2 for the insert. 5.2 for the parallelism stream gathering. 5.3 for the late filter (where I would focus) and 5.2 for the Nested Loops join.
I'm still wondering about stats. The stream aggregate thinks it's processing 24.7 million and only processes 568k. Again, gigantic disparity.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2020 at 8:55 pm
New plan after updating statistics (exec sp_updatestats). https://www.brentozar.com/pastetheplan/?id=HydNy_zg8
There is a difference between both queries because in the new one I'm filtering the table CALENDAR to use only the range of dates defined with @FDATO and @TDATO. That's the only difference.
Tables are not too big:
SELECT COUNT(*) FROM dbo.STDORD 27653
SELECT COUNT(*) FROM dbo.STDORDRE 26578
SELECT COUNT(*) FROM dbo.DBVARE 18163
January 7, 2020 at 10:06 pm
These rowcounts are not too big, but when you take 17,738 rows (the main 3 tables in the query) and CROSS APPLY them to 728 rows (calendar table) then that's where your estimated rows starts getting all out of line with the actual rows. So why are you generating this cross product? Then you're taking that result and doing essentially a self join to determine the MAX(DATO) value by KUNDE, but computing it many many times more than necessary.
January 7, 2020 at 10:16 pm
I wonder if the query is really doing what you wish it to do or if there is a better way to do it.
first cross apply "explodes" the records - a filter then applies and then the second cross apply explodes the resulting records again before filtering for the max(dato).
Would you mind explaining in plain english what are you trying to do - e.g. what are the requirements for this query
January 8, 2020 at 1:11 am
If I were tuning this query. I'd take it apart. Get the root table and ensure that I'm pulling only the rows out of that in which I'm interested. Then slowly add back the joins and additional filtering, one at a time, again, ensuring that I'm getting the right rows. Also, that I'm getting good index use. The key, and I've repeated it over & over and I just don't think it's sinking in, is that we're not getting index use. We're scanning entire tables, bringing all the rows back, putting it all together in a giant heap, THEN, filtering some of the data out. That's pretty much the opposite of what we want. We want to filter the data on retrieval. Only move the rows we need and only when we need them. You're just moving almost everything. Is that what you want?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2020 at 5:56 am
Let me explain the reason of my code. The original SQL code (developed by another guy) was this one:
DECLARE @FDATO INT = 79961
DECLARE @TDATO INT = 80051
DECLARE @DATO INT
IF OBJECT_ID('tempdb..#STDORDDATO') IS NOT NULL
DROP TABLE #STDORDDATO
create table #STDORDDATO (Prec int, Dato int)
set @dato = @fdato
while @dato <= @tdato
begin
insert into #STDORDDATO (Prec, Dato)
select std.prec, @dato
from stdord sto
inner join STDORDRE std on std.KUNDE = sto.KUNDE and std.DATO = sto.DATO
inner join DBVARE var on var.NR = std.VARENR
CROSS APPLY (SELECT MAX(DATO) AS dato FROM STDORD
WHERE DATO <= @dato AND KUNDE = sto.KUNDE AND ISNULL(ANN, 0) <> 1) CA
where sto.dato = ca.dato and sto.TYPE = 1
and var.TYPE < 5
and (datepart(dw, dbo.MCS_ClarionDateToSQL(@dato)) = 2 and (ISNULL(STD.D1, '') <> '' or ISNULL(STD.BD1, '') <> '')
or datepart(dw, dbo.MCS_ClarionDateToSQL(@dato)) = 3 and (ISNULL(STD.D2, '') <> '' or ISNULL(STD.BD2, '') <> '')
or datepart(dw, dbo.MCS_ClarionDateToSQL(@dato)) = 4 and (ISNULL(STD.D3, '') <> '' or ISNULL(STD.BD3, '') <> '')
or datepart(dw, dbo.MCS_ClarionDateToSQL(@dato)) = 5 and (ISNULL(STD.D4, '') <> '' or ISNULL(STD.BD4, '') <> '')
or datepart(dw, dbo.MCS_ClarionDateToSQL(@dato)) = 6 and (ISNULL(STD.D5, '') <> '' or ISNULL(STD.BD5, '') <> '')
or datepart(dw, dbo.MCS_ClarionDateToSQL(@dato)) = 7 and (ISNULL(STD.D6, '') <> '' or ISNULL(STD.BD6, '') <> '')
or datepart(dw, dbo.MCS_ClarionDateToSQL(@dato)) = 1 and (ISNULL(STD.D7, '') <> '' or ISNULL(STD.BD7, '') <> ''))
set @dato = @dato + 1
END
I don't like that loop so I tried to replace it with the table CALENDAR and the CROSS APPLY. Results are the same but the new one is faster (which is reasonable because I've replaced those functions and I believe it's optimized).
Maybe my approach was not the best and there is an easy way to get the same but I can't see how to do it. As you can see, tables are not so big and result is about 47K rows in that range of dates so I'm sure it can be done much faster.
January 8, 2020 at 12:44 pm
Certainly, your intent is the right one. A loop is just a horrible way to code within SQL Server (depending, looping through tables for maintenance, looping through databases for backups, etc., normal stuff). However, you're not explaining the logic. What is with all the blank comparisons over and over? Those are going to help to kill performance. Do you really have lots and lots of empty strings in your database? If so, why on earth are you doing that? Also, if so, why try to filter them out. Only find things that match a value instead of trying to exclude things that have no value at all.
Also, 47k is pretty much all the data in those tables. Why are you moving all the data all the time? What purpose does that serve? Is that data replaced daily or something so that it's new every time? See the logic of this just isn't clicking with us. Understanding what we're attempting to do and why will make tuning the query easier.
By the way, every time I hear "I don't need to know the business. It's all 1s & 0s," it's this sort of thing that immediately comes to mind. In fact, yes, you do need to understand the business in order to arrive at a good database design, good queries, good constraints and all the rest. Anyhoo, end of rant.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2020 at 2:09 pm
Thanks again, Grant. I'm trying to collect more info, especially why those empty columns and the CROSS APPLY. I will get back soon.
January 8, 2020 at 7:27 pm
while we are curious to see if the code is indeed doing what is supposed to do (Business rules may be correct) I decided to give an attempt at it - untested and not sure if code even works as we weren't given DDL to create the required tables, neither sample data.
It still explodes the records but possibly with less impact.
and I do hope I got it right
please do come back to us with time differences between your code and this one as well as the explain plan
declare @Fdato int = 79961
declare @Tdato int = 80051
declare @Dato int
if object_id('tempdb..#STDORDDATO') is not null
drop table #STDORDDATO
create table #STDORDDATO
( Prec int
, Dato int
)
if object_id('tempdb..#temp_stdord') is not null
drop table #temp_stdord;
/*
create a table with all required entries from calendar table for the supplied period
joining to dbo.STDORD
one entry per date per KUNDE with the max dato for the group
*/
select ds.CLARION_DATE
, ds.SQL_DAYOFWEEK as DP
, st.KUNDE
, max(st.Dato) as Dato
into #temp_stdord
from dbo.STDORD st
cross join (select cl.CLARION_DATE
, cl.SQL_DAYOFWEEK
from dbo.Calendar cl
where cl.CLARION_DATE between @Fdato and @Tdato
and st.dato <= ds.CLARION_DATE
and st.ANN <> 1
) ds
group by ds.CLARION_DATE
, ds.SQL_DAYOFWEEK
, st.KUNDE
create clustered index #temp_stdord_ix1 on #temp_stdord
( KUNDE
, Dato
, DP
)
insert into #STDORDDATO (Prec
, Dato)
select std.Prec
, ds.CLARION_DATE
from dbo.stdord sto
inner join dbo.STDORDRE std
on std.KUNDE = sto.KUNDE
and std.dato = sto.dato
inner join dbo.DBVARE var
on var.NR = std.VARENR
inner join #temp_stdord ds
on ds.Dato = sto.Dato
and ds.KUNDE = st.KUNDE
and sto.TYPE = 1
and var.TYPE < 5
-- if std.Dx or std.BDx are null columns then the original code of isnull(xx, '') should be used assuming it was correct to do it that way
and (ds.DP = 2 and (std.D1 <> '' or std.BD1 <> '')
or ds.DP = 3 and (std.D2 <> '' or std.BD2 <> '')
or ds.DP = 4 and (std.D3 <> '' or std.BD3 <> '')
or ds.DP = 5 and (std.D4 <> '' or std.BD4 <> '')
or ds.DP = 6 and (std.D5 <> '' or std.BD5 <> '')
or ds.DP = 7 and (std.D6 <> '' or std.BD6 <> '')
or ds.DP = 1 and (std.D7 <> '' or std.BD7 <> '')
)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply