On Dec 4, 2007 12:56 PM, m h
Since the query is confidential I can't really put it here, but I can describe it. I'm pulling stuff out of my fact table and two other dimension tables. The select clause sums a few columns, and on one sums the product of one column and the sum of a few others. The coalesce function is used to convert NULLs to 0. The where statement joins the tables using the = operator and then has two "IN" clauses. Then there is a group by (since I'm aggregating in the select) and an order by.
Any hints on how to increase performance? Do I need to tune my query or tweak other things? (Yes I know the laptop machine is not apples to apples to the vmware machine)
Am open to suggestions.
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 * 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) * Added indexes for non-PK columns I'm joining on. No difference! Does this have something to do with the tables in the "tmp" schema? Does it make queries slower? cheers, -matt