Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    15

    Unanswered: Isql is very slow to run a stored procedure

    Hi all!

    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:
    Code:
    isql -U<user> -S<server name> -P<password> <<EOF
    exec production..doTheJob
    #Strange autocorrect - I can't post the real name of the procedure :)))
    go
    EOF
    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.)

    Is there any way to improve isql speed?

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,

    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...


  3. #3
    Join Date
    Jul 2012
    Posts
    15
    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.

  4. #4
    Join Date
    Jul 2012
    Posts
    15
    Just tried one more time: DBArtisan ~1200 rows/sec. then isql ~700 rows/sec.
    Any ideas?

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Maybe there is a data transfer with a larger packet size in DBArtisan
    Try isql -A packet_size

  6. #6
    Join Date
    Jul 2012
    Posts
    15
    Quote Originally Posted by pdreyer View Post
    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?
    Last edited by CbIP; 08-08-12 at 02:54.

  7. #7
    Join Date
    Jul 2012
    Posts
    15
    Small update.
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •