Recently, the application of a business developed database. There are two data tables: A and B, where A records about 20,000, and the data in B is 2 million, and now requires data screening with A.ID = B.ID.
For this request, I made a view view1, associating A.ID with B.ID, running the query of the view, get the result for 2 seconds. At this time, there is a special demand: write a stored procedure, there is a circulation of a cursor during the storage process, which repeated 10,000 times, and uses a query for VIEW1 in each cycle. For example, as follows:
Circulating tuning body (repeating 10,000 times) {SELECT @ text = text from view1 where id = @ id - where @ text, @ ID is variable Update form1 set text = @ text where id = @ id}
Execute the above stored procedure, it is found that it takes a 7-minute calculation time. After analysis, the efficiency full loss is on the scan of View1 view, although it takes only 2 seconds to query this view, but in this case Next, you need too much time.
Therefore, it is improved that the stored procedure is as follows:
Create Table #Temptab (ID nvarchar (20), text nvarchar (20)) Query View1 view, insert record into #Temptab temporary table ...
Circulating tuning body (repeating 10,000 times) {SELECT @ text = text from #temptab where id = @ ID - where @ text, @ id is variable Update form1 set text = @ text where id = @ id}
Don't smash the above changes, at this time, the stored procedure takes only 50 seconds.
For the above phenomenon, the analysis is as follows: When a view is queried in multiple cycles, a large amount of time will be consumed, so the calculation of the data of the view will be calculated to increase the speed.
If you have any gave, please reply, discuss it together.
