Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Angry Unanswered: stored procedures VS embedded programming


    Well, I was wondering which solution is faster (stored procedures or embedded programming) I've being done some benchmarks and, it seems the stored procedures are the best option:

    First test:
    INSERT each time a set of 4,000 rows in a table, once using a Stored Procedure ( see the preceding line with the text: "-------- Stored Procedure ------") and then,
    without it (see the preceding line with the text: "-------- Without Stored Procedure ------"):

    -------- Stored Procedure ------
    BEGIN: [2004/02/18 17:51:50.000]
    END: [2004/02/18 17:52:35.193] Elapsed sec.millisec: 45.193
    -------- Without Stored Procedure ------
    BEGIN: [2004/02/18 17:52:38.000]
    END: [2004/02/18 17:53:24.950] Elapsed sec.millisec: 46.950

    Second test:
    Get the data from one row in the table A and;
    Using these data, insert one row in the table B;
    Update the row in the table A with the new data.

    --------- Stored Procedure ------
    BEGIN: [2004/02/19 17:51:12.000]
    END: [2004/02/19 17:52:19.505] Elapsed sec.millisec: 67.505
    --------- Without Stored Procedure ------
    BEGIN: [2004/02/19 17:51:12.000]
    END: [2004/02/19 17:52:37.442] Elapsed sec.millisec: 85.442

    --------- Stored Procedure ------
    BEGIN: [2004/02/19 17:52:40.000]
    END: [2004/02/19 17:53:18.045] Elapsed sec.millisec: 38.045
    --------- Without Stored Procedure ------
    BEGIN: [2004/02/19 17:52:40.002]
    END: [2004/02/19 17:53:33.193] Elapsed sec.millisec: 53.191

    Sybase Version 11.5
    Programming Language "C"
    Library "CT Library"
    OS AIX

    Any suggestion, comment or advice?


  2. #2
    Join Date
    Jun 2003

    Re: stored procedures VS embedded programming

    [QUOTE][SIZE=1]Originally posted by gdt_bra_arg

    Hi There

    Stored procedures are always better because of Precopiled execution and reduced Network traffic

  3. #3
    Join Date
    Oct 2003
    Stored procedures are always faster, because of the following reasons:

    1. When the stored procedure is created it is compiled and the plan or the sequence tree is stored in the system table. When the stored procedure is first executed, it is optimized and the final query plan is stored in the procedure cache. the subsequent execution of the stored procedure will use the stored query plan from the procedure cache. Which saves a lot of time.
    2. The compiled code is stored in the database, therefore it reduces the network usage each time it is executed.
    3. Apart from performance impprovements, stored procedures also help in security issues. Users do not need direct permissions on the tables and columns, they just need execute permission on the stored proceudre.

    where as for queries,

    1. Compiled and optimized each time they are executed
    2. The whole of the sql text has to travel through the network from client to the server
    3. Users need select permission on table or selected columns to be able to run a SQL

Posting Permissions

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