Stored Procedure with parameters Vs direct Query

[zacksoniar]

Hi guys,

1. In my application,to Connect to DB, i used to write direct string queries in any function, assigning them appropriate variables using '+' sign. Then I had separate Data Access class to which I used to pass this query where it used to get executed.Code was like this:

  void someFunction()
     { 
          clsDatabase objDB = new clsDatabase();
          strSQL = "INSERT INTO QUERY"+param1;
          objDB.ProcessQuery(strSQL);
     }

Now, I have changed my way, I have written a stored procedure with parameters. I call a function of Data Access class which calls this stored procedure. I pass parameters to function which are assigned to strored procedure parameters.

  void someFunction()
     { 
          clsDatabase objDB = new clsDatabase();
          objDB.functionWhichCallsStoredParameters(param1);
     }

Both approches work, but i want to know which one is good & faster.

2. Second Thing I want to know is, In one of my classes, I m making object of ClsDatabase in static method. I think its gonna use same objects again & again, creating problems for concurrency.Plz, clarify.

  static void someFunction()
     { 
          clsDatabase objDB = new clsDatabase();
          objDB.functionWhichCallsStoredParameters(param1);
     }

Thanks in advance.

[zacksoniar]

Please, Let me know if my question is not clear to u guys...no reply so far...???

[ck9663]

On your first question, the second one, by default is usually faster.Even if the query are exactly the same. This is because SQL Server stores the way it executed the stored procedure using the optimizer. If you use a dynamic sql, it will run it as if it is executed the first time and the way it will be executed might not always be the most efficient way to execute the query.

Your second question is more of a front-end question than a back-end question.

Happy Coding!!!

~~ CK

[debasisdas]

In any database the stored procedure is a pre-compiled object. The SQL part in the stored procedure is not parsed / compiled at run time which is thecase with any raw SQL. Thats why stored procedure will always give you better performance.