-1

Currently we have developers writing SQL Queries written in java code, which is placed in over different areas of code. The queries themselves are not much optimized. The DBA's cannot take ownership of optimizing the query as they are in application level.

Will writing each and every query in a stored procedure help to make this situation better ? What is the best practice to control the quality of SQL in the application ?

Yogesh R
  • 32
  • 4
  • 1
    It won't necessarily help to put each and every query in there, but putting the ones that you think could change over the lifetime of the application, or that could be optimized by a skilful DBA, will help. You don't want to make a hard and fast 100% rule about this. – user207421 Jun 16 '16 at 10:08
  • Have you considered how the Java code will be tested? If you limit the business logic to the Java later, it will be testable via unit tests with an in-memory database. – Andy N Jun 16 '16 at 10:09

1 Answers1

1

As Technical Papers Stored Procedures are little better than SQL Commands. Reason: Every request for run SQL command contains following steps :

  • Syntax Check
  • Compile
  • Run

But when you use Stored Procedure the request directly RUN. It's good to know that every SQL request have execute plan before run , for stored procedure this section will be saved and directly run. You can recompile stored procedure with sp_recompile command.

Mahdi Sheikhi
  • 86
  • 8