SQL Server Performance

Hardware, Software problem or both?

Discussion in 'Performance Tuning for Hardware Configurations' started by Luis Martin, May 17, 2006.

  1. Luis Martin Moderator

    Hi Guru (sound like a new member)

    This is the situation in one of my clients:

    Server: 2 Processors 3.2 HT off, 2GB RAM, 2 RAID 10 with 15000 RPM disks.
    Windows 2003 last sp.
    SQL 2000 SE SP4
    SQL Server dedicated.
    Database: 100GGB.

    The application is installed in each work station.

    There is one process to take 5 minutes to run in any WS except one.
    In that WS the process run in 2 seconds.
    All WS has XP SP2 except that one with W98.

    If I trace the process when is running in W98 or XP, is the same query.

    That query in QA run in 5 minutes also, in my laptop. (XP, SP2, 3.2 HT off, etc).

    The networks guys said: all is ok.

    The guy besides the W98 has XP. I change the patch cord between both. Same result with XP and same result with W98.

    Now, I restore the database in my office (Pentium IV 2.8 GB, 500MB RAM, IDE disks), run the same process using the application from my laptop and run in 2 seconds, but if I run the query with QA, more than 5 minutes. But I#%92m alone, so is not comparable with real environment.

    All of me said: is network problem, but why run fine with 98 and slow in XP?

    I don#%92t post the query, because is a 3rd party tool and I can#%92t change a single line.

    But if you want to see it, let me know.

    Last: the rest of the process run in similar times in any WS.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  2. cmdr_skywalker New Member

    Luis,
    from what you're saying, the application makes a substantial impact in running the same query and the application seems to run faster in Win98. What is the returned value of the Query? The Win98 API/Architecture is more open compared to XP. Is it possible that the application is not really using the value but only the attribute (for instance, .EOF of ADO/RDO recordset)?

    May the Almighty God bless us all!
    www.empoweredinformation.com
  3. Luis Martin Moderator

    The query returns no more than 100 rows.
    About the rest, I have to check it.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  4. Luis Martin Moderator

    A litle more information.

    The application use ODBC with TCP to conect with Server.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  5. joechang New Member

    l'd like to see the execution plan

    on each system, particularly if there is a difference in plans
  6. Luis Martin Moderator

    I'm in process of restore last database at my office, so I'll show execution plan tomorrow.
    Thanks Joe.






    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  7. Luis Martin Moderator

    Well, tomorrow is now.<br /><br />From application (profiler time) 5826 miliseconds.<br />From QA (profiler time) 41780 miliseconds.<br /><br />SELECT CASE WHEN C.Fecha &lt;= 'May 10 2006 12:00:00:000AM' THEN 'S' ELSE 'N' END, C.Codplan, J.descuento, C.Fecha, C.Nrocupon, C.Nrotarjeta, <br />CASE WHEN J.codtab is null THEN M.Importe ELSE m.cantidad * isnull((Ind.Valor/Ta.Factor),1) END, M.Codpag, CASE WHEN T.Codcmp='TV' THEN <br />M.CodcuePres ELSE M.Codcue END, M.Codcen, M.CodcenB, CASE WHEN cu.codtab is null THEN 0 ELSE (CASE WHEN J.Codtab is null OR <br />isnull(J.Codtab,0) &lt;&gt; isnull(Cu.Codtab,0) THEN M.Importe /(1.0000 / 1 ) ELSE M.Cantidad END) END, M.Nrotrans, M.Secuencia, 'K', P.Tipovalor, <br />'E', A.Codcue, J.codctabcopres, (CASE WHEN Cu.Codtab is null THEN (CASE WHEN J.Codtab is null THEN M.importe ELSE m.cantidad * <br />isnull((Ind.Valor/Ta.Factor),1) END) ELSE (CASE WHEN J.Codtab is null OR isnull(J.Codtab,0) &lt;&gt; isnull(Cu.Codtab,0) THEN M.Importe /(1.0000 / <br />1) ELSE M.Cantidad END) END ) * J.descuento / 100 FROM Cupones C (NOLOCK) LEFT JOIN cajasreg CR (NOLOCK) ON C.nrotrans=CR.nrotrans JOIN Mvscaja M (NOLOCK) ON (C.Nrotrans=M.Nrotrans AND <br />C.Secuencia=M.Secuencia AND CR.Refcaja=M.refcaja) JOIN Mediospago P (NOLOCK) ON (M.Codpag=P.Codpag) JOIN Transac T (NOLOCK) ON <br />(C.nrotrans=T.Nrotrans) JOIN Tarjetplanes J (NOLOCK) ON (C.Codplan=J.Codplan AND C.Codtarjeta = J.Codtarjeta AND J.Codemp=T.Codemp) JOIN Cajas <br />A (NOLOCK) ON (J.Codctabcopres=A.Codcaj AND T.Codemp=A.codemp AND A.Cajprefi='C' ) JOIN Cuentas CU (NOLOCK) ON A.Codcue=CU.Codcue <br />LEFT JOIN Tablas Ta (NOLOCK) ON J.Codtab = Ta.Codtab LEFT JOIN Indices Ind (NOLOCK) ON Ta.Codtab = Ind.Codtab AND Ind.Fecha=(SELECT <br />max(N.fecha) FROM Indices N (NOLOCK) WHERE N.codtab=Ta.codtab AND N.fecha &lt;= <br />'May 10 2006 12:00:00:000AM' ) WHERE C.Nrotransegr is null AND C.Codtarjeta='01 ' AND T.Nrotranselim is null AND T.Codemp=1.000000000000000e+000 AND <br />isnull(Cu.Codtab,'') = isnull(NULL ,'') AND T.Codsuc=isnull(NULL ,6.100000000000000e+001 ) AND A.Cajprefi='C' AND ((T.Codcmp='TV' <br />AND CR.Codcaj2 ='61 ' ) OR (T.Codcmp&lt;&gt;'TV' AND CR.Codcaj = '61 ' )) ORDER BY C.Fecha<br /><br /><br /><br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1013]=If ([C].[FECHA]&lt;='May 10 2006 12:00AM') then 'S' else 'N', [Expr1014]=If ([J].[CODTAB]=NULL) then Convert([M].[IMPORTE]) else ([M].[CANTIDAD]*isnull([Ind].[VALOR]/Convert([Ta].[FACTOR]), 1.00000000000)), [Expr1015]=If ([T].[CODCMP]='TV') then [M].[CODCUEPRES] else [M].[CODCUE], [Expr1016]=If ([CU].[CODTAB]=NULL) then 0.0000000000000 else If ([J].[CODTAB]=NULL OR isnull([J].[CODTAB], 0)&lt;&gt;isnull([CU].[CODTAB], 0)) then ([M].[IMPORTE]/1.000000) else Convert([M].[CANTIDAD]), [Expr1019]=If ([CU].[CODTAB]=NULL) then If ([J].[CODTAB]=NULL) then Convert([M].[IMPORTE]) else ([M].[CANTIDAD]*isnull([Ind].[VALOR]/Convert([Ta].[FACTOR]), 1.00000000000)) else Convert(If ([J].[CODTAB]=NULL OR isnull([J].[CODTAB], 0)&lt;&gt;isnull([CU].[CODTAB], 0)) then ([M].[IMPORTE]/1.000000) else Convert([M].[CANTIDAD]))*[J].[DESCUENTO]/100)),1,2,1,Compute Scalar,Compute Scalar,DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1013]=If ([C].[FECHA]&lt;='May 10 2006 12:00AM') then 'S' else 'N', [Expr1014]=If ([J].[CODTAB]=NULL) then Convert([M].[IMPORTE]) else ([M].[CANTIDAD]*isnull([Ind].[VALOR]/Convert([Ta].[FACTOR]), 1.00000000000)), [Expr1015]=If ([T].[CODCMP]='TV') then [M].[CODCUEPRES] else [M].[CODCUE], [Expr1016]=If ([CU].[CODTAB]=NULL) then 0.0000000000000 else If ([J].[CODTAB]=NULL OR isnull([J].[CODTAB], 0)&lt;&gt;isnull([CU].[CODTAB], 0)) then ([M].[IMPORTE]/1.000000) else Convert([M].[CANTIDAD]), [Expr1019]=If ([CU].[CODTAB]=NULL) then If ([J].[CODTAB]=NULL) then Convert([M].[IMPORTE]) else ([M].[CANTIDAD]*isnull([Ind].[VALOR]/Convert([Ta].[FACTOR]), 1.00000000000)) else Convert(If ([J].[CODTAB]=NULL OR isnull([J].[CODTAB], 0)&lt;&gt;isnull([CU].[CODTAB], 0)) then ([M].[IMPORTE]/1.000000) else Convert([M].[CANTIDAD]))*[J].[DESCUENTO]/100),[Expr1013]=If ([C].[FECHA]&lt;='May 10 2006 12:00AM') then 'S' else 'N', [Expr1014]=If ([J].[CODTAB]=NULL) then Convert([M].[IMPORTE]) else ([M].[CANTIDAD]*isnull([Ind].[VALOR]/Convert([Ta].[FACTOR]), 1.00000000000)), [Expr1015]=If ([T].[CODCMP]='TV') then [M].[CODCUEPRES] else [M].[CODCUE], [Expr1016]=If ([CU].[CODTAB]=NULL) then 0.0000000000000 else If ([J].[CODTAB]=NULL OR isnull([J].[CODTAB], 0)&lt;&gt;isnull([CU].[CODTAB], 0)) then ([M].[IMPORTE]/1.000000) else Convert([M].[CANTIDAD]), [Expr1019]=If ([CU].[CODTAB]=NULL) then If ([J].[CODTAB]=NULL) then Convert([M].[IMPORTE]) else ([M].[CANTIDAD]*isnull([Ind].[VALOR]/Convert([Ta].[FACTOR]), 1.00000000000)) else Convert(If ([J].[CODTAB]=NULL OR isnull([J].[CODTAB], 0)&lt;&gt;isnull([CU].[CODTAB], 0)) then ([M].[IMPORTE]/1.000000) else Convert([M].[CANTIDAD]))*[J].[DESCUENTO]/100,53.93469,0,5.393469E-06,142,0.1538831,[C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA], [M].[CODPAG], [M].[CODCEN], [M].[CODCENB], [M].[NROTRANS], [M].[SECUENCIA], [P].[TIPOVALOR], [J].[CODCTABCOPRES], [J].[DESCUENTO], [A].[CODCUE], [Expr1013], [Expr1014], [Expr1015], [Expr1016], [Expr1017], [Expr1018], [Expr1019],NULL,PLAN_ROW,0,1<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ta].[CODTAB])),1,3,2,Nested Loops,Left Outer Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ta].[CODTAB]),NULL,53.93469,0,0.000225447,179,0.1538777,[C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA], [M].[CODPAG], [M].[CODCUE], [M].[CODCUEPRES], [M].[CODCEN], [M].[CODCENB], [M].[NROTRANS], [M].[SECUENCIA], [M].[CANTIDAD], [M].[IMPORTE], [P].[TIPOVALOR], [T].[CODCMP], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODCUE], [CU].[CODTAB], [Ta].[FACTOR], [Ind].[VALOR],NULL,PLAN_ROW,0,1<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[J].[CODTAB])),1,4,3,Nested Loops,Left Outer Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[J].[CODTAB]),NULL,53.93469,0,0.000225447,170,0.1398087,[C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA], [M].[CODPAG], [M].[CODCUE], [M].[CODCUEPRES], [M].[CODCEN], [M].[CODCENB], [M].[NROTRANS], [M].[SECUENCIA], [M].[CANTIDAD], [M].[IMPORTE], [P].[TIPOVALOR], [T].[CODCMP], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODCUE], [CU].[CODTAB], [Ta].[FACTOR], [Ta].[CODTAB],NULL,PLAN_ROW,0,1<br /> | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[C].[FECHA] ASC)),1,5,4,Sort,Sort,ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[C].[FECHA] ASC),NULL,53.93469,0.01126126,0.0005841695,111,0.1288829,[C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA], [M].[CODPAG], [M].[CODCUE], [M].[CODCUEPRES], [M].[CODCEN], [M].[CODCENB], [M].[NROTRANS], [M].[SECUENCIA], [M].[CANTIDAD], [M].[IMPORTE], [P].[TIPOVALOR], [T].[CODCMP], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODCUE], [CU].[CODTAB],NULL,PLAN_ROW,0,1<br /> | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[M].[CODPAG])),1,6,5,Nested Loops,Inner Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[M].[CODPAG]),NULL,53.93469,0,0.0002286458,455,0.1170375,[C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA], [M].[CODPAG], [M].[CODCUE], [M].[CODCUEPRES], [M].[CODCEN], [M].[CODCENB], [M].[NROTRANS], [M].[SECUENCIA], [M].[CANTIDAD], [M].[IMPORTE], [P].[TIPOVALOR], [T].[CODCMP], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODCUE], [CU].[CODTAB],NULL,PLAN_ROW,0,1<br /> | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[C].[SECUENCIA], [CR].[REFCAJA], [T].[NROTRANS]) WITH PREFETCH),1,7,6,Nested Loops,Inner Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[C].[SECUENCIA], [CR].[REFCAJA], [T].[NROTRANS]) WITH PREFETCH,NULL,54.69996,0,0.0002286458,399,0.1060476,[C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA], [M].[CODPAG], [M].[CODCUE], [M].[CODCUEPRES], [M].[CODCEN], [M].[CODCENB], [M].[NROTRANS], [M].[SECUENCIA], [M].[CANTIDAD], [M].[IMPORTE], [T].[CODCMP], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODCUE], [CU].[CODTAB],NULL,PLAN_ROW,0,1<br /> | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[T].[NROTRANS], [T].[CODCMP]) WITH PREFETCH),1,9,7,Nested Loops,Inner Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[T].[NROTRANS], [T].[CODCMP]) WITH PREFETCH,NULL,54.69996,0,0.0002286458,328,0.09153968,[C].[SECUENCIA], [C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA], [CR].[REFCAJA], [T].[NROTRANS], [T].[CODCMP], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODCUE], [CU].[CODTAB],NULL,PLAN_ROW,0,1<br /> | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[T].[NROTRANS], [J].[CODPLAN]) WITH PREFETCH),1,11,9,Nested Loops,Inner Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[T].[NROTRANS], [J].[CODPLAN]) WITH PREFETCH,NULL,54.69996,0,0.0002286458,291,0.08018737,[C].[SECUENCIA], [C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA], [T].[NROTRANS], [T].[CODCMP], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODCUE], [CU].[CODTAB],NULL,PLAN_ROW,0,1<br /> | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[CODEMP])),1,13,11,Nested Loops,Inner Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[CODEMP]),NULL,54.69996,0,0.0002286458,222,0.06567466,[T].[NROTRANS], [T].[CODCMP], [J].[CODPLAN], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODCUE], [CU].[CODTAB],NULL,PLAN_ROW,0,1<br /> | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[CODCUE]) WITH PREFETCH),1,14,13,Nested Loops,Inner Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[CODCUE]) WITH PREFETCH,NULL,24,0,0.00010032,191,0.05699971,[J].[CODPLAN], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODEMP], [A].[CODCUE], [CU].[CODTAB],NULL,PLAN_ROW,0,1<br /> | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[J].[CODEMP], [J].[CODCTABCOPRES])),1,16,14,Nested Loops,Inner Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[J].[CODEMP], [J].[CODCTABCOPRES]),NULL,24,0,0.00010032,168,0.04861398,[J].[CODPLAN], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB], [A].[CODEMP], [A].[CODCUE],NULL,PLAN_ROW,0,1<br /> | | | | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[TARJETPLANES].[PK_TARJETPLANES] AS [J]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[J].[CODTARJETA]='01 ')),1,17,16,Clustered Index Scan,Clustered Index Scan,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[TARJETPLANES].[PK_TARJETPLANES] AS [J]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[J].[CODTARJETA]='01 '),[J].[CODEMP], [J].[CODTARJETA], [J].[CODPLAN], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB],24,0.03980072,0.0003106,93,0.04011132,[J].[CODEMP], [J].[CODTARJETA], [J].[CODPLAN], [J].[CODCTABCOPRES], [J].[DESCUENTO], [J].[CODTAB],NULL,PLAN_ROW,0,1<br /> | | | | | | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[CAJAS].[PK_CAJAS] AS [A]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[CODEMP]=[J].[CODEMP] AND [A].[CAJPREFI]='C' AND [A].[CODCAJ]=[J].[CODCTABCOPRES]) ORDERED FORWARD),1,18,16,Clustered Index Seek,Clustered Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[CAJAS].[PK_CAJAS] AS [A]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[A].[CODEMP]=[J].[CODEMP] AND [A].[CAJPREFI]='C' AND [A].[CODCAJ]=[J].[CODCTABCOPRES]) ORDERED FORWARD,[A].[CODEMP], [A].[CODCUE],1,0.0063285,7.961E-05,84,0.008301056,[A].[CODEMP], [A].[CODCUE],NULL,PLAN_ROW,0,24<br /> | | | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[CUENTAS].[IXC03.04.28_CUENTAS_CodCue_CodTab] AS [CU]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CU].[CODCUE]=[A].[CODCUE]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />isnull([CU].[CODTAB], 0)=0) ORDERED FORWARD),1,19,14,Index Seek,Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[CUENTAS].[IXC03.04.28_CUENTAS_CodCue_CodTab] AS [CU]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CU].[CODCUE]=[A].[CODCUE]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />isnull([CU].[CODTAB], 0)=0) ORDERED FORWARD,[CU].[CODTAB],1,0.0063285,7.9603E-05,31,0.00826909,[CU].[CODTAB],NULL,PLAN_ROW,0,24<br /> | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1063], [Expr1064], [Expr1065])),1,20,13,Nested Loops,Inner Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1063], [Expr1064], [Expr1065]),NULL,2.279165,0.0063285,8.105038E-05,39,0.008348938,[T].[NROTRANS], [T].[CODSUC], [T].[CODEMP], [T].[CODCMP],NULL,PLAN_ROW,0,24<br /> | | | | | | |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1063]=Convert(61)-1, [Expr1064]=Convert(61)+1, [Expr1065]=If (Convert(61)-1=NULL) then 0 else 6|If (Convert(61)+1=NULL) then 0 else 10)),1,21,20,Compute Scalar,Compute Scalar,DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1063]=Convert(61)-1, [Expr1064]=Convert(61)+1, [Expr1065]=If (Convert(61)-1=NULL) then 0 else 6|If (Convert(61)+1=NULL) then 0 else 10),[Expr1063]=Convert(61)-1, [Expr1064]=Convert(61)+1, [Expr1065]=If (Convert(61)-1=NULL) then 0 else 6|If (Convert(61)+1=NULL) then 0 else 10,1,0,0,0,0,[Expr1063], [Expr1064], [Expr1065],NULL,PLAN_ROW,0,1<br /> | | | | | | | |--Constant Scan,1,22,21,Constant Scan,Constant Scan,NULL,NULL,1,0,0,0,0,NULL,NULL,PLAN_ROW,0,1<br /> | | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[TRANSAC].[IXC060515CTP_TRANSAC_CodEmp_NroTraselim_CodSuc_GdelNegro04] AS [T]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[T].[CODEMP]=[A].[CODEMP] AND [T].[NROTRANSELIM]=NULL AND [T].[CODSUC] &gt; [Expr1063] AND [T].[CODSUC] &lt; [Expr1064]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(Convert([T].[CODEMP])=1 AND Convert([T].[CODSUC])=61) AND ([T].[CODCMP]='TV' OR [T].[CODCMP]&lt;&gt;'TV')) ORDERED FORWARD),1,54,20,Index Seek,Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[TRANSAC].[IXC060515CTP_TRANSAC_CodEmp_NroTraselim_CodSuc_GdelNegro04] AS [T]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[T].[CODEMP]=[A].[CODEMP] AND [T].[NROTRANSELIM]=NULL AND [T].[CODSUC] &gt; [Expr1063] AND [T].[CODSUC] &lt; [Expr1064]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(Convert([T].[CODEMP])=1 AND Convert([T].[CODSUC])=61) AND ([T].[CODCMP]='TV' OR [T].[CODCMP]&lt;&gt;'TV')) ORDERED FORWARD,[T].[NROTRANS], [T].[CODSUC], [T].[CODEMP], [T].[CODCMP],2.279165,0.0063285,8.105038E-05,39,0.008348938,[T].[NROTRANS], [T].[CODSUC], [T].[CODEMP], [T].[CODCMP],NULL,PLAN_ROW,0,24<br /> | | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[CUPONES].[IXC060417CT_CUPONES_NroTransegr_CodTarjeta_CodPlan_Gnegro01] AS [C]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[C].[NROTRANSEGR]=NULL AND [C].[CODTARJETA]='01 ' AND [C].[CODPLAN]=[J].[CODPLAN] AND [C].[NROTRANS]=[T].[NROTRANS]) ORDERED FORWARD),1,55,11,Index Seek,Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[CUPONES].[IXC060417CT_CUPONES_NroTransegr_CodTarjeta_CodPlan_Gnegro01] AS [C]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[C].[NROTRANSEGR]=NULL AND [C].[CODTARJETA]='01 ' AND [C].[CODPLAN]=[J].[CODPLAN] AND [C].[NROTRANS]=[T].[NROTRANS]) ORDERED FORWARD,[C].[SECUENCIA], [C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA],1,0.0063285,7.9613E-05,78,0.01428406,[C].[SECUENCIA], [C].[CODPLAN], [C].[FECHA], [C].[NROCUPON], [C].[NROTARJETA],NULL,PLAN_ROW,0,54.69996<br /> | | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[CAJASREG].[IXCCTP050617_CAJASREG_Ariel01] AS [CR]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CR].[NROTRANS]=[T].[NROTRANS]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(([T].[CODCMP]='TV' AND [CR].[CODCAJ2]='61 ') OR ([T].[CODCMP]&lt;&gt;'TV' AND [CR].[CODCAJ]='61 ')) AND ([CR].[CODCAJ2]='61 ' OR [CR].[CODCAJ]='61 ')) ORDERED FORWARD),1,56,9,Index Seek,Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[CAJASREG].[IXCCTP050617_CAJASREG_Ariel01] AS [CR]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[CR].[NROTRANS]=[T].[NROTRANS]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(([T].[CODCMP]='TV' AND [CR].[CODCAJ2]='61 ') OR ([T].[CODCMP]&lt;&gt;'TV' AND [CR].[CODCAJ]='61 ')) AND ([CR].[CODCAJ2]='61 ' OR [CR].[CODCAJ]='61 ')) ORDERED FORWARD,[CR].[REFCAJA], [CR].[CODCAJ], [CR].[CODCAJ2],1,0.0063285,7.9603E-05,45,0.01098801,[CR].[REFCAJA], [CR].[CODCAJ], [CR].[CODCAJ2],NULL,PLAN_ROW,0,54.69996<br /> | | | |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[MVSCAJA].[IXC060417CTP_MVSCAJA_NroTrans_RefCaja_Secuencia_GNegro01] AS [M]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[M].[NROTRANS]=[T].[NROTRANS] AND [M].[REFCAJA]=[CR].[REFCAJA] AND [M].[SECUENCIA]=[C].[SECUENCIA]) ORDERED FORWARD),1,57,7,Index Seek,Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[MVSCAJA].[IXC060417CTP_MVSCAJA_NroTrans_RefCaja_Secuencia_GNegro01] AS [M]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[M].[NROTRANS]=[T].[NROTRANS] AND [M].[REFCAJA]=[CR].[REFCAJA] AND [M].[SECUENCIA]=[C].[SECUENCIA]) ORDERED FORWARD,[M].[CODPAG], [M].[CODCUE], [M].[CODCUEPRES], [M].[CODCEN], [M].[CODCENB], [M].[NROTRANS], [M].[SECUENCIA], [M].[CANTIDAD], [M].[IMPORTE],1,0.0063285,7.961E-05,80,0.01427923,[M].[CODPAG], [M].[CODCUE], [M].[CODCUEPRES], [M].[CODCEN], [M].[CODCENB], [M].[NROTRANS], [M].[SECUENCIA], [M].[CANTIDAD], [M].[IMPORTE],NULL,PLAN_ROW,0,54.69996<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[MEDIOSPAGO].[PK_MEDIOSPAGO] AS [P]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[P].[CODPAG]=[M].[CODPAG]) ORDERED FORWARD),1,58,6,Clustered Index Seek,Clustered Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[MEDIOSPAGO].[PK_MEDIOSPAGO] AS [P]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[P].[CODPAG]=[M].[CODPAG]) ORDERED FORWARD,[P].[TIPOVALOR],1,0.0063285,7.9603E-05,64,0.01076128,[P].[TIPOVALOR],NULL,PLAN_ROW,0,54.69996<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[TABLAS].[PK_TABLAS] AS [Ta]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ta].[CODTAB]=[J].[CODTAB]) ORDERED FORWARD),1,59,4,Clustered Index Seek,Clustered Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[TABLAS].[PK_TABLAS] AS [Ta]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ta].[CODTAB]=[J].[CODTAB]) ORDERED FORWARD,[Ta].[FACTOR], [Ta].[CODTAB],1,0.0063285,7.9603E-05,65,0.01070036,[Ta].[FACTOR], [Ta].[CODTAB],NULL,PLAN_ROW,0,53.93469<br /> |--Hash Match(Cache, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ta].[CODTAB]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ta].[CODTAB]=[Ta].[CODTAB])),1,60,3,Hash Match,Cache,HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ta].[CODTAB]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ta].[CODTAB]=[Ta].[CODTAB]),NULL,1,0,0.0003009376,16,0.01384352,[Ind].[VALOR],NULL,PLAN_ROW,0,53.93469<br /> |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1011])),1,61,60,Nested Loops,Inner Join,OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1011]),NULL,1,0,4.18E-06,71,0.0133004,[Ind].[VALOR],NULL,PLAN_ROW,0,2<br /> |--Stream Aggregate(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1011]=MAX([N].[FECHA]))),1,62,61,Stream Aggregate,Aggregate,NULL,[Expr1011]=MAX([N].[FECHA]),1,0,1E-07,15,0.006646221,[Expr1011],NULL,PLAN_ROW,0,2<br /> | |--Top(1),1,63,62,Top,Top,NULL,NULL,1,0,1E-07,63,0.006646021,[N].[FECHA],NULL,PLAN_ROW,0,2<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[INDICES].[PK_INDICES] AS [N]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[N].[CODTAB]=[Ta].[CODTAB] AND [N].[FECHA] &lt;= 'May 10 2006 12:00AM') ORDERED BACKWARD),1,64,63,Clustered Index Seek,Clustered Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[INDICES].[PK_INDICES] AS [N]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[N].[CODTAB]=[Ta].[CODTAB] AND [N].[FECHA] &lt;= 'May 10 2006 12:00AM') ORDERED BACKWARD,[N].[FECHA],1,0.003322911,0.0005054868,63,0.006645821,[N].[FECHA],NULL,PLAN_ROW,0,2<br /> |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[INDICES].[PK_INDICES] AS [Ind]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ind].[CODTAB]=[Ta].[CODTAB] AND [Ind].[FECHA]=[Expr1011]) ORDERED FORWARD),1,69,61,Clustered Index Seek,Clustered Index Seek,OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Vestiditos].[dbo].[INDICES].[PK_INDICES] AS [Ind]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ind].[CODTAB]=[Ta].[CODTAB] AND [Ind].[FECHA]=[Expr1011]) ORDERED FORWARD,[Ind].[VALOR],1,0.0063285,7.9607E-05,63,0.006645821,[Ind].[VALOR],NULL,PLAN_ROW,0,2<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.<br />Leonardo Da Vinci<br /></font id="size1"><br /><font size="1"> Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  8. derrickleggett New Member

    I don't suppose there is an MDAC difference between the two machines is there? If so, see if that makes a difference.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

    XP WS has the last MDAC.
    W98 WS at least MDAC 2.5, and no one in the client want to change anything in that WS.
  9. cmdr_skywalker New Member

    how many records are displayed, all at once? The application can project a fast fetch by showing the first N records while fetching the data in background (ex. app client cursor).

    May the Almighty God bless us all!
    www.empoweredinformation.com

Share This Page