About Disconnected Record Sets

Hi,
I am using disconnected ADO record sets while using an Access DB for
storing
my intermediate values in processing. I had initially used a server side
cursor, however it gave a bad performance while inserting a record one at a
time.
Now using a client side cursor gave a decent performance while inserting
records, but while finding records within the record set, I get deteriorated
performance which far overrides the benefits I receive during record
inserts.
I am using 'Find' while Deleting and updating records within the
recordset. My understanding is the Find will check the disconnected set for
the record that matches the Find criteria and fetches the record from the
server if no record matching the criteria is found. Is this assumption
right?
Any ideas why the performance is bad while using disconnected record
sets for Find? If anything, it should be as bad as it was while using server
side cursor because at that point, I used to fire a SELECT query everytime.
Thanks in advance,
MadhuHi
Does this posting by Bob help?
http://tinyurl.com/gkrv4
John
"Madz" <noemail@.none.net> wrote in message
news:eLXqL4sYGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am using disconnected ADO record sets while using an Access DB for
> storing
> my intermediate values in processing. I had initially used a server side
> cursor, however it gave a bad performance while inserting a record one at
> a
> time.
> Now using a client side cursor gave a decent performance while
> inserting
> records, but while finding records within the record set, I get
> deteriorated
> performance which far overrides the benefits I receive during record
> inserts.
> I am using 'Find' while Deleting and updating records within the
> recordset. My understanding is the Find will check the disconnected set
> for
> the record that matches the Find criteria and fetches the record from the
> server if no record matching the criteria is found. Is this assumption
> right?
> Any ideas why the performance is bad while using disconnected record
> sets for Find? If anything, it should be as bad as it was while using
> server
> side cursor because at that point, I used to fire a SELECT query
> everytime.
> Thanks in advance,
> Madhu
>
>|||Hi,
I am not using disconnected record sets per se. I am using a client side
cursor. I have set the the cursorlocation property to use client side
cursor.
Let me briefly explain wherein I am facing these problems. I set the
cursorlocation and the maxrows properties on the recordset and open the
recordset with a "SELECT * FROM Table". Now, while looping through the
recordset, I do not see any benefits of using a client side cursor in terms
of time i.e. it takes as much time as it did while using a server located
cursor. I got around my other performance deteriorations by using command
executes for inserts and deletes.
Why does looping thru' the recordset in this case take the same amount
of time as a server located cursor?
Thanks,
Madhu
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23J1hv4tYGHA.2376@.TK2MSFTNGP03.phx.gbl...
> Hi
> Does this posting by Bob help?
> http://tinyurl.com/gkrv4
> John
> "Madz" <noemail@.none.net> wrote in message
> news:eLXqL4sYGHA.3704@.TK2MSFTNGP03.phx.gbl...
>|||Hi
Without knowing exact details of your systems it may be hard to tell why
but...using SELECT * may mean you are returning unnecessary data back to
your client. The quality of the client system may effect the response times.
If you are continually using find then it could mean that you need to order
the recordset better! I would expect MoveNext to be quicker than find. Do
you always movefirst before the find, in which case it could be slower.
John
"Madz" <noemail@.none.net> wrote in message
news:OhHzB06YGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am not using disconnected record sets per se. I am using a client
> side cursor. I have set the the cursorlocation property to use client side
> cursor.
> Let me briefly explain wherein I am facing these problems. I set the
> cursorlocation and the maxrows properties on the recordset and open the
> recordset with a "SELECT * FROM Table". Now, while looping through the
> recordset, I do not see any benefits of using a client side cursor in
> terms of time i.e. it takes as much time as it did while using a server
> located cursor. I got around my other performance deteriorations by using
> command executes for inserts and deletes.
> Why does looping thru' the recordset in this case take the same amount
> of time as a server located cursor?
> Thanks,
> Madhu
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23J1hv4tYGHA.2376@.TK2MSFTNGP03.phx.gbl...
>|||Hi John,
I am no longer using Find method. Let me explain :-)...
I had 2 connections open to two different databases. One of them
required serial access to data while the other needed random. I was
initially using the Find method in both the cases but was well advised
against it because I read in forums that recordset method of updating data
is ill served against using command object.
The database that needed random access to data has now been modified to
use command object thru'out [and hence, I have no issues with this] while
the one requiring serial access to data uses client side cursor for obvious
reasons. Now my question :-)...
Why does using client side cursor still not tremendously improve my
response time? I see that all the records are dragged into my process even
though I have set MaxRows property on the client side cursor. Even with
this, I see that traversing thru a recordset of about 20000 records takes
about 2-3 minutes. I am using Access 2000 as my database. I hope I have been
clear!
Thanks,
Madhu
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e0jVwP7YGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Hi
> Without knowing exact details of your systems it may be hard to tell why
> but...using SELECT * may mean you are returning unnecessary data back to
> your client. The quality of the client system may effect the response
> times. If you are continually using find then it could mean that you need
> to order the recordset better! I would expect MoveNext to be quicker than
> find. Do you always movefirst before the find, in which case it could be
> slower.
> John
>
> "Madz" <noemail@.none.net> wrote in message
> news:OhHzB06YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||Hi
Once all the recordset has been retrieved locally I would also expect moving
through the recordset to be quicker, therefore I can't say why in this
instance it is not. If you were using SQL Server you could tell when the
server was being accessed i.e. you were re-fetching the data, but I do know
know how to tell this for Access. The only suggestion I can put forward is
to check that you have a suitable amount of free space on the system, make
sure that your disc is not fragmented and use perfmon to check that you
don't have any resource bottlenecks.
John
"Madz" <noemail@.none.net> wrote in message
news:%23Vn%23Li7YGHA.2208@.TK2MSFTNGP03.phx.gbl...
> Hi John,
> I am no longer using Find method. Let me explain :-)...
> I had 2 connections open to two different databases. One of them
> required serial access to data while the other needed random. I was
> initially using the Find method in both the cases but was well advised
> against it because I read in forums that recordset method of updating data
> is ill served against using command object.
> The database that needed random access to data has now been modified to
> use command object thru'out [and hence, I have no issues with this] while
> the one requiring serial access to data uses client side cursor for
> obvious reasons. Now my question :-)...
> Why does using client side cursor still not tremendously improve my
> response time? I see that all the records are dragged into my process even
> though I have set MaxRows property on the client side cursor. Even with
> this, I see that traversing thru a recordset of about 20000 records takes
> about 2-3 minutes. I am using Access 2000 as my database. I hope I have
> been clear!
> Thanks,
> Madhu
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e0jVwP7YGHA.4060@.TK2MSFTNGP02.phx.gbl...
>