Re: [MonetDB-users] Profiling queries

On Dec 5, 2007 12:15 AM, Niels Nes wrote:

On Tue, Dec 04, 2007 at 11:58:55PM -0700, m h wrote:

...

On Dec 4, 2007 12:56 PM, m h wrote:

...

In the meantime I've done the following in an attempt to make the query faster all to no avail (ie the speed stays the same):

* Remove the calculation doing summing and coerce'ing (this was in the selection part were I guess you do not have much data left) * Change IN statement to = * Remove primary KEY for date table and join date table with between clause (actually surprised this didn't make it slower) Only if the otherside has a foreign key it would slow down.

Single column primary key's don't help, as these are handled by in memory hash tables which are created when needed (ie without influence of the user).

...

* Added indexes for non-PK columns I'm joining on. No difference! So your query involves many join expressions ?

I don't think it's that many, 3-4...

...

Does this have something to do with the tables in the "tmp" schema? Does it make queries slower?

We never use the 'tmp' schema for columns other then really temporaries ourselfs, so I have know real data on this, but known how the queries are translated I would expect any impact.

I'll work on using non tmp schema. I thought I read something about indexes not working there, but I'm probably just imagining it.... ok, names have been changed to protect the innocent, here's an anonymized query (the column types are in the comments to the right): SELECT item_dim.deptnm AS "DEPARTMENT", --varchar(30) item_dim.upcdesc AS "DESCRIPTION", --varchar(30) item_dim.freeformsz AS "SIZE", --varchar(30) loc_dim.orglevel1 AS "STORE CHAIN", --varchar(30) loc_dim.store AS "STORE NAME", --varchar(30) item_dim.upc AS "UPC", -- bigint sum(sale_fact.units * (coalesce(sale_fact.dealamt, 0) + coalesce(sale_fact.purbillback, 0) + coalesce(sale_fact.scanbillback, 0))) AS "current allowances", sum(sale_fact.sales - coalesce(sale_fact.cost, 0)) AS margin, sum(sale_fact.sales) AS sales, sum(sale_fact.units) AS units FROM item_dim, loc_dim, sale_fact WHERE sale_fact.adweekid = 16 AND --int item_dim.upc = sale_fact.upc AND --bigint loc_dim.stornum = sale_fact.stornum AND --int loc_dim.store IN ('FOO STORE') AND --varchar item_dim.groupnumnm IN ('BAR ITEMS') --varchar GROUP BY item_dim.deptnm, item_dim.upcdesc, item_dim.freeformsz, loc_dim.orglevel1, loc_dim.store, item_dim.upc ORDER BY item_dim.depnm ASC thanks, -matt