Monday, December 15, 2008

Small trick to speed up your select query

If you want to select data from a database table, but somebody else is inserting data in same table, then in general case, you will have to wait till the insert completes. Your query is deadlocked.


In many cases, the dead lock is justified. It is because, the select data will be more correct after the insertion completes. But it may not be the case always. If you want to select sales data from 2005 to 2007, but someone is inserting data of date 2008, then you will have nothing to do with what other is inserting. In such case, you can use nolock keyword of SQL Server, to perform select operation. It simply allows performing raw read in the table. It ignores locks and also does not lock the table. So, the select operation becomes faster.


It is used like this.


Select *

From    CSAVatInvoiceHdr a (nolock)

Where  csavatinvoicedt >= '2005-01-01'

And      csavatinvoicedt <= '2007-01-01'