Board index » delphi » Low speed of SELECT statement.

Low speed of SELECT statement.

Hi All,

I select data from a table which contains 70,000 records. It spend few
seconds to show the data.

However, I get data from master-details tables which contains 70,000
records. I use TQuery to select it. E.g. 'SELECT M.SONo, M.CustCode,
M.SODate, D.ItemCode, D.ItemQty, D.UnitPrice, D.ItemAmt FROM SO M, SOItem D
WHERE (M.SONo=D.SONo) AND (M.CustCode = "ABC")'. When the query set to
ACTIVE, it spend more time (around 1 minute...) to retrieve data. I tried to
add indexes to the tables (SONo, CustCode, SODate, ItemCode and so on). The
speed is better than no index but I feel that it does not accept.

How can I improve the speed?

Thanks a lot...

Jonathan Man

 

Re:Low speed of SELECT statement.


Quote
Paul wrote:

> However, I get data from master-details tables which contains 70,000
> records. I use TQuery to select it. E.g. 'SELECT M.SONo, M.CustCode,
> M.SODate, D.ItemCode, D.ItemQty, D.UnitPrice, D.ItemAmt FROM SO M, SOItem D
> WHERE (M.SONo=D.SONo) AND (M.CustCode = "ABC")'. When the query set to
> ACTIVE, it spend more time (around 1 minute...) to retrieve data. I tried to
> add indexes to the tables (SONo, CustCode, SODate, ItemCode and so on). The
> speed is better than no index but I feel that it does not accept.

        Adding indices is good.  

        Try rewriting your query as a JOIN -- the server may optimize it
better.

SELECT
  M.SONO, M.CUSTCODE, M.SODATE,
  D.ITEMCODE, D.ITEMQTY, D.UNITPRICE, D.ITEMAMT
FROM
  SO M
  INNER JOIN SOITEM D
    ON M.SONO = D.SONO
WHERE
  M.CUSTCODE = "ABC"

        Also, make sure you're not doing anything which would force a FetchAll,
such as using the Query's RecordCount property, doing a Locate, or using
a BDE Lookup field.  Run the query outside of the application -- if it's
faster in, say, SQL Explorer, then something inside of your app is
bogging down.

        If you're using InterBase, try running your query in a tool like
IB_WISQL (free from http://www.ibobjects.com) which will show you the
query PLAN.  Make sure that the PLAN is using the indices you created
and not the natural table order.

        HTH,

        -Craig

--
Craig Stuntz            Vertex Systems Corporation
Senior Developer        http://www.vertexsoftware.com

Other Threads