SQL Server Performance Forum – Threads Archive

why is it faster?

Hi everyone, I was wondering why the response waitime of my SELECT sql statement using create index lidx on mytable(loanno, denter, dconfirm, dprint) is slower than create index lidx on mytable(loanno) create index lidx on mytable(denter, dconfirm, dprint) With the latter index creation, my SELECT statement gave the result 75% quicker. But if I use the index that was created in the former, it’s really slow. I’m not really good in index. I just know that when you have index, it speeds up retrieval or search. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Any help would be greatly appreciated! V

It all depends on how is your select statment. Can you paste the select statment here

Hi, are you using Index hint in your select statement — index hint force to use specified index i think loanno is Primary key field and you are creating covering index with <blockquote id="quote">quote:<hr height="1" noshade id="quote">create index lidx on mytable(loanno, denter, dconfirm, dprint)<hr height="1" noshade id="quote"></blockquote id="quote"> and as you have clustered index on loanno field which is primary key field and hence all your records are physically sorted based on this loanno for more information please refer BOL [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

]]>