Locking in a Recordset vs SELECT statement

Locking in a Recordset vs SELECT statement

Post by JRSte » Fri, 22 Oct 1999 04:00:00




Quote:>I'm confused between the locking mechanisms of an ADO recordset that is
>using a SQL statement.  Take the following code:

>  rsCustomer.LockType = adLockPessimistic
>  rsCustomer.Open "SELECT * FROM Customers WITH (NOLOCK)"

>What takes precedence, the recordset or the SQL statement?

Excellent question.

If only there were *any* documentation on the subject!

In my experience, the SQL text seems to take precedence -- and
sometimes the adLockPessimistic fails all by itself.

Joshua Stern

 
 
 

1. SQL Select statement vs sp AND literals vs variables

Hi,

I have a stored procedure that creates and populates temp tables, selects,
and groups records, returning desired recordset. When running sp on a small
dataset, it takes about 2 sec. On the large dataset, about 700,000 records
in the main table, it takes about 300 secs (5min) which is not acceptable.
To optimize a query, I have parsed the stored procedure into separate
select/insert statements. After all optimizations, using literals instead of
variables, I was able to cut a 5 min run to 2 seconds. Unfortunately, once I
plug my select/insert statements back to sp, or I change literal to
variable, I'm back to the 5 min.




query runs much faster

INSERT INTO #temp_trx
SELECT
   ...
FROM B, A

AND A.COMPANY_CODE = B.COMPANY_CODE
...

Any help is greatly appreciated.

Thanks,

Yan.

2. Delete of a single row using an indexed primary key

3. USING A FIELD FROM MAIN SELECT STATEMENT IN SUB SELECT STATEMENT

4. Need help with this SP. Setting permissions through a script works in 2K not in 7..

5. Result sets using select in Query Anlyzer vs BCP vs Select Into

6. Knowing which version of SQL 7 was installed -- standard and enterprise versions

7. Page vs Recordset Locking

8. Data transfer between SQL Server and text file

9. recordset.Edit() Locking vs. Stale Data

10. Locking Recordsets - dbOptimistic vs. dbPessimistic

11. Locking on select statement

12. Access recordsets vs. SQL statements

13. Exclusive Page Lock on a SELECT statement