OK I will try to ask this and make sense out of this also.
First off I am doing this on a back-end admin page.
I have a DB with lets say 5000 records and a dozen or more fields.
On the page which will display these records I am planning on using pagination to display so many records at once. So far so good.
What i would like to do is provide a row above the table of records where the user can enter text to filter the display on each column.
I plan to use AJAX and filter as the user types which in itself is not a problem.
The problem I see is the numerous DB queries and I don't see querying the DB each time the user types a letter as a practical approach. (I could be wrong).
What I was thinking of doing is loading the full record set into an array and then filter out the array of the needed data as the user types.
I have not done any intense array manipulations like this before and am wondering if this seems like a practical approach.
I would like to ask for any advice or suggestions on this. I may even have my head in the wrong area and not see the forest thru the trees.
>First off I am doing this on a back-end admin page.
>I have a DB with lets say 5000 records and a dozen or more fields.
>On the page which will display these records I am planning on using >pagination to display so many records at once. So far so good.
>What i would like to do is provide a row above the table of records >where the user can enter text to filter the display on each column.
>I plan to use AJAX and filter as the user types which in itself is not a >problem.
Why AJAX? Why not do it the simple way with a "refresh" button?
>The problem I see is the numerous DB queries and I don't see querying >the DB each time the user types a letter as a practical approach. (I >could be wrong).
>What I was thinking of doing is loading the full record set into an >array and then filter out the array of the needed data as the user types.
>I have not done any intense array manipulations like this before and am >wondering if this seems like a practical approach.
I don't think so. You still have to fetch the data from the DB whenever
the user changes the filter. Every AJAX request to the server is just
another HTTP request, completely independent from all others before.
You can't store big data from one request and then just output parts of
it on every coming request (unless you store all the data in a session,
but this wouldn't make much sense here and would still cause a lot of IO
overhead).
In other words: Every request (including the ones via AJAX) causes your
script to start from scratch again, so you have to fetch the data and
filter it over and over again anyway. Also don't forget that after the
user changes the filter options, the new data has to be transferred to
the client. When there's a lot of records, this might take some time.
Don't expect a real-time display and ask yourself: Do you really need
AJAX stuff or wouldn't it be enough to use a good old-fashioned form
with a "refresh" button?
> First off I am doing this on a back-end admin page.
> I have a DB with lets say 5000 records and a dozen or more fields.
> On the page which will display these records I am planning on using
> pagination to display so many records at once. So far so good.
> What i would like to do is provide a row above the table of records
> where the user can enter text to filter the display on each column.
> I plan to use AJAX and filter as the user types which in itself is not a
> problem.
> The problem I see is the numerous DB queries and I don't see querying
> the DB each time the user types a letter as a practical approach. (I
> could be wrong).
> What I was thinking of doing is loading the full record set into an
> array and then filter out the array of the needed data as the user types.
> I have not done any intense array manipulations like this before and am
> wondering if this seems like a practical approach.
If you are worried about retrieving matching rows from a 5000 row DB table using a SQL query, I doubt that using a file to store a serialized 5000 element bi-dimensional array and looping though it to find matches will be faster; not to mention the mess of keeping data up-to-date and taking care of concurrent access.
A 10,000,000 record table is a big table. A 5,000 record table is not. Just make sure that your SQL knowledge goes beyond "SELECT * FROM foo". I've seen too many amateurs in forums building web sites without even using the WHERE clause.
> OK I will try to ask this and make sense out of this also.
> First off I am doing this on a back-end admin page.
> I have a DB with lets say 5000 records and a dozen or more fields.
> On the page which will display these records I am planning on using pagination to
> display so many records at once. So far so good.
> What i would like to do is provide a row above the table of records where the user
> can enter text to filter the display on each column.
> I plan to use AJAX and filter as the user types which in itself is not a problem.
> The problem I see is the numerous DB queries and I don't see querying the DB each
> time the user types a letter as a practical approach. (I could be wrong).
> What I was thinking of doing is loading the full record set into an array and then
> filter out the array of the needed data as the user types.
> I have not done any intense array manipulations like this before and am wondering if
> this seems like a practical approach.
> I would like to ask for any advice or suggestions on this. I may even have my head
> in the wrong area and not see the forest thru the trees.
> Thanks
> Scotty
I think 5000 records is not much, nowadays.
We have sortable tables with client side scripting, why not filter the records with
client side scripting?
> OK I will try to ask this and make sense out of this also.
> First off I am doing this on a back-end admin page.
> I have a DB with lets say 5000 records and a dozen or more fields.
> On the page which will display these records I am planning on using
> pagination to display so many records at once. So far so good.
> What i would like to do is provide a row above the table of records
> where the user can enter text to filter the display on each column.
> I plan to use AJAX and filter as the user types which in itself is not a
> problem.
> The problem I see is the numerous DB queries and I don't see querying
> the DB each time the user types a letter as a practical approach. (I
> could be wrong).
> What I was thinking of doing is loading the full record set into an
> array and then filter out the array of the needed data as the user types.
> I have not done any intense array manipulations like this before and am
> wondering if this seems like a practical approach.
> I would like to ask for any advice or suggestions on this. I may even
> have my head in the wrong area and not see the forest thru the trees.
> Thanks
> Scotty
If you're going to g the ajax route, I can recommend
http://www.sencha.com/products/extjs/. You will find an example there
that does almost exactly what you are talking about.
Scott Johnson wrote:
> The problem I see is the numerous DB queries and I don't see querying
> the DB each time the user types a letter as a practical approach. (I
> could be wrong).
You do not have a PHP problem unless you are using PHP client-side. Start a client-side timer that must reach zero before the request is made that invokes the database query server-side. Reset it when the user types a second character into the form control. It has been done before.
> What I was thinking of doing is loading the full record set into an
> array and then filter out the array of the needed data as the user types.
That might be feasible if the original data was not modified after retrieval and it was not too large for the client system to process. In all other cases you want to do the filtering server-side.
> I have not done any intense array manipulations like this before and am
> wondering if this seems like a practical approach.
You would not be manipulating *PHP* arrays (but see above), so …
PointedEars
-- When all you know is jQuery, every problem looks $(olvable).
Thomas 'PointedEars' Lahn wrote:
> Scott Johnson wrote:
>> What I was thinking of doing is loading the full record set into an
>> array and then filter out the array of the needed data as the user types.
> That might be feasible if the original data was not modified after
> retrieval and it was not too large for the client system to process.
> In all other cases you want to do the filtering server-side.
That is, in the database query (`… WHERE …'). If you do not want to rely on the client-side (i. e., have it to work without XHR as well) and do not care if your displayed data reflects that currently in the database, you could also store the unfiltered query result in the session, provided your session storage can grow large enough without a relevant performance penalty on access.
>> I have not done any intense array manipulations like this before and am
>> wondering if this seems like a practical approach.
> You would not be manipulating *PHP* arrays (but see above), so …
If you would store the query result as a PHP array in $_SESSION, then you could use PHP array filtering functions on the array element of $_SESSION.
PointedEars
-- Prototype.js was written by people who don't know javascript for people
who don't know javascript. People who don't know javascript are not
the best source of advice on designing systems that use javascript.
-- Richard Cornford, cljs, <f806at$ail$1$8300d...@news.demon.co.uk>
>> The problem I see is the numerous DB queries and I don't see querying
>> the DB each time the user types a letter as a practical approach. (I
>> could be wrong).
> You do not have a PHP problem unless you are using PHP client-side. Start a
> client-side timer that must reach zero before the request is made that
> invokes the database query server-side. Reset it when the user types a
> second character into the form control. It has been done before.
>> What I was thinking of doing is loading the full record set into an
>> array and then filter out the array of the needed data as the user types.
> That might be feasible if the original data was not modified after retrieval
> and it was not too large for the client system to process. In all other
> cases you want to do the filtering server-side.
>> I have not done any intense array manipulations like this before and am
>> wondering if this seems like a practical approach.
> You would not be manipulating *PHP* arrays (but see above), so …
> PointedEars
Man I never even considered the possible data change in which with using preloaded arrays the page would have to be reloaded to update the data. So that alone is a deal breaker for the array method.
>> First off I am doing this on a back-end admin page.
>> I have a DB with lets say 5000 records and a dozen or more fields.
>> On the page which will display these records I am planning on using
>> pagination to display so many records at once. So far so good.
>> What i would like to do is provide a row above the table of records
>> where the user can enter text to filter the display on each column.
>> I plan to use AJAX and filter as the user types which in itself is not a
>> problem.
> Why AJAX? Why not do it the simple way with a "refresh" button?
>> The problem I see is the numerous DB queries and I don't see querying
>> the DB each time the user types a letter as a practical approach. (I
>> could be wrong).
>> What I was thinking of doing is loading the full record set into an
>> array and then filter out the array of the needed data as the user types.
>> I have not done any intense array manipulations like this before and am
>> wondering if this seems like a practical approach.
> I don't think so. You still have to fetch the data from the DB whenever
> the user changes the filter. Every AJAX request to the server is just
> another HTTP request, completely independent from all others before.
> You can't store big data from one request and then just output parts of
> it on every coming request (unless you store all the data in a session,
> but this wouldn't make much sense here and would still cause a lot of IO
> overhead).
> In other words: Every request (including the ones via AJAX) causes your
> script to start from scratch again, so you have to fetch the data and
> filter it over and over again anyway. Also don't forget that after the
> user changes the filter options, the new data has to be transferred to
> the client. When there's a lot of records, this might take some time.
> Don't expect a real-time display and ask yourself: Do you really need
> AJAX stuff or wouldn't it be enough to use a good old-fashioned form
> with a "refresh" button?
> Micha
Thanks Micha
You bring up a great issues to the idea of accessing the data without a separate page load. The data would need to be loaded into a session to be accessible with subsequent calls to the server w/o calling an entire page load.
Not sure how loading a large data set into a session would affect performance. I know 5000 records is not all the large in the big picture of large things but I like to 'try' to think ahead to larger issues.
But Thomas brought up a good point of data being modified after the initial page load would not be represented any longer. So I think the separate data scheme is not a feasible solution any longer.
> El 10/02/2012 2:18, Scott Johnson escribió/wrote:
>> First off I am doing this on a back-end admin page.
>> I have a DB with lets say 5000 records and a dozen or more fields.
>> On the page which will display these records I am planning on using
>> pagination to display so many records at once. So far so good.
>> What i would like to do is provide a row above the table of records
>> where the user can enter text to filter the display on each column.
>> I plan to use AJAX and filter as the user types which in itself is not a
>> problem.
>> The problem I see is the numerous DB queries and I don't see querying
>> the DB each time the user types a letter as a practical approach. (I
>> could be wrong).
>> What I was thinking of doing is loading the full record set into an
>> array and then filter out the array of the needed data as the user types.
>> I have not done any intense array manipulations like this before and am
>> wondering if this seems like a practical approach.
> If you are worried about retrieving matching rows from a 5000 row DB
> table using a SQL query, I doubt that using a file to store a serialized
> 5000 element bi-dimensional array and looping though it to find matches
> will be faster; not to mention the mess of keeping data up-to-date and
> taking care of concurrent access.
> A 10,000,000 record table is a big table. A 5,000 record table is not.
> Just make sure that your SQL knowledge goes beyond "SELECT * FROM foo".
> I've seen too many amateurs in forums building web sites without even
> using the WHERE clause.
Yeah I was thinking that it would take more processing to use a separate data set, filter it, reorder it and then display a subset of that would be a bit cumbersome.
Thomas brought up a good idea to use a timer between key presses that would limit the queries to a time separation if the user types fast.
> On Feb 10, 1:18 am, Scott Johnson<nooneh...@chalupasworld.com> wrote:
>> OK I will try to ask this and make sense out of this also.
>> First off I am doing this on a back-end admin page.
>> I have a DB with lets say 5000 records and a dozen or more fields.
>> On the page which will display these records I am planning on using
>> pagination to display so many records at once. So far so good.
>> What i would like to do is provide a row above the table of records
>> where the user can enter text to filter the display on each column.
>> I plan to use AJAX and filter as the user types which in itself is not a
>> problem.
>> The problem I see is the numerous DB queries and I don't see querying
>> the DB each time the user types a letter as a practical approach. (I
>> could be wrong).
>> What I was thinking of doing is loading the full record set into an
>> array and then filter out the array of the needed data as the user types.
>> I have not done any intense array manipulations like this before and am
>> wondering if this seems like a practical approach.
>> I would like to ask for any advice or suggestions on this. I may even
>> have my head in the wrong area and not see the forest thru the trees.
>> Thanks
>> Scotty
> If you're going to g the ajax route, I can recommend
> http://www.sencha.com/products/extjs/. You will find an example there
> that does almost exactly what you are talking about.
Hmmm. Very worth a deeper look. Thanks for the resource.
> > On Feb 10, 1:18 am, Scott Johnson<nooneh...@chalupasworld.com> wrote:
> >> OK I will try to ask this and make sense out of this also.
> >> First off I am doing this on a back-end admin page.
> >> I have a DB with lets say 5000 records and a dozen or more fields.
> >> On the page which will display these records I am planning on using
> >> pagination to display so many records at once. So far so good.
> >> What i would like to do is provide a row above the table of records
> >> where the user can enter text to filter the display on each column.
> >> I plan to use AJAX and filter as the user types which in itself is not a
> >> problem.
> >> The problem I see is the numerous DB queries and I don't see querying
> >> the DB each time the user types a letter as a practical approach. (I
> >> could be wrong).
> >> What I was thinking of doing is loading the full record set into an
> >> array and then filter out the array of the needed data as the user types.
> >> I have not done any intense array manipulations like this before and am
> >> wondering if this seems like a practical approach.
> >> I would like to ask for any advice or suggestions on this. I may even
> >> have my head in the wrong area and not see the forest thru the trees.
> >> Thanks
> >> Scotty
> > If you're going to g the ajax route, I can recommend
> >http://www.sencha.com/products/extjs/. You will find an example there
> > that does almost exactly what you are talking about.
> Hmmm. Very worth a deeper look. Thanks for the resource.
> On 2/9/2012 5:39 PM, Michael Fesser wrote:
>> .oO(Scott Johnson)
>>> First off I am doing this on a back-end admin page.
>>> I have a DB with lets say 5000 records and a dozen or more fields.
>>> On the page which will display these records I am planning on using
>>> pagination to display so many records at once. So far so good.
>>> What i would like to do is provide a row above the table of records
>>> where the user can enter text to filter the display on each column.
>>> I plan to use AJAX and filter as the user types which in itself is not a
>>> problem.
>> Why AJAX? Why not do it the simple way with a "refresh" button?
>>> The problem I see is the numerous DB queries and I don't see querying
>>> the DB each time the user types a letter as a practical approach. (I
>>> could be wrong).
>>> What I was thinking of doing is loading the full record set into an
>>> array and then filter out the array of the needed data as the user
>>> types.
>>> I have not done any intense array manipulations like this before and am
>>> wondering if this seems like a practical approach.
>> I don't think so. You still have to fetch the data from the DB whenever
>> the user changes the filter. Every AJAX request to the server is just
>> another HTTP request, completely independent from all others before.
>> You can't store big data from one request and then just output parts of
>> it on every coming request (unless you store all the data in a session,
>> but this wouldn't make much sense here and would still cause a lot of IO
>> overhead).
>> In other words: Every request (including the ones via AJAX) causes your
>> script to start from scratch again, so you have to fetch the data and
>> filter it over and over again anyway. Also don't forget that after the
>> user changes the filter options, the new data has to be transferred to
>> the client. When there's a lot of records, this might take some time.
>> Don't expect a real-time display and ask yourself: Do you really need
>> AJAX stuff or wouldn't it be enough to use a good old-fashioned form
>> with a "refresh" button?
>> Micha
> Thanks Micha
> You bring up a great issues to the idea of accessing the data without a
> separate page load. The data would need to be loaded into a session to
> be accessible with subsequent calls to the server w/o calling an entire
> page load.
Whether the data are in a session or not has nothing to do with whether the page is loaded or not. If the data reside on the server (whether in the database or the session variable), it will take a request to the server (either a page load or ajax) to access the data. If the data are on the client, no request to the server is required.
> Not sure how loading a large data set into a session would affect
> performance. I know 5000 records is not all the large in the big picture
> of large things but I like to 'try' to think ahead to larger issues.
Significantly.
> But Thomas brought up a good point of data being modified after the
> initial page load would not be represented any longer. So I think the
> separate data scheme is not a feasible solution any longer.
> Thanks
Too many variables to say for sure. It might be ok for a shopping cart, for instance. But not for lap times in a car race.
-- ==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attglobal.net
==================
Scott Johnson wrote:
> On 2/10/2012 2:44 AM, Captain Paralytic wrote:
>> If you're going to g the ajax route, I can recommend
>> http://www.sencha.com/products/extjs/. You will find an example there
>> that does almost exactly what you are talking about.
> Hmmm. Very worth a deeper look. Thanks for the resource.
Because of its *ext*ensive size and potential compatibility issues due
to browser sniffing, you should only use ExtJS for an intranet desktop application. I happen to have helped built such a monster last year,
with ExtJS for the frontend and Zend Framework and MySQL on the backend.
Am 2012-02-11 00:03, Thomas 'PointedEars' Lahn meinte:
> Scott Johnson wrote:
>> On 2/10/2012 2:44 AM, Captain Paralytic wrote:
>>> If you're going to g the ajax route, I can recommend
>>> http://www.sencha.com/products/extjs/. You will find an example there
>>> that does almost exactly what you are talking about.
>> Hmmm. Very worth a deeper look. Thanks for the resource.
> Because of its *ext*ensive size and potential compatibility issues due
> to browser sniffing, you should only use ExtJS for an intranet desktop > application. I happen to have helped built such a monster last year,
> with ExtJS for the frontend and Zend Framework and MySQL on the backend.
Sounds like pimcore. Had to do some minor adjustments on an
ExtJS-MVC-Application (with Coldfusion backend) - pretty atrocious.
Gregor Kofler wrote:
> Thomas 'PointedEars' Lahn meinte:
>> Scott Johnson wrote:
>>> On 2/10/2012 2:44 AM, Captain Paralytic wrote:
>>>> If you're going to g the ajax route, I can recommend
>>>> http://www.sencha.com/products/extjs/. You will find an example there
>>>> that does almost exactly what you are talking about.
>>> Hmmm. Very worth a deeper look. Thanks for the resource.
>> Because of its *ext*ensive size and potential compatibility issues due
>> to browser sniffing,
… and UI built completely by client-side scripting instead of building on HTML (not accessible or search-engine friendly) …
>> you should only use ExtJS for an intranet desktop application. I happen
>> to have helped built such a monster last year, with ExtJS for the
>> frontend and Zend Framework and MySQL on the backend.
> Sounds like pimcore.
No, it was a custom built-from-scratch hardware management application for the intranet of a multinational corporation. Not my idea.
> Had to do some minor adjustments on an ExtJS-MVC-Application (with
> Coldfusion backend) - pretty atrocious.
ACK.
PointedEars
-- Use any version of Microsoft Frontpage to create your site.
(This won't prevent people from viewing your source, but no one
will want to steal it.)
-- from <http://www.vortex-webdesign.com/help/hidesource.htm> (404-comp.)
On Fri, 10 Feb 2012 09:26:21 +0100, ?lvaro G. Vicario wrote:
> If you are worried about retrieving matching rows from a 5000 row DB > table using a SQL query, I doubt that using a file to store a serialized > 5000 element bi-dimensional array and looping though it to find matches > will be faster; not to mention the mess of keeping data up-to-date and > taking care of concurrent access.
Kinda depends on the application. A reasonable reason to load 5000 rows
into a structure would be if you're doing some really fast lookups and
you have the memory to spare for it. A place that you might be wanting
to do that is a custom ETL tool for a single process. Pull data from
tape, update old codes to new codes on the fly, store data in a
database. Sure, you can DO it in the database with a salting of joins
and ALTERs, but if you're already getting the data out of something that
needs a program to do it anyway, why not fix it on the fly?
HOWEVER, that's a really specialized case. The vast amjority of the
time, you're not going to know that you're probably going to need the
whole table, and you're going to be way better off letting a database
engine manage it. What you're actually doing may actually only end up
needing a few hundred rows, and the DB will very quickly have those in a
cache and accessible for very little overhead. In which case, you're
moving far less data off of the disk (which is achingly slow in
comparison to fetching the same data from a cache, even layers deep),
and your net result is much more speed. The cache resources are going to
be allocated and used ANYWAY already, so you're not even "using less" by
neglecting the DB.
> A 10,000,000 record table is a big table. A 5,000 record table is not. > Just make sure that your SQL knowledge goes beyond "SELECT * FROM foo". > I've seen too many amateurs in forums building web sites without even > using the WHERE clause.
"SELECT *" is "shovelling too much data", similar to above, even in
addition to how much trouble gets caused when someone comes along and
sticks another column onto the front of the record layout in a few
years.
And EVERY table ends up being used far longer than anyone ever expects.
My SO's senior project for a college course is still out there after
most of a decade, doing its thing, and is actively used by a couple of
hundred people a month.
-- Technical points aside, you could probably beat someone to
death with a Newton if you had to. Try that with a Palm Pilot!
--Dan Duncan in comp.sys.newton.misc
