Order By Clause
lloy0076
What do I do to get rid of the order by clause that seems to be put in
automatically for me?
DSL
John Siracusa
> What do I do to get rid of the order by clause that seems to be put in
> automatically for me?
I'm assuming you mean this:
http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Manager.pm#sort_by
"If selecting sub-objects (via require_objects or with_objects) that
are related through "one to many" or "many to many" relationships, the
first condition in the sort order clause must be a column in the
primary table (t1). If this condition is not met, the list of primary
key columns will be added to the beginning of the sort order clause
automatically."
If so, the only way to get rid of it is not to include any ...-to-many
sub-objects. When including ...-to-may sub-objects, that sort is
required to keep the sub-objects with their parent objects.
-John
Ben Tilly
Is it possible to choose to have the sort done on the client rather
than the server? The reason why I ask is that frequently client
resources are easier to scale than server resources, so sometimes
sysadmins and DBAs don't like to see unnecessary work like sorts done
on the database.
I am painfully aware of this because the sysadmin I have to deal with
doesn't like to see any order by running on his transactional
PostgreSQL databases. (Luckily I run my reports against a reporting
backup, so he doesn't mind my doing ordering there.)
Cheers,
Ben
John Siracusa
> Is it possible to choose to have the sort done on the client rather
> than the server?
That'd require fetching all rows before returning the first, which is
incompatible with the iterator interface. Right now, both the
get_objects_iterator() interface and the get_objects() interface only
fetch as much as they need before packaging up a set of related rows.
That could be changed with some significant code alterations, but it'd
likely incur a speed hit from sorting out all those (effectively)
randomly returned rows into sensible groups.
> The reason why I ask is that frequently client
> resources are easier to scale than server resources, so sometimes
> sysadmins and DBAs don't like to see unnecessary work like sorts done
> on the database.
If that's your concern, why not do the join(s) client-side as well,
which would side-step this entire issue?
-John
Ben Tilly
> > Is it possible to choose to have the sort done on the client rather
> > than the server?
>
>
> That'd require fetching all rows before returning the first, which is
> incompatible with the iterator interface. Right now, both the
> get_objects_iterator() interface and the get_objects() interface only
> fetch as much as they need before packaging up a set of related rows.
> That could be changed with some significant code alterations, but it'd
> likely incur a speed hit from sorting out all those (effectively)
> randomly returned rows into sensible groups.
Ah. Good reason. :-)
> > The reason why I ask is that frequently client
> > resources are easier to scale than server resources, so sometimes
> > sysadmins and DBAs don't like to see unnecessary work like sorts done
> > on the database.
>
> If that's your concern, why not do the join(s) client-side as well,
> which would side-step this entire issue?
Because frequently doing a join on the server takes substantially less
resources than doing the same join on the client side. Just think of
the extra round trips and network traffic that is required. Not to
mention the latency you add.
Also optimizing a query plan can be tricky, and an attempt to do so on
the client side that comes up with a bad plan can be a lot worse than
letting the database handle it.
Cheers,
Ben
John Siracusa
>> If that's your concern, why not do the join(s) client-side as well,
>> which would side-step this entire issue?
>
> Because frequently doing a join on the server takes substantially less
>
> Also optimizing a query plan can be tricky, and an attempt to do so on
> the client side that comes up with a bad plan can be a lot worse than
> letting the database handle it.
These are all reasons I'd let the db do the sorting too... ;)
-John
don....@gmail.com
your DBA doesn't realize that, perhaps a job req for a new DBA would
be in order.
Ben Tilly
First of all I am repeating the position of a sysadmin, not a DBA.
Secondly the claim that a sort on the primary key is effectively free
can be false depending on the way that indexes have been set up or the
query plan chosen by the database. I'd expect a competent DBA to be
able to describe situations where either of these factors could make
the sort on the primary key no longer be free.
Cheers,
Ben