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 2106
(tr***@guisolut ions.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****@sommarsk og.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.tCont racts.iContract ID AS contract_id,
2 AS status_id,
'Agent' AS payeetype,
faxagency.tAgen ts.iAgentID AS payee_id,
-1 AS paymenttype_id,
1 AS feetype_id,
0 AS issubagent,
faxagency.tCont ractTermAgentDe alerFeeAmounts. fPrimaryAgentFe e*faxagency.tCa ncellations.fPe rcentage*-1
AS Amount,
'Agent',
faxagency.tCanc ellations.iCanc ellationID
FROM faxagency.tBatc hs
INNER JOIN faxagency.tCont racts ON faxagency.tCont racts.iBatchNum ber =
faxagency.tBatc hs.iBatchNumber
INNER JOIN faxagency.tCanc ellations ON
faxagency.tCont racts.iContract ID = faxagency.tCanc ellations.iCont ractID
INNER JOIN faxagency.tCont ractFeeAdjustme nts ON
faxagency.tCont ractFeeAdjustme nts.iContractID =
faxagency.tCont racts.iContract ID
INNER JOIN faxagency.tPlan s ON faxagency.tPlan s.iPlanID =
faxagency.tCont racts.iPlanID
INNER JOIN faxagency.tPlan Types ON faxagency.tPlan Types.iPlanType ID =
faxagency.tPlan s.iPlanTypeID
INNER JOIN faxagency.tPlan ContractTerms ON
faxagency.tPlan ContractTerms.i PlanContractTer mID =
faxagency.tCont racts.iPlanCont ractTermID
INNER JOIN faxagency.tTPAs ON faxagency.tTPAs .iTPAID =
faxagency.tPlan s.iTPAID
INNER JOIN faxagency.tCont ractTerms ON
faxagency.tCont ractTerms.iCont ractTermID =
faxagency.tPlan ContractTerms.i ContractTermID
INNER JOIN faxagency.tDeal erCustomers ON
faxagency.tDeal erCustomers.iDe alerCustomerID =
faxagency.tCont racts.iDealerCu stomerID
INNER JOIN faxagency.tCont ractTermAgentDe alers ON
faxagency.tCont ractTermAgentDe alers.iDealerID =
faxagency.tDeal erCustomers.iDe alerID
INNER JOIN faxagency.tCont ractTermAgents ON
faxagency.tCont ractTermAgents. iContractTermAg entID =
faxagency.tCont ractTermAgentDe alers.iContract TermAgentID AND
faxagency.tCont ractTermAgents. iPlanContractTe rmID
= faxagency.tPlan ContractTerms.i PlanContractTer mID
INNER JOIN faxagency.tCont ractTermAgentDe alerFeeAmounts ON
faxagency.tCont ractTermAgentDe alerFeeAmounts. iContractTermAg entDealerID
= faxagency.tCont ractTermAgentDe alers.iContract TermAgentDealer ID AND
faxagency.tCont ractTermAgentDe alerFeeAmounts. dStartDate <=
faxagency.tCont racts.dSaleDate AND
faxagency.tCont ractTermAgentDe alerFeeAmounts. dEndDate >=
faxagency.tCont racts.dSaleDate
INNER JOIN faxagency.tAgen ts ON faxagency.tCont ractTermAgents. iAgentID
= faxagency.tAgen ts.iAgentID
INNER JOIN faxagency.tDeal ers ON faxagency.tBatc hs.iDealerID =
faxagency.tDeal ers.iDealerID
INNER JOIN faxagency.tMont hs ON faxagency.tTPAs .iTPAID =
faxagency.tMont hs.iTPAID
WHERE EXISTS (SELECT 1 FROM faxagency.tCanc ellations WHERE
faxagency.tCanc ellations.iCont ractID =
faxagency.tCont racts.iContract ID)
AND ((faxagency.tCo ntractTermAgent DealerFeeAmount s.fPrimaryAgent Fee <>
0) OR
(faxagency.tCon tractTermAgentD ealerFeeAmounts .iSubAgentID1 = 0
AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID2 = 0
AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID3 = 0
AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID4 = 0
AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID5 = 0))
AND (faxagency.tBat chs.bActive = 1)
AND (faxagency.tCon tracts.bActive = 1)
AND (faxagency.tPla ns.bUsePercenta ges = 0 AND faxagency.tPlan s.bActive
= 1)
AND (faxagency.tPla nContractTerms. bAssigned = 1)
AND (faxagency.tCon tractTerms.bAct ive = 1)
AND (faxagency.tCon tractTermAgentD ealerFeeAmounts .bAssigned = 1 )
AND (faxagency.tCan cellations.bAct ive = 1 and
faxagency.tCanc ellations.dProc essDate BETWEEN
faxagency.tMont hs.dStartDate AND faxagency.tMont hs.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 tContractTermAg entDealerFeeAmo unts
WHERE iContractTermAg entDealerID =
tContractTermAg entDealers.iCon tractTermAgentD ealerID
AND bAssigned = 1
)
Also, you could add the predicate "AND tCancellations. bActive=1" to the
existing EXISTS subquery.
Hope this helps,
Gert-Jan tr***@guisoluti ons.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.tCont racts.iContract ID AS contract_id, 2 AS status_id, 'Agent' AS payeetype, faxagency.tAgen ts.iAgentID AS payee_id, -1 AS paymenttype_id, 1 AS feetype_id, 0 AS issubagent, faxagency.tCont ractTermAgentDe alerFeeAmounts. fPrimaryAgentFe e*faxagency.tCa ncellations.fPe rcentage*-1 AS Amount, 'Agent', faxagency.tCanc ellations.iCanc ellationID FROM faxagency.tBatc hs INNER JOIN faxagency.tCont racts ON faxagency.tCont racts.iBatchNum ber = faxagency.tBatc hs.iBatchNumber INNER JOIN faxagency.tCanc ellations ON faxagency.tCont racts.iContract ID = faxagency.tCanc ellations.iCont ractID
INNER JOIN faxagency.tCont ractFeeAdjustme nts ON faxagency.tCont ractFeeAdjustme nts.iContractID = faxagency.tCont racts.iContract ID INNER JOIN faxagency.tPlan s ON faxagency.tPlan s.iPlanID = faxagency.tCont racts.iPlanID INNER JOIN faxagency.tPlan Types ON faxagency.tPlan Types.iPlanType ID = faxagency.tPlan s.iPlanTypeID INNER JOIN faxagency.tPlan ContractTerms ON faxagency.tPlan ContractTerms.i PlanContractTer mID = faxagency.tCont racts.iPlanCont ractTermID INNER JOIN faxagency.tTPAs ON faxagency.tTPAs .iTPAID = faxagency.tPlan s.iTPAID INNER JOIN faxagency.tCont ractTerms ON faxagency.tCont ractTerms.iCont ractTermID = faxagency.tPlan ContractTerms.i ContractTermID INNER JOIN faxagency.tDeal erCustomers ON faxagency.tDeal erCustomers.iDe alerCustomerID = faxagency.tCont racts.iDealerCu stomerID INNER JOIN faxagency.tCont ractTermAgentDe alers ON faxagency.tCont ractTermAgentDe alers.iDealerID = faxagency.tDeal erCustomers.iDe alerID INNER JOIN faxagency.tCont ractTermAgents ON
faxagency.tCont ractTermAgents. iContractTermAg entID = faxagency.tCont ractTermAgentDe alers.iContract TermAgentID AND faxagency.tCont ractTermAgents. iPlanContractTe rmID = faxagency.tPlan ContractTerms.i PlanContractTer mID INNER JOIN faxagency.tCont ractTermAgentDe alerFeeAmounts ON
faxagency.tCont ractTermAgentDe alerFeeAmounts. iContractTermAg entDealerID = faxagency.tCont ractTermAgentDe alers.iContract TermAgentDealer ID AND
faxagency.tCont ractTermAgentDe alerFeeAmounts. dStartDate <= faxagency.tCont racts.dSaleDate AND
faxagency.tCont ractTermAgentDe alerFeeAmounts. dEndDate >= faxagency.tCont racts.dSaleDate
INNER JOIN faxagency.tAgen ts ON faxagency.tCont ractTermAgents. iAgentID = faxagency.tAgen ts.iAgentID INNER JOIN faxagency.tDeal ers ON faxagency.tBatc hs.iDealerID = faxagency.tDeal ers.iDealerID INNER JOIN faxagency.tMont hs ON faxagency.tTPAs .iTPAID = faxagency.tMont hs.iTPAID WHERE EXISTS (SELECT 1 FROM faxagency.tCanc ellations WHERE faxagency.tCanc ellations.iCont ractID = faxagency.tCont racts.iContract ID) AND ((faxagency.tCo ntractTermAgent DealerFeeAmount s.fPrimaryAgent Fee <> 0) OR (faxagency.tCon tractTermAgentD ealerFeeAmounts .iSubAgentID1 = 0 AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID2 = 0 AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID3 = 0 AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID4 = 0 AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID5 = 0)) AND (faxagency.tBat chs.bActive = 1) AND (faxagency.tCon tracts.bActive = 1) AND (faxagency.tPla ns.bUsePercenta ges = 0 AND faxagency.tPlan s.bActive = 1) AND (faxagency.tPla nContractTerms. bAssigned = 1) AND (faxagency.tCon tractTerms.bAct ive = 1) AND (faxagency.tCon tractTermAgentD ealerFeeAmounts .bAssigned = 1 ) AND (faxagency.tCan cellations.bAct ive = 1 and faxagency.tCanc ellations.dProc essDate BETWEEN faxagency.tMont hs.dStartDate AND faxagency.tMont hs.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***@guisolut ions.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****@sommarsk og.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 differences between
different models of experiments. I would also being using information
from this website in my work also:
http://www.ncbi.nlm.nih.gov/
|
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 another window and
ask user why they left it blank. I then want to save that "why data"
in an access database table. I don't want to force them to put in
age, but I need to know why they skipped the field.
|
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 would just as soon place the
employer in control of what directory he wishes to save it in, and there are two
salient reasons for this:
1. I want him to OWN the document
2. I want him to FIND the document, quickly, on his hard drive
In any...
|
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 XmlDocument was also done in a jiffy.
The final step is to save the document to disk and then ship it to another
system using ftp. The xml orderfile produced must fit a set specification of
the recieving system. That specification states that empty elements...
|
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 textbox (textbox1) and a HTML Button control.
Page B consists of a datagrid control.
| |
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 as an attachment it of course
opens in the browser... we want to force the user to save it to the computer
(at the least give them a dialog asking what they want to do with it)
instead of just opening it right to the browser... any way to do this?...
|
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 calling up another form or report that uses
some of the same data.
We came up with a work around that saves the current record's ID, does a
|
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 is going on
internally, making it difficult for me to trace the SQL connectivity errors I
seem to be getting suddenly.
I have my own SQL Express database, on which I've run aspNet_regsql utility
|
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 now the way I have this set up, but it's
confusing and slow. When they browse for a place to save the reports,
they see all of the drives on the terminal server as well as their own
client drives. So they're likely to want to choose "My...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |