Dynamic statements are stored in the cache in compiled versions and raw sql versions. At next request, strings are compared. They will not be equal so dB2 will recompile or reprpep the second one and insert it in the cache taking twice the amount of space.
If you replace by parameter markers, compile the statement then the strings will compare and the first compiled stmt. will be used.
You save one prep, one insert. On the other hand, you'll have to go back to the appl., prompt the user for the values, go back to the server and execute.
You can set a parm. DEFERED PREPARE, that will carry both the prep and execute together in one trip. As the stmt. is already compiled, the execute will folllow immediately and you save all around.
In your case if F=1 or F=2 are replaced with F=? (set should also be with markers) and F= always return a single row, markers will improve performance significantly.
HTH,
Pierre.
Ian D. Bjorhovde wrote:
[EMAIL PROTECTED]">----- Original Message -----
From: "Juan Lanus" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, March 21, 2001 9:02 AM
Subject: Re: DB2EUG: Overhead for Dynamic SQL in UDB/AIX?Hi, Bill
Please let me throw two ideas I'm sure you are already aware of:
1- DB2 caches dymanic SQL statements. The great advantage of those
parameter markers is that they help the optimizer to recognize repeating
statements even with different data.
Juan,
For the following two statements:
update mytable set (a,b,c,d,e) = (1,2,3,4,5) where f = 1;
update mytable set (a,b,c,d,e) = (6,7,8,9,0) where f = 2;
Are you saying that, (without using parameter markers), DB2 should
get a hit in the package cache for the second statement?
Thanks,
ian d. bjorhovde cephalad corporation
[EMAIL PROTECTED] intelligent solutions for
http://cephalad.com systems and data management
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
