Select is Slow

Question

Select is Slow

Viewing 4 posts - 16 through 18 (of 18 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

To me it sounds like a combination of paging and catch-all-queries.

Therefore, a combination of Gails catch-all article[/url] together with Pauls Optimising Server-Side Paging[/url] should get you startet.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

prabhu.st SSCrazy Points: 2552 More actions · Answer 2

As far as I understood, your table should be implemented with a Primary Key (Clustered preferrable), because then only Query Analyzer could sort the Data in a faster way, then as per your last reply you are trying to make some paging concept which you cannot achieve simply through a select statement where you need to implement the RANKING functions (row_number(), rank(),...) whatever suits your need.

and More Importantly, for Instance, you need to use the "Dynamic Query" (though not suggested) to get your requirement done base on the customer Input for the Filter condition and for the ORDER BY too..

I believe for a million records no need to go for a partition level of implementation.

let me know if this helps..

Thanks,

Prabhu

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 3

LutzM (12/18/2013)
To me it sounds like a combination of paging and catch-all-queries.
Therefore, a combination of Gails catch-all article[/url] together with Pauls Optimising Server-Side Paging[/url] should get you startet.

Agreed. And without any other criteria, it's still going to be "slow" because it's going to work with the million rows as index scans at best. Sorting by virtually any column or combination of columns is going to require a whole lot of indexes (which actually may not help much due to row lookups to get the data) which is going to cause the table to more than double or triple in size, as well. If you have the disk space for it, that'll be fine but caching it in memory is going to take it's toll.

--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)

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 4

yuvipoy (12/17/2013)
page will be having 1000 records (between 1 and 1000 ) in first go.. for each and every scroll there be another 1000 rows (between 1001 and 2000 )and so on...

So 1000 pages of data, with 100,000 cells to look through for each page.

And user is possibly changing the sort each time?

Just imagining the side to side and up and down scrolling - and the time involved for the mind to digest it, hard for me to believe this is what they really need.

I'd try to find out more of what they are looking for.

I would try dumping a smaller subset of information out in Excel, and letting the user work with it, could bring out some insight.