Re: SQL over WAN with ADO, how to optimize?

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/10/04 

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

Relevant Pages

  • Re: Permissions???
    ... > Server: SQL Server 2000. ... > the user could Write/Read data from server and execute stored procedures. ... > The connection was made with DSNs. ... ADO gives the following message when using stored ...
    (microsoft.public.data.ado)
  • Re: Quick test for ActiveX?
    ... ADO functions after "upgrading" to IE8, ... issue is related to ActiveX not being enabled on their system, ... used to test client capabilities. ... Stream -- handling of binary data being the notable exception. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Help!!!
    ... Ensure your stored procedures include 'SET NOCOUNT ON'. ... > Server: SQL Server 2000. ... > I had an ADO conection to the from a VB Application, ... > The connection was made with DSNs. ...
    (microsoft.public.sqlserver.security)
  • Re: Connection trough MarshalByRefObject
    ... Do you use ADO? ... client on server, server throws COMException. ... Recordset wynik = new Recordset; ...
    (microsoft.public.dotnet.framework)
  • Re: SQL over WAN with ADO, how to optimize?
    ... > and the half of the code have done already, I'm using ADO 2.8. ... so everything is doing by the client. ... Use stored procedures exclusively. ... roundtrips to the server. ...
    (microsoft.public.data.ado)
Loading