Re: using SQLserver, ADO, Delphi: performance issue

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 01:42:10 -0500

Joe Hatem wrote:
> We have been considering using SQLserver as the backend for a large
> business application developed in Delphi.
> We intelinked an ADOconnection, an ADOtable and a DBgrid.
> The table is about 200,000 records.
>
> No matter what options we enabled or disabled on the ADOconnection or
> the ADOtable, the MSsql Profiler shows that the client end is fetching
> all the 200,000 records, and then showing only the required screenful
> of 20 records or so.
> When the requested display is not indexed, we see the first 20 records
> at once, but the client keeps fetching records, until all are
> transferred.
> When the requested display is indexed, we get no display until all
> records have been fetched,
>
> We cannot believe that this is the modus operandi of ADO, we must be
> missing something fundamental.
> Any suggestion ?

I agree with Aaron that you want to do paging. ADO is not the root
cause. Your code is (or the Delphi control is). ADO supports server-side
(standard) read-only, forward-only result sets. These result sets are
only fetched when you request a fetch operation. Even in that case,
however, you would likely want to fetch everything as quickly as
possible. Until you do, the ADO connection cannot execute anything else
and you are going to leave shared locks on the server on some of the
unfetched pages. You may be using a client-side cursor. In this case,
ADO is fetching the rows behind the scenes for you. This is a better
option when you have to perform a lot of row processing and think moving
through the results is going to take some time. I don't know what an ADO
Table is. Is that a Dephi object? You'll probably want to use an ADO
recordset with the proper parameters. Moving 200,000 rows across the
network is not ideal. I strongly suggest looking into a paged solution
or make the user query a subset of data first to reduce the result set
load. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Re: Delphi 8 and ado.net
    ... > different to using say ADO in Delphi? ... The Delphi ADO implementation is basically a wrapper around the Recordset ... have cursors into the database, there are no cursors in ADO.NET. ...
    (borland.public.delphi.non-technical)
  • Re: Sub Selects?
    ... > some nice Delphi DB technique to do this, or should I just build up the ... Due to the way that the ado ... lets assume that you have a list called SelectedIds ... I'd recommend against using the filtering method. ...
    (borland.public.delphi.database.ado)
  • Re: recursive sp, fmtonly, ado,
    ... This was the first thing I've checked, because I suspect Delphi. ... It's in somewhere inside ADO or OLEDB. ... >> create procedure fmttest ...
    (microsoft.public.sqlserver.programming)
  • Re: Field position in a record, how to change?
    ... If you know how to move fields with DAO you can use Ole to access the ... I have found how to obtain the (ADO) FieldDefs for the ... (I am searching documentations for this option but your answer might ... I may just have found the answer in my saved Delphi 5 help files ...
    (borland.public.delphi.database.ado)
  • Re: BDE alternatives?
    ... Remember that Hannes is a developer for NexusDB, ... Anyway, I think every Delphi DB solution will, after the next Delphi.NET ... In either case, in practice, you put down an ADO connection ...
    (borland.public.delphi.non-technical)