locked
Query Optimization - OLTP to Mart RRS feed

  • Question

  • We get a database from a datacenter that was originally OLTP and we tweak it for a Reporting/Mart profile (large select statements).

    We sometimes see 10 fold speed increases with optimization.

    We have a table at the 'top' of the schema (left in most queries) 12M rows - Catalog

    We report mostly cars in the table - _only_ _400K_ _rows_ leading to my question, index of product type integer.  There are only two product types - cars and everything else.

    PK on the Catalog is an Identity, Clustered.  There are 7 tables in our standard query, max is 82M rows toward the right in the query containing descriptions, localized.

    Optimizing for catalog _section_ gets performance gains, section is toward the middle of the table joins.

    The ProductType Field (INT) is not currently indexed.  Type = Cars is in all/most queries.

    Query plans are showing a key lookup for productType (2%).  Seeks on all other operators.

    My question is regarding how/if to index this attribute field (product type) in the table at the 'top' of the schema, which is a catalog.  I think of this situation as denormalization within an entity.

    Any help or comments greatly appreciated.


    Bill
    Saturday, December 17, 2011 6:54 PM

Answers

  • The following article is on performance tuning:

    http://www.sqlusa.com/articles/query-optimization/

    >how/if to index this attribute field (product type)

    How much selectivity? Not much? Questionable to index it. Perhaps it can be included in covering index if any.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Proposed as answer by wmtell Wednesday, December 21, 2011 11:09 PM
    • Marked as answer by Naomi N Thursday, December 22, 2011 12:56 AM
    Saturday, December 17, 2011 7:42 PM

All replies

  • The following article is on performance tuning:

    http://www.sqlusa.com/articles/query-optimization/

    >how/if to index this attribute field (product type)

    How much selectivity? Not much? Questionable to index it. Perhaps it can be included in covering index if any.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Proposed as answer by wmtell Wednesday, December 21, 2011 11:09 PM
    • Marked as answer by Naomi N Thursday, December 22, 2011 12:56 AM
    Saturday, December 17, 2011 7:42 PM
  • If you are considering denormalization of the schema for reporting, you may consider building dimensional data model for this purpose.

    http://en.wikipedia.org/wiki/Dimensional_modeling

    Saturday, December 17, 2011 8:29 PM
  • There are only two product types, 1) Cars  2) Everything Else (parts).

    We need to report the cars, only 400k rows out of 12 million.


    Bill
    Saturday, December 17, 2011 8:45 PM
  • Have you considered to partition the table(s)? 
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, December 18, 2011 1:41 AM
  • There are only two product types, 1) Cars  2) Everything Else (parts).

    We need to report the cars, only 400k rows out of 12 million.

    Hi Bill,

    In this scenario, you may take consider implementing filtered index if you are using SQL Server 2008 or upper.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Stephanie Lv

    TechNet Community Support

    • Proposed as answer by Naomi N Monday, December 19, 2011 2:51 AM
    Monday, December 19, 2011 2:14 AM
  • You may go for an Indexed View having records related to only Cars. And use this view to fetch the records.

    It will surely help in upgrading the performance.


    -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

    Monday, December 19, 2011 4:32 AM
  • I'll ask the moderator to accept an answer (the first) from SQL USA; it appears the old msn account has officially switched over to the live account.

    Thanks much, good answers all.

     


    Bill
    Wednesday, December 21, 2011 11:15 PM