How do you use NOLOCK with TADOQuery and TADOTable?

views:

answers:

+1 Q:

How do you use NOLOCK with TADOQuery and TADOTable?

I have the SQL text "SELECT * FROM TABLE1 WITH (NOLOCK)".

Two questions:

How do I make my TADOQuery use the NOLOCK hint without having to include that in the SQL text? I have literally thousands of TADOQuery's with their SQL dynamically built, and it would be difficult to add WITH (NOLOCK) to all of them, not to mention I use with with database platforms other than MSSQL. Is there a TADOQuery property?
How do I achieve the same thing with a TADOTable? TADOTable's don't have any SQL, so how do I tell it to use the NOLOCK hint?

Thanks

+2 A:

Hints are database specific, thereby you have to issue the proper one for each database supported. In some databases there's also nothing equivalent, for example Oracle does not allow anything alike (readers never block writers and writers never block readers, thereby no need).

IMHO you shouldn't use TADOTable components at all - anyway AFAIK you have no way to specify an hint for the generated query - which is far too generic anyway.

Also you should be VERY careful to use NOLOCK. It means READ UNCOMMITTED, aka dirty reads. You're bypassing transaction protection, and unless you have a very good reason to bypass it you shouldn't - the gain in performance may be not worth the loss in data integrity and consistency.

ldsandon 2010-10-01 15:44:58

Additional info for C. Smith: NOLOCK is a hint to the SQL optimizer. If there's no SQL, there's no SQL optimizer (you don't have access to it, anyway), which means you can't give it the hint.

Ken White 2010-10-01 17:50:53

Even in transactional environments, the gain can be very high with SQL Server (in my case 2000, later 2005), since it too quickly promotes rowlocks to table locks under load. Adding nolock to all tables that are just lookup (and only change say once a few times an year when the machine is under maintenance anyway) improved performance (measured under live stress, not a synthetic) of queries with many (5-6) table joins with sometimes over 100%.

Marco van de Voort 2010-10-02 10:00:37

Yes, that's the same reason MySQL ISAM tables are faster than InnoDB: they don't use transactions. The problem is how the locking mechanism is designed in some databases - others never escalate the lock. I didn't say "never use NOLOCK". I just said *be very careful*, because the OP said he has *thousand* of queries to change, like any hint you should use it the proper way in the proper situation only - knowing which effects it has.

ldsandon 2010-10-02 12:38:28

+1 A:

if you use MS SQL, run below statement once on connection

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Cheer AP

APZ28 2010-10-01 23:32:05

Yes. And do you know the implications? If it is not the default level there is a good reason. Transactions and locks are there to protect your data integrity. Before throwing that away, I would think twice - there should be a very good reason.

ldsandon 2010-10-02 16:47:35

Does this statement apply to that one session only (i.e. I need to do it every time I start my app)? Or is it set to the database itself permanently? Does it affect only SELECT statements or all SQL statements?

C. Smith 2010-10-08 13:50:52

It is for the session only. It will affect all DML statements, for example any where clause will "read" the database. Your query may (and will...) return results that never existed in the database. Unless your database is read-only you are looking for trouble. Using a NOLOCK hint for a given query, knowing what to do is ok. Using it for a whole session unless the database is read-only is very dangerous. Performance is not everything. User usually prefer the correct result, even if slower, than the wrong one faster.

ldsandon 2010-10-10 16:13:37

ansaurus

tags:

views:

answers:

How do you use NOLOCK with TADOQuery and TADOTable?

related questions