in my vehicle tracking application, i m using sqlServer database, which have a highly populated and highly accessed table "VehilcePosition", which stores all vehicle locations. for showing vehicle latest locations, i have to query table by self join and group function MAX on dateTime. i had already put indexes on columns, but reading query takes some time and acquires shared lock, which blocks Insertion in the table. As this table is being updated (INSERTION of rows) and Read most frequently. application mostly stops due to timeout exception because of shared lock during READ and Exclusive lock during write. i need a suggestion how could i optimize my database to avoid such situation. i m using Core i7 PC (not server machine), is there only solution to upgrade hardware to some server machines.
2 Answers
The problem with NOLOCK is that you'll get occasionally wrong answers.
Hardware is also not the issue.
The best solution is to use SQL Server's "Read Commited Snapshot Isolation" This is a database level setting that returns to the reader the latest version of a row while rows are being updated. It is often the solution to this type of problem.
Another solution is to use table partitioning, but that's an Enterprise Edition (And Developer Edition) feature. With table partitioning in SQL 2008, you can specify set lock excalation Auto, which will escalate locks only to the partition level. If you've got enough partitions (15,000 allowed) most queries won't block.
But the read commited snapshot isolation is the best solution.
-
+1 3rd option, separate table of "cached metrics"; write a quick stored proc that checks this table for the cached MAX(dateTime) value along with a timestamp of when this was last updated; if last updated is "too long ago" then do an actual query and update the cache; otherwise return the cached value. 4th option, same idea as last, but insert-trigger on VehiclePosition table to update cache table.– Chris SOct 15, 2012 at 14:45
but reading query takes some time and acquires shared lock, which blocks Insertion in the table
You do not want the locks, tell SQL Server not to put locks there. There isa hint for that.
I am using Core i7 PC (not server machine), is there only solution to upgrade hardware to some
server machines.
Incidentally this is more about "learn to use sql server properly by reading the documentation" thatn you upgrading new hardware. NOLOCK for example springs to my mind as a way to tell athe read query not to lock the rows.
