Slow Query

[dawtes]

I am trying to use this query to create a view and use it in crystal report. However, this query is considerably slow. I am wondering if there is a way to make this query run faster.
Thanks



SELECT DISTINCT G.CSECNUM,E.IPLINENO, LL.LETTING, E.EIITEM, I.IDESCR, P.CONTID ,I.IUNITS,E.IQTY,P.CPROJNUM, P.PRROUTE
FROM PROPOSAL P, PROPITEM Q, ITEMLIST I, PROJECT J, PROPPROJ X, ESTITEM E, ESTCATG G, LETPROP LL
WHERE I.ITEM = E.EIITEM
AND G.PCN = J.PCN
AND G.CSECNUM = Q.SECTION
AND J.PCN = X.PCN
AND P.CONTID = X.CONTID
AND E.PCN = J.PCN
AND P.CSPECYR = I.ISPECYR
AND E. IPLINENO = Q.IPLINENO
AND LL.LCONTID = P.CONTID
AND Q.LINEFLAG = 'L'
AND LL.LETTING='05082601'

 

[smcnulty2000]

In this particular case you might get more info in one of the dba based forums. Also your database brand and version would be good to know. Methods for performance tuning are different from db to db.

That being said, I think the keyword "distinct" is slaughtering your performance. Distinct requires a serious amount of overhead to run because it causes the database to have to sort and discard records. It always affects performance.

If you decide to remove the word "distinct" from the query, you can get some similar results in Crystal using grouping.

I can't say for sure if that will be faster. But it might be.

If you really want to dig into it, you can find out how to do an 'explain plan' for your database type. This shows what order joins are occurring in and how the indexes are used. They are rather cryptic if you've never seen one before, though.

You also might be either avoiding an index or using one where you shouldn't. Or your index needs rebuilding if you are using it appropriately. The list is actually pretty long as to what might be going on. These points might get you started.

Good luck.

Scott.