How can I optimize the performance of SQL Server query and analysis?

A Data Analysis System was developed some time ago, using Asp.net + sqlserver 2000. Because the data volume is collected in real time, two or three pieces of data are inserted per second on average. The customer uses the system to analyze the data during working days.

During table creation, the latest data is stored in the History Table due to the large amount of data. Some manually processed data or automatically processed data based on conditions (the automatically processed data accounts for about 3/4 of all data. The remaining items must be processed manually or not .) The table for the corresponding month, as shown in the back_8 table now. Each month's backup data is stored in the back table for one year.

The index creation and performance optimization problems are the issues. At present, the analysis system is almost unavailable, and it takes several minutes for each analysis to come out.

Generally, there are few cross-month queries, and the query conditions are basically not cross-month. That is, the user-selected Start Time and end time are in the same month. The efficiency of cross-month queries is not considered for the time being, because users are rarely used.

However, you need to check two tables, one is the history table and the other is the back _ current month table. Because the name of the next table is unknown, a lot of dynamic query statements are used in the stored procedure to pass the table name. The two tables are separately queried, and then the records are merged using union.

One is the site_name field, and the parameters of the query condition are also a list. That is, you must select site_name in the interface. The user selected data on the interface is the other three fields in the basecell table: region, county, city, and base station. Find the site_name_all field that matches the site_name field in basecell based on the user selected (one or more. (The user selects a Chinese base station and the matching field is in the English name ).

The conditions for most use of all analyses are the above two. There are still some less common conditions, such as the severity of the fault. This condition is only a few options. I don't want to worry about it over the first two.

The entire situation is described above.

Is it because site_name also uses in queries to slow down the speed? However, the site_name list is not fixed, and hundreds of thousands may exist. So it is very difficult to optimize. No idea. Which of the following experts has a good solution?