2, the different types of divided into a few tables, vertical slice
3, commonly used joins to build an index
4, the server to put a few hard drives, data, logs, indexes are stored on separate disks, so that you can improve IO throughput
5, with the optimizer
6, consider redundancy, so that you can reduce the connection
7, you can consider the creation of statistical tables, that is, real-time generation of totals table, so that you can avoid each query is counted once
mrzxc and so on said good, consider your system, pay attention to load balancing, query optimization, 250,000 is not big, you can build a table, and then press the mrzxc's 3 4 5 7 optimization. Speed, there are so many factors that affect it, and the larger the amount of data the more obvious.
1, storage The hard disk will be divided into NTFS format, NTFS is faster than FAT32, and depends on the size of your data files, 1G or more you can use multiple database files, so that you can access the load spread to multiple physical hard disk or disk array.
2, tempdb tempdb should also be a separate physical hard disk or disk array, it is recommended to be placed on RAID 0, so that it has the highest performance, do not set a maximum value on it so that it automatically grows
3, log files log files should also be separated from the data files in a different physical hard disk or disk array, which also improves the performance of the hard disk I / O. The data files should also be separated from the physical hard disk, so that the hard disk I / O performance.
4, partitioned view is to split your data horizontally on the cluster server, it is suitable for large-scale OLTP, SQL clusters, if your database is not particularly large access is not recommended.
5, clustered index your table must have a clustered index, in the use of clustered index query, block query is the fastest, such as the use of between, should be physically continuous for him, you should minimize it updaet, should make it physically discontinuous.
6, non-clustered indexes non-clustered indexes have nothing to do with the physical order, the design of it must have a high degree of selectivity, you can improve the query speed, but the table update when these non-clustered indexes will affect the speed and occupy a lot of space, if you are willing to space and modification time in exchange for the speed of the can be considered.
7, indexed view If you build an index on the view, the result set of the view will be stored, the performance of a particular query with a lot of performance can be improved, but the same for the update statement it will also seriously reduce performance, generally used in the data warehouse in a relatively stable data.
8, maintenance index you will build the index, regular maintenance is very important, with dbcc showcontig to observe the page density, scanning density, etc., in a timely manner with the dbcc indexdefrag to organize the table or view of the index, in the necessary time to rebuild the index with dbcc dbreindex can be subjected to a good effect. Whether you are using a few tables 1, 2, 3 points can improve performance, 5, 6, 8 points you must do, as for 4, 7 points depending on your needs, I personally do not recommend. Typed more than half an hour want to be writing a paper, I hope to help you.