6 Joins in Jet hangs my access query 2

[XaRz]

I'm trying to export an TSQL select to JET and I'm not able to do it, because the query hangs msaccess.
Here comes the JET SQL command:

SELECT ALVC.ALVC_CODI, FRVC.FRVC_CODI, SECC.SECC_DESC, Max(ALVCTOTL.ALVCTOTL_IMPORTE_BRUTO) AS Expr1, CLIE.CLIE_DESCUENTO2, ALVC.ALVC_IMPORTE_NETO

FROM FRVC, FRVL INNER JOIN (ALVL INNER JOIN (ALVCTOTL INNER JOIN (ALVC INNER JOIN (CLIE INNER JOIN SECC ON CLIE.CLIE_PK=SECC.SECC_CLIE_PK) ON ALVC.ALVC_SECC_PK=SECC.SECC_PK) ON ALVCTOTL.ALVCTOTL_ALVC_PK=ALVC.ALVC_PK) ON ALVL.ALVL_ALVC_PK=ALVC.ALVC_PK) ON (FRVL.FRVL_LINEA_PADRE_PK=ALVL.ALVL_PK) AND (FRVL.FRVL_TB_PADRE='ALVL')

WHERE ((CLIE.CLIE_CODI)='05083') AND ((ALVC.ALVC_FECHA_ALTA)>#6/30/2005#) AND ((FRVC.FRVC_ANULADO)=0) AND ((ALVC.ALVC_ANULADO)=0)

GROUP BY ALVC.ALVC_CODI, FRVC.FRVC_CODI, SECC.SECC_DESC, CLIE.CLIE_DESCUENTO2, ALVC.ALVC_IMPORTE_NETO;

And here the Ibexperts TSQL command that works like a charm:

SELECT alvc_codi,frvc_codi,secc_desc,MAX(alvctotl_importe_bruto), clie_descuento2,alvc_importe_neto

FROM CLIE

    JOIN SECC ON CLIE_PK = SECC_CLIE_PK
    JOIN ALVC ON ALVC_SECC_PK = SECC_PK
    JOIN ALVCTOTL ON ALVCTOTL_ALVC_PK = ALVC_PK
    JOIN ALVL on ALVL_ALVC_PK = ALVC_PK
    JOIN FRVL on (FRVL_LINEA_PADRE_PK = ALVL_PK AND FRVL_TB_PADRE ='ALVL')
    JOIN FRVC ON FRVC_PK = FRVL_FRVC_PK

WHERE CLIE_CODI  = '05083'
AND FRVC_FECHA_ALTA > '06/30/2005'

and FRVC_ANULADO = 0
and ALVC_ANULADO = 0

group BY clie_descuento2, secc_desc, alvc_codi, alvc_importe_neto, frvc_codi

I presume the Problem is the join with this syntax:

JOIN FRVL on (FRVL_LINEA_PADRE_PK = ALVL_PK AND FRVL_TB_PADRE ='ALVL')


Wich in JET SQL I presume that don't understand well
But I've tried to put it in the where clause like this:

SELECT ALVC.ALVC_CODI, FRVC.FRVC_CODI, SECC.SECC_DESC, Max(ALVCTOTL.ALVCTOTL_IMPORTE_BRUTO) AS Expr1, CLIE.CLIE_DESCUENTO2, ALVC.ALVC_IMPORTE_NETO

FROM FRVC, FRVL INNER JOIN (ALVL INNER JOIN (ALVCTOTL INNER JOIN (ALVC INNER JOIN (CLIE INNER JOIN SECC ON CLIE.CLIE_PK=SECC.SECC_CLIE_PK) ON ALVC.ALVC_SECC_PK=SECC.SECC_PK) ON ALVCTOTL.ALVCTOTL_ALVC_PK=ALVC.ALVC_PK) ON ALVL.ALVL_ALVC_PK=ALVC.ALVC_PK) ON (FRVL.FRVL_LINEA_PADRE_PK=ALVL.ALVL_PK) 

WHERE ((CLIE.CLIE_CODI)='05083') AND ((ALVC.ALVC_FECHA_ALTA)>#6/30/2005#) AND ((FRVC.FRVC_ANULADO)=0) AND ((ALVC.ALVC_ANULADO)=0)AND ((FRVL.FRVL_TB_PADRE='ALVL'))

GROUP BY ALVC.ALVC_CODI, FRVC.FRVC_CODI, SECC.SECC_DESC, CLIE.CLIE_DESCUENTO2, ALVC.ALVC_IMPORTE_NETO;

But it hangs as same way than the other jet command.
Someone has any hints?

 

[PCLewis]

Are there a lot of record in FRVC?

In your JET statement, I see "FROM FRVC, FRVL ..." which could be a cause for delays, especially if there are a lot of records in both tables.

I note that in you TSQL command, you are pulling records form one table (effectively one due to the joins) so your two SQL's are not the same.

Note that with the comma in the FROM clause, any database that I know of will perform a cartesian product of both sets of data. So if there are 5000 records in FRVC, and 1000 in the other joined tables, you'll cause Access to create a temporary table with room for all fields in the cross product and 5000 * 1000 records.

I don't think you intended for that? It might be the cause for hanging.

 

[XaRz]

In the last minute I solved it!. In fact you are right JET SQL COMMAND WAS INCOMPLETE!

In one hang access has droped one join and its condition,

The correct SQL that works in JET is:

SELECT ALVC.ALVC_CODI, FRVC.FRVC_CODI, SECC.SECC_DESC, Max(ALVCTOTL.ALVCTOTL_IMPORTE_BRUTO) AS Expr1, CLIE.CLIE_DESCUENTO2, ALVC.ALVC_IMPORTE_NETO
FROM (FRVL INNER JOIN (ALVL INNER JOIN (ALVCTOTL INNER JOIN (ALVC INNER JOIN (CLIE INNER JOIN SECC ON CLIE.CLIE_PK=SECC.SECC_CLIE_PK) ON ALVC.ALVC_SECC_PK=SECC.SECC_PK) ON ALVCTOTL.ALVCTOTL_ALVC_PK=ALVC.ALVC_PK) ON ALVL.ALVL_ALVC_PK=ALVC.ALVC_PK) ON FRVL.FRVL_LINEA_PADRE_PK=ALVL.ALVL_PK) INNER JOIN FRVC ON FRVL.FRVL_FRVC_PK=FRVC.FRVC_PK
WHERE (((FRVL.FRVL_TB_PADRE)='ALVL') AND ((CLIE.CLIE_CODI)='05083') AND ((ALVC.ALVC_FECHA_ALTA)>#6/30/2005#) AND ((FRVC.FRVC_ANULADO)=0) AND ((ALVC.ALVC_ANULADO)=0))
GROUP BY ALVC.ALVC_CODI, FRVC.FRVC_CODI, SECC.SECC_DESC, CLIE.CLIE_DESCUENTO2, ALVC.ALVC_IMPORTE_NETO;

And more important! I've translated to HSQL for Openoffice.org 2 beta2 Base program: Here the code that works!:

 SELECT "ALVC"."ALVC_CODI", "FRVC"."FRVC_CODI", "SECC"."SECC_DESC", MAX("ALVCTOTL"."ALVCTOTL_IMPORTE_BRUTO" ), "CLIE"."CLIE_DESCUENTO2", "ALVC"."ALVC_IMPORTE_NETO" 

FROM "CLIE" "CLIE", "SECC" "SECC", "ALVC" "ALVC", "ALVCTOTL" "ALVCTOTL", "ALVL" "ALVL", "FRVL" "FRVL", "FRVC" "FRVC" 

WHERE ( "CLIE"."CLIE_PK" = "SECC"."SECC_CLIE_PK" AND "SECC"."SECC_PK" = "ALVC"."ALVC_SECC_PK" AND "ALVC"."ALVC_PK" = "ALVCTOTL"."ALVCTOTL_ALVC_PK" AND "ALVC"."ALVC_PK" = "ALVL"."ALVL_ALVC_PK" AND "ALVL"."ALVL_PK" = "FRVL"."FRVL_LINEA_PADRE_PK" AND "FRVC"."FRVC_PK" = "FRVL"."FRVL_FRVC_PK" ) AND ( ( "FRVL"."FRVL_TB_PADRE" = 'ALVL' AND "CLIE"."CLIE_CODI" = '05083' AND "FRVC"."FRVC_FECHA_ALTA" > {D '2005-06-30' } AND "FRVC"."FRVC_ANULADO" = 0 AND "ALVC"."ALVC_ANULADO" = 0 ) ) 
GROUP BY "ALVC"."ALVC_CODI", "SECC"."SECC_DESC", "CLIE"."CLIE_DESCUENTO2", "ALVC"."ALVC_IMPORTE_NETO", "FRVC"."FRVC_CODI"

Thanks anyway!

 

[PCLewis]

For the record, Access may not have been hanging - just doing the task which happened to be a lot bigger than you had planned.

The 2nd query in your last post is pretty scary. I say scary because Access will first create a temporary table of all the records in CLIE times all records in SECC times all in ALVC times all in ALVCTOTL times all in ALVL times all in FRVL times all in FRVC!

If there ar eonly a small number of records in these tables then it probably runs fast. BUt all it takes is for those tables to have 100 records each and you would have 100 ^ 7 records for Access to filter.

 

[XaRz]

Thanks for the tip.

The second query you mention in last post is an HSQL query generated by Openoffice.org 2.0beta2 db program. In fact I can't understand completely your reasoning because I'm too newbie yet in access or SQL's query construction.

By the way, first I need to consult the specs of HSQL and second the HSQL builder that OObase uses. Understand that this is a beta HSQL generation program...and perhaps the SQL resultant is not well depurated.

Best regards,

 

[lespaul]

you may want to read Understanding SQL Joins to get a grasp of the cartesian join that PCLewis mentioned.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Here the JetSQL version of the T-SQL code:
SELECT alvc_codi,frvc_codi,secc_desc,MAX(alvctotl_importe_bruto), clie_descuento2,alvc_importe_neto
FROM [highlight]((((([/highlight]CLIE
JOIN SECC ON CLIE_PK = SECC_CLIE_PK[highlight])[/highlight]
JOIN ALVC ON ALVC_SECC_PK = SECC_PK[highlight])[/highlight]
JOIN ALVCTOTL ON ALVCTOTL_ALVC_PK = ALVC_PK[highlight])[/highlight]
JOIN ALVL on ALVL_ALVC_PK = ALVC_PK[highlight])[/highlight]
JOIN FRVL on (FRVL_LINEA_PADRE_PK = ALVL_PK AND FRVL_TB_PADRE ='ALVL')[highlight])[/highlight]
JOIN FRVC ON FRVC_PK = FRVL_FRVC_PK
WHERE CLIE_CODI = '05083'
AND FRVC_FECHA_ALTA > [highlight]#2005-06-30#[/highlight]
and FRVC_ANULADO = 0
and ALVC_ANULADO = 0
group BY clie_descuento2, secc_desc, alvc_codi, alvc_importe_neto, frvc_codi

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886