Обсуждение: Web + Slicing/Paging datas
Hi! In a mod_py application I wanna write a wrapper that handle all PSQL data view with paging/slicing. For example: I have 1.500 records. I wanna show only N (f. ex: 15) records in the view, other records are accessable with a pager (links): [First, P-2, P-1, P, P+1 P+2, Last] F. Ex: First, 5, 6, {7}, 8, 9, Last Ok, I can realize this with count, and next to define the select's start/and limit parameters. But I heard about the count(*) is slow in PG. This paging is a typical problem: I need to paid two times for the datas. First time I get all data, but I only count them. Next time I get only the slice of records I need. As I saw, some systems with less data do this: 1.) Inserts all records to a temp table. Check the affected rows (as count). Slicing the records. Fetch the slice records. Destroy temp table. 2.) Select all records. Fetching all records. Dropping all not needed elements. Return needed records. Close cursor. Every solution is slow, the 1.) is because of storing the records (bulk insert) 2.) is because of fetching not needed records (network speed). So I wanna ask: what are you doing if you wanna use paging/slicing of records? The first (count/slicing) solution is enough fast for you? Thanks for your help: dd
durumdara wrote: > Hi! > > In a mod_py application I wanna write a wrapper that handle all PSQL > data view with paging/slicing. > > .. > Inserts all records to a temp table. > Check the affected rows (as count). > Slicing the records. > Fetch the slice records. > Destroy temp table. how about KEEPING the data in this slice format, and keeping the count somewhere ? you'd need to update the count and slice info when new data gets added, but perhaps thats better than doing it every time you want to view a slice?
Hi! 2009.04.23. 9:23 keltezéssel, John R Pierce írta: > durumdara wrote: >> Hi! >> >> In a mod_py application I wanna write a wrapper that handle all PSQL >> data view with paging/slicing. >> >> .. >> Inserts all records to a temp table. >> Check the affected rows (as count). >> Slicing the records. >> Fetch the slice records. >> Destroy temp table. > > > how about KEEPING the data in this slice format, and keeping the count > somewhere ? you'd need to update the count and slice info when new > data gets added, but perhaps thats better than doing it every time you > want to view a slice? > > This is a complicated thing. These datas are provided by a query, with some user's conditions. For example: Text = Free String Search Live = This data is living Category = Some category SubCategory = Some subcategory ... etc. So your way is possible working with this pseudo code: def Slicing(PageIndex, Conditions): # Del recs that have older than 30 minutes delsql = "delete from pagingtable where inserted < %s" % IncWithMinutes(Now, -30) ExecSQL(delsql) # The Query datasql = BuildSQL(Conditions) # Have same query in the pool? checksql = "select count from pagingtable where sql='%s'" % SafeEscape(datasql) records = ExecSQL(checksql) if records: # Yes count = records[0]['COUNT'] else: # No, we must run a count sql to check countsql = BuildSQL(Conditions, Count = 1) datarecords = ExecSQL(countsql) datarecord = datarecords[0] count = datarecord['COUNT'] # We must insert it to the paging table InsertPagingRecord(datasql, count) ... # Use the count ... finalsql = BuildSQL(Conditions, WithPaging = PageIndex) finalrecords = ExecSQL(finalsql) ... But possible it is too complex and I fear that it cause more pain than I winning in it... (deadlocks?)... Thanks for your help: dd
On 2009-04-23, durumdara <durumdara@gmail.com> wrote: > Hi! > > In a mod_py application I wanna write a wrapper that handle all PSQL > data view with paging/slicing. > > For example: > I have 1.500 records. I wanna show only N (f. ex: 15) records in the > view, other records are accessable with a pager (links): For small numbers of records you can use SELECT ... ... LIMIT how_many OFFSET where_to_start If there are a large number of results this may be inefficient.
On Tue, Apr 28, 2009 at 2:21 PM, Jasen Betts <jasen@xnet.co.nz> wrote: > On 2009-04-23, durumdara <durumdara@gmail.com> wrote: >> Hi! >> >> In a mod_py application I wanna write a wrapper that handle all PSQL >> data view with paging/slicing. >> >> For example: >> I have 1.500 records. I wanna show only N (f. ex: 15) records in the >> view, other records are accessable with a pager (links): > > For small numbers of records you can use > > > SELECT ... > ... LIMIT how_many OFFSET where_to_start > > > If there are a large number of results this may be inefficient. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Perhaps another way would be to make use of some unique column(s). Choose suitable column(s) which you have a unique constraint, construct your query and include an "ORDER BY" clause on the unique column(s) either ascending or descending (choose one and stick with it). In this same query include a filter (the WHERE clause) on the unique column(s) and use a greater than or less than filter on a value (this value will be the value of the unique column of the last record that was returned in the previous call to this query), for the first run of this query supply a value either smaller than the unique column could possibly have in your data, for example 0 for a field where values start at 1. Each time you run your query, store the value of the chosen column of the last record. Now use this value in the WHERE clause in the next call. And so on. I hope you get the idea. May be an example: Select id,field2,field3 from sometable WHERE id>[dynamic_id_value] ORDER BY id ASC LIMIT [desired_number_of_records_per_page] ; You will need to substitute the [dynamic_id_value] with the value of the "id" field of the last returned record. Allan.