Recordset speed problem
From: Vlad (vovan.c_at_verizon.net)
Date: 04/08/04
- Next message: Rohtash Kapoor: "Re: Books On Line"
- Previous message: Peter Nunez: "Books On Line"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 8 Apr 2004 12:32:17 -0400
I'm moving VB 6 app from Access (DAO) to SQL Server (ADO).
With DAO all recordsets are created with
Set RS = dbObject.OpenRecordset("MySQL", ....)
approach.
When I replaced an Access db with another one containing only links to SQL
Server db, everything continued to work with almost the same speed.
Then I started to convert DAO recordsets to ADO recordsets working directly
with SQL Server. If I use
Set RS =New ADODB.Recordset
RS.Open "MySQL", objConnection, , adOpenStatic, adLockOptimistic
approach, creation of recordset takes significant time. Maybe not too much
for a single recordset, but in some cases I need to open about 20 recordsets
at a time and it takes 5-10 seconds. It's not acceptable.
If I create all recordsets through executing Command objects with stored
procedures, then everything is very fast. This approach requires to write a
lot of additional codes (Stored Procedures themselves, Parameter objects,
Command objects).
Is there any way to speed up creation of recordsets without using the second
approach? I tried to use different cursors with no success. Any advice
please.
I also was wondering if it's possible to create an updatable recordset with
Stored Procedure. Or all of them are read only?
Thank you
Vlad
- Next message: Rohtash Kapoor: "Re: Books On Line"
- Previous message: Peter Nunez: "Books On Line"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
