Re: cancel ADO2.8 asynchron queries over WAN, takes long time

From: Wart (nospamWart_at_epix.net)
Date: 12/13/04 

Date: Mon, 13 Dec 2004 18:12:26 -0500

I would add that a Select without a Where clause should be treated with
suspicion. While there may be a need for all the records in the table, if
there is not, then limit the number of records transmitted and reap the
performance rewards.
CF
"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:ulE3VxS4EHA.3380@TK2MSFTNGP09.phx.gbl...
> SÁRINGER Zoltán wrote:
>> Hi David,
>> thank you for the replies. About ADO via ADSL, tomorrow I will make
>> some "deep" tests, and we will discuss the results. (but in advance:
>> it's slow at case of just a simple SELECT stataments, not update, see
>> my detailed answer on tomorrow)
>>
>>
>> About the .cancel method (asynchronous query):
>>
>> of course, I'm using .cancel for simple SELECTs only, with clientside
>> cursor. So there are not transactions, data manipulating.. (I'm using
>> MSDE 2000, not sqlserver, is this matter?)
>>
>> rs.Open "SELECT * FROM table1", cn, adOpenKeyset, adLockOptimistic,
>> adAsyncFetch
>>
>> What I realized:
>> the .open method first creates the recordset at the server. At this
>> point there is a normal delay, its not problem, the asynchfetch
>> parameter refers for "fetching" only.
>> So the fetching is asynchron, and if I run it at the localhost, or
>> LAN, it works: I give the .cancel, (asks server to stop transfer
>> records??) It takes for 1-2 seconds also, but its not a real problem.
>> BUT via INTERNET, at 5-6 Kbit/sec speed, the .cancel method doesnt
>> receive something or i dont know... It doenst stop it at all, the
>> thread works until the all record arrive, but I will not informed by
>> rs_FetchComplete event, becouse of the .cancel...
>> Do you mean?
>> I tried to set these:
>> rs.Properties("Initial Fetch Size")
>> rs.Properties("Background Fetch Size") without result.
>>
>> I really hope, the solution of my problems will be a corrent cursor
>> and locktype. I use clientside cursor to minimalize the traffic...
>> where did I make mistake? Why unable to "throw" that thread the
>> .canel method???
>> It I set ts=nothing, and I creat a new recordset, the user will
>> experince that the query has stopped, and can start a new query (stop
>> it, start new, stop it, start new..) during this making newer and
>> newer "endless" threads..
>>
>>
>> Zoltan
>
> First of all, you should neve, ever do a SELECT * from an application. It
> almost always returns more column data than is necessary and it makes the
> application difficult to read.
>
> You are also using:
> "rs.Open "SELECT * FROM table1", cn, adOpenKeyset, adLockOptimistic,
> adAsyncFetch"
> I would change this to a forward-only, read-only, server-side cursor
> (firehose cursor without any cursor management required by client or
> server). You do not want to be managing any types of cursors on the client
> because they generate all sorts of activity between client and server and
> doing so over a slow link is a bad choice.
>
> You should also scrap using any SELECT statements in your application. Why
> haven't you designed this application to use stored procedures? You must
> use stored procedures over slow links because it once again cuts down on a
> lot of the traffic.
>
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com