July 13, 2009 at 6:54 am
select ID,AuditMaster_ID,CallTo,CallTypeTag from auditdata
where AuditData.TATCallType = 'null' and Calltolen=12 and AuditData.CallTo like '91%'
put index on calltolen
now the above query taking 1.02 minutes
July 13, 2009 at 7:14 am
Dear Friend
check attached the execution plan here..
please replat ASAP
thanks
July 13, 2009 at 7:17 am
That is not what is required.
Please read the article http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 13, 2009 at 7:25 am
Dear Friend...
Please check attached the Excel...
thanx for the article...
July 13, 2009 at 7:26 am
Dear Friend...
Please check attached the Excel...
thanx for the article...
Replay ASAP
July 13, 2009 at 7:28 am
July 13, 2009 at 7:35 am
Dear Friend..
Ok now u check the below attached the Excel sheet..
July 13, 2009 at 7:36 am
you could also post all the indexes on your table.
I have a feeling you have a clustered index on that table that you haven't mentioned yet.
I also have a feeling that you going to need to make you index cover your whole query.
as Dave said this is all guess work until you supply us with the query plans
----------------------------------------------
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 7:37 am
Can you post index definitions please? From the plan there looks like an obvious index but without seeing the existing indexes....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 13, 2009 at 7:40 am
From your last spreadsheet we can see there is a clustered index being used.
I'm going to say that creating a non-clustered index covering you where clause and your selet output will increase the performance.
----------------------------------------------
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 7:44 am
I just noticed that you have posted in a SQL Server 2000 forum, so I won't post what I was going to post because it applies to 2005. If you are using 2005 let us know.
As has been mentioned, the query is doing a clustered index scan, but without knowing what indexes you have there isn't a whole lot of help we can offer beyond what has already been offered.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 13, 2009 at 7:52 am
Dear friends..
select ID,AuditMaster_ID,CallTo,CallTypeTag from auditdata
where AuditData.TATCallType = 'null' and Calltolen=12 and AuditData.CallTo like ='91%'
i running above query
in the table i have only two column index
1.ID--Clustered index (data type : int)
2.Calltolen -non clusered index (data type : int)
July 13, 2009 at 7:55 am
can i add indexed to the columns whom datatype is Varchar????
July 13, 2009 at 8:07 am
Time for some row counts
select count(*) from AuditData
go
select count(*) from AuditData where TATCallType = 'null'
go
select count(*) from AuditData where Calltolen=12
go
select count(*) from AuditData where CallTo like ='91%'
1) What are the results ?
2) Do any of them use an index in the query plan ?
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy