Re: Problem with result ordering - Mailing list pgsql-general

> 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/
>