Perl - Database - List View issue

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hai, I have a problem in my program. Aim of the script is that will execute the select query on the given table with required conditions, get list of results and show it in a kind of partly lists (like showing results 1-10, 1-20 etc)

I want to show out in the view like 'showing 1-10 of 300 results' and upon clicking it should display 'showing 11-20 of 300 results' etc.

The problem is the select query will take long time to get result, so the option is to use 'LIMIT' operator in query which will work fine except that my program will miss out total number of results returned from the query.

I found there could be two alternatives for this. First is firing up two queries. one to get list of 1-10 and second query to get count of the query execution (like select count(*) from table)

Second alternative would be to always get select * from table and show up first 10 records or like wise. This suffers from my previous mentioned problem of query taking long time.

I am sure there would be another excellent method for this. (lot of sites are using this including perlmonks!). So how about doing this out, suggestions please.

Note: The query could be anything and dynamic joins could be resulted, so maintaing separate count for the table is also rulled out

Thanx for any suggestions
gopi

Comment on Perl - Database - List View issue

Replies are listed 'Best First'.
Re: Perl - Database - List View issue by davorg (Chancellor) on Jan 10, 2003 at 09:56 UTC
Take a look at Data::Page and Data::Pageset. -- <http://www.dave.org.uk> "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]
Re: Perl - Database - List View issue by osama (Scribe) on Jan 10, 2003 at 10:10 UTC
One way to do it is to use a database to store the results of the query (you could also use that to cache queries - faster for often repeated queries - if your database does not support that) see this pseudocode: Get result of select distinct id from table where QUERY store the IDs (probably separated by commas, or another delimiterm as well as QUERY, and number of results in a database table); To display a page, split the delimited results and use an array slice and just query the database for the rest of the fields for the IDs in the current page (where id in (xx,yy,zz,...etc.) ). This would only be good for small result sets... I don't even recommend showing search that has more than 10-20 pages ... I would probably display a message at the last page saying that there are a lot of results, and ask the user to narrow down the query.	[reply]
Re: Perl - Database - List View issue by AcidHawk (Vicar) on Jan 10, 2003 at 09:57 UTC
Just a thought, Why not do the select * ONCE and save all records into either a temp file or a Hash etc... Only display what was asked for. ie 1-10 or 11-20.. then have another linmk that could re-run the query should that be necessary..?(possible fast and plenty updates/inserts) ----- Of all the things I've lost in my life, its my mind I miss the most.	[reply]
Re: Re: Perl - Database - List View issue by gopi (Initiate) on Jan 10, 2003 at 10:14 UTC
Data::Page and PageSet modules are for the list of values that we have in our hand(variables), that is not what I want. I wanted to eliminate the long processing time which takes during query execution by cutting off with limit but still be able to get the total no of count any time. having a select * ONCE and save it into hashes also rulled out for two reasons * the queries will be dynamic (same query may not come for service next time) * if the query is going to return say 20000 records, imagine the amount memory required to store them in RAM(it would be atleast in MBs) and perl will be smart enough not to release the memory even after the hash is destroyed, which would be used for future requests. This makes other system processes not getting the memory Though I am currently using the second method only, i am rulling it out because of this memory usage problem and i want to use much better algorithm than this Thanx anyway for replies, Any further suggestions are welcomed gopi	[reply]