Re: [GENERAL] Problem with result ordering
Hi Ted, Am Donnerstag, 25. Januar 2007 19:53 schrieb Ted Byers: The question I'd ask before offering a solution is, Does the order of the id data matter, or is it a question of having all the results for a given id together before proceeding to the next id? The answer to this will determine whether or not adding either a group by clause or an order by clause will help. Is there a reason you client app doesn't submit a simple select for each of the required ids? You'd have to do some checking to see whether it pays to have the ordering or grouping operation handled on the server or client. Other options to consider, perhaps affecting performance and security, would be parameterized queries or stored procedures. Yes, the reason is, that a large list of ids are generated from a users request, coming from outside our application (for example as an EXCEL sheet), and the output msut be ordered by the ordering in this list. Surely we can handle this in our client application (java code), but I think it would be more sexy to have this possibility in the database logic, since our client-application should not do much more than retrieve data from the db and then present it. Thanks for your comments Thorsten -- CappuccinoSoft Business Systems Hamburg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem with result ordering
Hi Ted, Hi Thorsten, Am Donnerstag, 25. Januar 2007 19:53 schrieb Ted Byers: The question I'd ask before offering a solution is, Does the order of the id data matter, or is it a question of having all the results for a given id together before proceeding to the next id? The answer to this will determine whether or not adding either a group by clause or an order by clause will help. Is there a reason you client app doesn't submit a simple select for each of the required ids? You'd have to do some checking to see whether it pays to have the ordering or grouping operation handled on the server or client. Other options to consider, perhaps affecting performance and security, would be parameterized queries or stored procedures. Yes, the reason is, that a large list of ids are generated from a users request, coming from outside our application (for example as an EXCEL sheet), and the output msut be ordered by the ordering in this list. Surely we can handle this in our client application (java code), but I think it would be more sexy to have this possibility in the database logic, since our client-application should not do much more than retrieve data from the db and then present it. To be honest, your rationale here makes no sense to me, business or otherwise. I think I'd be remiss if I didn't tell you this. Of course, what you do is up to you, but I never concern myself with what is 'more sexy' when designing a distributed application. I can see a number of situations in which your approach would result in terrible performance. If you have a lot of users, and you're putting all the workload on your data server, all the client apps will end up spending a lot of time waiting for the server to do its thing. Ordering the display of data, while it can be helped by the database, is really a presentation issue and IMHO the best place for that, by default, is the client app (i.e. do it server side only if there is a very good reason to do so). If I understand you correctly, you have a java application as the client app, and it receives your users' ID data, from whatever source. I'd assume, and hope, that you have built code to read the IDs from sources like your Excel spreadsheet, but that doesn't matter that much. For the purpose of this exchange, it wouldn't matter if you made your clients enter the data manually (though IMHO it would be sadistic to make users manually enter a long list of values when you can as easily have the program read them from a file of whatever format). The point it that you say our client-application should not do much more than retrieve data from the db and then present it, and this implies you get the IDs into your client application. You say you're getting a large list of ids coming from outside our application. If your database is large, and your list of IDs is long, you may be taking a huge performance hit by making the database perform either an ordering or grouping that both would be unnecessary if you constructed a series of simple parameterized queries in your client app and executed them in the order you desire. Whether or not this matters will depend on just how large your large is, how many simultaneous users there'd be, and how powerful the server hardware is (but I'd be quite upset if one of my developers made me pay more for a faster server just because he or she thought it would be sexy to do all the work on the server rather than the client). Given what you've just said, if I were in your place, I'd be addressing the ordering issues in the client java application first, and then look at doing it in the database only if doing this work in the client app presented problems that could be addressed by doing the work on the server. Equally importantly, if there is a reason to not take the obvious and simple option, I'd be doing some performance evaluation based on enough test data to reasonably simulate real world usage so that I'd have hard data on which option is to be preferred. Thanks for your comments Thorsten You're welcome. I hope you find this useful. Ted -- CappuccinoSoft Business Systems Hamburg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem with result ordering
2007/1/25, Thorsten Körner [EMAIL PROTECTED]: Hi, when I fire the following query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); I will get the same results in the same order, as in in the next query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (11042,42197,672089,26250,16279); I wonder, how it is possible, to retrieve the results in the same order, as queried in the list. The listed IDs are from an application outside the database. Version is PostgreSQL 8.2.1 Has anyone an idea, how to do this, while PostgreSQL knows nothing about hints, like oracle does? obvious solution is to create temporary table like create temp table tmp ( id serial, key integer ); then populate it with your list in order, and then join it with your source table. but it will require some extra coding, either in your app or in PL set-returning function F. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Problem with result ordering
Hi, when I fire the following query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); I will get the same results in the same order, as in in the next query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (11042,42197,672089,26250,16279); I wonder, how it is possible, to retrieve the results in the same order, as queried in the list. The listed IDs are from an application outside the database. Version is PostgreSQL 8.2.1 Has anyone an idea, how to do this, while PostgreSQL knows nothing about hints, like oracle does? THX, Thorsten ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem with result ordering
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/25/07 09:45, Thorsten Körner wrote: Hi, when I fire the following query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); I will get the same results in the same order, as in in the next query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (11042,42197,672089,26250,16279); I wonder, how it is possible, to retrieve the results in the same order, as queried in the list. The listed IDs are from an application outside the database. Version is PostgreSQL 8.2.1 Has anyone an idea, how to do this, while PostgreSQL knows nothing about hints, like oracle does? What do you mean same order? The order that they are listed in the IN() clause? I doubt it. SQL is, by definition, set-oriented and the only ways to guarantee a certain output sequence are ORDER BY and GROUP BY, and they use collating sequences. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFuNLmS9HxQb37XmcRAmTSAJ9mbcf8AptR4YsjdG7xBocasldfdgCdEGSz MNjSxmx3KBP79LXRzTgQ2Qk= =nif4 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem with result ordering
Thorsten =?iso-8859-1?q?K=F6rner?= [EMAIL PROTECTED] writes: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); I wonder, how it is possible, to retrieve the results in the same order, as queried in the list. You could rewrite the query as select ... from tablename where m_id = 26250 union all select ... from tablename where m_id = 11042 union all select ... from tablename where m_id = 16279 union all select ... from tablename where m_id = 42197 union all select ... from tablename where m_id = 672089 This isn't guaranteed by the SQL spec to produce the results in any particular order either; but there's no good reason for PG to rearrange the order of the UNION arms, whereas there are plenty of good reasons to try to optimize fetching of individual rows. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problem with result ordering
am Thu, dem 25.01.2007, um 16:45:23 +0100 mailte Thorsten Körner folgendes: Hi, when I fire the following query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); I will get the same results in the same order, as in in the next query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (11042,42197,672089,26250,16279); I wonder, how it is possible, to retrieve the results in the same order, as queried in the list. The listed IDs are from an application outside the database. a little trick; store your order-definition and the where-condition in a separate table. Our table: test=*# select * from foo; id | val +--- 1 | text1 2 | text2 3 | text3 4 | text4 5 | text5 (5 rows) Our sort-order: -- col a: the sort-order -- col b: the where-condition test=*# select * from o; a | b ---+--- 1 | 3 2 | 5 3 | 1 (3 rows) test=*# select foo.id, foo.val from foo join o on foo.id=o.b order by o.a; id | val +--- 3 | text3 5 | text5 1 | text1 (3 rows) You can try it without this table, only with generate_series or so. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem with result ordering
Hi Fillip, thanks for your hint, I have tested it on a development database, and it worked well. Are there any experiences how this will affect performance on a large database, with very high traffic? Is it recommended to use temp tables in such an environment? THX in advance Thorsten Am Donnerstag, 25. Januar 2007 17:02 schrieb Filip Rembiałkowski: 2007/1/25, Thorsten Körner [EMAIL PROTECTED]: Hi, when I fire the following query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); I will get the same results in the same order, as in in the next query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (11042,42197,672089,26250,16279); I wonder, how it is possible, to retrieve the results in the same order, as queried in the list. The listed IDs are from an application outside the database. Version is PostgreSQL 8.2.1 Has anyone an idea, how to do this, while PostgreSQL knows nothing about hints, like oracle does? obvious solution is to create temporary table like create temp table tmp ( id serial, key integer ); then populate it with your list in order, and then join it with your source table. but it will require some extra coding, either in your app or in PL set-returning function F. -- CappuccinoSoft Business Systems Hamburg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem with result ordering
The question I'd ask before offering a solution is, Does the order of the id data matter, or is it a question of having all the results for a given id together before proceeding to the next id? The answer to this will determine whether or not adding either a group by clause or an order by clause will help. Is there a reason you client app doesn't submit a simple select for each of the required ids? You'd have to do some checking to see whether it pays to have the ordering or grouping operation handled on the server or client. Other options to consider, perhaps affecting performance and security, would be parameterized queries or stored procedures. Much depends on the design and implementation of your client app. I know, e.g., that in ASP.NET 2, and later, you can handle multiple resultsets from a single datasource, so a trivially simple SQL script that consists of the simplest SELECT statements might be a viable option. But it is hard to advise since you don't say if you have access to or control over the source code for the client app or what it is written in. In my experience, I always have to run some benchmarks for a given distributed application to figure out how best to distribute the workload, and there are always plenty of different ways to do things, with often big differences in performance and security. It seems never to be trivial to figure this out without some testing before a final decision. I can never just assume that it is best to do all the processing in the RDBMS backend to my apps. HTH Ted - Original Message - From: Thorsten Körner [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Thursday, January 25, 2007 10:45 AM Subject: [GENERAL] Problem with result ordering Hi, when I fire the following query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); I will get the same results in the same order, as in in the next query: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (11042,42197,672089,26250,16279); I wonder, how it is possible, to retrieve the results in the same order, as queried in the list. The listed IDs are from an application outside the database. Version is PostgreSQL 8.2.1 Has anyone an idea, how to do this, while PostgreSQL knows nothing about hints, like oracle does? THX, Thorsten ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem with result ordering
Tom Lane wrote: Thorsten =?iso-8859-1?q?K=F6rner?= [EMAIL PROTECTED] writes: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); You could rewrite the query as select ... from tablename where m_id = 26250 union all select ... from tablename where m_id = 11042 union all select ... from tablename where m_id = 16279 union all select ... from tablename where m_id = 42197 union all select ... from tablename where m_id = 672089 This isn't guaranteed by the SQL spec to produce the results in any particular order either; but there's no good reason for PG to rearrange the order of the UNION arms, whereas there are plenty of good reasons to try to optimize fetching of individual rows. Or a variant of this, SELECT m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089) ORDER BY m_id=26250, m_id=11042, m_id=16279, m_id=42197, m_id=672089; -- Tommy Gildseth http://www.gildseth.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
