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: 245813 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 100262 More actions · Answer 3

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

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!