Re: SQL over WAN with ADO, how to optimize?
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/10/04
- Next message: rseedle: "SQLclient fill method get exception error"
- Previous message: David Gugick: "Re: cancel ADO2.8 asynchron queries over WAN, takes long time"
- In reply to: SÁRINGER Zoltán: "SQL over WAN with ADO, how to optimize?"
- Next in thread: Yuancai \(Charlie\) Ye: "Re: SQL over WAN with ADO, how to optimize?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 10 Dec 2004 14:43:08 -0500
SÁRINGER Zoltán wrote:
> Hello,
>
> this become a common question... is any common answer ?
> I want to use VB6 application to connect SQL2000 via ADSL 512/128..,
> and the half of the code have done already, I'm using ADO 2.8.
> All bussiness logic on the thick client side.., and I choosed ADO
> becouse I used these events to audit changes (instead of writing lot
> of triggers), so everything is doing by the client. I minimized the
> requests to sql, but seems extremly slow via internet.
>
> Try to speed up, how?
> What connection and recordset parameters (included the most detailed
> parameters too..!) should use for:
> 1) common record listing queries?
> 2) update
>
> What is your opinion, if sql server is used for only store data with
> ADO only, is it a real poor programming modell, doesnt exist any
> advantage?
>
> Why is it faster UPDATE a record with TSQL than ADO ?
Minimize round trips. Never return large result sets. Use forward-only,
read-only (firehose), server-side cursors. Client-side cursors could
work as well if you need larger result sets returned, but large results
are not ideal. Do not requery the same data. Once you get the data for
things like lookup tables, save it locally at least for the session, if
not longer by persisting to a file. Use NT Authentication for security.
Use stored procedures exclusively. If you can combine multiple procedure
calls into a single call to a master stored procedure to limit the
roundtrips to the server. Check your ADO network packet size and make
sure it's appropriate to the data your return. So if your results are
typically small, set it to a correspondingly small value. Do not allow
fuzzy searches that can result in large result sets being returned. If
possible, use the NOLOCK hint on SELECT queries that can work with dirty
data to limit locking on the server.
-- David Gugick Imceda Software www.imceda.com
- Next message: rseedle: "SQLclient fill method get exception error"
- Previous message: David Gugick: "Re: cancel ADO2.8 asynchron queries over WAN, takes long time"
- In reply to: SÁRINGER Zoltán: "SQL over WAN with ADO, how to optimize?"
- Next in thread: Yuancai \(Charlie\) Ye: "Re: SQL over WAN with ADO, how to optimize?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
