Select Top 1 timeout 4

[Transcend]

Hi guys

I've been left to fix up a heap of problems with an application i'm not familiar with.

One problem is that i keep getting a timeout when i'm trying to perform a delete function in the application. The reason its timing out though is because before it performs the delete it does another query that looks like

select top 1 * from event where act_id=1237

is there any way to get this query to run any faster??

the primary key of the event table is event_id, and i don't think there is any index on the act_id field.

Transcend

 

[mmilan]

Add an index for the act_id field - it should have an impact...

You might also consider changing the timeout property on your database connection.

You don't mention one in your SQL, but any order by clause would be something to be avoided in this case.

mmilan

 

[Transcend]

Hi and thanks for your reply

Unfortunately i can't simply add an index to the table because of ownership and politcal battles!

I will try setting the timeout programmatically. There is no order by claus on the sql statement.

Transcend

 

[JeffTullin]

If you are getting the data through a recordset, try just issuing a 'select * from event where act_id = 1237'
Then take the first record it shows you.

Also, it may be that event has a lot of fields.
If so, in my experience things work a lot faster if you just select the fields you NEED instead of 'select *'

Jeff

 

[Transcend]

Thanks JeffTulin i'll take that into account

Although the event table only has 13 columns, it DOES have a hell of a lot of data in there

haven't been able to look at the problem for long yet as i'm fighting this flu! *groan*

I think that the code is just trying to establish whether any records exist in the event table anyway. I will see what just selecting the Event_ID does, and try setting the command timeout also.

Thanks again

Transcend

 

[Transcend]

mmilan can i ask why an order by should be avoided in this particular case?

Transcend

 

[chiph]

An ORDER BY would require the entire table to be scanned, then sorted, before skimming the first record off (the TOP 1 clause) and returning it to the client. Lots of work for the server.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[SemperFiDownUnda]

Chiph - if the act_id column does not have an index (which it sounds like it doesn't) then an ORDER BY doesn't alter anything on the initial read. A table scan is still required in that case. An ORDER BY would be applied to the result set.

Using a SELECT TOP 1 .... Should be the fastest method of getting the row.

I agree that the only thing you can do to speed this proceedure up is not do a SELECT TOP 1 *...

Name your columns explicitedly.

If you are doing A LOT of processing on this table then you might consider creating a copy of the table and indexing it. Not good for a few one offs or if the data changes during your processing but it can pay off rather quickly. The new table needs only the primary key of the original table and the column you want to search on. Make the index on the search column then do your searches using a JOIN and you'll find the searches will be exponentially faster. If you can't do that then I suggest going to management and tell them you can't bend the laws of physics and nature and either they put the index on the table you need or let you have the db powers to do what I just described.


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Transcend]

Thanks guys

I've convinced the DBA an index is required on the act_id column. The query now runs instantly rather than taking a minute or so.

Thanks again!

Transcend

 

[mmilan]

No prob - and thanx to ChipH for answering your question of me (correctly) in my absence.

mmilan.