Re: Accessing A Large MS SQL Table Over ADO Components

deliMawi
Date: 10/11/04 

Date: Mon, 11 Oct 2004 17:29:12 +0300

OK,

What if I want to display a listing (maybe a grid) of all records from a
table wich
has more than 500.000 records. When the user clicks on a record I want to
fecth
that record to edit form and let the user edit the record.

I do not want to fecth all the records to the grid. I want to fecth first
few hundered
records and fetch next if the user scrolls. Is the best way is to use a
server-side cursor?

P.S: I am confused, I do not want to slow down the server using server-side
cursors.
Is there an other way for example fetching first 500 records using
client-side cursors
and after scrolling fetch next from server again using client-side cursors?
Or again usign
server-side cursors but closing them after fetching the first few hundered
and than
re-opening and fetching next when the user scrolls etc.?

Thanks,
deliMawi

"Viatcheslav V. Vassiliev" <support@oledbdirect.com> wrote in message
news:416a2ebe$1@newsgroups.borland.com...
>> And I want to know, if I use a standart DB Grid, does the dataset
> component
>> fetches all the records to client?
>
> Client-side cursor fetches all rows, server-side does not. This does not
> depend on DBGrid or other controls.
>
>> What about the cursor location. Setting the cursor to serverside has any
>> advantages? Because I had some performance loss when I set it up to
>> Server
>> Side?
>
> Server-side cursor does not fetch all rows to client, so if server is on
> the
> same computer, you could try server-side cursor, if server is on LAN - try
> and look does it perform good, if server is remote - do not use
> server-side
> cursor for UI. Also, server-side cursor, unlike client-side cursor, holds
> resources on server - if many users will open datasets with server-side
> cursor, you may have problems on server, especially if some user(s) will
> go
> to lunch and leave cursor open.
>
>> Could you please forward me some links or docs about Delphi + ADO + SQL
>> Server Performance issues?
>
> This newsgroup :)
>
> For best performance and access to low-level features you may try OLEDB
> Direct (http://www.oledbdirect.com). It works with OLEDB - the same
> interfaces that ADO uses on low-level. For example, you will be able to
> insert 1 000 000 rows in up to 10 sec.
>
> //------------------------------------------
> Regards,
> Vassiliev V. V.
> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
> ADO.Net
> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
> MS Jet (Access) and Interbase (through OLEDB)
>
> <deliMawi> сообщил/сообщила в новостях следующее:
> news:4168f71a@newsgroups.borland.com...
>> Hi all again,
>>
>> We will be developing a large database application and currently we are
>> working on some base forms and planning the architecthture.
>>
>> An avarage table will be holding around 1000000 records in our
> application.
>>
>> If we want to display the data on a readonly grid wich component is the
> best
>> to use, TADODataSet or TADOQuery ?
>> What about if the grid is not readonly?
>> And I want to know, if I use a standart DB Grid, does the dataset
> component
>> fetches all the records to client?
>> What about the cursor location. Setting the cursor to serverside has any
>> advantages? Because I had some performance loss when I set it up to
>> Server
>> Side?
>> And also ADO Components are a good choice?
>>
>> Could you please forward me some links or docs about Delphi + ADO + SQL
>> Server Performance issues?
>>
>> Thanks in advance
>> deliMawi
>>
>>
>
>

Relevant Pages

  • 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: 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: 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: Huge memory comsumption of ADODB Connection object
    ... unless the cursor is returning data to the client* I don't see ... I suppose this could cause some extra memory ... Is there a similar behavior in Sybase? ... Server was originally based on that rdbms, so there very well may be. ...
    (microsoft.public.data.ado)