ASP. NET unlimited classification-my opinion

Recently, an information publishing system is being developed to allow unlimited cities, unlimited classification, custom fields, and custom display lists. the system was about to be completed, but the efficiency was not very high. previously, I used to read and load data into the cache at one time, and then read data from the cache relationship in the class like an infinitely large number of items. The results showed that in the case of not many infinitely large pieces of data, the loading speed for the first time is acceptable. Later, the slower the loading speed became. I read some documents and optimized them.

For example, non-Recursive Implementation of ASP. NET unlimited classification (Stored Procedure Edition)
Http://hi.baidu.com/webrecord/blog/item/194031081bad8dd062d986f2.html

This method is fine, but it is difficult to store it. It is very fast to read it. If I need to sort it according to custom conditions, I have to write n more code in addition to some other conditions.

The following is my method.

1. The data is stored in normal mode.
2. Create a stored procedure to read data.

Set ansi_nulls on
Set quoted_identifier on
Go
Create procedure [DBO]. [city_getlistinfo] (@ pid int)
As
Begin
Set nocount on
Declare @ level int, @ line char (20)
Create Table # City (cityid int, level int, [ID] [int] identity (1, 1) not null ,)
Create Table # C (item int, level INT)
Insert into # C values (@ PID, 1)
Select @ level = 1

While @ level> 0
Begin
If exists (select * from # C where level = @ level)
Begin
Select @ pid = item
From # C
Where level = @ level
Insert into # city values (@ PID, @ level)
Delete from # C
Where level = @ level
And item = @ PID
Insert # C
Select cityid, @ LEVEL + 1
From [city table]
Where citypid = @ PID order by citysort ASC, cityname ASC
If @ rowcount> 0
Select @ level = @ LEVEL + 1
End
Else
Select @ level = @ level-1
End

Select. cityid,. cityname, case when. citydomain is null then'' else. citydomain end as citydomain,. citypid,. citysort, case when. citysys is null then'false' else. citysys end as citysys, B. level
From # City B, [city table] A where a. cityid = B. cityid order by B. ID ASC

End
The sequence is automatically sorted during reading.

3. Create another table with the same structure as the existing table. Now I use the city table to create the city optimization table.
4. Create a stored procedure

Set ansi_nulls on
Go
Set quoted_identifier on
Go

Create procedure [DBO]. [city_optimizeupdate]
-- Add the parameters for the stored procedure here
As
Begin

Truncate table [city optimization table]
Insert into [t_sys_optimize_city]
Exec [DBO]. [city_getlistinfo]
@ Pid = 0
End

The results are permanently stored in the city optimization table, but the results cannot be updated automatically.

5. Add a trigger to the [city table]

Create trigger [DBO]. [Trigger _ city table]
On [DBO]. [city table]
For update, insert, delete
As
Begin

Set nocount on;

Exec [DBO]. [city_optimizeupdate]

6. Read the [city optimization table] directly after reading the table.