Using stored procedures performance will improve

Question

I need to know the advantages of using stored procedures.

Presently client has 3-tier architecture. Most of the tables are innodb.Most of the servers have ~64G of ram and all the servers are linux 64bit.Recently they started clusters also for some servers.

Client is more precise on knowing the things at db level rather than a write-up of advantages and disadvantages.

Related for the SQL Server part(?) of your question. [What are the pros and cons to keeping SQL in Stored Procs versus Code](http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code) — Mikael Eriksson, Jan 02 '13 at 07:15

score 1 · Accepted Answer · edited Apr 22 '13 at 19:28

3 tier will optimize the resource utilization and improve your program drastically. You wont feel it when you are building simple application when large applications you will clearly get difference. For example take a look at how this 3 tier is used and see the difference.

public class businesscheck
{
    datacheck dldata = new datacheck();

    public int PeronalDetails(int bh, string bse, string bt, string bde)
    {
        string sql = "[dbo].[newsinsert]";
        int bheader = bheaderid + 1;
        int result = dldata.SQLSP(sql, bheader, bsubtype, btitle, bdescr);
        return result;
    }

    public void deleterecord(int a)
    {
        dldata.SQLC();
    }
}

Then finally when you call this from your front layer system can easily pass the values and programmers and designers can work separately.

    protected void Button1_Click(object sender, EventArgs e)
    {

      int MyInteger = Convert.ToInt32(TextBox1.Text);
        string subtype = txt2.Text;


        obj1.PeronalDetails( header, subtype, title, desc);

        txt2.Text = "";

    }

Using stored procedures performance will improve

1 Answers1