i'm having problem with a query generated by mondrian OLAP Server . it's taking too long. the query look like as follows.
select "TABLE1"."column0" as "c0" from "FACT_TABLE" as "FACT_TABLE",
"TABLE7" as "TABLE7",
"TABLE6" as "TABLE6",
"TABLE5" as "TABLE5",
"TABLE4" as "TABLE4",
"TABLE4" as "TABLE3",
"TABLE2" as "TABLE2",
"TABLE1" as "TABLE2"
where "FACT_TABLE"."column1" = 'VALUE' and
"FACT_TABLE"."column2" =0 and
"FACT_TABLE"."column3" = 0 and
"TABLE2"."table1Fk" = "TABLE1"."table1Id" and
"TABLE3"."table2Fk" = "TABLE2"."table2Id" and
"TABLE4"."table3Fk" = "TABLE3"."table3Id" and
"TABLE5"."table4Fk" = "TABLE4"."table4Id" and
"TABLE6"."table5Fk" = "TABLE5"."table5Id" and
"TABLE7"."table6Fk" = "TABLE6"."table6Id" and
"FACT_TABLE"."table7Fk" = "TABLE7"."table7Id"
group by "TABLE1"."column0"
order by "TABLE1"."column0" ASC
FACT_TABLE has 1.346.000 rows aprox. TABLE7 has 895 rows aprox. TABLE6 has 445 rows. TABLE5 has 183 rows. TABLE4 has 258 rows. TABLE3 = TABLE4. TABLE2 has 126 rows. TABLE1 has 29 rows.
The query is taking 2.00 seg aprox over the production enviroment, and i really dont know what to do to improved query performace.
im over a 8GB RAM. intel xeon L5420 at 2.50GHZ. MSSQL 2005.
i'll apreciated any help that you can give me
Have a look at the execution plan. It will notify you if it notices any tables that could use a non-clustered index to improve query time.
That link helps a lot with optimizing queries (and learning a ton of other things.)