• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2001
  • Last Modified:

Which is Faster Static SQL or Dynamic SQL inside a Procedure?

Hi,
I'd like to know which method is better (in terms of perfomance) for developing a procedure.
I have a procedure which accepts a number of paramters and depending upon the parameters the filter condition is to be applied. The 2 ways of developing are

1. Declare a temporary VARCHAR variable and dynamically create the sql
E.g ssql = "SELECT col1, col2, col3 FROM table1, table2 WHERE table1. col1 = table2.col1"
IF NOT ISNULL(parameter1) THEN
ssql = ssql + " AND table1.col1 IN (" + parameter1 + ")"
end if
executesql ssql

2. Write the SQL statically
E.g.
SELECT col1, col2, col3 FROM table1, table2
WHERE table1. col1 = table2.col1
AND (ISNULL(parameter1) OR table1.col1 IN (parameter1))

Which of this method is more efficient? Does the ISNULL keyword prevent the DB engine from creating the plans/using the proper index?

Expecting a detailed explanation.

Thanks in advance.

Regards,
Unni
0
unniks
Asked:
unniks
  • 4
  • 4
  • 3
  • +3
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello unniks,
Of course static SQL will be faster if the  query has to be executed multiple times. Each query has to go under several steps before executing a query via Compilation , optimization etc. In case of a dynamic query, it need to go thru all these steps every time it is getting called, but in case of a dynamic sql, it can store the execution plan. and if for the next call a valid execution plan is there in the cache it will just executes this query accordingly



Aneesh R
0
 
unniksAuthor Commented:
Thanx Aneesh.
Did I miss anything? Does the database not compile/optimize when you create the procedure. Both the above sample is inside a stored procedure. My understanding was that both will be compiled but for the dynamic one the database cannot create the plans so will do that at runtime only.
Please correct me if I'm wrong.

Unni
0
 
imitchieCommented:
dynamic SQL will be faster, under normal circumstance.
because query plans are reused, SQL Server will create a plan to take into account the possibility of having to evaluate each branch of an optional parameter, i.e.

@param1 is null or field1 = @param1
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
imitchieCommented:
As for dynamic SQL, each variant gets a query plan of it's own. For MSSQL 2000, you'll want to use sp_executeSQL to make use of the parameterized queries for maximum effect. SQL2005 implicitly turns constant param queries into parameterized queries.  Oracle and MySQL have similar optimisation techniques as well.
0
 
JuozasVCommented:
Several things:
1. Dynamic SQL will be cached, so you will "win" parsing time if the SQL is already in cache, however for long queries this time is very minimal and does not have general influence.
2. Static SQL has advantage when it comes to internal cursors - they are much better managed by oracle itself (i.e. Oracle will not :forget" to close the open cursor :). Also, implicit cursors will not get certain errors for long running queries (i.e. "Snapshot too old" wich plagued me a lot of times until I gave up to static sql).

Since you do not use parameters in above example (in joins or etc.), I would use static SQL.

Now answer to the INDEX usage portion - if you use function on JOIN or some column fetch check, then Oracle will not use ordinary index (you should create FUNCTION BASED INDEX in order to optimize). However your example above is ISNULL(parameter1), since it is some number or varchar and not used in table scans/joins, then it has little effect since INDEX is something created on database objects, but not variables.

Third - when Oracle compiles, it does not (!) create any execution plans - this is done in runtime always for either static and dynamic SQL ! :)

Cheers,
Drugelis
0
 
JuozasVCommented:
One more comment (sorry) - dynamic SQL has its core advanatage of being flexible - i.e. you can construct some query out of string concatenation (but your query seems like not in that area, unless it is not full and more complex than you wrote).

Cheers
0
 
schwertnerCommented:
It depends from theusage of bind variables for passing parameters.

Reason:

Oracle keeps in the SHARED_POOL based on LRU model the executed
SQLs, nomatter how they are created: static or dynamic. So if you
use bind variables as much a possible you will encrease the chance
to use parsed SQL statement and so simply to use "soft parsing".

But if you hard code values of the parameters in the dynamic SQL
statement you will loose the chance to reuse the parsed execution plan ...

So the answer is the speed of a SQL is depending
on the efforts to achieve reusability.
0
 
schwertnerCommented:
Why your question is placed in

Database MySQL  ???????
0
 
imitchieCommented:
Can we keep on track and answer the question in relation to building a Stored Procedure with "optional" parameters? I think the "general" comments about Dynamic SQL vs Static SQL are all nice and informative, but let's discuss this "specific" case.
0
 
unniksAuthor Commented:
Hi schwertner
This procedure is targeted for MySQL 5.0 version.


Unni
0
 
JuozasVCommented:
Specific case imho is this:
1. I would use static SQL for such simple queries generally (the only way I would turn to dynamic is for short queries that have very high usage - i.e. OLTP; since this will tell me the parsing time is usually critical).
But
2. If we have a lot of parameters and SQL is much more complex, then dynamic construction will be more flexible and build simpler queries, and most probably should run generally faster. But this is not the rule as each SQL statement is quite individual together with underlying data (which is usually underestimated). For example, sometimes I make SQL "more complex" to gain performance - i.e. add additional criteria which can reduce the matrix set and so oracle CBO can use this for effectiveness :)

The above example is too simple to use dynamic SQL.
0
 
unniksAuthor Commented:
Hi
I think this session is going away from the point.
This question was raised as one of my friend pointed out that in the case 2 of my samples, the db engine will not be able to use (find out) the index on col1 (if the index is present) as db engine will ignore on seeing "ISNULL OR..." statement and had suggested for dynamic SQL.
But I was under the impression that for static queries the plans will be created on the first go (execution) and as there is no change in the sql statement the plans need not be recreated and the engine will re-use the plans thus being faster. Not only that the db engine will consider all joins/filters.
Please correct me if I'm wrong.

Thankx a lot.

Unni.
0
 
schwertnerCommented:
The engine can not detect which SQl statement is dynamic and which static. It simply get SQL statement from the user session, looks for similar in the shared pool and either parse the new one and executes it or executes an stored statement.
0
 
Mark GeerlingsDatabase AdministratorCommented:
I think by posting this question in three different zones: MS SQL Server, Oracle Database, and MySQL Server, you are getting conflicting responses, because these different databases are optimized very differently!  Oracle is the only one of these three that I know well, and in Oracle dynamic SQL will *ALWAYS* be slower than static SQL statements in stored procedures.  Why?  Because with static SQL statements, Oracle can (and does) do all of the overhead at compile time.  The result is: very fast runtime executions.  This is also true in Oracle for SQL statements in stored procedures that use bind variables - they can execute very fast.

SQL Server however appears to be designed to handle dynamic SQL just as it does static SQL (neither one as fast as static SQL in Oracle) so with different databases, the results will be very different.

I have no experience at all with MySQL, so I don't know how it handles dynamic vs. static SQL statements in stored procedures.
0
 
schwertnerCommented:
@ Markgeer:
You wrote:
Because with static SQL statements, Oracle can (and does) do all of the overhead at compile time

I do not agree with this in regard of Dynamic SQL.
The dynamic SQL is dynamic!!! mainly this means also that at compile time the SQL statement is unknown. It is defined after the subroutine compile time, at the run time ... And I believe that when Oracle gets the dynamic SQL it will first try to figure out an equivalent statement in the Shared Area.
0
 
Mark GeerlingsDatabase AdministratorCommented:
To schwertner:

I think you and I are in agreement.  With dynamic SQL, Oracle cannot do the parsing, object binding and execution plan overhead at compile time, so it has to do this work at runtime.  Yes, Oracle can still look for an equivalent SQL statement in the Shared Pool area of memory.  But static SQL statements in stored procedures still require less overhead processing at runtime, so in Oracle stored procedures at least, static SQL statements execute faster than dynamic ones.
0
 
unniksAuthor Commented:
Thanks a lot for the help. This session did clear my doubts regarding the general 'DB'.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 4
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now