Query Optimization

Question

Hi folks, 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

Answer 1

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.

http://blogs.msdn.com/b/craigfr/archive/tags/joins/

That link helps a lot with optimizing queries (and learning a ton of other things.)

Answer 2

I would suspect that you don't have indexes on your foreign key fields.

This is not causing your problem but I personally wouldn't want to use a tool that creates implicit instead of explicit joins. How can you trust something that is using a syntax that was replaced 18 years ago with something better?

Oh something else I just noticed, I don't see any aggregates, so why are you grouping by?