The first thing to notice is that a file scan is being performed on the LINFAC table. This could be speeded with an index on each of the constraining columns LINFAC.EMP and LINFAC.TIPFAC.
The inner joins are being performed via hash lookups, the fastest available method. There are 23469 matches between tables LINFAC and GCMCL and 131 matches between GCML and GCAREPRE, based on the join conditions specified. There may be some gain to be had by indexing the foreign key columns LINFAC.CODCLI and GCML.CODREP1. ----- Original Message ----- From: "Cesar Riba" <[EMAIL PROTECTED]> Date: Fri, 2 Apr 2004 10:37:21 +0200 To: "'U2 Users Discussion List'" <[EMAIL PROTECTED]> Subject: RE: [UV] Consult about SQL > Hello, > > Another question > > Because a SQL query executed in Universe it take 20 seconds and the same > query in MSAccess take 3 seconds. > > > This is the result of EXPLAIN of this query > > >SELECT LINFAC.GRUPART, SUM(LINFAC.BASPP * LINFAC.CAN) As SUMCANT, > GCAREPRE.NOMBRE AS NOMREPRE, > SQL+GCAREPRE.CODIGO AS CODREPRE > SQL+FROM > SQL+GCAREPRE INNER JOIN GCMCL ON GCAREPRE.CODIGO = GCMCL.CODREP1 > SQL+INNER JOIN LINFAC ON [EMAIL PROTECTED] = LINFAC.CODCLI > SQL+WHERE LINFAC.EMP = 1 AND > SQL+(LINFAC.TIPFAC='01' OR LINFAC.TIPFAC='03' OR LINFAC.TIPFAC='05' OR > LINFAC.TIPFAC='06' OR LINFAC.TIPFAC='10') > SQL+GROUP BY LINFAC.GRUPART, GCAREPRE.CODIGO, GCAREPRE.NOMBRE HAVING > SUM(LINFAC.BASPP*LINFAC.CAN) IS NOT NULL > SQL+EXPLAIN; > Optimizing query block 0 > Table restriction: LINFAC.EMP = 1 AND (LINFAC.TIPFAC = '10' OR > (((LINFAC.TIPFAC= '01' OR LINFAC.TIPFAC = '03') OR LINFAC.TIPFAC = '05') OR > LINFAC.TIPFAC = '06')) > > Driver source: LINFAC > Access method: file scan > > 1st join primary: LINFAC est. cost: 30445 > secondary: GCMCL est. cost: 23469 > type: hashed join (primary key) > > 2nd join primary: GCMCL est. cost: 23469 > secondary: GCAREPRE est. cost: 131 > type: hashed join (primary key) > > Grouped by: GRUPARTCODIGONOMBRE > > > Thanks, > > -----Mensaje original----- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > nombre de Ray Wurlod > Enviado el: jueves, 01 de abril de 2004 22:44 > Para: U2 Users Discussion List > Asunto: Re: [UV] Consult about SQL > > > The best way to speed inner joins is to index the column that is the foreign > key. > > For example, if your join is of the form: > SELECT T1.ORDERNO, T2.CUSTNO, T2.CUSTNAME > FROM ORDERS T1, CUSTOMERS T2 > WHERE T1.CUSTNO = T2.CUSTNO ; > > then you need an index on ORDERS.CUSTNO > > In other cases, depending on exactly what the query is, you can also > sometimes obtain benefit by rewriting the SQL to use sub-queries, unions, > and other techniques. > > > ----- Original Message ----- > From: "Cesar Riba" <[EMAIL PROTECTED]> > Date: Thu, 1 Apr 2004 11:10:18 +0200 > To: <[EMAIL PROTECTED]> > Subject: Consult about SQL > > > Hello, > > > > > > I'm a problem when use a SQL query that have one or various INNER JOIN > this > > is very very slow. How I can accelerate this tipus of query. > > > > > > > > Thanks. > > > > > > CÚsar > > > > > > > > > > > > > > -- > > u2-users mailing list > > [EMAIL PROTECTED] > > http://www.oliver.com/mailman/listinfo/u2-users > > -- > u2-users mailing list > [EMAIL PROTECTED] > http://www.oliver.com/mailman/listinfo/u2-users > > -- > u2-users mailing list > [EMAIL PROTECTED] > http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users