Thread: What's faster?
Hello all! I'm performing a query that returns me hundreds of records... but I need cut them in pages that have 15 items! (using PHP) So, is it faster: select blablabal from _complex_query if (count($result) > 15) show_pages; show_only_15_rows($result); or: select count(*) from _complex_query if ($result1 > 15) show_pages; select blablabal from _complex_query LIMIT ... (see the LIMIT!) show $result On the first, I can use pg_num_rows instead of count(), too. what do you think? Thank you!
Hi, Second option. For the first option, if your query returns say 10,000 rows then php has to actually fetch 10,000 rows from the database before anything else happens even though you're only displaying 15. Silas Justiniano wrote: > Hello all! > > I'm performing a query that returns me hundreds of records... but I > need cut them in pages that have 15 items! (using PHP) > > So, is it faster: > > select blablabal from _complex_query > if (count($result) > 15) show_pages; > show_only_15_rows($result); > > or: > > select count(*) from _complex_query > if ($result1 > 15) show_pages; > select blablabal from _complex_query LIMIT ... (see the LIMIT!) > show $result > > On the first, I can use pg_num_rows instead of count(), too. > > what do you think?
Silas Justiniano wrote:
> Hello all!
>
> I'm performing a query that returns me hundreds of records... but I
> need cut them in pages that have 15 items! (using PHP)
>
> So, is it faster:
>
> select blablabal from _complex_query
> if (count($result) > 15) show_pages;
> show_only_15_rows($result);
>
> or:
>
> select count(*) from _complex_query
> if ($result1 > 15) show_pages;
> select blablabal from _complex_query LIMIT ... (see the LIMIT!)
> show $result
If you don't care about how many pages you'll get, try:
select blablabal from _complex_query LIMIT (15+1);
if (count($result) > 15) show_pages;
show_only_15_rows($result);
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
Depending on your keys neither. Rather let the DB handle the resultset. count(*) is quite slow. How about something like select blablabla from _complex_query order by _key_ (optional DESC or ASC) OFFSET xxx LIMIT 15 where your offset would be a parameter from the php side and is basically the page number of the number of pages you want to display. The only drawback of that is that you will never see the total number of hits. So maybe you do a count(*) ONCE and then use the above query to loop over the resultset - or you don't show the number of pages and just have a "next results" and "previous results" button that adjusts the offset parameter. On Wednesday 08 February 2006 19:45, Silas Justiniano wrote: > Hello all! > > I'm performing a query that returns me hundreds of records... but I > need cut them in pages that have 15 items! (using PHP) > > So, is it faster: > > select blablabal from _complex_query > if (count($result) > 15) show_pages; > show_only_15_rows($result); > > or: > > select count(*) from _complex_query > if ($result1 > 15) show_pages; > select blablabal from _complex_query LIMIT ... (see the LIMIT!) > show $result > > On the first, I can use pg_num_rows instead of count(), too. > > what do you think? > > Thank you! > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax: +1 707 568 6416
On Thu, Feb 09, 2006 at 10:52:03AM -0800, Uwe C. Schroeder wrote: > Depending on your keys neither. > Rather let the DB handle the resultset. count(*) is quite slow. > > How about something like > > select blablabla from _complex_query order by _key_ (optional DESC or ASC) > OFFSET xxx LIMIT 15 > > where your offset would be a parameter from the php side and is basically the > page number of the number of pages you want to display. > The only drawback of that is that you will never see the total number of hits. > So maybe you do a count(*) ONCE and then use the above query to loop over the > resultset - or you don't show the number of pages and just have a "next > results" and "previous results" button that adjusts the offset parameter. Another possibility is to put a limit of 151. If you get 151 rows you print 1 2 3 .. 8 9 10 More. If you get less you know how many pages. As you get to page 5 you can limit to 225+1. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Hi all, I'm trying to insert more than one t-uple in a single sql query. Just like copy table (field1,field2,...) from .... It must be something like insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...), (c1,c2,...) Thanks in advance! Gonzalo A. Villegas
Hi, You can't do that in postgres, sorry. That's a mysql-ism. Gonzalo Villegas wrote: > It must be something like > > insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...), > (c1,c2,...)
On Thu, 9 Feb 2006 17:57:03 -0500, "Gonzalo Villegas" <chalo1970@hotmail.com> wrote: > > > Hi all, > > I'm trying to insert more than one t-uple in a single sql query. Just like > > copy table (field1,field2,...) from .... > > It must be something like > > insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...), > (c1,c2,...) insert into table (field1,field2) select v1,v2 union all select b1,b2 union all select c1,c2 klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
There is a little trick you can do though, it goes something like this: insert into table (field1, field2, field3) select v1, v2, v3 union b1, b2, b3 union select c1, c2, c3 I originally did this because it was significantly faster on SQL Server 2000 than doing the inserts individually. Usually I did it with up to maybe 20 rows at a time that were all grouped to some kind of common parent. Some version of postgres a long time ago broke my code because it did some stricter type checking and so I had to make sure that I was never putting single quotes around int and that date fields were strictly typecasted so that it wouldn't think they were strings. It does work now though as long as I do that and I use it all the time. I don't know if it gets the same sort of speed boost in postgres as it did in sql server. As long as they are all done within a single transaction in postgres it may not matter whether you do them individually or batched like that. If you really have a lot of data you want to insert at once why not just use COPY? Rick On Feb 9, 2006, at 4:13 PM, Chris wrote: > Hi, > > You can't do that in postgres, sorry. That's a mysql-ism. > > Gonzalo Villegas wrote: > >> It must be something like >> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...), >> (c1,c2,...) > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly >
>> You can't do that in postgres, sorry. That's a mysql-ism.
>>
>> Gonzalo Villegas wrote:
>>
>>> It must be something like
>>> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
>>> (c1,c2,...)
Actually, that's not a mysql-ism, it's SQL-spec syntax. We haven't got
round to implementing it, partly because the SELECT ... UNION ALL ...
syntax provides a perfectly good substitute. It is on the TODO list
though.
I wouldn't recommend trying to insert more than a few dozen rows with
the UNION ALL approach, else the planner overhead might swamp any
savings. If you want to insert thousands of rows at once, you almost
certainly want to find a way to use COPY.
regards, tom lane
On Thu, Feb 09, 2006 at 07:12:45PM -0500, Tom Lane wrote: > >> You can't do that in postgres, sorry. That's a mysql-ism. > >> > >> Gonzalo Villegas wrote: > >> > >>> It must be something like > >>> insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...), > >>> (c1,c2,...) > > Actually, that's not a mysql-ism, it's SQL-spec syntax. We haven't > got round to implementing it, partly because the SELECT ... UNION > ALL ... syntax provides a perfectly good substitute. It is on the > TODO list though. Another way it's different from COPY is that the VALUES can take expressions. What all would need to change in order to implement this? There appear to be things in src/backend/parser and src/bin/psql that bear on this. Would libpq and ecpg need to change? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 415 235 3778 Remember to vote!
Thank you very much. But what do you think of: select foobar from table where <condition>; if (pgsql_num_rows($result) > 15) show_pages; show_only_15($result); Thank you!
What happens if that query returns 5,000 rows? Postgres does actually have to fetch all of those rows, and then php has to allocate memory etc to store them. It's not very efficient. Silas Justiniano wrote: > Thank you very much. But what do you think of: > > select foobar from table where <condition>; > if (pgsql_num_rows($result) > 15) show_pages; > show_only_15($result);