Slow performance for a SQL query
I have a SQL query I am running, but it is experiencing significant slowdown. The main table - has over 1 million records and a unique key of cusID. It has a one to many relationship with the other two tables mentioned. The query code is as follows:
Software/Hardware used:
Select customer.cusID,customer.VisitDate, Customer.ExitDate, Customerchrgs.chrgsDate,
mainCharge.cpt_code,
mainCharge.chrgs_code,
Customer.cust_type
from customer, Customerchrge, mainCharge
where ((customer.VisitDate >= 20080318) and
(Customer.ExitDate <= 20080418))
and
((Customer.cusID = Customerchrgs.cusID) and
(Customer.cusID = mainCharge.cusID));
customer index= cusID
mainCharge index = CusID + SeqNo
Customerchrgs = CusID + SeqNo
Why might the peformance of this query be so poor? Software/Hardware used:
ASKED:
October 23, 2008 6:22 PM
UPDATED:
October 24, 2008 12:45 AM
RELATED QUESTIONS
- Performance degraded after changing optimizer mode
- Challenge improving query performance on large tables
- Filtering a select based on the value of one column and the max value of another column
- Different SQL index used, dynamic SQL using STRSQL vs. SQLRPGLE in a job
- Combining Access 2003 Queries for Dates Criteria
Answer Wiki:
Have you had a look at the execution plan for the query ?
If not, have a look at this: <a href="http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx">SQL Server Query Execution Plan Analysis</a>
The execution plan is always helpful, it will tell you, among other things, if the indexes are in fact being used.
I think an index on VisitDate and ExitDate could help to improve the query's performance, and I would recommend to compare the fields to a value of its exact data type, since some databases (such as Oracle) does not use indexes when an implicit data type conversion is made during the query execution.
------------------------------------------------
One reason is going to be because your Customer table is not indexed correctly. You need an index on the VisitDate and ExitDate columns as well as the CusId column.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
