SQL Server performance with Query Delete Top

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

SQL Server performance with Query Delete Top

Author

Topic

rom84
Starting Member

4 Posts

Posted - 2009-07-06 : 22:21:15

Hi all,

I have a problem with my sql server 2005.
I have a table non-index with only one field with data type bigint.And I have a store procedure have query below :

Create table #tblPassportID(PassportID bigint)

Delete top(1) PassportID_Pool
Output deleted.PassportID
Into #tblPassportID

Select @PassportID = PassportID From #tblPassportID

The problem is when my table have 1.000.000 records, my server run with CPU about 4%.
But when my table have 100.000 records, my server run with CPU 100%.

I don't know why? Is my query have problem?

Please help me!
Thank you very much!

@Note: at that time, my server only run this sql server 2005 application and my database only run that query. My database in server have size about 30Gz

Infomation about my server :
Ram : 8Gz
Intel Xeon 2,4x2,4Gz
Window server 2003 service pack 2

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-08 : 23:43:19

sould put clustered index on passportID but may want to try this:

Declare @passportID bigint
SELECT top 1 @passportID = passportID from PassportID_Pool
DELETE PassportID_Pool where passportID = @passportID

SELECT @passportID

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-08 : 23:44:04

also, any triggers on table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources