We presently possess a explore our website that enables customers to go in to start dating ? range. The page calls a saved method that queries for that time frame and returns the right data. However, lots of our tables contain 30m to 60m rows. If your user joined to start dating ? selection of annually (or some large range), the database would grind to some halt.
Can there be any solution that does not involve placing a time constraint around the search? Paging has already been carried out to show just the first 500 rows, however the database continues to be getting hit hard. We can not put a tough limit on the amount of results came back since the user "may" need these.
When the user inputed time frame would be to large, have the application perform the search in small time frame steps. Possibly utilizing a slow start approach: first search is restricted to, say 30 days range and when it bings back under the 500 rows, search the 2 preceding several weeks til you have 500 rows.
You will need to begin with newest dates for climbing down order with earliest dates for climbing order.
It may sound in my experience like this can be a design and never a technical problem. Nobody ever needs countless records of information quickly.
You are going to need to request a little hard questions: Can there be another way to get people their data compared to web? It is possible to better method for you to request for blocking? The facts the customers need these details for and it is possible to method for you to provide that much cla of confirming rather than spewing everything?
Reexamine what it's the customers would like.
We can not put a tough limit around the quantity of results came back since the user "may" need these.
You appear to become stating that you cannot avoid the user from asking for large datasets for business reasons. I can not use whatever techical way around that.
Index your date area and pressure a question to make use of that index:
CREATE INDEX ix_mytable_mydate ON mytable (mydate) SELECT TOP 100 * FROM mytable WITH (INDEX ix_mytable_mydate) WHERE mydate BETWEEN @start and @end
It appears the optimizer selects
FULL TABLE SCAN if this sees the big range.
Would you please publish the query you utilize and execution plan of this query?
Have no idea which of those are possible
- Make use of a internet search engine as opposed to a database?
- Do not let very general searches
- Cache the outcomes of popular searches
- Break the database into shards on separate servers, mix the outcomes in your application.
- Do multiple queries with more compact date ranges internally
It may sound as if you really aren't paging. I'd possess the saved procedure have a range (that you simply calculated) for that pages after which only get individuals rows for that current page. Presuming the data does not change frequently, this could lessen the strain on the database server.
How's your table data physically structured i.e. partitioned, split across Filegroups and disk storage etc. ?
Are you currently using table partitioning? Otherwise you need to consider using aligned partitioning. You can partition your computer data by date, say a partition for every year for example.
Where I to request a question spanning 3 years, on the multiprocessor system, I possibly could at the same time access the 3 partitions at the same time, therefore enhancing query performance.
How's it going applying the paging?
I recall I faced an issue such as this a couple of in the past and also the problem was related to the way i implemented the paging. Nevertheless the data which i was coping with wasn't as large as yours.
Parallelize, and set it in ram (or perhaps a cloud). You will find that once you need to access considerable amounts of information simultaneously, rdbms end up being the problem rather than the answer. Nobody doing visualizations utilizes a rdbms.