Stored Procedure performance

[mapelli0]

I have a problem when running stored procedure from MyDAC 4.40.

I use EMS Manager for creating and browsing the databse and I created a stored procedure that does not retrive a resultset but make a series os calculation and update a table.

The problem I have is that when I run the SP from withing EMS everything goes fine, it takes about 20 sec or so to do all, but when I run it using TMyStoredProc the SP take abut 10 min to execute.

Is there something I am missing, something I can do to speed up like just sending the call to the server or something?

I don't need a result set or anything for that matter.

I was doing so research and it takes a long time inside the cursor I have inside the sp when I call it from TMyStoredProc.

using MyDAC 4.40
Delphi 7
Mysql 5.0.41
EMS 4.2.1.1

[Dimon]

If you don't retrieve the resultset, you can use TMyCommand component. Set the TMyCommand.SQL property and call the Execute method.
You can use the following code:

Code: Select all

MyCommand.SQL.Text := 'CALL proc_name()';
MyCommand.Execute;

If you use TMyStoredProc you should set the TMyStoredProc.UniDirectional property to True and the TMyStoredProc.FetchRows property to 1.

[mapelli0]

nope, still take 10 min or so to get the proc done, tried both ways with
TMyStoredProc.UniDirectional property to True and the TMyStoredProc.FetchRows property to 1 and TMycommand, both take 10 min to run a sp.

something is wrong with the way those 2 commands send the call to the server.

My wild guess is that the client is trying to get a result set from the cursor withing the sp or something.

Not even on ado it take that long, since I use this proc over ASP too to fill the table

[mapelli0]

Please I need an answer to this problem, even if it is, ya it takes time to call procs that don't return a result set or something

[Dimon]

On executing a procedure, TMyStoredProc sends a query to MySQL server and waits until the procedure execution is completed.
You cann't get control until the procedure execution is completed.

Please send a complete small sample to dmitryg*crlab*com to demonstrate this problem, including a script to create procedure and a script to create and fill table.

[eduardosic]

Please I need an answer to this problem, even if it is, ya it takes time to call procs that don't return a result set or something

Show a Script of the Store procedure and the Table.