--- In firebird-support@yahoogroups.com, Alexandre Benson Smith <iblist@...> wrote:

>

> > Here is my query which is a join between three tables. prdInCatTab holds the keys to product and prdCategory and it's a one-to-many relationship:
> >
> > SELECT FIRST 10 SKIP 500 *
> > FROM product JOIN prdInCatTab ON prdInCatTab.PINCPrdId = product.prdId
> > JOIN prdCategory ON prdCategory.catId = prdInCatTab.PINCCatId
> > WHERE prdCategory.catShowFlag = '1'
> > AND product.prdHideFlag != '1'
> > AND prdInCatTab.PINCPrimary = '1'
> > ORDER BY prdInCatTab.PINCRank DESC, product.nameUp ASC;
> >
> > And here is the plan:
> >
> > PLAN SORT (JOIN (PRDINCATTAB NATURAL, PRDCATEGORY INDEX (PK_PRDCATEGORY), PRODUCT INDEX (PK_PRODUCT)))
> >
> > Well, my actual question could be: Why does Firebird consume such a large amount of sorting space? It is almost impossible for a web server which has over 100 connections to allocate such amounts of memory space. As I wrote in my first post here are my numbers:
> >
> > 1. Table product's size is between 30 and 40 MB. The other two have small size.
> >
> > 2. TempCacheLimit = 536870912 with Classic Super Server if you want to have an acceptable run(4-5 secs first run on my laptop, around 2 secs all next runs).
> >
> > 3. Firebird server does actually need from 200 to 450 MB for each run.
> >
> > Although my production servers are much faster than my laptop, I replaced the above query with another that uses a subquery as a virtual table. It turned out very fast and not memory consuming:
> >
> > SELECT * FROM product
> > JOIN prdInCatTab ON prdInCatTab.PINCPrdId = product.prdId
> > JOIN prdCategory ON prdCategory.catId = prdInCatTab.PINCCatId
> > JOIN
> > (
> > SELECT FIRST 10 SKIP 500 product.prdId as bprdid
> > FROM product JOIN prdInCatTab ON prdInCatTab.PINCPrdId = product.prdId
> > JOIN prdCategory ON prdCategory.catId = prdInCatTab.PINCCatId
> > WHERE catShowFlag = '1'
> > AND product.prdHideFlag != '1'
> > AND prdInCatTab.PINCPrimary = '1'
> > ORDER BY PINCRank DESC, product.nameUp ASC
> > ) AS b ON product.prdid = b.bprdid
> > AND prdInCatTab.PINCPrimary = '1'
> >
> > and the plan:
> > PLAN JOIN (JOIN (SORT (JOIN (B PRDINCATTAB NATURAL, B PRDCATEGORY INDEX (PK_PRDCATEGORY), B PRODUCT INDEX (PK_PRODUCT))), JOIN (PRDINCATTAB INDEX (FK_PINC_PRODUCT), PRODUCT INDEX (PK_PRODUCT))), PRDCATEGORY INDEX (PK_PRDCATEGORY))
> >
> > But you can't always substitute your queries easily, can you?
> > And it still bothers me as a Firebird user how postgres does it so well.
> >
> > Regards,
> > Haris
> >
> >
> >
> >
>
>
> There are blobs in any of the tables ? Since you are using select *, any
> blobs will be returned. IIRC there is a memory consumition above the
> expected when blobs are used in sorting (group by, distinct, etc.) I
> can't recall the exact details and in wich FB version are affected.
> Perhaps some one could put more information about it. Something
> regarding the memory be freed just when the transaction is commited or
> something like that.
>
> But, could you try to remove * and put the just the necessary fields ?
>
> If not, could you put the table strucure of the involved tables ?
> Perhaps you have large varchar fields that leads to a huge memory usage.
>
> see you !
>

Hi Set,
Thanks for your reply.

Yes, there are blobs and quite large varchar fields(160). I have already tested that if I select only some columns then the query execution time is dramatically reduced. My opinion is that it doesn't matter which columns you select, what matters is total size of the returned dataset. Actually, that's why my solution with the subquery works fast. Because it does the sorting on a dataset with very few char and integer columns. Maybe the optimizer could follow a similar plan.

Regards,
Haris