SQL Server Performance Forum – Threads Archive

Read last entry of table

Hi All, This might be a very primary question for you. I want to read last entry of the table. Suppose I have table as, ID Name 1 a 2 b 3 c 4 d and this table is dynamic. I want to read last name i.e. ‘d’. I wrote a query like, select Name from table where ID = (select max(ID) from table) 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? Thanks in advance. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Chioo.

Have you got an index on ID? It should be fast if you have.

Thats right, create an index on ID column of the dynamic table and then it would be faster

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…"

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.

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.

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)

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

Thanks a lot for your suggestions. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> 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? I forgot to mention I am using MySQL. Chioo.

I don’t know about MySQL, this is MSSQL Server forum.

>>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

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> I forgot to mention I am using MySQL. <hr height="1" noshade id="quote"></blockquote id="quote"> A quite important fact, don’t you think? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] 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> – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Interesting what happens at almost the same time at three distinct places of the world. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Sorry. My mistake. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] I meant to say MSSQL only.

Ah, so it is mssql? Then you don’t need additional index, however it is strange that your query is slow.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Interesting what happens at almost the same time at three distinct places of the world. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] See, I improved my typing speed [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

>>Interesting what happens at almost the same time at three distinct places of the world. Yes Interesting [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] >>Sorry. My mistake. I meant to say MSSQL only. You mean you are using SQL Server? Madhivanan Failing to plan is Planning to fail

Yes. I am using SQL server. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I am new in SQL so sometimes get confused of terminology.

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

]]>