How to improve performance of select statement in Dataware house app

Question

How to improve performance of select statement in Dataware house app

ravi@sql

SSCrazy

Points: 2121
More actions
March 10, 2016 at 5:52 am

#399834

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

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".