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
RE: [UV] Consult about SQL
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') ORLINFAC.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
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
Re: Consult about SQL
You might try creating indexes or using the EXPLAIN clause to see if they are being used. --- Cesar Riba [EMAIL PROTECTED] escribió: 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 Los mejores usados y las más tentadoras ofertas de 0km están en Yahoo! Autos. Comprá o vendé tu auto en http://autos.yahoo.com.ar -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
