SLOW QUERY 1

[johnny45]

I'm trying to run the following query through excel VBA to an accpac 5.5 pervasive database:

SELECT ICITMV.VENDNUM, ICITMV.VENDNAME, ICILOC.ITEMNO, ICILOC.QTYONHAND, ICILOC.QTYONORDER, ICILOC.QTYSALORDR, ICILOC.QTYONHAND-ICILOC.QTYSALORDR AS NET_AVAIL,  ICSTATI.SALESQTY, ICSTATI.SALESQTY/365 AS DDD
FROM ICSTATI INNER JOIN (ICILOC INNER JOIN ICITMV ON ICILOC.ITEMNO = ICITMV.ITEMNO) ON ICSTATI.ITEMNO = ICITMV.ITEMNO
WHERE (((ICITMV.VENDNUM)='22280') AND ((ICSTATI.YEAR)='2008') AND ((ICSTATI.PERIOD)=10));

I get this when I run from Aqua Studio:
60 record(s) selected [Fetch MetaData: 15/ms] [Fetch Data: 72407/ms]

[Executed: 26/01/09 9:33:00 EST AM ] [Execution: 5141/ms]

Is there a way to make this faster ? Is the SQL badly built ?

 

[mirtheil]

Not knowing the database, I can't say if it's "bad" SQL or not. It's possible that the indexes are not being used.
Specifically, you'll want to check on:
ICITMV.VENDNUM
ICSTATI.YEAR
ICILOC.ITEMNO
ICITMV.ITEMNO

Make sure those are indexed.
You could also use the Query Plan and Query Plan Viewer to see how the engine is processing the query.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[johnny45]

Thanks for the post

 

[mirtheil]

You're welcome. Thanks for the star. I hope you can resolve the slow performance.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com