SQL Query Tuning
-
I want to reduce the query execution time for the following sql query.
it is taking 3 hrs to complete its execution.I want to reduce its execution time. Anybody help me to optimize this query.
Query:
Environment : sqlserver2000
TableName RecordCount
Tranche 3670
CompanyTrans 250,000
AcctEntry 750,000
***************************************************
SELECT DISTINCT CompanyInstitNbr
INTO #Comapnies
FROM Tranche
create table #NotMisc
(
Seq int identity,
CompanyInstitNbr integer,
TransNbr integer,
Combo varchar(255),
Typ varchar(3),
PostingDate datetime,
MaxSeq int null,
Acct varchar(255),
CrDr varchar(4)
)
insert into #NotMisc(CompanyInstitNbr,TransNbr,Combo,Typ,PostingDate,MaxSeq,Acct,CrDr)
SELECT A.CompanyInstitNbr, A.TransNbr,
CONVERT (char (10), A.PostingDate, 111) +
CONVERT (char (5), A.TransNbr) +
CONVERT (char (8), A.ValueDate, 1),
TransTypeCode ,
A.PostingDate,
CONVERT (int, NULL),
CONVERT (char (4), AcctNbr) +
CONVERT (char (3), SubAcctNbr) + SubAcctCode ,
CONVERT (char (2), CONVERT (int, SIGN (AcctEntryUsdAmt + AcctEntryQty))) + '|'
FROM CompanyTrans C, AcctEntry A, #Comapnies CS
WHERE CS.CompanyInstitNbr = C.CompanyInstitNbr
AND A.CompanyInstitNbr = C.CompanyInstitNbr
AND A.TransNbr = C.TransNbr
AND TransTypeCode NOT LIKE 'MS%'
AND SIGN (AcctEntryUsdAmt + AcctEntryQty) <> 0
ORDER BY A.CompanyInstitNbr, A.TransNbr, AcctNbr, SubAcctNbr, SubAcctCode
SELECT CompanyInstitNbr, TransNbr,
COUNT (*) HowMany, MAX (Seq) MaxSeq
INTO #MaxSeqNotMisc
FROM #NotMisc
GROUP BY CompanyInstitNbr, TransNbr
PRINT 'INSERTed #NotMisc'
SELECT T.CompanyInstitNbr, T.TransNbr, MAX (ProjectId + '-' + TrancheNbr) Tranche
INTO #MaxTranche
FROM TrnchTrans T, #NotMisc
WHERE #NotMisc.CompanyInstitNbr = T.CompanyInstitNbr
AND #NotMisc.TransNbr = T.TransNbr
GROUP BY T.CompanyInstitNbr, T.TransNbr
SELECT Typ, MaxSeq, CONVERT (varchar (196), NULL) FlatAcct, Space (9) Tranche
INTO #FlatNotMisc
FROM #NotMisc
WHERE 1 = 3
PRINT 'INSERTed #FlatNotMisc'
UPDATE #NotMisc
SET CrDr = '+1|'
WHERE CrDr = '1 |'
UPDATE #NotMisc
SET MaxSeq = #MaxSeqNotMisc.MaxSeq
FROM #MaxSeqNotMisc
WHERE #MaxSeqNotMisc.CompanyInstitNbr = #NotMisc.CompanyInstitNbr
AND #MaxSeqNotMisc.TransNbr = #NotMisc.TransNbr
CREATE UNIQUE INDEX AIdx2 ON #NotMisc (Seq)
PRINT 'INDEXed All'
DECLARE NotMiscCurs CURSOR FOR
SELECT Seq, MaxSeq, Typ, Acct, CrDr,
N.CompanyInstitNbr, N.TransNbr, Tranche
FROM #NotMisc N, #MaxTranche M
WHERE N.CompanyInstitNbr = M.CompanyInstitNbr
AND N.TransNbr = M.TransNbr
ORDER BY Seq
DECLARE @Seq int,
@MaxSeq int,
@MaxMaxSeq int,
@Typ TransTypeCode_tp,
@CompanyInstitNbr InstitutionNbr_tp,
@TransNbr TransNbr_tp,
@Acct char (11),
@CrDr char (3),
@Tranche char (9),
@RemarksText varchar (80),
@FlatAcct varchar (196)
SELECT @Seq = 0, @MaxMaxSeq = MAX (MaxSeq), @FlatAcct = ''
FROM #NotMisc
OPEN NotMiscCurs
SET NOCOUNT ON
WHILE 1 = 1
BEGIN
FETCH NotMiscCurs INTO @Seq, @MaxSeq, @Typ, @Acct, @CrDr,
@CompanyInstitNbr, @TransNbr, @Tranche
IF @@Fetch_Status <> 0 OR @@ERROR <> 0
BREAK
SELECT @FlatAcct = @FlatAcct + @Acct + @CrDr
IF @Seq = @MaxSeq
BEGIN
INSERT #FlatNotMisc (Typ, MaxSeq, FlatAcct, Tranche)
VALUES (@Typ, @MaxSeq, @FlatAcct, @Tranche)
SELECT @FlatAcct = NULL
END
END
PRINT 'Done looping'
SET NOCOUNT OFF
SELECT CONVERT (char (8), MAX (PostingDate), 1) LastPosted,
MAX (C.Tranche) Tranche,
A.Typ,
COUNT (*) HowManyTrans,
FlatAcct
INTO #Report
FROM #FlatNotMisc C, #NotMisc A
WHERE C.MaxSeq = A.MaxSeq
AND Seq = A.MaxSeq
GROUP BY FlatAcct, A.Typ
SELECT LastPosted,
(SELECT MAX (Combo)
FROM #FlatNotMisc C, #NotMisc A
WHERE C.MaxSeq = A.MaxSeq
AND Seq = A.MaxSeq
AND C.FlatAcct = #Report.FlatAcct
AND #Report.LastPosted = PostingDate) MiscKey,
HowManyTrans,
Tranche,
Typ,
FlatAcct
INTO #Report2
FROM #Report
insert into MastBcp
(
typ,
TransTypeName,
LastPosted,
TransNbr,
valueDate,
Tranche,
HowmanyTrans,
FlatAcct
)
SELECT Typ , TransTypeNme , LastPosted,
SUBSTRING (MiscKey, 11, 5) ,
SUBSTRING (MiscKey, 16, 8) ,
Tranche,
HowManyTrans,
FlatAcct
FROM #Report2, TransType
WHERE UPPER (Typ) *= TransTypeCode
and not exists (select typ,TransTypeName,LastPosted,TransNbr,valueDate,Tranche,HowmanyTrans,FlatAcct from MastBcp)
ORDER BY Typ, CONVERT (datetime, LastPosted)
*************************************************************************
Thanks and regards
Karthik
karthik
-
Hi,
I don't have time for a full analysis, but a couple of thoughts came to mind immediately.
I have found from experience that updating temporary tables and table variables tends to be a very slow
process, especially if they are un-indexed. You could try indexing your temporary tables when you create them, selecting the columns on which you are joining them to the permanent tables. I try and avoid updating temporary tables and table variables. Temporary tables will generally be slower than using permanent tables, because the queries are unlikely to be cached, and the data will be held in the temp database, which may not be on the most approriate physical device for intensive update operations.As an alternative to using a temporary table, you could consider using inline SELECTS
SELECT ...
FROM (SELECT DISTINCT CompanyInstitNbr FROM Tranche ) AS CS
...
WHERE CS.CompanyInstitNbr = C.CompanyInstitNbr
In which case, you would want make sure you had an index on the Tranche table for the CompanyInstitNbr column.
I am not sure how many records you are expecting in #NotMisc, but Consider using a CASE statement on SIGN (AcctEntryUsdAmt + AcctEntryQty) rather than the UPDATE
UPDATE #NotMisc
SET CrDr = '+1|'
WHERE CrDr = '1 |'
eg:
CASE WHEN CONVERT (char (2), CONVERT (int, SIGN (AcctEntryUsdAmt + AcctEntryQty))) + '|' = '1 |' THEN '+1|' ELSE CONVERT (char (2), CONVERT (int, SIGN (AcctEntryUsdAmt + AcctEntryQty))) + '|' END
David
If it ain't broke, don't fix it...
-
I would suggest running it with the execution plan on, as well as the IO and time statistics. That way you can work out which of the queries are the worst performers. The exec plan will show the relative cost and the io and time stats will show the durations and io impact of each query.
Once you have that, you'll have a good idea which parts of the query are the worst, and which ones to focus on.
set
statistics io on
go
set statistics time on
go
SET NOCOUNT ON
EXEC <stored proc here>
go
set statistics io off
go
set statistics time off
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Also not analysed it in detail but what jumps at me is that you are using a cursor. Cursors are slow slow slow !!! You should be able to use a set-based query to achieve the same effect.
Additionally, table variables can lead to an inefficient query plan on multi-processor machines. The plan wants to use parrallel processing but table variables don't allow it.
-
I agree with the others that this is WAY too big of a request for a forum, and also haven't analyzed the steps in detail. Having said that:
1) I would bet that at least one if not several of the temp tables aren't necessary. If you do keep them, consider building indexes on join columns after populating data but before accessing them.
2) The cursor is DEFINITELY not necessary and is gonna be a DOG from a performance standpoint if there are more than a few rows in the cursor set.
3) If you DO use a cursor, it should almost ALWAYS be declared FAST_FORWARD. This is HIGHLY optimized. It will STILL be slower than set-based logic -often by 1 or 2 orders of magnitude!
4) All table references should be prefixed with owner/schema. Consider use of NOLOCK hint too.
5) Learn to use the INNER, LEFT, RIGHT... JOIN syntax instead of the old style *=, =*, = syntax. The latter will not be supported by microsoft in the future (and currently isn't in 2005 for left/right).
6) and not exists (select typ,TransTypeName,LastPosted,TransNbr,valueDate,Tranche,HowmanyTrans,FlatAcct from MastBcp)
change this to SELECT *. No need to force grabbing all those columns in an EXISTS clause.
NOTE: As a consultant I will be happy to rewrite your process for my usual hourly rate. :-)) I can guarantee at least 1 order of magnitude improvement in time, probably close to 2. My record is almost 6 orders of magnitude, so you should feel good about not being anywhere near the worst I have seen! LOL
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
What is mean by 1 ,2 or 6 order magnitude ? Really i dont know about it.Please explain me and give some real time example.
Is there any way to avoid cursor ?
karthik
-
Say you have a set of code that takes something takes 1000 seconds to run. A 1 order of magnitude improvement means it would then take 100 seconds to run. 2 orders of magnitude means it would take 10 seconds to run. SIX orders of magnitude means it would run in 0.01 seconds - REALLY fast!
There is almost ALWAYS a way to use set-based logic for processing, and when you can it is almost always faster. I bet your code is one of these times. Set-based thinking is a COMPLETELY different beast from old-school row/procedure based development, however, and the ability to use it effectively does not come overnight. But if you (or anyone else on these forums) does database development, the effort you spend to be good at set-based logic will pay HUGE dividends for the rest of your db-dev career.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
How many magnitudes are there ? 6 is the maximum value or anything else ?
karthik
-
I think you are missing the point and focusing on the wrong thing - language barrier I suspect. I was just saying that cursors are slow and set-based logic is fast. Take it at that and forget about magnitudes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply