Slow performance for a SQL query

Tags:
SQL performance
SQL Query
SQL Server performance
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:
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?
ASKED: October 23, 2008  6:22 PM
UPDATED: October 24, 2008  12:45 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following