What are Parameter Markers?

Parameter Markers

Parameter Markers show up as ? in queries when you look at them in a dynamic SQL snapshot or in the sysibmadm views. They show up when the value for a variable is not hard-coded at the time of preparation, but instead is changeable at execution time. I’ve included a couple of code snippets to illustrate what I’m talking about.

Parameterized vs. non-parameterized queries

When I start going off on the advantages and disadvantages of parameterized queries, I invariably get a few blank looks. So I’ve included an example here (coded in perl, but not fully complete), to give an idea of what I’m talking about.

Please excuse my partial messy perl syntax – the concepts are the same in other languages

Non-parameter:

•$orders_id=1234;

•$sql=“select * from orderitems where orders_id=$orders_id”;

•my $sth = $dbh->prepare($sql);

•$sth->execute();

If you re-execute the statement, you can only do it for orders_id 1234, even if you change the value of $orders_id

Advantages: Makes full use of runstats, including distribution statistics

Disadvantages: Re-compiles on every execution, meaning time is taken to calculate the access path; Also, every execution has an entry in the dynamic package cache, making analysis more difficult

Parameter:

•$orders_id=1234;

•$sql=“select * from orderitems where orders_id=?”;

•my $sth = $dbh->prepare($sql);

•$sth->execute(($orders_id));

If you re-execute the statement, you can change the value of orders_id or pass an entirely different variable on execution.

Advantages: Access plan is calculated only once, eliminating overhead for calculating access plan. Executions are grouped in the dynamic sql snapshot, making spotting problems easier.

Disadvantages: Does not make use of distribution statistics, meaning that performance (especially for data that is not normally distributed) may be poorer

Overall

In general, I’m a fan of not using parameter markers, because in many databases the performance advantage of using distribution statistics is greater than the performance disadvantage of calculating the access plan. However, the overall suggestion that comes out of IBM is to use parameter markers just about all of the time to get rid of the overhead of calculating the access plan. I suppose it depends on how normal the distribution of your data is.