selecting records from a table with heavy inserts
-
Dear Members,
I am new to SQL and am facing a problem. I have a table that is getting inserted with records almost every second. That is the reason that I did not have indexes in the table (I believe indexing makes the insertion slower!).
Now if I query the table and perform joins or an ORDER BY, I am not getting the response.
The records in the table are arround 80000 (and increasing per minute).
Further, when I query other tables I am getting response. I am not understanding what might have gone wrong.
Can anyone solve my problem?
Thanks for ur help
Supriy
-
Not having any index means SQL must do a table scan to find the rows that qualify for your request. Thus, the lock. Add at least a PK, but also an index can can be used by your where clause. FK fields should also be indexed.
Note: I assume you are not trying to retrieve the entire table.
Depending on what you are trying to do, you could consider using a lock hint (NOLOCK or READPAST) if necessary. This is a last option, not a recommendation.
-
There are a few things you can do.
A) I highly recommend indexing the table! It is imperative to query performance. You just need to be careful about exactly what you index. For instance, a clustered index on a column that increases with each insert (i.e. an identity or a DATETIME column with default set to GETDATE()) will make inserts faster by creating an artificial "hot spot" at the end of your table. A very large non-clustered index on several columns will slow down inserts quite a bit since data will need to be duplicated over to it every time. So will a clustered index on the wrong columns, which can cause page splits and fragmentation... You should try to use a clustered index to increase performance, and if you're querying on it, great. You should also define a non-clustered primary key on whatever column(s) you'll actually be querying against. But make sure to keep it narrow.
B) You can investigate the "NOLOCK" and "READPAST" table hints to get past the blocking being created by your inserts. Since you haven't told us what the table is being used for, I'm not sure if they'll be appropriate to your situation. The NOLOCK hint ignores all locks and will return whatever it can find, whether or not the data has been committed. The READPAST hint will "read past" locks, returning only data that isn't locked by something else. You use them with the WITH operator:
SELECT *
FROM YourTable WITH (NOLOCK)
If you want more help, please post DDL for your table and a detailed description of how it's being used, including what kinds of queries you're executing against it.
--
Adam Machanic
whoisactive
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply