Slow SQL Query

30 pts.
Tags:
Slow SQL Query
SQL queries
SQL query optimization
when i run below all SQL Query simulataneously against 1500000 Rows its take 10 minutes i put the non-clustered index on the columns TATCallType ,CallTo the data type of both columns is varchar select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,0))='' and len(AuditData.CallTo)=10 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,1))='0' and len(AuditData.CallTo)=11 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,2))='00' and len(AuditData.CallTo)=12 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,2))='91' and len(AuditData.CallTo)=12 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,4))='0091' and len(AuditData.CallTo)=14 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,3))='910' and len(AuditData.CallTo)=13 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,4))='9191' and len(AuditData.CallTo)=14 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,4))='9100' and len(AuditData.CallTo)=14 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,4))='0091' and len(AuditData.CallTo)=15 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,2))='91' and len(AuditData.CallTo)=13 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,6))='009191' and len(AuditData.CallTo)=16 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,8))='00500003 ' and len(AuditData.CallTo)=18 select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,8))='00500004 ' and len(AuditData.CallTo)=18 please help me its urgent thanks in advance

Answer Wiki

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

First you need to get rid of the Substring function in the where clause. Using any functions against the columns in the where clause will kill your performance as your index becomes useless. Instead you should be doing something like this.

<pre>select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and AuditData.CallTo LIKE ’00500004%’ and len(AuditData.CallTo)=18 </pre>

There isn’t much you can do about the LEN() function. Your best bet would be to put a calculated column on the table (if your platform supports it) which has then length of the CallTo column in it, then index that column.

If you are using SQL 2005 or higher you can include the additional columns in the index so that key lookups aren’t needed.

If you are using a database which doesn’t support included columns add the columns being returned as indexed columns, with the columns in the where clause first, then the columns being returned after them.

Discuss This Question: 3  Replies

 
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
  • Denny Cherry
    What database platform is this? And what version?
    66,010 pointsBadges:
    report
  • Mxaxyogesh2002
    i m using SQL server 2000
    30 pointsBadges:
    report
  • Denny Cherry
    OK, with SQL 2000 most of what I posted above still applies. You'll want to still create the computed column, and index that and the other column in the table, then add the other columns you are returning as indexed columns.
    66,010 pointsBadges:
    report

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