why is it faster? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

why is it faster?

Hi everyone,<br /><br />I was wondering why the response waitime of my SELECT sql statement using<br /><br /> create index lidx on mytable(loanno, denter, dconfirm, dprint)<br /><br />is slower than <br /><br /> create index lidx on mytable(loanno)<br /> create index lidx on mytable(denter, dconfirm, dprint)<br /><br /><br />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.<br /><br />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=’:)‘ /><br /><br />Any help would be greatly appreciated!<br /><br />V<br />
It all depends on how is your select statment. Can you paste the select statment here
Hi,<br />are you using Index hint in your select statement — index hint force to use specified index<br />i think loanno is Primary key field and you are creating covering index with <br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">create index lidx on mytable(loanno, denter, dconfirm, dprint)<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />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<br /><br />for more information please refer BOL <br /><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami<br />