Sql Performance For A Returning Stored Procedure

I have been asked the following question, what would you look into when you want to improve a stored procedure performance? The stored procedure is returning some value and have th

Solution 1:

Check the indexes on the tables used in the joins. Particularly, are the columns used in the joins indexed?

Example -

SELECT     *
FROM       SomeTable a
    JOIN   SomeOtherTable b on a.ItemId = b.ItemId

If these tables are large, indexing ItemId in both tables will typically help performance a lot.

You should do the same thing for any columns that are used in the WHERE clause, if your query has one.

WHERE  a.ProductId =@SomeVariableYouPassedToTheStoredProc

Indexing ProductId may help in this case.

Query performance is something you could go into a rabbit hole on, but this is a logical (and quick) place to start.

Solution 2:

There are a lot of things you can do to optimize procedures, but it sounds like your SQL statement is pretty simple. Some things to watch out for:

• Inline functions. These can cause SQL to do a row by row evaluation and slow things down
• Data conversions on join statements. These can prevent indexes from being used.
• Make sure columns being joined on/in the where clause are indexed (for large data sets)

You can check out this website for more performance tips, but I think I covered most of what you need for simple statements:

SQL Optimizations School

Solution 3:

The fact that it's a stored procedure has little to nothing to do with it. Optimise the sql inside.

As to how, all the usual suspects, including written by the sort of eejit who thinks you can guess what's wrong.

Copy the sql from the proc into a suitable tool, prefix it with Explain to see what's going on.

Solution 4:

I presume there are others options. For example: 1. each of those joins could use restrict conditions which looks like 'and permited_used_name = (select user_name from user_list where )'. The value could be derived once during procedure start (I mean the first string of procedure) to not overload the DB by many similar queries. 2. starting from Oracle11 you could declare a function as function with cached results (i.e. function is calculated once and isn't recalculated each time when it is invoked) defining a set of tables which changes invalidate cache.

At any case the question is mostly DB-specific.

Solution 5:

Run the Query Analyser on the SQL statement