Unanswered: Isql is very slow to run a stored procedure
I faced with one problem using isql it is very slow to run a stored procedure.
I have a stored procedure, which creates a couple of temp tables, then loads data from production tables to the first table, somehow prepares data, then loads rows to the second temp table, runs cursor to calculate necessary data on the second table and finally updates the production table. Pretty hard, I know...
This procedure must be run at every night. At night I can only use isql to run it:
isql -U<user> -S<server name> -P<password> <<EOF
#Strange autocorrect - I can't post the real name of the procedure :)))
When I run this procedure using DBArtisan - it takes about 10-12 min to calculate 740 000 rows (max speed I got was 1303 rows/sec.)
But when server runs this procedure using isql it takes up to 40 min. to calculate that amount of rows (max speed I got was only 392 rows/sec.)
That event seems strange, mainly because the isql should be faster than DBArtisan.
With Isql your running directly from the machine, you get the ouputs imediatly, on DBartisan you have to get the result to memory, and then display it.
Are you running at the same time the query, i mean one after another? ( or one at night and one at day ?)
What order are you running? (First dbartisan and then isql?) could be some open transaction thats locking some table...
Yep - I'm running the same query first in DBArtisan twice (speed: 1150 rows/s. and 1270 rows/s) and then in Isql (speed: 650 rows/s). I'm running this query during a day.
There are no other working procedures in the database, when my procedure runs at night. So I thought, it should be even faster, not slower...
BTW, this procedure has no output. All statistics is stored in a table.
Should I try 'recompile' parameter maybe?
One again the main Idea of the procedure:
1. Create a cursor and load ~1000 rows form a production table.
2. For each row execute another procedure. (May be paste "recompile" parameter here?)
3. This procedure creates 2 temp tables, loads data to the first table, prepares them and loads into the other temp table. Then it runs a cursor on the second temp table and calculates necessary data.
4. After the data is generated it is loaded into a production table.
5. Repeat 3,4 ~1000 times for each row.
Maybe there is a data transfer with a larger packet size in DBArtisan
Try isql -A packet_size
I've just checked - isql asd DBArtisan use the same packet size of 2560 bytes (maximum packet size is also set to 2560 bytes).
Also, I found an error in the Sybase log. It says, that "Space available in the log segment has fallen critically low..." This error occurs only at night. If I run proc during a day (using DBArtisan and isql) everything works fine (except for isql speed). Maybe there is another process running at night except for my proc?
I found, that isql uses all the available log in the database while executing the stored procedure. When there is no free log space available it returns an error.
DBArtisan uses only small amount of log segment.
Any ideas, why this happens?