Re: VB connection to SQL server

Tech-Archive recommends: Speed Up your PC by fixing your registry

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

Date: Wed, 11 Aug 2004 16:08:52 +0200

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.
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.
>
> 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
>
>

Relevant Pages

  • Re: Using Access to view and edit SQL Server table with Bigint primary key
    ... Your problem has been an ongoing problem between Access and sql server ... ADO to edit your data. ... recordsets will not work with sub-forms and the link master/child settings, ... I can't imagine anyone suggesting temp tables on the sql server side, ...
    (comp.databases.ms-access)
  • Re: Need help with DAO and SQL Server locking issue
    ... I will forward your response to the client. ... > Absolutely nobody uses DAO to work with SQL Server, ... I'd advise moving code that uses recordsets ... >>Locks are being placed on tables inside of a transaction each time the ...
    (microsoft.public.sqlserver.odbc)
  • Re: VB-ADO-SQL Server : SQL Server performs logins after some queries
    ... If your query works very slow, then you could face an issue with ... > with a SQL Server 2K database as well as MS Access> DAO. ... Can using of ADODB recordsets instead of object give a far more better> performance? ... >>>>> At the start of the application the connection object is created ...
    (microsoft.public.vb.database.ado)
  • Parallel BTrieve und SQL Server Zugriff
    ... nicht gefunden dann in SQL Server Tabelle suchen. ... Recordsets erstellt, rs und rs2 ... Damit der ganze Code nicht doppelt geschrieben werden muss habe ich ... Dazu eine Boolean Variable uss die Wahr wird wenn im SQL Server ...
    (microsoft.public.de.access)
  • ADO recordsets on the client
    ... long time developing an application using disconnected ADO recordsets. ... client as possible, and initially wrote my web application in VBScript ... I then decided to use the http_request object to xfer ADO recordsets to ... technology that WILL WORK? ...
    (microsoft.public.inetserver.asp.db)