I have a large table with 10+ millions records in a SQL Server database. The table contains certain type of data for all 50 states in the US. So if I create 50 views, one for each state, from this table, would the performance of making queries from my application be improved? Other suggestions?
A:
You're on the right track:
Start with making sure you index the data to reflect the reads you need to be fast. Then, since filtering does make it faster, I would consider require filtering on state by allowing access to the data via a stored procedure (with a parameter for state).
Brad
2010-09-14 18:20:17
+7
A:
No. A view is a macro that expands so the same tables end up in the plan anyway.
Unless it's indexed. 50 indexed views is most likely overkill.
If you have slow performance with 50 million rows (it's not that much really) then it's an indexing problem.
Edit:
To start with, I'd use the weighted missing index dmv query to see where you get the most bang for your buck
gbn
2010-09-14 18:23:16
+2
A:
Normal (non-indexed) views cannot improve performance - they could be thought of as 'shorthands' or 'aliases' for SELECT queries, as they have no physical structure under them.
Indexed views are a different beast, but it doesn't seem that you need them so far.
What you need is to create appropriate indexes on your table, and, possibly, to redesign the table (for instance, split it to several tables).
If you want more specific advice, post here the table structure and some examples of typical queries (the ones you want to optimise for).
VladV
2010-09-14 18:23:40
A:
If you have the proper index and usage in your execution plans, then the big issues is the quantity of memory cache and read speed of the disk. Creating views won't fix any of those, it is still the same data on the same disk/cache, just a different logical way of referring to it.
xyz
2010-09-14 18:24:23
Unless those are materialized views, of course.
Denis Valeev
2010-09-14 18:27:35
@Denis, you would materialize calculated data to save calculating it each time, the op is just looking to access what they have faster, so the view doesn't help anything
xyz
2010-09-14 19:30:40
@xyz, indexed views for each state would act as partitioning, i.e. removing all the data irrelevant to the state a view is created for and obviously speed things up.
Denis Valeev
2010-09-14 20:14:12
A:
One simple suggestion:
use [YourDataBase]
select * from sys.dm_db_missing_index_details as ddmid
Denis Valeev
2010-09-14 18:25:56
A:
That is such a tiny database that if you are having performance problems your indexing wrong, you databases design is bad or you have designed poorly performing queries. SQL server can handle trillions of records without breaking a sweat if designed correctly.
Views BTW can be performance Killers if you use views that call views.
HLGEM
2010-09-14 20:30:32