Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31

    Unanswered: Is stored procedure are similar to prepared statement?

    I am trying to figure out what is basic difference between stored procedure and prepared statement, It seems same to me,
    we do create sp before and call it with different IN or Out parameter, likewise prepared statement, we create before and used with different value,
    i think one thing is different, we can not keep prepared statement for next time like stored procedure, we can use sp in different project but not prepared statement,
    Is this is a difference between these two mysql concept?
    I am using mysql c api for database connectivity,
    ex- select * from table name where id=? for this i can use sp or prepared statement both, what i should use, what is basic feature of prepared statement, which scenario i should use these two concept
    one thing is more , i read out only once prepared statement is parsed, it shows fast execution of query, what is actual meaning of this, how it is benifit, if possible then give me example where prepared statement is best alternative.
    Thanks in advance.
    Amit Pathak
    S/w Developer
    Zero Systems Pvt Ltd..

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    There is nothing similar between both except it is possible to use prepare statements in stored procedures. Prepare statements are extremely useful when you have to dynamically create SQL statements. For instance, you are not sure of which table you need to call or which fields of a table to access, then prepare statements will allow you to do that.

    Stored procedures on the other hand allow you to group SQL statements together along with flow control to achieve and higher levels of business logic within the database.

    The database server cache's your SQL statements as they are being sent to the server, parsed and executed. As stored procedures contains a lot of static SQL statements (they can also contain prepare statements for dynamic but this is not the norm), the SQL statements will already be cached and will simply need to be executed giving better overall performance. Dynamic SQL statements may or may not already exist in the server cache in which case it may end up having the parse and execute. A once off call will not indicate the performance differences but if you are looping through millions of records then the performance difference will be more noticeable.

    Also if you think about it, if you have a client application on a separate server than your database. Each SQL statement needs to be sent across the network which is expensive but computing standards, so by having all the SQL statements executed in a single unit (stored procedure) implies less network traffic which again speeds up the overall processing.

    A final advantage of having stored procedures is that this can contain your business logic. For example, data needs to be validated and updated in multiple tables based on your business rules. It then means that your logic is contained in a single location. Then whether you are using java, PHP, C you make the same call to the stored procedure. If you need to change the business logic this needs to be changed in one single location rather then in multiple interfaces.

    On the flip side stored procedures basically tie you in with a single database vendor. Migrating from one database flavour to another implies a rewrite of the stored procedures as they are usually not portable.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31

    you mean to say repeation of same query must used prepared staement..

    Quote Originally Posted by it-iss.com View Post
    There is nothing similar between both except it is possible to use prepare statements in stored procedures. Prepare statements are extremely useful when you have to dynamically create SQL statements. For instance, you are not sure of which table you need to call or which fields of a table to access, then prepare statements will allow you to do that.

    Stored procedures on the other hand allow you to group SQL statements together along with flow control to achieve and higher levels of business logic within the database.

    The database server cache's your SQL statements as they are being sent to the server, parsed and executed. As stored procedures contains a lot of static SQL statements (they can also contain prepare statements for dynamic but this is not the norm), the SQL statements will already be cached and will simply need to be executed giving better overall performance. Dynamic SQL statements may or may not already exist in the server cache in which case it may end up having the parse and execute. A once off call will not indicate the performance differences but if you are looping through millions of records then the performance difference will be more noticeable.

    Also if you think about it, if you have a client application on a separate server than your database. Each SQL statement needs to be sent across the network which is expensive but computing standards, so by having all the SQL statements executed in a single unit (stored procedure) implies less network traffic which again speeds up the overall processing.

    A final advantage of having stored procedures is that this can contain your business logic. For example, data needs to be validated and updated in multiple tables based on your business rules. It then means that your logic is contained in a single location. Then whether you are using java, PHP, C you make the same call to the stored procedure. If you need to change the business logic this needs to be changed in one single location rather then in multiple interfaces.

    On the flip side stored procedures basically tie you in with a single database vendor. Migrating from one database flavour to another implies a rewrite of the stored procedures as they are usually not portable.
    First of all, thanks to share your knowledge with me,
    ****Yes it is true that stored procudure reduces complexity of systems as well as reduce network traffice,
    ******* I want to know if i write a query "select * from table_name where id=?
    inside stored procedure, then whenever we call it, 1->it will parse again and again or not? *************2->for how amount of time data is place inside the cache?,
    ***If i talk about prepared statement, then really we require to make dynamic query sometimes inside stored procedure,
    *****************3->I want to know, is prepared statement only have a existence inside stored procedure? you said if we want to insert millions of record then that time it will be beneficial not the query parsed again and again, and it can be happen by prepared statement, so only in this kind of scenario prepared statement should be use?
    Amit Pathak
    S/w Developer
    Zero Systems Pvt Ltd..

Tags for this Thread

Posting Permissions

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