View Incident Report

Serious

Reported By: Michael Eubanks
Reported On: 11/17/2003
For: Version 3.26 Build 1

# 1456 SQL SELECT Performance Slower Than Necessary with Multiple Indexed AND Conditions in WHERE Clause

Why are the first two queries so slow and how can I speed them up?

There are single segmented indexs on "Date" "Transaction Type" and "Item Number". The transaction file is large, about 500 meg.

Using Delphi 6 DBISAM 3.26 C/S configuration with two computers on 100Mbit network. Server is Windows 2000, Clint is Windows XP.

Query 1:

Select "Unique ID" from "Transaction File" where 
"Date" between '2003-10-25' and '2003-10-25' and "Transaction Type" =
38 and "Item Number" = 502

This query takes 19 seconds returns 142 rows

Query 2, remove the filter on "Item Number":

Select "Unique ID" from "Transaction File" where 
"Date" between '2003-10-25' and '2003-10-25' and "Transaction Type" =
38

This query also takes 19 seconds and returns 1379 rows


Query 3, remove the filter on "Transacton Type":

Select "Unique ID" from "Transaction File" where 
"Date" between '2003-10-25' and '2003-10-25' 

This query takes .63 seconds and returns 6189 rows

Resolution

Fixed Problem on 11/19/2003 in version 3.27 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap