Read data based on date-range in huge table is slow

Question

Post reply

Read data based on date-range in huge table is slow

Viewing 8 posts - 16 through 23 (of 23 total)

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

oRBIT SSC-Addicted Points: 430 More actions · Answer 1

Michael L John wrote:

Instead of changing the clustered index, can you add another index?

Yeah perhaps.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

oRBIT wrote:

If "Time" were the first in order in the index, that would be much better?

Maybe for your query but might crush everyone else's performance.

Looking at the graphic that was attached, only 50 actual rows were returned. Put an NCI on the Time column, maybe including a filter if the code uses one, and call it a day.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101200 More actions · Answer 3

I wouldn't just arbitrarily add a non-clus index without checking existing index usage ("index usage stats") AND reviewing missing indexes ("missing index" stats).

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

ScottPletcher wrote:

I wouldn't just arbitrarily add a non-clus index without checking existing index usage ("index usage stats") AND reviewing missing indexes ("missing index" stats).

Nor would I change the Clustered Index in such a fashion. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

Can you consider Columnstore index , non clustered, if you need many columns & read is huge in the DB query.

Can we have full plan or highly used area of plan to have a better look.

As it is said above looks date is not properly configured in the cluster index and it might be in the condition, so we can see huge reads in cluster index scan.

Regards
Durai Nagarajan

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

durai nagarajan wrote:

Can you consider Columnstore index , non clustered, if you need many columns & read is huge in the DB query.
Can we have full plan or highly used area of plan to have a better look.
As it is said above looks date is not properly configured in the cluster index and it might be in the condition, so we can see huge reads in cluster index scan.

I've heard a lot of people make such suggestions on other forums. It seems to me, according to the MS documentation, that column-store really only works well on low cardinality things and it doesn't seem likely to be the case here.

So, without an ounce of sarcasm or irony, you've caught my attention on the subject. With that, I'll ask, have you done something similar and had it work? I ask in earnest because, if the answer is "yes", I'm going to take the time to setup a good sized test and see if I can learn something new.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

durai nagarajan SSCoach Points: 15778 More actions · Answer 7

Hello Jeff ,

thanks but i suggested to consider not to create directly. as i don't see the plan, I suggested based on the data column he pulled.

I never created one after the issue but I missed the second page of comments , before posting that.

Regards
Durai Nagarajan

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

durai nagarajan wrote:

Hello Jeff ,
thanks but i suggested to consider not to create directly. as i don't see the plan, I suggested based on the data column he pulled.
I never created one after the issue but I missed the second page of comments , before posting that.

Thank you for the feedback, Durai. I appreciate it. And, drat... I was really hopeful. I might just take the time to do a test anyway, especially since I've never done it. Again, thank you for your response.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)