stored proc vs dynamic sql

[t***@gmail.com]

hello all!

We've been doing some benchmark testing to compare using dynamic SQL vs a stored procedure and have come up with a couple questions that we'd like some help with.

we are on version 11.7.FC6 on AIX 6.1 in a HDR environment.....

The test is very simple, we are doing 100,000 executions of the same SQL statement where a single row is returned based on the PK and all columns of the table are returned.

The table is called Customer and its primary key is PK_Customer_ID (ID).

For the dynamic SQL test we run the following 100,000 times:
SELECT ID AS ID1_9_0_, ACTIVE AS ACTIVE7_9_0_, CREATE_DATE AS CREATE8_9_0_,
CREATED_BY AS CREATED9_9_0_, UPDATE_DATE AS UPDATE10_9_0_, UPDATE_VERSION AS UPDATE11_9_0_,
UPDATED_BY AS UPDATED12_9_0_, BILL_TO_IDENT AS BILL2_9_0_, CUSTOMER_IDENT AS CUSTOMER4_9_0_,
CUSTOMER_NAME AS CUSTOMER3_9_0_, PARENT_ID AS PARENT5_9_0_,
PROJECT_IDENT AS PROJECT6_9_0_, SSCC_PREFIX AS SSCC13_9_0_,
SSCC_SEQ_NAME AS SSCC14_9_0_
FROM CUSTOMER AS CUSTOMEREN0_
WHERE ID = ?

For the stored procedure test, we have a stored procedure (batch_run_test) that contains the same query and we call the stored procedure 100,000 times:
{call batch_run_test(?)}

For both tests, the same value of '3' for the bind variable was used for all 100,000 calls.

We collected the isreads and bufreads for the customer table, the pk_customer_id index and the systables.

For the dynamic SQL test:
Customer: 0 isreads, 100000 bufreads
pk_customer_id: 100000 isreads, 100000 bufreads
systables: 101197 isreads, 404158 bufreads

For the stored procedure test:
Customer: 0 isreads, 100000 bufreads
pk_customer_id: 100000 isreads, 100000 bufreads
systables: 75 isreads, 169 bufreads

Our questions are as follows:

1) What exactly are isreads? The book definition says it is a call to the ISAM read function, but what does that really mean in comparison to bufreads?

2) Why does the customer table have 0 isreads but the pk_customer_id index have 100,000 isreads?

3) Why are there such high numbers of reads on systables for the dynamic SQL? Especially the 400,000+ bufreads. The exact same SQL was run every time. Shouldn't this SQL statement have very quickly gone to SQL statement cache and executed from there and avoided additional calls to read systables?

Thanks in advance...........

[Marco Greco]

Post by t***@gmail.com
hello all!
We've been doing some benchmark testing to compare using dynamic SQL vs a stored procedure and have come up with a couple questions that we'd like some help with.
we are on version 11.7.FC6 on AIX 6.1 in a HDR environment.....
The test is very simple, we are doing 100,000 executions of the same SQL statement where a single row is returned based on the PK and all columns of the table are returned.
The table is called Customer and its primary key is PK_Customer_ID (ID).
SELECT ID AS ID1_9_0_, ACTIVE AS ACTIVE7_9_0_, CREATE_DATE AS CREATE8_9_0_,
CREATED_BY AS CREATED9_9_0_, UPDATE_DATE AS UPDATE10_9_0_, UPDATE_VERSION AS UPDATE11_9_0_,
UPDATED_BY AS UPDATED12_9_0_, BILL_TO_IDENT AS BILL2_9_0_, CUSTOMER_IDENT AS CUSTOMER4_9_0_,
CUSTOMER_NAME AS CUSTOMER3_9_0_, PARENT_ID AS PARENT5_9_0_,
PROJECT_IDENT AS PROJECT6_9_0_, SSCC_PREFIX AS SSCC13_9_0_,
SSCC_SEQ_NAME AS SSCC14_9_0_
FROM CUSTOMER AS CUSTOMEREN0_
WHERE ID = ?
{call batch_run_test(?)}
For both tests, the same value of '3' for the bind variable was used for all 100,000 calls.
We collected the isreads and bufreads for the customer table, the pk_customer_id index and the systables.
Customer: 0 isreads, 100000 bufreads
pk_customer_id: 100000 isreads, 100000 bufreads
systables: 101197 isreads, 404158 bufreads
Customer: 0 isreads, 100000 bufreads
pk_customer_id: 100000 isreads, 100000 bufreads
systables: 75 isreads, 169 bufreads
1) What exactly are isreads? The book definition says it is a call to the ISAM read function, but what does that really mean in comparison to bufreads?
2) Why does the customer table have 0 isreads but the pk_customer_id index have 100,000 isreads?
3) Why are there such high numbers of reads on systables for the dynamic SQL? Especially the 400,000+ bufreads. The exact same SQL was run every time. Shouldn't this SQL statement have very quickly gone to SQL statement cache and executed from there and avoided additional calls to read systables?
Thanks in advance...........
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

1) the sql layer, in executing the query plan, will call the rsam layer
(through isread) for each value it the active set.
The rsam layer in turn, to produce each values, will have to read from the
buffers 0 or more times (0 is not a mistake - oversemplification, but it could
be that the next value in the active set has already been read, from the same
buffer as one of the previous values), depending on query plans and physical
placement of the data.

2) that's because the engine is taking a key only plan

3) That's auto_reprepare at work: every time you execute the same prepared
statements, auto_reprepare needs to check that no table has changed since the
original prepare.
This is done by checking the dictionary cache, which in turn, needs to access
systables (mostly because a dictionary cache entry could have changed because
of rollbacks).
The stored procedure doesn't have to do that, at least for directly referenced
tables.

Now, with regards to 3), 12.10.xC5 has 2 nifty enhancements.
1- the dictionary cache can keep track of systables, and doesn't need to read
systables every time.
That'll save you most of those 400k systables buffreads.
2- you can set AUTO_REPREPARE to use a new optimistic mode (that's 3, BTW): if
the current statement has been checked less than a second ago, auto_reprepare
will not check it again. This may lead to a few more spurious -710, but can
gain you some performance.