July 13, 2009 at 4:38 am
Dear Friends,
its urgent plz help me
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
July 13, 2009 at 4:58 am
July 13, 2009 at 5:04 am
Thanks ,
i will check and get back to u...
July 13, 2009 at 5:23 am
Dear Friends,
Thanks for your replay..
i remove index on the that both columns...but now its take more that 10 minutes to run
July 13, 2009 at 5:31 am
You have two problems with the query.
1 you covering index only covers the columns in the where clause and not the columns in your output clause so you will get lookups.
2 As mentioned above because you are using functions on your columns in the where clause the indexes won't actually be of any use.
try and reconstruct the query.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 13, 2009 at 5:44 am
Dear Friends,
Thanks for your replay..
as u say if i m using function in where clause then no use of index
then now
i removeed index on the that both columns which i using in where clause but now its take more that 10 minutes to run..
July 13, 2009 at 5:50 am
could you post the actual execution plan, as a *.sqlplan zipped up and attached to the job.
Could you also give me a little break down what you trying to return from the query there maybe a better way to do this.
Full table definition in the form a create statement and all the indexes in the form a create statement may be very useful as well.
I'm going to guess that the query is slower because now a table scan is being used, where as before you probably had an index scan because one of your columns could still be used.
Like I said though this is a guess and I will know more when I see the above information.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 13, 2009 at 5:58 am
ok i will attache the execution plan with a 5 minutes...
July 13, 2009 at 6:06 am
ok..here i m attached the Execution plan JPG file for the below query.
insert into Auditdata_callto select ID,AuditMaster_ID,CallTo,CallTypeTag from auditdata
where AuditData.TATCallType = 'null' and len(AuditData.CallTo)=12 and substring(AuditData.CallTo,1,convert(int,2))='91'
above query takes around 5 minutes
because 90 % of records affected with above query..
i want to fast the above query..
any solution for that....
July 13, 2009 at 6:12 am
how long does the actuall select part of you query take without the insert?
I can't really read that plan so I'm not sure what the stats are on the first operator, is that Primary key scan or seek?
Perhaps post the text plan output.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 13, 2009 at 6:21 am
Heres my thoughts....
You seem to care a lot about the length of AuditData.CallTo , add a column to AuditData and update it with the value. Add an index on it.
Avoid using substring where like will do, if you are testing the left characters sqlserver can use an index.
ie
substring(AuditData.CallTo,1,convert(int,2))='91'
should be AuditData.CallTo like '91%'
AuditData.TATCallType = 'null'
should that be 'AuditData.TATCallType is NULL' ?
because 90 % of records affected with above query
Are you saying that 90% of you total number of records will match this condition ?
July 13, 2009 at 6:21 am
ok
i will attach Execution Plan within 5 mintues..
July 13, 2009 at 6:26 am
Dear Fried
without insert
this below SQL Query takes 59 secods with index on Tatcalltype column
select ID,AuditMaster_ID,CallTo,CallTypeTag into callto from auditdata
where AuditData.TATCallType = 'null' and len(AuditData.CallTo)=12 and substring(AuditData.CallTo,1,convert(int,2))='91'
why this difference..
July 13, 2009 at 6:31 am
OK so now we getting somewhere.
what indexes do you have on the table you inserting into?
Also do you have any triggers on that table.
I would also take note of Dave's suggestions and that will increase the speed of the select part of the query.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 13, 2009 at 6:50 am
No trigger used in the Query
as u say i m, adding new column in the Table where i update the Callto len
and adding index to that column..
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply