Re: getting number of records returned by SQL in PHP -- WEB400

RE: "I would expect that the subselect "might" run for each line in the SQL, but that the SQL Optimizer will cache it rendering the issue moot."

Unfortunately, it will not cache the sub-select. It will be re-run for every row returned. I tried this on a select returning 576 records, and I had to kill the query it ran so long.

The other problem with this solution is that if you have a complex WHERE clause, you have to repeat all the conditions on the sub-select, which is really the crux of the matter. If not for that issue, doing a separate SELECT count(*) would be a simple solution.

Some of these issues can be mitigated using stored procedures. When I have time I will post some thoughts and examples.

- John V.

-----Original Message-----

From: web400-request@xxxxxxxxxxxx
Sent: Dec 15, 2009 1:00 PM
To: web400@xxxxxxxxxxxx
Subject: WEB400 Digest, Vol 7, Issue 376

Send WEB400 mailing list submissions to
web400@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/web400
or, via email, send a message with subject or body 'help' to
web400-request@xxxxxxxxxxxx

You can reach the person managing the list at
web400-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific
than "Re: Contents of WEB400 digest..."

*** NOTE: When replying to this digest message, PLEASE remove all text unrelated to your reply and change the subject line so it is meaningful.

Today's Topics:

1. Re: getting number of records returned by SQL in PHP (Mike Pavlak)

----------------------------------------------------------------------

message: 1
date: Tue, 15 Dec 2009 09:24:52 -0800
from: "Mike Pavlak" <Mike.Pavlak@xxxxxxxx>
subject: Re: [WEB400] getting number of records returned by SQL in PHP

I can run that function in 1 SQL statement. This approach makes the
last column in the result set an integer containing the total number of
rows in the SQL select.

I would expect that the subselect "might" run for each line in the SQL,
but that the SQL Optimizer will cache it rendering the issue moot.
Those of you with large data sets please let us know your experience
relative to just running the result data set + the separate query to
count. I would love to hear how this sucker performs and scales. Also
include you version of IBM i so we all know flavor of DB2 is running!
It ran pretty zippy on my machine but I only had 5 records in the result
set...

SELECT CUSTOMER_NUMBER, CUSTOMER_NAME, CUSTOMER_ADDRESS_LINE1,
CUSTOMER_ADDRESS_LINE2, CUSTOMER_CITY, CUSTOMER_STATE,
CUSTOMER_ZIP_CODE, (select count(*) from zenddata/customer)
as total_number_rows FROM zenddata/customer

Regards,

Mike

mike.p@xxxxxxxx Cell: (408)679-1011 Office: (815)722-3454

Zend Server for IBM i Beta avilable at
http://www.zend.com/en/products/server/zend-server-5-new-ibmi

John Valance
JValance Consulting
jvalance@xxxxxxxxxxx
802 355-4024