Re: VB connection to SQL server

From: Christian Schäffler (christian.schaeffler_at_messring.de)
Date: 08/12/04 

Date: Thu, 12 Aug 2004 12:11:46 +0200

Hi Chris,

I never meant to claim that I'm 100% right and you are wrong. It just
depends on the type of application you have.
Best practice would be to test both proposals with test applications and
databases. 

-- 
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:O02PhE$fEHA.3632@TK2MSFTNGP11.phx.gbl...
> 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: suid bit files + securing FreeBSD (new program: LockDown)
    ... I found the design maybe LockDown or your IDS could use ... So you need at least one CFC server, ... the client boots, it will just use the files it already have and update ... The multicast address the client is a member of. ...
    (FreeBSD-Security)
  • Re: VB connection to SQL server
    ... "Chris Barber" wrote in message ... > recordsets (client side cursor) to allow you to persist the recordsets to ... aware that the data is probably out of date, a client sided cursor might fit ... That is because the SELECT statement is executed on the server and the data ...
    (microsoft.public.vb.database)
  • Re: Distributed applications and OOD
    ... On the server-side, client ... > still it is the only working design for distributed applications. ... implementing an OOA model in an EJB/J2EE environment, ... in the client, in the DB, or in the server. ...
    (comp.object)
  • Re: Architectural feedback
    ... That can influence your design. ... bank branches absolutely must be able to function with a failed server (or ... This means a lot of functionality is duplicated on the ... > with WinForms-based apps (assume all client machines are Windows running ...
    (microsoft.public.dotnet.general)
  • 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)