Re: Optimizing queries that use multiple tables and many order by columns

On Thu, Aug 26, 2010 at 2:51 AM, Wappler, Robert <rwappler(at)ophardt(dot)com>wrote:

> Do you really have the requirement to sort anything? Or let me ask it
> the other way round: Assuming you have too much data, to sort it on the
> application side, which user can read all this from one single table in
> the user interface?
>

The tool that I'm using to pull this information together is really easy to
use and maintain when you use it's database drivers to generate the queries.
The extra sort here is so that the I could order the dataset by company,
then by job number, then by the specific lab number, where jobs are assigned
to a single company, and labs are assigned to a given job. The idea is for
the application to be a substitute for bringing the dataset into a
spreadsheet and peruse it there. I could just sort by company XOR both job
and lab, but in the case of sorting by company, all of the companies job
numbers would not necessarily be in order, and likewise the labs within the
jobs would also not. This could be smoothed over by cutting down the dataset
to a subset based on a few criteria, which is the next approach to take.

>
> > In the application here, these queries are used by a client
> > application to fill a window's listbox that can be scrolled
> > up or down. If the user changes direction of the scroll, it
> > initiates a new cursor and query to fetch a page of results.
> > If the scrolling motion is in the same direction, it simply
> > continues to fetch more results from the cursor. But each
> > time the direction of movement changes, there can be a
> > significant lag.
> >
>
> Then, obviously you shouldn't create a new cursor. You can create
> backwards scrollable cursors. See the SCROLL option of the DECLARE
> statement.
>

These queries are generated by the database driver and are not easily
tweakable. Generally they use a subset of whatever is available via the ODBC
interface. So, although not optimal, it's not something that I can improve
in the shortterm.

> > Any suggestions would be helpful! I'll assume for now that
> > the indexes and queries can't be improved, but rather that I
> > should tweak more of the postmaster settings. Please correct
> > me if you know better and have time to reply.
> >
>
> These options heavily depend on the environment and the data set, I
> always see them as some last resort, because they might slow down other
> queries if tweaked to much towards a specific thing. I have not yet
> played around with this a lot. The things simply work fast enough here.
> Others can give you better hints on this.
>

Thanks for you tips and insight. I'll make getting this portion of the
system "good enough" and look to refactor later when needed.

> > P.S. Is it possible to have indexes that involves several
> > columns from different but related tables? If so, where can I
> > learn about them?
>
> Nope. An index is tied to one table only. But another option is, to
> precalculate the join. Depending on your needs (especially INSERT/UPDATE
> performance), you could use triggers and/or a regular batch job, which
> writes the joined results in another table. There you can index these
> columns accordingly. In general, this is ugly and leads to redundancy
> but can give a big performance boost and is sometimes the only option.
>

That's an option. I do use triggers now to log user changes to the tables,
this wouldn't be too hard to do, but a bit hard to maintain down the road,
perhaps. It's great to have a backup plan in the case that I have a backlog
of support requests regarding the UI lbeing too laggy.

Kind Regards,
-Joshua

>
> --
> Robert...
>
>
>