SQL Server Performance

Read last entry of table

Discussion in 'General Developer Questions' started by chioo, May 3, 2006.

  1. chioo New Member

    Hi All,<br /><br />This might be a very primary question for you.<br /><br />I want to read last entry of the table. Suppose I have table as,<br /><br />ID Name<br />1 a<br />2 b<br />3 c<br />4 d<br /><br />and this table is dynamic. I want to read last name i.e. 'd'. <br /><br />I wrote a query like, <br />select Name from table where ID = (select max(ID) from table)<br /><br />But this takes considerably long time. Is there any fastest way for this? I will be using this quite frequently so cannot afford delay. What will be the faster way?<br /><br />Thanks in advance. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Chioo.
  2. thomas New Member

    Have you got an index on ID? It should be fast if you have.
  3. sunilmadan New Member

    Thats right, create an index on ID column of the dynamic table and then it would be faster
  4. ramkumar.mu New Member

    If ID is gonna be in ever increasing order, then this might work faster...


    SELECT Name
    FROM Table
    WHERE ID = (SELECT max(rowcnt)
    FROM sysindexes
    WHERE object_name(id) = 'Table')

    or

    SELECT Name
    FROM Table
    WHERE ID = (SELECT count(*)
    FROM Table)

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  5. mmarovic Active Member

    Add an index on id column as recommended and try:


    select top 1 name
    from table
    order by id desc

    Adding index will make your query fast, the query above may be slightly faster but I don't know if the execution time difference would be measurable at all.
  6. mmarovic Active Member

    Ramkumar, both your queries may return incorrect results and the second query would perform the slowest of all queries posted in the thread. The problem is that some rows might be deleted so last ID would be greater then row count.
  7. FrankKalis Moderator

    If you're going to use this to maintain some sort of sequence, you should reconsider this strategy as this is far too slow in a heavily inserted table. Change it to an IDENTITY column and let SQL Server take care of it and generate the sequence at the client.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  8. Madhivanan Moderator

    quote:Originally posted by ramkumar.mu

    If ID is gonna be in ever increasing order, then this might work faster...


    SELECT Name
    FROM Table
    WHERE ID = (SELECT max(rowcnt)
    FROM sysindexes
    WHERE object_name(id) = 'Table')

    or

    SELECT Name
    FROM Table
    WHERE ID = (SELECT count(*)
    FROM Table)

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
    It wont work on all cases
    What happens if the ids are 11,23,56?

    Madhivanan

    Failing to plan is Planning to fail
  9. chioo New Member

    <br />Thanks a lot for your suggestions. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I have a doubt regarding index. As ID is primary key of my table, I think it is already indexed. Will creating another index affect performance?<br /><br />I forgot to mention I am using MySQL. <br /><br />Chioo.
  10. mmarovic Active Member

    I don't know about MySQL, this is MSSQL Server forum.
  11. Madhivanan Moderator

    >>I forgot to mention I am using MySQL.

    This is SQL Server Forum. Post your MySQL questions at www.MySQL.com

    Madhivanan

    Failing to plan is Planning to fail
  12. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />I forgot to mention I am using MySQL. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />A quite important fact, don't you think? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />This is SQL Server country. You might be better off asking this on the MySQL mailing lists at<a target="_blank" href=http://www.mysql.com>http://www.mysql.com</a><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  13. FrankKalis Moderator

    Interesting what happens at almost the same time at three distinct places of the world. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  14. chioo New Member

    <br />Sorry. My mistake. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />] I meant to say MSSQL only.
  15. mmarovic Active Member

    Ah, so it is mssql? Then you don't need additional index, however it is strange that your query is slow.
  16. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Interesting what happens at almost the same time at three distinct places of the world. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">[<img src='/community/emoticons/emotion-1.gif' alt=':)' />] See, I improved my typing speed [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  17. Madhivanan Moderator

    &gt;&gt;Interesting what happens at almost the same time at three distinct places of the world. <br /><br />Yes Interesting [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />&gt;&gt;Sorry. My mistake. I meant to say MSSQL only.<br /><br />You mean you are using SQL Server?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  18. chioo New Member

    <br />Yes. I am using SQL server. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />I am new in SQL so sometimes get confused of terminology.
  19. Madhivanan Moderator

    If you have already primary key on id then no need of creating index on other column

    Madhivanan

    Failing to plan is Planning to fail

Share This Page