Re: Didn't ADO used to default to client-side cursors?
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/02/04
- Next message: Paul Clement: "Re: Didn't ADO used to default to client-side cursors?"
- Previous message: jxstern: "Didn't ADO used to default to client-side cursors?"
- In reply to: jxstern: "Didn't ADO used to default to client-side cursors?"
- Next in thread: jxstern: "Re: Didn't ADO used to default to client-side cursors?"
- Reply: jxstern: "Re: Didn't ADO used to default to client-side cursors?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 2 Dec 2004 15:26:17 -0500
jxstern wrote:
> I just chased down an apparent SQLServer performance problem to the
> fact that the VB6 app was keeping the recordset alive for thirty
> seconds. Profiler was reporting thirty seconds as the duration, when
> in fact the select runs in about 0.2 seconds and the 16k records
> transfer in about 0.8 seconds (then the VB app loads them into a big,
> fat grid - probably not a good idea anyway, but that's another topic).
>
> Setting cn.CursorLocation = adUserClient fixes it all up, as far as
> profiler and SQLServer 2000 care.
>
> My question is, didn't VB6 and ADO, back a few years ago, default to
> client-side cursors? I remember being shocked because the previous
> RDO had defaulted (with SQLServer 6.5) to server-side.
>
> Just a history question, but inquiring minds, and all that.
>
> Thanks.
>
> Josh
It's not that ADO is keep it alive, but your application is taking that
long to fetch all the data. This is probably a foward-only, read-only
result set, the best kind. For most result sets having the server create
the result set (which it has to do anyway) is the fastest because
there's no reason to create an additional ADO result set on the client
if your intention is to quickly scroll through the results and destroy
the result set object.
In your case, the application fetches through the result set and loads
all 16k records into a grid which is slowing things down and potentially
keeping locks on the server while those unfetched rows remain on the
server. You could eliminate this by using a NOLOCK hint. In your case,
you may see better results because the local ADO resultset can probably
load faster than the grid can load. Have you noticed whether the
application performs a little slower using the client-side cursor?
-- David Gugick Imceda Software www.imceda.com
- Next message: Paul Clement: "Re: Didn't ADO used to default to client-side cursors?"
- Previous message: jxstern: "Didn't ADO used to default to client-side cursors?"
- In reply to: jxstern: "Didn't ADO used to default to client-side cursors?"
- Next in thread: jxstern: "Re: Didn't ADO used to default to client-side cursors?"
- Reply: jxstern: "Re: Didn't ADO used to default to client-side cursors?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
