locked
Composite PK RRS feed

  • Question

  • Hi guys,

    I have a composite key from 3 fields in table in SQL Server 2008 R2. I need to use in external where clause only one of this field. It works so long. I know that I have to use n or n-1 fields in where but I need only in one. In execution plan I can see that it table scan appears. Could you please advice, how it is possible to accelerate the query?

    Here is code:

    SELECT * FROM [DBMI_US_Data]..[FOBO_IG614ARORA] t
    		  INNER JOIN dbo.PackData_Details p ON  p.[MI_Date] = t.[MI_Date] AND p.[File_ID] = t.[File_ID]  AND p.[File_ROW_ID] = t.[File_ROW_ID]
    		  INNER JOIN Consolidated_Files f ON  f.[MI_Date] = t.[MI_Date] AND f.[File_ID] = t.[File_ID] 
    		  where t.MI_Date = '2011-12-14'

    Wednesday, April 4, 2012 12:19 PM

Answers

  • The order the columns were declared in your primary key is relevant. Did you declare the MI_Date as the first column of your index?

    What's the data type of MI_Date? Is it datetime?

    Did you try creating a nonclustered index on the MI_Date column alone?


    if a problem looks too big, break it into smaller objects

    • Marked as answer by SunnyInga Wednesday, April 4, 2012 1:32 PM
    Wednesday, April 4, 2012 12:54 PM

All replies

  • may this help some what :

    SELECT * FROM [DBMI_US_Data]..[FOBO_IG614ARORA] t
    		  INNER JOIN dbo.PackData_Details p ON  p.[MI_Date] = t.[MI_Date] AND p.[File_ID] = t.[File_ID]  AND p.[File_ROW_ID] = t.[File_ROW_ID]
    			AND t.MI_Date = '2011-12-14'
    		  INNER JOIN Consolidated_Files f ON  f.[MI_Date] = t.[MI_Date] AND f.[File_ID] = t.[File_ID] 
    

    Wednesday, April 4, 2012 12:20 PM
  • Thanks Paresh. I tried it and the result the same - too long. It doesn't help
    Wednesday, April 4, 2012 12:22 PM
  • What are the columns and order in the PK and what other indexes exist on the tables?  I would start by making sure each table has an index on MI_Date (as the first column, preferably clustered) along with the remain columns specified in the ON clauses.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, April 4, 2012 12:26 PM
  • The order is

    [MI_Date], File_ID, Row_ID.

    Everyting is OK in ON clause. Everywhere there are clustered indexes. The problem is in additional clause for filtering using MI_Date for axact value. When I add this clause the query executes too long. Without - works very fas

    Wednesday, April 4, 2012 12:33 PM
  • Do you have indexes on join columns ??

    Thanks and regards, Rishabh , Microsoft Community Contributor

    Wednesday, April 4, 2012 12:42 PM
  • Yes, I have

    Wednesday, April 4, 2012 12:44 PM
  • The order the columns were declared in your primary key is relevant. Did you declare the MI_Date as the first column of your index?

    What's the data type of MI_Date? Is it datetime?

    Did you try creating a nonclustered index on the MI_Date column alone?


    if a problem looks too big, break it into smaller objects

    • Marked as answer by SunnyInga Wednesday, April 4, 2012 1:32 PM
    Wednesday, April 4, 2012 12:54 PM
  • Thanks! It's helped
    Wednesday, April 4, 2012 1:32 PM