Query

[emuye]

Hi,
I want to use the below query to generate a report using Cr 8.5 however, when i run this query it is exteremly slow .I do not really know why that is happening and i really appreciate if you throw your help how i can enhance this query and get my report at a resonable amount of time.any suggestion is valuable to me...
thank you





select DISTINCT B.DATELET,P.CCNTY1,P.CFACSSUP, P.CPROJNUM, P.PRROUTE,Q.IPLINENO,I.ITEM,I.IDESCR,Q.QTY,I.IUNITS
from LETPROP L, PROJECT R, PROPPROJ J, PROPOSAL P, PROPITEM Q, BIDLET B, BIDTABS C, ITEMLIST I
WHERE L.LETTING = B.LETTING
AND B.LETTING = C.LETTING
AND P.CSPECYR = I.ISPECYR
AND Q.PRPITEM = I.ITEM
AND L.LCONTID = P.CONTID
AND Q.CONTID = P.CONTID
AND C.VENDOR = L.AVENDOR
AND C.CALL = L.CALL
AND C.LINEFLAG = Q.LINEFLAG
AND C.IPLINENO = Q.IPLINENO
AND B.DATELET >= '01-JAN-04' AND B.DATELET < '01-JAN-05'
AND R.PCN = J.PCN
AND L.LETSTAT = 'A'

 

[Turkbear]

Hi,
Way too little info:

What database?
What Connection method?
How many records?
Is the Where clause being passed to the database?
Are the fields in the where clause indexed?

Help us help you..

 

[GJParker]

There are a number of problems with this sql

1. contains circular links i.e.

L to B to C back to L
to to
P to I to Q back to P

2. Unlinked table

R links to J and neither of these tables link to the others.

This SQL could take ages to run and I very much doubt would return the correct results.

Try this amended SQL and see what happens, alternatively do the linking in crystal and allow it to take care of the SQL generation.

select distinct B.DATELET,P.CCNTY1,P.CFACSSUP, P.CPROJNUM, P.PRROUTE,Q.IPLINENO,I.ITEM,I.IDESCR,Q.QTY,I.IUNITS
from LETPROP L, PROPOSAL P, PROPITEM Q, BIDLET B, ITEMLIST I
where P.CONTID = Q.CONTID and
Q.PRPITEM = I.ITEM and
P.CONTID = L.LCONTID and
L.LETTING = B.LETTING and
B.DATELET >= '01-JAN-04' AND B.DATELET < '01-JAN-05' and 
L.LETSTAT = 'A'

HTH

Gary Parker
MIS Data Analyst
Manchester, England