March 3, 2004 at 2:07 am
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
March 4, 2004 at 2:57 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply