Re: VB connection to SQL server

From: Chris Barber (chris_at_blue-canoe.co.uk.NOSPAM)
Date: 08/11/04 

Date: Wed, 11 Aug 2004 23:18:27 +0100

See comments inline (not arguing, just clarifying my original suggestions):

Chris.

"Christian Schäffler" <christian.schaeffler@messring.de> wrote in message
news:O7I$8y6fEHA.704@TK2MSFTNGP09.phx.gbl...
Hi, 

-- 
Keep on fighting!
Christian
Please reply to this message, to let others know whether it was useful to
help you solve your problems or not.
Original Message:
"Chris Barber" <chris@blue-canoe.co.uk.NOSPAM> wrote in message
news:%23XHE6X4fEHA.2524@TK2MSFTNGP09.phx.gbl...
> You need to think long and hard about caching data and only checking if it
needs updating (eg.
> re-requesting).
>
> Limit the data retrieved by only asking for the minimal set of fields and
records
absolutely right
>, use disconnected
> recordsets (client side cursor) to allow you to persist the recordsets to
XML or store them in a
> local Access DB / XML files.
Microsoft MSDN quotation
[...]Use client-side cursors appropriately   Except for static data that is
not subject to change by other users over time, data that is replicated on
the client machine begins to lose its relevance and accuracy as soon as it
arrives[...]
If the db-user only performs SELECT-like operations on the data and he is
aware that the data is probably out of date, a client sided cursor might fit
your requirements.
But it makes it quite impossible to perform UPDATE, INSERT and DELETE
operations since you never know, unless you refetch the data, if not someone
else changed or deleted the data you are currently editing.
* You do know when you do the .UpdateBatch because each record will have a status assigned that can
be checked to see if it was successfully updated.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthupdatebatch.asp
In my view this is the best mechanism since the data transmitted back (using appropriate filter) is
minimal and does not require the recordset to be refreshed first to see if data has changed. Good
design minimises the possibility that records will have changed in the meantime and some form of
custom record lock flag can completely solve this (eg. set a field value for the records to be 'in
use' and code to deny changes unless this is not set).
Also the amount of data is quite important.
Maybe you know Microsoft Enterprise Manager.
It has an option to view all data contained in a table (or even a view!).
You ever recognized that a 2000 row-table and a view containing about 1GB of
merged data open in merely the same time?
That is because the SELECT statement is executed on the server and the data
is displayed asynchronously. When scrolling down the datatable, more of the
rows are fetched and transferred to your computer (it might take some
seconds to fetch the next bunch of data)
Now imagine you would be forced to let the execute first and than all the
data would be transferred to you machine. It would take hours.
* Hence the requirement to minimise the data content. My view is that a recordset request is made
because all the data is required and as such the client side cursor (retrieves all the data in one
go) is the best mechanism for this. Obviously requesting 2000+ records (in my view) constitutes a
'bad' design. Estimating the transfer time is relatively simple goiven a known number of records and
the field types and should be part of the design considerations.
The *are* good reasons to use server-side cursors (each record is streamed as the recordset is
navigated) but 'good' design of the application and queries usually negates this (in my experience
only of course).
>
> Basically, it's all about minimising the data retrieved and caching
wherever possible. Disconnected
> recordsets also allow 'offline' editing of the records since you can
reconnect the recordset at a
> later time and update (check the record status afterwards for failed
updates - eg. a record has been
> deleted in the meantime).
>
> Chris.
>
> "tonyvber" <tonyvber@discussions.microsoft.com> wrote in message
> news:8C06A793-2ECF-4B3E-8D03-306CB057DFB1@microsoft.com...
> I have an application using VB 6 and SQL server 2000.
> The Database server is in the office, and people use the Vb program from
> workstation in the office, in otherwords it's running in a LAN.
> Now, the company want to move DB server to outside Data center.
> So, the connection from my VB program to SQL server need to go through
> Internet.
> I just tested it, it's very slow. Currently in the VB program, i have a
Data
> environment and a connection with MS OLE DB Provider for SQL server.
>
> How can I improve its performance?
> -- 
> thanks
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004
>
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004

Relevant Pages

  • Re: VB connection to SQL server
    ... > the client machine begins to lose its relevance and accuracy as soon as it ... > aware that the data is probably out of date, a client sided cursor might ... > design minimises the possibility that records will have changed in the ... >> The Database server is in the office, and people use the Vb program from ...
    (microsoft.public.vb.database)
  • Re: cancel ADO2.8 asynchron queries over WAN, takes long time
    ... >> the .open method first creates the recordset at the server. ... I use clientside cursor to minimalize the traffic... ... You do not want to be managing any types of cursors on the client ... > doing so over a slow link is a bad choice. ...
    (microsoft.public.vb.database.ado)
  • Re: cancel ADO2.8 asynchron queries over WAN, takes long time
    ... >> the .open method first creates the recordset at the server. ... I use clientside cursor to minimalize the traffic... ... You do not want to be managing any types of cursors on the client ... > doing so over a slow link is a bad choice. ...
    (microsoft.public.data.ado)
  • Re: cancel ADO2.8 asynchron queries over WAN, takes long time
    ... >> the .open method first creates the recordset at the server. ... I use clientside cursor to minimalize the traffic... ... You do not want to be managing any types of cursors on the client ... > doing so over a slow link is a bad choice. ...
    (microsoft.public.sqlserver.programming)
  • Re: (Math) Expression Evaluator
    ... For a number of> reasons, I don't have a server that I can pass these calculations to, so> this all has to be done on the client. ... >> Chris, ... >> perhaps an expression for a DataColumn, or an XSLT transformation which> will ...
    (microsoft.public.dotnet.languages.csharp)