Board index » delphi » a (same) join with two tables: great performance with (same) 100K rows.

2006-07-18 12:18:34 AM
delphi55
search started: 1153152654207
search ended: 1153152654363 = 157 milliseconds
SELECT DISTINCT
ORDER_HEADER.STORE,ORDER_HEADER.ORDERS,ORDER_DETAIL.PURCHASE_NO,
ORDER_HEADER.LAST_NAME,ORDER_HEADER.FIRST_NAME,ORDER_DETAIL.INSTALLATION_DATE,
ORDER_DETAIL.LABOR_END_DATE,ORDER_HEADER.ADDRESS1,ORDER_HEADER.CITY,
ORDER_HEADER.PHONE1,ORDER_HEADER.PHONE2,ORDER_HEADER.STATE,ORDER_HEADER.ZIPCODE,
ORDER_HEADER.FAX1,ORDER_HEADER.BEEPER,ORDER_HEADER.CELLULAR,
ORDER_HEADER.MIDDLE_INIT,ORDER_HEADER.ADDRESS2,ORDER_HEADER.CLIENT
FROM ORDER_HEADER
LEFT OUTER JOIN
ORDER_DETAIL ON ORDER_HEADER.CLIENT=ORDER_DETAIL.CLIENT AND
ORDER_HEADER.STORE=ORDER_DETAIL.STORE AND
ORDER_HEADER.ORDERS=ORDER_DETAIL.ORDERS
WHERE ORDER_HEADER.WAREHOUSE = 'Wilmington_MA' AND
LAST_NAME>= 'A' AND
LAST_NAME <= 'AZZZZZZ'
ORDER BY LAST_NAME
vs (the slow one)
search started: 1153152863128
search ended: 1153152883894 = 20766 milliseconds
WHERE ORDER_HEADER.WAREHOUSE = 'Wilmington_MA'
AND (ORDER_DETAIL.INSTALLATION_DATE_YYMMDD>='060717'
AND ORDER_DETAIL.INSTALLATION_DATE_YYMMDD<='060717'
OR ORDER_DETAIL.LABOR_END_DATE_YYMMDD IS NOT NULL AND
ORDER_DETAIL.LABOR_END_DATE_YYMMDD>='060717'
AND ORDER_DETAIL.INSTALLATION_DATE_YYMMDD<='060717')
ORDER BY ORDER_HEADER.LAST_NAME,
ORDER_DETAIL.INSTALLATION_DATE
is there any way to speed it up?
 

Mehmet F. Erten writes:
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:
blogs.teamb.com/craigstuntz/articles/403.aspx
 

The problem is probably in Left OUTER Join. If you have Order header and
Order Details i don't understand why you need outer join. Inner join will
give you headers which has some details and much better performance. If you
notice that query optimizer didn't use some index the trick is to put som
dummy expression into where condition. Also duplicate condition on
LABOR_END_DATE is not necessery. it is enough to put just>= condition and
delete is not null part.
"Mehmet F. Erten" <XXXX@XXXXX.COM>writes

with 100 thousands orders I have significant performance issue to resolve.
(I will attempt to see it when it is one Million orders) Current gdb size
1.2GB
Here is the reason for the query selection/columns:
There are users in the multiple locations (warehouses) each needs to see
his/her warehouse's orders.
A Order Header should have Order Details But May Not Yet (we need to see
them as well to work with)
A job has a Start date and may or may not have an End date.
All the active jobs should be selected: Today we need to see if starting
today, ending today and on-going today.
The following queries are all the same except the where clause which is
dynamically build by the user interaction (selection)
I tried VIEWS, INDEXES so far no good.
I tried and will try SUBQUERY more but looks like limited column selection
slows it down so it won't have any use.
(see the previous Order Detail Only Queries)
Only Last Name search with the less amount of selected data works perfect.
1: SELECT DISTINCT
ORDER_HEADER.STORE,ORDER_HEADER.ORDERS,ORDER_DETAIL.PURCHASE_NO,ORDER_HEADER.LAST_NAME,ORDER_HEADER.FIRST_NAME,ORDER_DETAIL.INSTALLATION_DATE,ORDER_DETAIL.LABOR_END_DATE,ORDER_HEADER.ADDRESS1,ORDER_HEADER.CITY,ORDER_HEADER.PHONE1,ORDER_HEADER.PHONE2,ORDER_HEADER.STATE,ORDER_HEADER.ZIPCODE,ORDER_HEADER.FAX1,ORDER_HEADER.BEEPER,ORDER_HEADER.CELLULAR,ORDER_HEADER.MIDDLE_INIT,ORDER_HEADER.ADDRESS2,ORDER_HEADER.CLIENT
FROM ORDER_HEADER LEFT OUTER JOIN ORDER_DETAIL ON
ORDER_HEADER.CLIENT=ORDER_DETAIL.CLIENT AND
ORDER_HEADER.STORE=ORDER_DETAIL.STORE AND
ORDER_HEADER.ORDERS=ORDER_DETAIL.ORDERS WHERE ORDER_HEADER.WAREHOUSE =
'Wilmington_MA' AND (ORDER_DETAIL.INSTALLATION_DATE_YYMMDD='060719' OR
ORDER_DETAIL.LABOR_END_DATE_YYMMDD>='060719' AND
ORDER_DETAIL.INSTALLATION_DATE_YYMMDD<'060719') ORDER BY
ORDER_HEADER.LAST_NAME, ORDER_DETAIL.INSTALLATION_DATE
1153319485488 One Date Search within start - end date range
1153319506331 = 20843 milliseconds
2: SELECT DISTINCT
ORDER_HEADER.STORE,ORDER_HEADER.ORDERS,ORDER_DETAIL.PURCHASE_NO,ORDER_HEADER.LAST_NAME,ORDER_HEADER.FIRST_NAME,ORDER_DETAIL.INSTALLATION_DATE,ORDER_DETAIL.LABOR_END_DATE,ORDER_HEADER.ADDRESS1,ORDER_HEADER.CITY,ORDER_HEADER.PHONE1,ORDER_HEADER.PHONE2,ORDER_HEADER.STATE,ORDER_HEADER.ZIPCODE,ORDER_HEADER.FAX1,ORDER_HEADER.BEEPER,ORDER_HEADER.CELLULAR,ORDER_HEADER.MIDDLE_INIT,ORDER_HEADER.ADDRESS2,ORDER_HEADER.CLIENT
FROM ORDER_HEADER LEFT OUTER JOIN ORDER_DETAIL ON
ORDER_HEADER.CLIENT=ORDER_DETAIL.CLIENT AND
ORDER_HEADER.STORE=ORDER_DETAIL.STORE AND
ORDER_HEADER.ORDERS=ORDER_DETAIL.ORDERS WHERE ORDER_HEADER.WAREHOUSE =
'Wilmington_MA' AND ORDERS>= '99' ORDER BY ORDERS
1153319524831 Orders Search
1153319597925 = 73094 milliseconds (110K inserted rows)
2: SELECT DISTINCT
ORDER_HEADER.STORE,ORDER_HEADER.ORDERS,ORDER_DETAIL.PURCHASE_NO,ORDER_HEADER.LAST_NAME,ORDER_HEADER.FIRST_NAME,ORDER_DETAIL.INSTALLATION_DATE,ORDER_DETAIL.LABOR_END_DATE,ORDER_HEADER.ADDRESS1,ORDER_HEADER.CITY,ORDER_HEADER.PHONE1,ORDER_HEADER.PHONE2,ORDER_HEADER.STATE,ORDER_HEADER.ZIPCODE,ORDER_HEADER.FAX1,ORDER_HEADER.BEEPER,ORDER_HEADER.CELLULAR,ORDER_HEADER.MIDDLE_INIT,ORDER_HEADER.ADDRESS2,ORDER_HEADER.CLIENT
FROM ORDER_HEADER LEFT OUTER JOIN ORDER_DETAIL ON
ORDER_HEADER.CLIENT=ORDER_DETAIL.CLIENT AND
ORDER_HEADER.STORE=ORDER_DETAIL.STORE AND
ORDER_HEADER.ORDERS=ORDER_DETAIL.ORDERS WHERE ORDER_HEADER.WAREHOUSE =
'Wilmington_MA' AND LAST_NAME>= 'D' AND LAST_NAME <= 'DZZZZZZ' ORDER BY
LAST_NAME
1153319611081 Last Name Search
1153319689550 = 78469 milliseconds (110K inserted rows)
2: SELECT DISTINCT
ORDER_HEADER.STORE,ORDER_HEADER.ORDERS,ORDER_DETAIL.PURCHASE_NO,ORDER_HEADER.LAST_NAME,ORDER_HEADER.FIRST_NAME,ORDER_DETAIL.INSTALLATION_DATE,ORDER_DETAIL.LABOR_END_DATE,ORDER_HEADER.ADDRESS1,ORDER_HEADER.CITY,ORDER_HEADER.PHONE1,ORDER_HEADER.PHONE2,ORDER_HEADER.STATE,ORDER_HEADER.ZIPCODE,ORDER_HEADER.FAX1,ORDER_HEADER.BEEPER,ORDER_HEADER.CELLULAR,ORDER_HEADER.MIDDLE_INIT,ORDER_HEADER.ADDRESS2,ORDER_HEADER.CLIENT
FROM ORDER_HEADER LEFT OUTER JOIN ORDER_DETAIL ON
ORDER_HEADER.CLIENT=ORDER_DETAIL.CLIENT AND
ORDER_HEADER.STORE=ORDER_DETAIL.STORE AND
ORDER_HEADER.ORDERS=ORDER_DETAIL.ORDERS WHERE ORDER_HEADER.WAREHOUSE =
'Wilmington_MA' AND LAST_NAME>= 'C' AND LAST_NAME <= 'CZZZZZZ' ORDER BY
LAST_NAME
1153319693706 Last Name Search
1153319694003 = 297 milliseconds (inserted rows not hit)
2: SELECT DISTINCT
ORDER_HEADER.STORE,ORDER_HEADER.ORDERS,ORDER_DETAIL.PURCHASE_NO,ORDER_HEADER.LAST_NAME,ORDER_HEADER.FIRST_NAME,ORDER_DETAIL.INSTALLATION_DATE,ORDER_DETAIL.LABOR_END_DATE,ORDER_HEADER.ADDRESS1,ORDER_HEADER.CITY,ORDER_HEADER.PHONE1,ORDER_HEADER.PHONE2,ORDER_HEADER.STATE,ORDER_HEADER.ZIPCODE,ORDER_HEADER.FAX1,ORDER_HEADER.BEEPER,ORDER_HEADER.CELLULAR,ORDER_HEADER.MIDDLE_INIT,ORDER_HEADER.ADDRESS2,ORDER_HEADER.CLIENT
FROM ORDER_HEADER LEFT OUTER JOIN ORDER_DETAIL ON
ORDER_HEADER.CLIENT=ORDER_DETAIL.CLIENT AND
ORDER_HEADER.STORE=ORDER_DETAIL.STORE AND
ORDER_HEADER.ORDERS=ORDER_DETAIL.ORDERS WHERE ORDER_HEADER.WAREHOUSE =
'Wilmington_MA' AND LAST_NAME>= 'E' AND LAST_NAME <= 'EZZZZZZ' ORDER BY
LAST_NAME
1153319697488 Last Name Search
1153319697519 = 31 milliseconds
2: SELECT DISTINCT
ORDER_HEADER.STORE,ORDER_HEADER.ORDERS,ORDER_DETAIL.PURCHASE_NO,ORDER_HEADER.LAST_NAME,ORDER_HEADER.FIRST_NAME,ORDER_DETAIL.INSTALLATION_DATE,ORDER_DETAIL.LABOR_END_DATE,ORDER_HEADER.ADDRESS1,ORDER_HEADER.CITY,ORDER_HEADER.PHONE1,ORDER_HEADER.PHONE2,ORDER_HEADER.STATE,ORDER_HEADER.ZIPCODE,ORDER_HEADER.FAX1,ORDER_HEADER.BEEPER,ORDER_HEADER.CELLULAR,ORDER_HEADER.MIDDLE_INIT,ORDER_HEADER.ADDRESS2,ORDER_HEADER.CLIENT
FROM ORDER_HEADER LEFT OUTER JOIN ORDER_DETAIL ON
ORDER_HEADER.CLIENT=ORDER_DETAIL.CLIENT AND
ORDER_HEADER.STORE=ORDER_DETAIL.STORE AND
ORDER_HEADER.ORDERS=ORDER_DETAIL.ORDERS WHERE ORDER_HEADER.WAREHOUSE =
'Wilmington_MA' AND (ORDER_DETAIL.INSTALLATION_DATE_YYMMDD='060719' OR
ORDER_DETAIL.LABOR_END_DATE_YYMMDD>='060719' AND
ORDER_DETAIL.INSTALLATION_DATE_YYMMDD<'060719') ORDER BY
ORDER_HEADER.LAST_NAME, ORDER_DETAIL.INSTALLATION_DATE
1153319714613 One Date Search within start - end date range
1153319738644 24031 milliseconds