Re: Optimizing queries that use multiple tables and many order by columns
| От | Joshua Berry |
|---|---|
| Тема | Re: Optimizing queries that use multiple tables and many order by columns |
| Дата | |
| Msg-id | AANLkTimpGBxi9AFwhkNC8zukUCJZqA8YTWXyJH=FPFBY@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Optimizing queries that use multiple tables and many order by columns ("Wappler, Robert" <rwappler@ophardt.com>) |
| Список | pgsql-general |
On Thu, Aug 26, 2010 at 2:51 AM, Wappler, Robert <rwappler@ophardt.com> wrote:
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.
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.
Thanks for you tips and insight. I'll make getting this portion of the system "good enough" and look to refactor later when needed.
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
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.
Then, obviously you shouldn't create a new cursor. You can create
> 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.
>
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 thatThese options heavily depend on the environment and the data set, I
> 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.
>
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 severalNope. An index is tied to one table only. But another option is, to
> columns from different but related tables? If so, where can I
> learn about them?
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...
В списке pgsql-general по дате отправления: