SQL Server Performance

Sql Query Tuning

Discussion in 'T-SQL Performance Tuning for Developers' started by karthi_sql20051@yahoo.co., Jun 5, 2007.

  1. I want to reduce the query execution time for the following sql query.<br /><br />it is taking 3 hrs to complete its execution.I want to reduce its execution time. Anybody help me to optimize this query.<br /><br />Query:<br /><br />Environment : sqlserver2000<br /><br />TableName RecordCount<br /><br />Tranche 3670<br /><br />CompanyTrans 250,000<br /><br />AcctEntry 750,000<br /><br />***************************************************<br /><br />SELECT DISTINCT CompanyInstitNbr<br />INTO #Comapnies<br />FROM Tranche<br /><br />create table #NotMisc<br />(<br />Seq int identity,<br />CompanyInstitNbr integer,<br />TransNbr integer,<br />Combo varchar(255),<br />Typ varchar(3),<br />PostingDate datetime,<br />MaxSeq int null,<br />Acct varchar(255),<br />CrDr varchar(4)<br />)<br /><br /><br />insert into #NotMisc(CompanyInstitNbr,TransNbr,Combo,Typ,PostingDate,MaxSeq,Acct,CrDr) <br />SELECT A.CompanyInstitNbr, A.TransNbr,<br /> CONVERT (char (10), A.PostingDate, 111) +<br /> CONVERT (char (5), A.TransNbr) +<br /> CONVERT (char (<img src='/community/emoticons/emotion-11.gif' alt='8)' />, A.ValueDate, 1),<br /> TransTypeCode ,<br /> A.PostingDate,<br /> CONVERT (int, NULL),<br /> CONVERT (char (4), AcctNbr) +<br /> CONVERT (char (3), SubAcctNbr) + SubAcctCode ,<br /> CONVERT (char (2), CONVERT (int, SIGN (AcctEntryUsdAmt + AcctEntryQty))) + '|' <br /><br />FROM CompanyTrans C, AcctEntry A, #Comapnies CS<br />WHERE CS.CompanyInstitNbr = C.CompanyInstitNbr<br /> AND A.CompanyInstitNbr = C.CompanyInstitNbr<br /> AND A.TransNbr = C.TransNbr<br /> AND TransTypeCode NOT LIKE 'MS%'<br /> AND SIGN (AcctEntryUsdAmt + AcctEntryQty) &lt;&gt; 0<br />ORDER BY A.CompanyInstitNbr, A.TransNbr, AcctNbr, SubAcctNbr, SubAcctCode<br /><br />SELECT CompanyInstitNbr, TransNbr,<br /> COUNT (*) HowMany, MAX (Seq) MaxSeq<br />INTO #MaxSeqNotMisc<br />FROM #NotMisc<br />GROUP BY CompanyInstitNbr, TransNbr<br />PRINT 'INSERTed #NotMisc'<br /><br />SELECT T.CompanyInstitNbr, T.TransNbr, MAX (ProjectId + '-' + TrancheNbr) Tranche<br />INTO #MaxTranche<br />FROM TrnchTrans T, #NotMisc<br />WHERE #NotMisc.CompanyInstitNbr = T.CompanyInstitNbr<br /> AND #NotMisc.TransNbr = T.TransNbr<br />GROUP BY T.CompanyInstitNbr, T.TransNbr<br /><br />SELECT Typ, MaxSeq, CONVERT (varchar (196), NULL) FlatAcct, Space (9) Tranche<br />INTO #FlatNotMisc<br />FROM #NotMisc<br />WHERE 1 = 3<br />PRINT 'INSERTed #FlatNotMisc'<br /><br />UPDATE #NotMisc<br />SET CrDr = '+1|'<br />WHERE CrDr = '1 |'<br /><br />UPDATE #NotMisc<br />SET MaxSeq = #MaxSeqNotMisc.MaxSeq<br />FROM #MaxSeqNotMisc<br />WHERE #MaxSeqNotMisc.CompanyInstitNbr = #NotMisc.CompanyInstitNbr<br /> AND #MaxSeqNotMisc.TransNbr = #NotMisc.TransNbr<br /><br /><br />CREATE UNIQUE INDEX AIdx2 ON #NotMisc (Seq)<br />PRINT 'INDEXed All'<br /><br />DECLARE NotMiscCurs CURSOR FOR<br />SELECT Seq, MaxSeq, Typ, Acct, CrDr,<br /> N.CompanyInstitNbr, N.TransNbr, Tranche<br />FROM #NotMisc N, #MaxTranche M<br />WHERE N.CompanyInstitNbr = M.CompanyInstitNbr<br /> AND N.TransNbr = M.TransNbr<br />ORDER BY Seq<br /><br />DECLARE @Seq int,<br /> @MaxSeq int,<br /> @MaxMaxSeq int,<br /> @Typ TransTypeCode_tp,<br /> @CompanyInstitNbr InstitutionNbr_tp,<br /> @TransNbr TransNbr_tp,<br /> @Acct char (11),<br /> @CrDr char (3),<br /> @Tranche char (9),<br /> @RemarksText varchar (80),<br /> @FlatAcct varchar (196)<br /><br />SELECT @Seq = 0, @MaxMaxSeq = MAX (MaxSeq), @FlatAcct = ''<br />FROM #NotMisc<br /><br />OPEN NotMiscCurs<br />SET NOCOUNT ON<br />WHILE 1 = 1<br /> BEGIN<br /> FETCH NotMiscCurs INTO @Seq, @MaxSeq, @Typ, @Acct, @CrDr,<br /> @CompanyInstitNbr, @TransNbr, @Tranche<br /> IF @@Fetch_Status &lt;&gt; 0 OR @@ERROR &lt;&gt; 0<br /> BREAK<br /> SELECT @FlatAcct = @FlatAcct + @Acct + @CrDr<br /> IF @Seq = @MaxSeq<br /> BEGIN<br /> INSERT #FlatNotMisc (Typ, MaxSeq, FlatAcct, Tranche)<br /> VALUES (@Typ, @MaxSeq, @FlatAcct, @Tranche)<br /> SELECT @FlatAcct = NULL<br /> END<br /> END<br /><br />PRINT 'Done looping'<br /><br />SET NOCOUNT OFF<br /><br /><br />SELECT CONVERT (char (<img src='/community/emoticons/emotion-11.gif' alt='8)' />, MAX (PostingDate), 1) LastPosted,<br /> MAX (C.Tranche) Tranche,<br /> A.Typ,<br /> COUNT (*) HowManyTrans,<br /> FlatAcct<br />INTO #Report<br />FROM #FlatNotMisc C, #NotMisc A<br />WHERE C.MaxSeq = A.MaxSeq<br /> AND Seq = A.MaxSeq<br />GROUP BY FlatAcct, A.Typ<br /><br /> <br /><br />SELECT LastPosted,<br />(SELECT MAX (Combo)<br />FROM #FlatNotMisc C, #NotMisc A<br />WHERE C.MaxSeq = A.MaxSeq<br />AND Seq = A.MaxSeq<br />AND C.FlatAcct = #Report.FlatAcct<br />AND #Report.LastPosted = PostingDate) MiscKey,<br /> HowManyTrans,<br /> Tranche,<br /> Typ,<br /> FlatAcct<br />INTO #Report2<br />FROM #Report<br />insert into MastBcp<br />(<br />typ,<br />TransTypeName,<br />LastPosted,<br />TransNbr,<br />valueDate,<br />Tranche,<br />HowmanyTrans,<br />FlatAcct<br />)<br />SELECT Typ , TransTypeNme , LastPosted,<br /> SUBSTRING (MiscKey, 11, 5) ,<br /> SUBSTRING (MiscKey, 16, <img src='/community/emoticons/emotion-11.gif' alt='8)' /> ,<br /> Tranche,<br /> HowManyTrans,<br /> FlatAcct<br />FROM #Report2, TransType<br />WHERE UPPER (Typ) *= TransTypeCode<br />and not exists (select typ,TransTypeName,LastPosted,TransNbr,valueDate,Tranche,HowmanyTrans,FlatAcct from MastBcp)<br />ORDER BY Typ, CONVERT (datetime, LastPosted)<br /><br /> *************************************************************************<br /><br />Thanks and regards<br /><br />Karthik<br />
  2. Adriaan New Member

    Have you tried writing a straightforward aggregate query (GROUP BY) just to see how far you can get?
  3. condeba92 New Member

    Dear Karthi:
    I think you need start to write a set-based store procedure.
    I have a topic in this forum where i was talk about this kinf of problem. It name is "Set logical store procedure".
    Thank's.

Share This Page