July 3, 2019 at 8:36 am
Hi all,
I apologise if my initial question is missing a lot of crucial information, but that is because a) I'm a self-taught accidental dba, and b) I'm not sure where to begin.
My issue is as follows. We have a database set up to specifically serve a reporting application (not Reporting Services, just a large data store to run queries against).
Now, if I try and run a report for a whole month, the query runs for hours and I end up cancelling it. However, I choose a date range of say the 1st to the 14th, it runs in about one minute. Likewise, if I run from the 15th to the 31ts it runs quickly. But if I try and run a full month, it just seems to spin and eventually blow TEMPDB.
Running SQL Server 2012 SP3
Database is 255Gb with 299,404,637 rows
I know this isn't enough info, so where should I begin?
Regards
Farren
July 3, 2019 at 9:13 am
There can be many reasons why your query runs slower for a month period (parameter sniffing, outdated stats, blocking, etc).
I suspect that main issue here is different execution plans for different time periods.
If you show your query and show how do you run it from "report" it will give some more info to think over.
"Database is 255Gb with 299,404,637 rows"
Does your db contain only 1 table?
July 3, 2019 at 9:37 am
Hi,
I have attached the sql being called by the report. It's just called from a simple front-end url with only a handful of paramemters to choose from, and in this case they are always the same, but only the date ranges are changed. Im not a coder, so it looks very complicated to me.
The database has multiple tables and some of the bigger ones have a LOT of columns.
Regards
Farren
July 3, 2019 at 10:03 am
you say if you split up the query for a month into 2, it works okish/fine
is the result fine if you split it up the query into these 2 and union the result or would that cause a wrong result?
the month always starts with the first and you can get the dynamic last day of the month using EOMONTH()
not a beauty but a possible solution if the query result is the same?
I want to be the very best
Like no one ever was
July 3, 2019 at 10:03 am
Well, try to run your query by parts and check time taken
However, i'm not sure how this part of the query works ...
AND RS_INTRASTAT_TAX_EXT.INTRASTAT_FLAG = case 'S'
when 'B' then 'A'
else 'D'
end
AND (
( 'T' = 'T' AND (ddt.DAY_DATE BETWEEN ({d '2019-06-01'}) AND ({d '2019-06-17'})))
OR ( 'T' = 'F' AND (ddf.DAY_DATE BETWEEN ({d '2019-06-01'}) AND ({d '2019-06-17'})))
OR ( 'T' = 'D' AND (ddi.DAY_DATE BETWEEN ({d '2019-06-01'}) AND ({d '2019-06-17'})))
)
AND ml.NAME IN ('Company Name - UK', 'Second Company Name - UK', 'Third Company Name - UK - 3310')
AND RS_INVOICE_LINE_TAXES.TAXABLE_COUNTRY_NAME = 'UNITED KINGDOM'
and
case when 'NOT_OR' = 'All' then
case when rs_invoice_lines.ATTRIBUTE_10 is null or rs_invoice_lines.ATTRIBUTE_10 = '' then 'x' else rs_invoice_lines.ATTRIBUTE_10 end
when 'NOT_OR' = 'NOT_OR' then
case when rs_invoice_lines.ATTRIBUTE_10 = 'OR' then 'x' else 'NOT_OR' end
ELSE rs_invoice_lines.ATTRIBUTE_10
end
=
case when 'NOT_OR' = 'All' then
case when rs_invoice_lines.ATTRIBUTE_10 is null or rs_invoice_lines.ATTRIBUTE_10 = '' then 'x' else rs_invoice_lines.ATTRIBUTE_10 end
else 'NOT_OR'
END;
Some parts will never be executed unless it's dynamic query built by application using its own logic.
Obviously, 'NOT_OR' never equals to 'All', 'T' is not equal to 'D' or 'F', etc.
It's not a solution, but being you and not being a coder, I would try to test the option 'forced parameterization' hoping that generic plan will be good for both time periods.
See here :
https://www.brentozar.com/blitz/forced-parameterization/
"The database has multiple tables and some of the bigger ones have a LOT of columns."
Imagine you have 10 cars, one has issue and you say that "one of my cars has issue and total horsepower of my cars is 1500 hp".
Does it make sense to say how many records in your db then?
July 3, 2019 at 10:14 am
Thanks Andrey,
I'll take a look at the 'forced parameterization' and get that implemented on a test environment.
Point taken on the cars analogy. I can see how focussing on total records in a database is of no use.
Regards
Farren
July 3, 2019 at 10:16 am
Thanks ktflash,
I'll feed this back to the developers and see if they can do anything to speed this up.
It's only more tricky as they are a third-party.
Regards
Farren
July 3, 2019 at 10:54 am
Your script contains two queries, do you know how much of the total execution time each one takes?
Can you provide an execution plan for each of them, as a .sqlplan attachment? Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 3, 2019 at 11:16 am
Strong recommendation, get a copy of my book in the link below. It'll show you have to capture more information about your queries (you should use Extended events to capture the behaviors) and then how to interpret them using execution plans (several people have already suggested this) and fixes for the problems.
"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
July 3, 2019 at 12:35 pm
Thanks Grant,
I'm going to do that right now and see how I get on.
Thanks for this recommendation.
Regards
Farren
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply