How to improve performance of select statement in Dataware house app

  • HI Champs,

    I have huge table of 70 million records and 300 columns, and it is partitioned based on Monthkey column.And it is having clustered index as well .

    Am having a select statement with Month_key as where clause and selecting all columns in the query.

    But it is taking more time .

    Cant reduce any columns it is required.

    How to improve the performance ?

    Regards,

    Ravi

  • ravi@sql (3/10/2016)


    HI Champs,

    I have huge table of 70 million records and 300 columns, and it is partitioned based on Monthkey column.And it is having clustered index as well .

    Am having a select statement with Month_key as where clause and selecting all columns in the query.

    But it is taking more time .

    Cant reduce any columns it is required.

    How to improve the performance ?

    Regards,

    Ravi

    Please post the query (as text) and the actual execution plan (as an attachment). Without these, it's difficult to offer any meaningful advice.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 1) If you are asking for lots of rows with lots of columns back to a client app that alone can take a LOT of time, even if SQL Server were to be able to instantly start feeding it back.

    2) We do need to see query and query plan and table indexing at a minimum.

    3) Poor hardware and/or poor virtualization and misconfigurations of lots of things can obviously be an issue (it is with EVERY client I start out with for performance work).

    4) Simple blocking can cause long-running query times. Use sp_whoisactive to see that.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Does the clustering key start with Monthkey? And the partitioning key?

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply