October 9, 2009 at 8:02 am
This block is a real bottleneck in my query:
SELECT I.COBDate, 'CAD+CSW' AS RiskIdShortName, CAST(RI1.DAYS_TO_MATURITY AS INTEGER) AS TimeBucketDays, SUM(C.AMOUNT) AS Amount
INTO
#tmpVariance
FROM
GA2_LINK.GlobalAnalytics2.dbo.COV C
INNER JOIN
#tmpCOBDate I ON C.INTERVAL_ID = I.INTERVAL_ID
INNER JOIN
GA2_LINK.GlobalAnalytics2.dbo.RF_INDEX RI1 ON
C.RF_ID1 = RI1.RF_ID
AND C.RF_ID2 = RI1.RF_ID
AND RI1.ASSET_TYPE = 'IR'
AND RI1.PARAM_TYPE = 'BASE'
AND RI1.RISK_CLASS IS NULL
AND RI1.TEXT_INFO IS NULL
AND RI1.ISO_CURR_CODE IN ('CAD') AND 'CAD+CSW' IN (@P_RISKID)-- IN ('CAD+CSW','USD+USW')--
GROUP BY I.COBDate, CAST(RI1.DAYS_TO_MATURITY AS INTEGER)
GA2_LINK
is a linked server (SQL Server 2000)
and
GA2_LINK.GlobalAnalytics2.dbo.COV
table has 1,521,577,838 records
Any suggestions what I can do to speed up an execution little bit?
What adds more confusion is that
the execution time varies so much.
I did 10 runs, one after another and the results are;
2:38 min
1:41 min
38 sec
2:53 min
.....
STATS is attached.
October 9, 2009 at 8:13 am
can you post the execution plan for the query, if you need help in doing that, check the links in my signature at the bottom of this post.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 9, 2009 at 8:36 am
I have attached Excel file with the results of
SET STATISTICS PROFILE ON
By the way,
Add or change indexes, table schema is not an option.
I will not have permissions to do that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply