I have a complex query (16 table join) that until five days ago took 30
sec to run. It now takes eight hours. I restored a backup of the
database from five days ago and the query plans are completely
different for the same exact query. Five days ago the final estimated
row count is 1.6M now it is 1.7E+10 OUCH! The amount of data added in
those five days is insignificant (percentage wise). I rebuilt all the
indexes using DBCC DBREINDEX with no change in the plan or results. Is
there anyway to save the query plan (from the backup) and run it
against the new data?
TIA,
Tracy 6 2086
(tr***@guisolutions.com) writes: I have a complex query (16 table join) that until five days ago took 30 sec to run. It now takes eight hours. I restored a backup of the database from five days ago and the query plans are completely different for the same exact query. Five days ago the final estimated row count is 1.6M now it is 1.7E+10 OUCH! The amount of data added in those five days is insignificant (percentage wise). I rebuilt all the indexes using DBCC DBREINDEX with no change in the plan or results. Is there anyway to save the query plan (from the backup) and run it against the new data?
No. The query plan exists in cache only.
If the query plan includes parallelism, try adding OPTION(MAXDOP 1) at
the end of the query - that may do wonders.
Another thing to try is UPDATE STATISTICS WITH FULLSCAN on all tables
(or at least the table where the optimizer goes astray).
While the amount of data added in these five days are insignificant as
such, you may have come over some threashold where the optimizer makes
a different decision. Or maybe the problem is that statistics were
updated in these five days, and the optimizer caught up with reality -
and unfortunately took the wrong path.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
If you are looking to force the same types of joins/merges, then it is
possible to force the system to use the old join/merge types using a
lot of join hints and OPTION FORCE ORDER (see BOL).
However, this does result in an EXTREMELY brittle query - any slight
change to the underlying data may mean that you are performing your
query in the most pessimal manner.
I probably wouldn't recommend this approach, unless you're happy to
hand optimize the query every time anything changes in the underlying
data - better to look at the stats, the indexes, etc, and leave the
query optimizer to do it's job.
Thanks for suggestions but unfortunately neither one helped. There was
no parallelism in the original query and OPTION(MAXDOP 1) did not
change the plan. I updated all statistics:
SET NOCOUNT ON
DECLARE @Table char(64)
DECLARE IndexCursor
CURSOR FOR SELECT name
FROM sysobjects
WHERE type='U'
AND uid=5
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @Table
WHILE @@FETCH_STATUS=0 BEGIN
EXEC ('UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN')
FETCH NEXT FROM IndexCursor
INTO @Table
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
GO
I made a seemingly silly change to the query that bought it down to one
hour. I added the EXISTS statement to the WHERE clause that mimics the
INNER JOIN in the SELECT statement. See below:
SET NOCOUNT ON
SELECT 1 AS source_id,
faxagency.tContracts.iContractID AS contract_id,
2 AS status_id,
'Agent' AS payeetype,
faxagency.tAgents.iAgentID AS payee_id,
-1 AS paymenttype_id,
1 AS feetype_id,
0 AS issubagent,
faxagency.tContractTermAgentDealerFeeAmounts.fPrim aryAgentFee*faxagency.tCancellations.fPercentage*-1
AS Amount,
'Agent',
faxagency.tCancellations.iCancellationID
FROM faxagency.tBatchs
INNER JOIN faxagency.tContracts ON faxagency.tContracts.iBatchNumber =
faxagency.tBatchs.iBatchNumber
INNER JOIN faxagency.tCancellations ON
faxagency.tContracts.iContractID = faxagency.tCancellations.iContractID
INNER JOIN faxagency.tContractFeeAdjustments ON
faxagency.tContractFeeAdjustments.iContractID =
faxagency.tContracts.iContractID
INNER JOIN faxagency.tPlans ON faxagency.tPlans.iPlanID =
faxagency.tContracts.iPlanID
INNER JOIN faxagency.tPlanTypes ON faxagency.tPlanTypes.iPlanTypeID =
faxagency.tPlans.iPlanTypeID
INNER JOIN faxagency.tPlanContractTerms ON
faxagency.tPlanContractTerms.iPlanContractTermID =
faxagency.tContracts.iPlanContractTermID
INNER JOIN faxagency.tTPAs ON faxagency.tTPAs.iTPAID =
faxagency.tPlans.iTPAID
INNER JOIN faxagency.tContractTerms ON
faxagency.tContractTerms.iContractTermID =
faxagency.tPlanContractTerms.iContractTermID
INNER JOIN faxagency.tDealerCustomers ON
faxagency.tDealerCustomers.iDealerCustomerID =
faxagency.tContracts.iDealerCustomerID
INNER JOIN faxagency.tContractTermAgentDealers ON
faxagency.tContractTermAgentDealers.iDealerID =
faxagency.tDealerCustomers.iDealerID
INNER JOIN faxagency.tContractTermAgents ON
faxagency.tContractTermAgents.iContractTermAgentID =
faxagency.tContractTermAgentDealers.iContractTermA gentID AND
faxagency.tContractTermAgents.iPlanContractTermID
= faxagency.tPlanContractTerms.iPlanContractTermID
INNER JOIN faxagency.tContractTermAgentDealerFeeAmounts ON
faxagency.tContractTermAgentDealerFeeAmounts.iCont ractTermAgentDealerID
= faxagency.tContractTermAgentDealers.iContractTermA gentDealerID AND
faxagency.tContractTermAgentDealerFeeAmounts.dStar tDate <=
faxagency.tContracts.dSaleDate AND
faxagency.tContractTermAgentDealerFeeAmounts.dEndD ate >=
faxagency.tContracts.dSaleDate
INNER JOIN faxagency.tAgents ON faxagency.tContractTermAgents.iAgentID
= faxagency.tAgents.iAgentID
INNER JOIN faxagency.tDealers ON faxagency.tBatchs.iDealerID =
faxagency.tDealers.iDealerID
INNER JOIN faxagency.tMonths ON faxagency.tTPAs.iTPAID =
faxagency.tMonths.iTPAID
WHERE EXISTS (SELECT 1 FROM faxagency.tCancellations WHERE
faxagency.tCancellations.iContractID =
faxagency.tContracts.iContractID)
AND ((faxagency.tContractTermAgentDealerFeeAmounts.fPr imaryAgentFee <>
0) OR
(faxagency.tContractTermAgentDealerFeeAmounts.iSub AgentID1 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID2 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID3 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID4 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID5 = 0))
AND (faxagency.tBatchs.bActive = 1)
AND (faxagency.tContracts.bActive = 1)
AND (faxagency.tPlans.bUsePercentages = 0 AND faxagency.tPlans.bActive
= 1)
AND (faxagency.tPlanContractTerms.bAssigned = 1)
AND (faxagency.tContractTerms.bActive = 1)
AND (faxagency.tContractTermAgentDealerFeeAmounts.bAss igned = 1 )
AND (faxagency.tCancellations.bActive = 1 and
faxagency.tCancellations.dProcessDate BETWEEN
faxagency.tMonths.dStartDate AND faxagency.tMonths.dEndDate)
One hour is still unacceptable. If I could execute the old plan (from
five days ago) against the new data I bet it would still run around 30
sec. Very frustrating.
Thanks for the suggestion. I thought of that but had the same concerns
that you have. It will become my full-time job.
Tracy,
The query you posted has a lot of literals. This is ideal for SQL-Server
and should yield the optimal (fast) query plan.
However, if you are not actually using an ad-hoc query with literals,
but for example a stored procedure with parameters or a stored procedure
with variables, then it is a different matter, and SQL-Server will
behave differently.
From the information you posted, there is not much to say about it (no
DDL, no query plan). Since adding the extra EXISTS clause helpt you a
lot, you could also add the following extra clause to see if that makes
any difference.
AND EXISTS (
SELECT 1
FROM tContractTermAgentDealerFeeAmounts
WHERE iContractTermAgentDealerID =
tContractTermAgentDealers.iContractTermAgentDealer ID
AND bAssigned = 1
)
Also, you could add the predicate "AND tCancellations.bActive=1" to the
existing EXISTS subquery.
Hope this helps,
Gert-Jan tr***@guisolutions.com wrote: Thanks for suggestions but unfortunately neither one helped. There was no parallelism in the original query and OPTION(MAXDOP 1) did not change the plan. I updated all statistics:
SET NOCOUNT ON DECLARE @Table char(64) DECLARE IndexCursor CURSOR FOR SELECT name FROM sysobjects WHERE type='U' AND uid=5 OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @Table WHILE @@FETCH_STATUS=0 BEGIN EXEC ('UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN') FETCH NEXT FROM IndexCursor INTO @Table END CLOSE IndexCursor DEALLOCATE IndexCursor GO
I made a seemingly silly change to the query that bought it down to one hour. I added the EXISTS statement to the WHERE clause that mimics the INNER JOIN in the SELECT statement. See below:
SET NOCOUNT ON SELECT 1 AS source_id, faxagency.tContracts.iContractID AS contract_id, 2 AS status_id, 'Agent' AS payeetype, faxagency.tAgents.iAgentID AS payee_id, -1 AS paymenttype_id, 1 AS feetype_id, 0 AS issubagent, faxagency.tContractTermAgentDealerFeeAmounts.fPrim aryAgentFee*faxagency.tCancellations.fPercentage*-1 AS Amount, 'Agent', faxagency.tCancellations.iCancellationID FROM faxagency.tBatchs INNER JOIN faxagency.tContracts ON faxagency.tContracts.iBatchNumber = faxagency.tBatchs.iBatchNumber INNER JOIN faxagency.tCancellations ON faxagency.tContracts.iContractID = faxagency.tCancellations.iContractID
INNER JOIN faxagency.tContractFeeAdjustments ON faxagency.tContractFeeAdjustments.iContractID = faxagency.tContracts.iContractID INNER JOIN faxagency.tPlans ON faxagency.tPlans.iPlanID = faxagency.tContracts.iPlanID INNER JOIN faxagency.tPlanTypes ON faxagency.tPlanTypes.iPlanTypeID = faxagency.tPlans.iPlanTypeID INNER JOIN faxagency.tPlanContractTerms ON faxagency.tPlanContractTerms.iPlanContractTermID = faxagency.tContracts.iPlanContractTermID INNER JOIN faxagency.tTPAs ON faxagency.tTPAs.iTPAID = faxagency.tPlans.iTPAID INNER JOIN faxagency.tContractTerms ON faxagency.tContractTerms.iContractTermID = faxagency.tPlanContractTerms.iContractTermID INNER JOIN faxagency.tDealerCustomers ON faxagency.tDealerCustomers.iDealerCustomerID = faxagency.tContracts.iDealerCustomerID INNER JOIN faxagency.tContractTermAgentDealers ON faxagency.tContractTermAgentDealers.iDealerID = faxagency.tDealerCustomers.iDealerID INNER JOIN faxagency.tContractTermAgents ON
faxagency.tContractTermAgents.iContractTermAgentID = faxagency.tContractTermAgentDealers.iContractTermA gentID AND faxagency.tContractTermAgents.iPlanContractTermID = faxagency.tPlanContractTerms.iPlanContractTermID INNER JOIN faxagency.tContractTermAgentDealerFeeAmounts ON
faxagency.tContractTermAgentDealerFeeAmounts.iCont ractTermAgentDealerID = faxagency.tContractTermAgentDealers.iContractTermA gentDealerID AND
faxagency.tContractTermAgentDealerFeeAmounts.dStar tDate <= faxagency.tContracts.dSaleDate AND
faxagency.tContractTermAgentDealerFeeAmounts.dEndD ate >= faxagency.tContracts.dSaleDate
INNER JOIN faxagency.tAgents ON faxagency.tContractTermAgents.iAgentID = faxagency.tAgents.iAgentID INNER JOIN faxagency.tDealers ON faxagency.tBatchs.iDealerID = faxagency.tDealers.iDealerID INNER JOIN faxagency.tMonths ON faxagency.tTPAs.iTPAID = faxagency.tMonths.iTPAID WHERE EXISTS (SELECT 1 FROM faxagency.tCancellations WHERE faxagency.tCancellations.iContractID = faxagency.tContracts.iContractID) AND ((faxagency.tContractTermAgentDealerFeeAmounts.fPr imaryAgentFee <> 0) OR (faxagency.tContractTermAgentDealerFeeAmounts.iSub AgentID1 = 0 AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID2 = 0 AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID3 = 0 AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID4 = 0 AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID5 = 0)) AND (faxagency.tBatchs.bActive = 1) AND (faxagency.tContracts.bActive = 1) AND (faxagency.tPlans.bUsePercentages = 0 AND faxagency.tPlans.bActive = 1) AND (faxagency.tPlanContractTerms.bAssigned = 1) AND (faxagency.tContractTerms.bActive = 1) AND (faxagency.tContractTermAgentDealerFeeAmounts.bAss igned = 1 ) AND (faxagency.tCancellations.bActive = 1 and faxagency.tCancellations.dProcessDate BETWEEN faxagency.tMonths.dStartDate AND faxagency.tMonths.dEndDate)
One hour is still unacceptable. If I could execute the old plan (from five days ago) against the new data I bet it would still run around 30 sec. Very frustrating.
(tr***@guisolutions.com) writes: Thanks for suggestions but unfortunately neither one helped. There was no parallelism in the original query and OPTION(MAXDOP 1) did not change the plan. I updated all statistics: ... I made a seemingly silly change to the query that bought it down to one hour. I added the EXISTS statement to the WHERE clause that mimics the INNER JOIN in the SELECT statement. See below:
Too bad that the simple tricks did not works. Looks like you are in
for some harder work then.
You will need to analyse the query plan, and see where it goes wrong.
Since I don't know the tables, nor the indexes, it's difficult for me
to have a guess, but I would assume that somewhere the optimizer discards
a non-clustered index, estimating that it would lead too many bookmark
lookups.
You may find it necessary to add index hints, but rearrangement of
some indexes could also be necessary.
Since the query is complex, it may be difficult to say something over
the newsgroup. Looking through 16 table definitions with their
indexes and a complex query plan is a quite a mouthful. (And the
non-use of table aliases in the query does not help.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Kyle |
last post by:
Hello all!
I'm looking into developing a biological program for modeling and
sequencing DNA and other biological processes. The program's main
focus would be to look for similarities and...
|
by: Dave |
last post by:
Hi,
I would like to know if this scenario is possible using Javascript.
Form has input field such as Age, weight etc. If a user does not
put in data in the field, I would like to open...
|
by: Curious Angel |
last post by:
I have a resume in PDF format and I want anyone who LEFT-OR-RIGHT clicks the link to
force the file to be saved, and in any event _not_ opened.
Since the PDF will be in his cache in any event, I...
|
by: Clark Spencer |
last post by:
I have built a small integration app using VS .NET 2003 that extracts
orderinformation from a 'webshop'. Extracting the orderinformation works fine.
Appending the order elements in the...
|
by: Martin |
last post by:
Hello Group
I'm having a bit of trouble figuring this out. I'm grateful for any
hints.
Let's assume I'm have a webapplication with two .aspx pages, Page A
and Page B.
Page A consists of a...
|
by: Brian Henry |
last post by:
Is there a way or method to make a file be saved to the computer instead of
being opened on the web? we wrote a web client messaging system that uses
SSL on our server and when you open a word doc...
|
by: Br |
last post by:
First issue:
When using ADPs you no longer have the ability to issue a me.refresh to
save the current record on a form (the me.refresh does a requery in an
ADP).
We usually do this before...
|
by: JoeFusion |
last post by:
Hi folks,
I am trying to use the new User Profile functionality in ASP.Net 2, and on
initial impression, it looked simple enough to use.
But I seem to be getting in a bit of a mess as to what...
|
by: evenlater |
last post by:
I have an Access application on a terminal server. Sometimes my users
need to export reports to pdf, rtf or xls files and save them to their
own client device hard drives.
They can do that right...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |