Delphi 6 - ADO - MS SQL Server

[AquaGrrlll]

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?

[Steve Zimmelman]

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-

Post by AquaGrrlll
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
CREATE PROCEDURE dpersUpdateIndividual
@ID bigint,
@Title varchar(10),
@Surname varchar(50),
@First_Name varchar(20),
@Middle_Names varchar(50),
AS
UPDATE Individual
SET
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?

[AquaGrrlll]

Well I was talking in relative terms rather than absolute milliseconds. The
time taken was about 150ms per record to do the edit/post. It doesn't sound
a lot, but we're expected to process about 700k records and it all adds up,
of course (29 hours to do the lot). I was just expecting it to halve the
amount of time taken when I used Stored Procs and was rather taken aback
when it didn't.

I'll have a go at your method, which seems intriguing and very lateral.

Cheers

Post by Steve Zimmelman
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.
ADOCommand1.CommandText :=
'dpersUpdateIndividual '+
InToStr(ID)+','+
QuotedStr(Title)+','+
QuotedStr(Surname)+','+
QuotedStr(FirstName)+','+
QuotedStr(MiddleName) ;
ADOCommand1.Execute ;
-Steve-

[Vitali Kalinin]

Post by AquaGrrlll
Well I was talking in relative terms rather than absolute milliseconds. The
time taken was about 150ms per record to do the edit/post. It doesn't sound
a lot, but we're expected to process about 700k records and it all adds up,
of course (29 hours to do the lot). I was just expecting it to halve the
amount of time taken when I used Stored Procs and was rather taken aback
when it didn't.

Where did you get those 700k records, which you need to update? If they are
located on the same server then you should use update with from clause.
Something like this:
update tableA set tableA.Title = TableB.Title from TableB where TableB.ID =
TableA.ID.
If they are coming from the external source and you need fast performance
then you should look at either of this:
1. DTS
2. BCP
3. IRowsetFastLoad
Idea is as follows all those enables bulk insert, i.e. you will create
TableB from external data and than do update against this TableB.

[Dennis Passmore]

I have had similar experience with trying to use
TADOStoredProc as it is a total waste of time.

Executing MsSQL TSQL using

ADOCommand1.CommandText :=
and
ADOCommand1.Execute

is always much better.

[Bo Berglund]

On Thu, 21 Sep 2006 16:04:33 -0400, Dennis Passmore

Post by Dennis Passmore
I have had similar experience with trying to use
TADOStoredProc as it is a total waste of time.
Executing MsSQL TSQL using
ADOCommand1.CommandText :=
and
ADOCommand1.Execute
is always much better.

And pre-storing the procedures permanantly inside the database to
begin with and then just calling them is even better. We have all of
our MSSQL business logic in stored procedures in the database. Then
our apps just supply the parameters in the calls, which we do either
through AdoConn.Execute or AdoQuery.Execute.
Works really good and isolates the database logic from the
applications.

[Brian Bushay TeamB]

Post by AquaGrrlll
Well I was talking in relative terms rather than absolute milliseconds. The
time taken was about 150ms per record to do the edit/post. It doesn't sound
a lot, but we're expected to process about 700k records and it all adds up,
of course (29 hours to do the lot). I was just expecting it to halve the
amount of time taken when I used Stored Procs and was rather taken aback
when it didn't.

Using the TadoConnectoin.Execute method is the lowest overhead way to execute
and SQL statement
--
Brian Bushay (TeamB)
***@NMPLS.com

[AquaGrrlll]

I've tried one or two of the methods suggested herein, but none of them
seemed to produce the desired effect. Just playing around with the settings,
however, I set the ExecuteOptions = [eoAsyncExecute]. Now it just rips
along, although I have some problems using it within a multi-threaded
application. I get the following:

Operation cannot be performed while executing asynchronously

This is, I'm assuming, because I'm setting the parameters while it's still
busy executing the stored procedure. Does anyone know a way around this?

[Angus Robertson - Magenta Systems Ltd]

Post by AquaGrrlll
I set the ExecuteOptions = [eoAsyncExecute]. Now it just
rips along, although I have some problems using it within a
Operation cannot be performed while executing asynchronously

You need to create a FIFO queue for your SQL statements, using a
message triggered from the ExecuteComplete event (where you must
check the result of each async operation) to execute the next
statement.

Very rarely, you'll still see the error you mention, so just put
the failed statement onto the top of the queue and execute it
again. This seems to be an ADO bug with it's own thread.

Angus

[AquaGrrlll]

Post by Angus Robertson - Magenta Systems Ltd
You need to create a FIFO queue for your SQL statements, using a
message triggered from the ExecuteComplete event (where you must
check the result of each async operation) to execute the next
statement.

When I set up the thread I give it it's own TADOConnection as well as the
TADOStoredProc:

FConnection := TADOConnection.Create(nil);
FConnection.ConnectionString := FConnectionStr;
FConnection.OnExecuteComplete := ConnectionExecuteComplete;

FExecuteComplete := True;

FUpdateIndividualProc := TADOStoredProc.Create(nil);
FUpdateIndividualProc.Connection := FConnection;
FUpdateIndividualProc.ProcedureName := 'dpersUpdateIndividual';
FUpdateIndividualProc.ExecuteOptions := [eoAsyncExecute];
FUpdateIndividualProc.Parameters.Refresh;

with the procedure ConnectionExecuteComplete setting FExecuteComplete to
True;

The following is called by the execute of the thread in a loop:

procedure TCRMIndividualThread.UpdateIndividual;
begin
while not FExecuteComplete and not Terminated do
Sleep(5);

//Fill in params of StoredProc
FExecuteComplete := False;
FUpdateIndividualProc.ExecProc;
end;

So the first time, FExecuteComplete is True, the stored procedure is
executed. Theoretically, the OnExecuteComplete of the connection will fire
when the command has been completed, FExecuteComplete is set to True and
around we go again. If the loop's a bit too fast the Sleep will keep
everything on hold until the connection says that the command has been
executed.

Problem is, that isn't happening. OnExecuteComplete never fires and the
whole thing hangs.

Any ideas why?

[AquaGrrlll]

How strange.

I just moved the code which sets up the connection and stored procedure from
a function that gets called by the thread Execute to the constructor of the
thread. And it all works now. I get that asychronous error crap again,
presumably from the destructor, when I terminate the thread.

Bizzare. Why would it make a difference where I created the connection
component so long as it was before I used it?

Well I'll hum along to the tune for now and maybe I'll find out the words
along the way.

Thanks for all the help.

[Angus Robertson - Magenta Systems Ltd]

Post by AquaGrrlll
When I set up the thread I give it it's own TADOConnection as well

Why are you using a thread? You can drive the whole thing with events,
the first event is when the connection to SQL is made, that triggers the
Execute EXEC statements, and ExecuteComplete then triggers any more
Exectutes, until you've finished. This is none blocking, and you can
have several connections to different databases running at the same
time, no threads.

Post by AquaGrrlll
Problem is, that isn't happening. OnExecuteComplete never fires and
the whole thing hangs.

I don't use TADOStoredProc, I just Execute on the connection, checking
Recordset in the event for success. The simpler, the safer.

I'm using these techniques to capture records arriving by multiple TCP
and UDP streams to different database tables, up to 50 records per
second, all in a single thread.

Angus

[Ece]

Hello,
I have some hits. Their may be usefull.
1- StoredProcedures only affect handkshaking costs and simplifies repetitive
tasks.
2- Ususally in large DB systems after updaing records, in some cases you
should first disable or delete indexing engine. After that all of jobs were
done, then create indexes again (Here you can server stored procedures for
simplicity and modularity). The indexing task may be done in system idle
times eg nights.

If these were not usefull, inform me!