I've got a large table with 10+ millions records inside a SQL Server database. The table consists of certain kind of data for those 50 states in america. Therefore if I create 50 sights, one for every condition, out of this table, would the performance of creating queries from the application be enhanced? Other suggestions?
No. A view is really a macro that grows therefore the same tables finish up within the plan anyway.
Unless of course it's indexed. 50 indexed sights is probably overkill.
For those who have slow performance with 50 million rows (it isn't much really) then it is an indexing problem.
To begin with, I'd make use of the weighted missing index dmv query to determine in which you make the most value for your money
Normal (non-indexed) sights cannot improve performance - they may be regarded as 'shorthands' or 'aliases' for Choose queries, because they don't have any physique under them.
Indexed sights really are a different animal, however it does not appear that you'll require them to date.
The thing you need would be to create appropriate indexes you are cooking, and, possibly, to redesign the table (for example, split it to many tables).
If you would like more specific advice, publish here the table structure plus some good examples of typical queries (those you need to optimise for).
You are on course:
Begin with ensuring you index the information to mirror the reads you have to be fast. Then, since blocking does allow it to be faster, I'd consider require blocking on condition by permitting accessibility data using a saved procedure (having a parameter for condition).
If you possess the proper index and usage inside your execution plans, then your large issues is the amount of memory cache and browse speed from the disk. Creating sights will not fix any one of individuals, it's still exactly the same data on a single disk/cache, only a different logical method of mentioning into it.
One easy suggestion:
use [YourDataBase] select * from sys.dm_db_missing_index_details as ddmid
That's this type of small database that if you're getting performance problems your indexing wrong, you databases design isn't good or else you have designed poorly carrying out queries. SQL server are designed for billions of records having to break a sweat if designed properly.
Sights BTW could be performance Murders if you are using sights that call sights.