× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.