I have a query, which I have to run each month for statistical data on our customers. This query exists in three variants - from biggest, looking at greatest numbers of parameters (17) to smallest, calculating just a couple (that is: 2) - and code is very similar. Report itself is a cohort-style (for us not familiar with statistical work: it looks at the customers "journey with us through time"), so while it starts small, every month it's getting closer to today it gets bigger (because as we grow we have more and more customers)
Generally every query follows this schema:
- prepare environment and get initial data (create calendar temp table and fill it, create temp tables for storing data that will be processed further, temp tables with customer data, custom indexes are added)
- loop through each month pulling data from multiple tables, transforming it and storing in temp table (call it:
#loop_month). at the end of each loop data from that table is inserted into another temp table (call it
#loop) and loop_month is dropped
- after the loop ends we have a table that holds aggregated data for each customer for each month. This means that for example two years' worth report will yield a number of rows equal to number of customers multiplied by number of months they are our customer. Biggest report crunches 15 years worth of data, so it easy to see that datasets can be huge, as in the meantime we're looking at each order he made in great detail.
- there is further transformation of data, two big
#loopand then it all is stored in the physical table that's used to run select queries and as a source for Excel connections
- all temp tables are dropped
- the end
It's a report, so I'm not running it in transaction.
This month one of the queries (the smallest, simplest one) cannot be completed. Before (last month), the heaviest version was taking about an hour, today the lightest one was terminated after 15 hours without finishing.
Since I cannot share the code - not to brag, but it is something we see is giving us an edge over competition, so it is a burn-before-reading secret - I know that this will be close to shooting in the dark. But maybe someone will have NVG?
For example: since it has trouble processing last three months, which will be a huge amount of data compared to earlier periods, maybe MSSQL has trouble holding on to temp tables holding few bilion rows (at some point there will be very bad row duplication from several joins, which I "fix" by making sure I'm selecting only needed columns in
select distinct command). How about indexing that big temp table? Is it a big temp table at all (one holding 1B rows of 65B each)? Is it cached on disk, or held exclusively in RAM? How about two temp tables like that?
SQL instance is on a pretty impressive hardware, with 170GB of RAM currently exclusively assigned to it (there are 8 more SQL instances on that server at the moment, most of them idle, rest of them not under any significant load). So we can exclude lack of processing power.
Database is in simple recovery mode and at this time it's less than 50GB in size, of which 37GB is index file, which was not shrunk from the time DB was in Full recovery mode (we don't have a DBA, so there is some junk lying around).
Query is quite long, so if option the execution plan is enabled at some point I will start mscorlib exception will be thrown. As far as the contents of the plan goes, after I finally stop the query in between of dismissing the error message I've implemented some of the recommendations there regarding indexes. In general, I've optimized the query as much as I could (indexes where needed, removing indexes where not needed, i.e. inserts etc), and it's doesn't feel it's performance issue as in "bad code". I looks like technical.
After tearing into the code and isolating the fragment that's offending I found that this fragment of code:
;with cte1 as (select distinct p.id, CONVERT(int,ROUND(DATEDIFF(hour,p.[Date1], p.date2)/8766.0,0)) g, left(re.re,4) p_code from d join p on p.id = d.ID join re on d.ID = re.ID ), cte2 as (select distinct id ,case when g < 20 then '< 20' when g >19 and g <30 then '20-29' when g >29 and g <40 then '30-39'g when g >39 and g <50 then '40-49' when g >49 and g <60 then '50-59' when g >59 and g <70 then '60-69' when g >69 and g <80 then '70-79' when g >79 and g <90 then '80-89' when g >89 and g <100 then '90-99' when g >99 then '100+' end "gg" from cte1 ) update #da set g = cte1.g, gg = cte2.gg, r = cte1.p_code FROM cte1 join cte2 on cte1.ID = cte2.ID where cte1.ID not in (111,222,333,44) and #da.id = cte1.ID
simply stops working properly when there are 4 iterations left for
while loop to go through (
CTE1 yields 52k rows,
CTE2 produces 17k rows).
What threw me off was that for preceding 29 iterations it was working as expected.
When I converted both
#temptables the problem went away and the report as a whole takes 40% less time to execute than before. Nothing else was changed, no intervention in data, no change in predicates.
I don't know what's going on and I don't think I can invest any more time into investigating with a backlog this already created. Has anyone had experienced something similar?