Re: getting number of records returned by SQL in PHP

[John Valance <jvalance@xxxxxxxxxxx>]

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