SQL Server Performance

why is it faster?

Discussion in 'General DBA Questions' started by v1rtu0s1ty, Aug 30, 2005.

  1. v1rtu0s1ty New Member

    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 />
  2. dineshasanka Moderator

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

    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 />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />

Share This Page