ReadOnly vs Optimistic Locking

• hrodberaht

  SSC-Addicted

  Points: 428

  More actions

  March 3, 2004 at 2:07 am

  #178698

  Hi,

  I have a VB6 application that connects through ADO (mdac 2.7) to SQL Server. I use adLockReadOnly as a lock type from VB to get my result set, which is used for reporting purposes. The query is complex and scans a [ledger] table vigirously.

  When running my application on one server (Quad Processor Win 2000 with Server SQL Server Standard, and plenty of memory), the query times-out. When I run the same query on the same data (which I imported), on another server (Single Processor Win 2000 with Server SQL Server Personal) everything works fine.

  Now when I switch the locking scheme to adLockOptimistic and run the query on the SQL Standard server I get instant response.

  This does not make sense to me, and I dont want to switch my locking scheme to Optimistic unnecessarily. I suspect that there is some or other default setting on SQL Server I have overlooked.

  Any Ideas?

  
  Robert

• Alamir

  SSCertifiable

  Points: 6085

  More actions

  March 4, 2004 at 2:57 am

  #497194

  if yuo are selecting data then the best way is to call a View ... or join 2 or more views together .. and in Views write "with (nolock)" in table hint..

  because MS SQL server is very bad in dealing with locks.

  I hope this help u

  
  Alamir Mohamed
  Alamir_mohamed@yahoo.com