August 31, 2009 at 12:09 pm
I am working on SQL code for a report.
It's 2 small inserts into temp tables
and one final JOIN between these two tables.
Query runs 4 min (it's too long for us).
When I do Execution Plan it shows that first INSERT1
(400 records) os 30% cost, second INSERT2 is 30% cost
and final INNER JOIN is 35%.
When I do a test and run just first insert separately it takes less than a second.
So how come its cost is so high and the execution is so fast?
August 31, 2009 at 12:17 pm
Costs aren't really all that reliable. They can be either artificially high or artificially low.
Why two inserts into temp tables and then a join? Why not just build the join right into a single query? (Breaking it up is ocassionally necessary, but it's usually better to let SQL do that kind of work itself.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 31, 2009 at 12:35 pm
I found where the bottleneck is:
My JOIN with [CreditSpreadCurve] view.
One of the tables there is 63 million records.
August 31, 2009 at 12:39 pm
That can certainly cause a performance hit.
Does it have appropriate indexes?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 31, 2009 at 1:35 pm
The problem is solved.
I just filtered [CreditSpreadCurve] by dates;
"...WHERE COB = @COB1 or @COB2..."
and inserted it in a temp table #CreditSpreadCurve_subset,
then joined with this temp table.
The query ran 21 sec.
Ho-ho!
It's much better.
August 31, 2009 at 1:44 pm
21 seconds is very long for a single query. Are you willing to post execution plan, query and table and index definitions and let us take a look at it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2009 at 2:19 pm
Yes, I can post all the stuff.
I just forgot how to save Execution Plan as a text file.
August 31, 2009 at 2:24 pm
I did right click in the Execution Plan Tab but "Save Execution Plan As.." is disabled.
August 31, 2009 at 2:29 pm
Then your server is SQL 2000. There's an explaination of how to save an exec plan on SQL 2000 in this article - http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2009 at 2:32 pm
It's SQL Server 2000.
Is there any way to show you Exec Plan in 2000?
August 31, 2009 at 2:49 pm
I tried SET SHOWPLAN_TEXT ON
and saved it as TEXT file.
See attachment.
Probably Table Definitions won't help you much
because there is no way we can change anything there.
ALTER view [dbo].[BondSpread] as
select q.COBDay [COB]
, r.[Index] [Index]
, sa.AliasCode [CUSIP]
, ta.AliasCode [ISO]
, q.Value [Spread]
, q.DataSrc [DataSrc]
, q.Flag [Flag]
from Quote q
, SecurityGrp sg
, SecurityAlias sa
, Config.dbo.dsrrequest r
, SecurityAlias ta
where q.FactCode = 'OA_SPREAD'
and q.SecurityId = sg.SecurityId
and sg.GrpSrc = 'MHS'
and sg.GrpCode = 'BOND'
and q.SecurityId = sa.SecurityId
and sa.AliasSrc = 'CUSIP'
and sa.AliasCode = left(r.CUSIP, 8)
and q.TenderId = ta.SecurityId
and ta.AliasSrc = 'ISO'
ALTER view [dbo].[CreditSpreadCurve] as
select sq.COBDay [COB]
, sa.AliasCode [Instrument]
, sq.TermCode [TermCode]
, t.DaysToMaturity Term
, m.[Name] [Market]
, ta.AliasCode [ISO]
, sq.Value [Spread]
, sq.DataSrc
, sq.Flag [Flag]
from SecurityGrp sg
, CurveQuote sq
, SecurityAlias ta
, SecurityAlias sa
, Term t
, Market m
where sq.FactCode = 'BASIS_SPREAD'
and sq.SecurityId = sg.SecurityId
and sg.GrpSrc = 'MHS'
and sg.GrpCode = 'CREDIT_SPREAD'
and sq.TenderId = ta.SecurityId
and sa.SecurityId = sq.SecurityId
and sa.AliasSrc = 'MHS'
and ta.AliasSrc = 'ISO'
and sq.TermCode = t.TermCode
and sq.MarketId = m.MarketId
August 31, 2009 at 2:59 pm
Please take a look at the article I posted for a way to save the plan in Excel. The text plans are really hard to read.
The table definition is not to see if it can be changed, but to see what the table looks like. Also need the index definitions please.
How are those views typically queried?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 7:16 am
Gila,
I followed your instructions from
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
but "SET STATISTICS PROFILE ON" generated 20 grids.
Do I have to copy each one of them and paste it to Excel?
September 1, 2009 at 7:25 am
Only the ones that are execution plans. If you take a quick look at each, it should be easy to see which are execution plans and which are real data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 7:30 am
ATTACHMENTS. UPDATED!
-------------------------
OK.
I saved all 20 grids from Query Analyzer
and pasted to "DSR_Validation.xls". It is attached.
[CreditSpreadCurve] view table definitions and indexes are also attached.
Question.
How do I get table definition with all the indexes?
Is there a way to generate a script that includes everything?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply