none
Write optimized SQL

    Question

  • I have one facttable named FactTable and I have millions of records in that fact table
    I have 3 dimension tables as follows. now I want to retrive 'Value' from FactTable for dim3's DDD column.
    So I have written a query in the following way
    SELECT F.* 
    FROM FactTable F
    LEFT JOIN Dim1 D1
    ON F.AAA = D1.AAA
    INNER JOIN Dim2 D2
    ON D1.BBB = D2.CCC
    INNER JOIN Dim3 D3
    ON D2.CCC = D3.CCC
    WHERE D3.DDD = 2
    I have used Left join to retrieve all the rows from fact table which belongs to Dim3.DDD = 2
    but the performance of query is wrost.how to write optimized query for such requirements.
    • Moved by Tom PhillipsModerator Thursday, August 25, 2011 4:29 PM TSQL question (From:SQL Server Database Engine)
    Thursday, August 25, 2011 9:50 AM

All replies

  • Is that possible to have CCC key in the fact table in order to produce only one JOIN?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, August 25, 2011 10:25 AM
  • No..Even I have thought of that but I cannot do that.
    Thursday, August 25, 2011 10:59 AM
  • Well, can you show us an execution plan of the query?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, August 25, 2011 11:02 AM
  • Hi,

    Please post the execution plan of the query. And you could follow the tunning advice of it to adjust the tables' index setting.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, August 30, 2011 2:09 AM
  • You can build an SSAS OLAP cube and get subsecond response. Guaranteed.

    OLAP videos: http://bit.ly/pHGRdD

     


    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    Tuesday, August 30, 2011 7:21 AM
  • Hi, Please try the below tsql -

    SELECT
      F.Col1,
      F.Col2
    FROM
      FactTable F
    LEFT JOIN Dim1 D1
      ON F.AAA = D1.AAA
    LEFT JOIN Dim2 D2
      ON D1.BBB = D2.CCC
    LEFT JOIN Dim3 D3
      ON D2.CCC = D3.CCC
    WHERE
      D3.DDD = 2
     
    And make sure you have the following indexes -

        On Dim3 - DDD,CCC
        On Dim2 - CCC,BBB
        On Dim1 - BBB,AAA
        And on FactTable - AAA and include Value column as Non-Key column

    Also, select only the required columns instead on using SELECT *

    This will help in improving the performance as per my understanding. 
    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Tuesday, August 30, 2011 7:40 AM
  • Can we write the SQL statement which I have posted in some other way. I am thinking of avoiding left join as left join will scan all fact table data and that may degarade the performance.
    Tuesday, August 30, 2011 5:12 PM
  • Well, if you use a LEFT JOIN, you are asking all rows of the outer table to be returned. So that more or less calls for a table scan.

    I don't think there is anything wrong with your query (apart from the fact that your LEFT JOIN is basically converted into an INNER JOIN because of the join with Dim2). If you have performance problems, you will have to look into the indexes, specifically the indexes that will cover the join columns and your WHERE expression. That is mainly the reason why several other replies have asked for the query plan.

     


    Gert-Jan
    Tuesday, August 30, 2011 7:38 PM
  • Oh...I am so sorry that I didn't realize that you want all the rows from fact table which belongs to Dim3.DDD = 2. I misread it as all the rows from fact table

    However, if you want only the matching rows, you can either use ALL INNER JOINS or your actual query will also have INNER JOINS ultimately. So, there will not be any difference.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Wednesday, August 31, 2011 4:30 AM