Solved

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

Posted on 2007-12-03
17
1,979 Views
Last Modified: 2010-05-18
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
Comment
Question by:unniks
  • 4
  • 4
  • 3
  • +3
17 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20394343
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
 

Author Comment

by:unniks
ID: 20394376
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20394377
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20394384
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
 
LVL 2

Expert Comment

by:JuozasV
ID: 20394568
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
 
LVL 2

Expert Comment

by:JuozasV
ID: 20394577
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
 
LVL 47

Expert Comment

by:schwertner
ID: 20394578
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
 
LVL 47

Expert Comment

by:schwertner
ID: 20394584
Why your question is placed in

Database MySQL  ???????
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 25

Expert Comment

by:imitchie
ID: 20394588
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
 

Author Comment

by:unniks
ID: 20394594
Hi schwertner
This procedure is targeted for MySQL 5.0 version.


Unni
0
 
LVL 2

Expert Comment

by:JuozasV
ID: 20394627
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
 

Author Comment

by:unniks
ID: 20394699
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
 
LVL 47

Expert Comment

by:schwertner
ID: 20395108
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
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 200 total points
ID: 20402633
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
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 50 total points
ID: 20409776
@ 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
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 20410906
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
 

Author Closing Comment

by:unniks
ID: 31412287
Thanks a lot for the help. This session did clear my doubts regarding the general 'DB'.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now