Re: TADOStoredProc

On Fri, 12 Oct 2007 01:43:11 +0100, Brian Bushay TeamB <BBushay@xxxxxxxxx> wrote:

Its not a CPU cycle issue. It is an issue with How ADO handles the change in the SQL. Every time you add to the SQL it sends those changes back to the database. That is not efficient so don't use Add() set All the commandtext or
the SQL.text property


Very good point! I bow to your superior knowledge of the internals of ADO.

(I don't hit this since I build all my SQL commands in a single delphi string for logging & timing purposes)


But even though I haven't done any testing (so I'm not letting any evidence limit my ability to pontificate :) ), I would still guess that it would be an "unusual" SQL statement where LAN round-trip latencies /significantly/ outweigh the time needed for the database to analyse exactly what that SQL was asking for, to determine the best route to achieve that result from the tables - and, especially, to rattle the disks, filter and marshal the results and then transfer all that data back.

Of course, YMMV if your code looks like...
SQL.Clear
SQL.Add ( 'Select' )
SQL.Add ( 'CustomerId,')
SQL.Add ( 'CustomerName,' )
....

My intention was only to say that if anyone is looking to solve a database performance problem, they may find it more profitable to first look at the strategy - such as limiting the number of separate database accesses (and then the amount of data transferred each time) - rather than fiddling with the tactics.

( Except for remembering to "DisableControls"! )

--
Paul Scott
Information Management Systems
Macclesfield, UK.
.

Relevant Pages

  • Re: CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to
    ... Go to the Database tab and click on the browse button next to the connection string. ... In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate. ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to meth
    ... Now register the assembly and the aggregate in the SQL Server database you want ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... But SQL does not have a pointer data type or the ... > being told to design a database. ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... a surrogate key should support the primary key. ... But SQL does not have a pointer data type or the ... > being told to design a database. ...
    (comp.databases.theory)