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

Reply via email to