logical read

Hi

I am trying to tune a sql query. The query return about 300 rows of uniqueindentifier. But in the statistics IO, I see Logical reads of 7228, that does not seems right? I tried to rebuild the index, but the logical read did not help. But I set up the database on another computer, and the IO on this on came down to 10. Is there something wrong with the computer with logical read of 7228

thanks

Pauli

There may not be enough memory available to SQL Server, and the data cache is being flushed.

What is the memory/load differences between the two computers?

|||Are the indexes the same in both databases? It really sounds like you are missing an index in the first database. Even on a machine with memory pressure, that much of a difference in logical IO's points at an index or statistics problem. Are you seeing physical reads when you run the query in the first database?

Logic for Stored Procedure(s)

I have a table containing a 'queue' of rows waiting to be processed by my application.

Is it possible to call a single stored procedure that selects a row, returns the data and then deletes the row ?

If not, what is the best logic for doing it with two stored procedures ?

The table contains a unique ID, DateTime and nVarChar colums and I could easily add a 'flag' if required.

Any suggestions appreciated.

Steve.A single stored prodedure can have many lines of code, so sure, what you are asking is possible.

I'd do it like this:

SET NOCOUNT ON
BEGIN TRANSACTION
 SELECT TOP 1 @.myID = uniqueID FROM myTable ORDER BY datetime DESC
 SELECT someFields FROM myTable WHERE uniqueID = @.myID
 DELETE FROM myTable WHERE uniqueID = @.myID
COMMIT

I'd also put some error checking code in there and perform a ROLLBACK if there's an error.

Terri|||I might create a flag on myTable indicating that the record has processed, move the tran to the client, and update the flag once the process has sucessfully completed.

If the server commits the tran before the client finishes processing and the client error you will have no "easy" way of recovering a deleted record.|||Thanks guys - that helps a lot.

Steve.