Re: Delphi 6 - ADO - MS SQL Server

How much time to post are you talking about? I have a client who has a 40 gig
DB, 100+ users, using SQL Server and ADO, with no performance issues.

Every table has a primary. We use TAdoQuery for reading and some posting, and
some stored procedures for updating. I stopped using the TADOStoredProc and use
mostly dynmaic SQL to address the SP's.

For example, your SP could be executed:

ADOCommand1.CommandText :=
'dpersUpdateIndividual '+
InToStr(ID)+','+
QuotedStr(Title)+','+
QuotedStr(Surname)+','+
QuotedStr(FirstName)+','+
QuotedStr(MiddleName) ;

ADOCommand1.Execute ;

-Steve-

"AquaGrrlll" <sparry@xxxxxxxxxxxxx> wrote in message
news:4512576c$1@xxxxxxxxxxxxxxxxxxxxxxxxx
I'm trying to improve the performance of an update on a database table.

Initially, I used TADODataSet components and updated the contents using
Edit/Post. The performance wasn't brilliant, so I decided to use stored
procedures. The procedure I built was as follows:


CREATE PROCEDURE dpersUpdateIndividual
@ID bigint,
@Title varchar(10),
@Surname varchar(50),
@First_Name varchar(20),
@Middle_Names varchar(50),
AS
UPDATE Individual
SET
vchSalutation = @Title,
vchFirstName = @First_Name,
vchMiddleName = @Middle_Names,
vchLastName = @Surname,
WHERE (iIndividualId = @ID)


Real simple. All I did from the Delphi side was use a TADOStoredProc,
fill in the parameters, call ExecProc. It didn't affect the performance
one iota. If anything, it got worse, although we have a busy network
which gets busier as the day wears on, so that may have been a factor.


I had a look at the table concerned and it had quite a few indexes, all
of which are updated when you update a record. I created other stored
procedures to drop these indexes and re-create them, so that they
wouldn't interfere with the Updates and tried again. A little
improvement, this time, but not as much as I would have thought.


All this has lead me to suspect that TADOStoredProc may be the culprit.
Does anyone know if there are any performance issues with this
component, or that there are some settings of either the component or
TADOConnection which would improve this?




.

Relevant Pages

  • Stored Procedure: CommandText does not return a result set
    ... I am trying to run the SQL Server stored procedure sp_helpindex. ... TADOStoredProc and ExecProc, ... TADODataset that runs a select against a SQL Server function and it ...
    (borland.public.delphi.database.ado)
  • Re: How to Mimic Access Externally Linked Tables using ADO?
    ... > using SQL Server for efficiency reasons. ... > connection up programmatically using ADO or ADOX or some SQL Server API? ... >> newsgroup, but if you have a good reason to send me e-mail, you'll find ... ADO does not have a query engine. ...
    (microsoft.public.data.ado)
  • Re: How to access the SQL server express from mfc application?
    ... There are different ways to do this via MFC. ... You could certainly try ADO, ... the entire contents of the database to a SQL Server Standard Server. ...
    (microsoft.public.vc.mfc)
  • Re: DAO vs ADO
    ... and ADO when working with SQL Server data. ... which makes DAO a good choice. ... > add additional overhead by loading Jet, ...
    (microsoft.public.access.conversion)
  • Re: DAO vs ADO
    ... and ADO when working with SQL Server data. ... which makes DAO a good choice. ... > add additional overhead by loading Jet, ...
    (microsoft.public.access.adp.sqlserver)