Solution 2: The original approach was the article table and keyword table. There may be a large number of duplicate keywords in the keyword table, so the intermediate table is used, and the keywords are no longer duplicated.
design: The first choice is to create a keyword table based on the original article table (Details). The fields include ItemID (primary key) and Keyword (keyword), as well as the intermediate tables DetailsKeywords, including DetailID (article ID) and KeywordID (keyword ID). The following are the main stored procedures:
UpdateRelatedDetails: Update keywords
Copy the codeThe code is as follows:
CREATE procedure
@DetailID INT,
@Keywords NVARCHAR(500)
AS
SET NOCOUNT ON
EXEC DeleteRelatedDetails @DetailID
DECLARE @I INT
DECLARE @Keyword NVARCHAR(50)
DECLARE @KeywordID INT
SET @Keywords=REPLACE(@Keywords,'，', ',')
SET @Keywords=REPLACE(@Keywords,'；', ',')
SET @Keywords=RTRIM(LTRIM(@Keywords))
SET @I=CHARINDEX(',', @Keywords)
WHILE @I>=1
BEGIN
SET @Keyword=LEFT(@Keywords, @I-1)
EXEC AddRelatedDetailKeyword @DetailID, @Keyword
SET @Keywords=SUBSTRING(@Keywords, @I+1,LEN(@Keywords)-@I)
SET @I=CHARINDEX(',', @Keywords)
END
IF @Keywords<>''
EXEC AddRelatedDetailKeyword @DetailID, @Keywords
SET NOCOUNT OFF
GO
AddRelatedDetailKeyword: Add a single keyword
Copy the codeThe code is as follows:
CREATE procedure
@DetailID INT,
@Keyword NVARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @KeywordID INT
SELECT @KeywordID=ItemID FROM DetailKeywords WHERE Keyword=@Keyword
IF @KeywordID IS NULL
BEGIN
INSERT INTO DetailKeywords (Keyword) VALUES(@Keyword)
SET @KeywordID=IDENT_CURRENT('DetailKeywords')
END
INSERT INTO DetailsKeywords (DetailID, KeywordID) VALUES(@DetailID, @KeywordID)
SET NOCOUNT OFF
GO
DeleteRelatedDetails: Delete the previous keyword, there are some problems
Copy the codeThe code is as follows:
CREATE PROCEDURE
@DetailID INT
AS
SET NOCOUNT ON
--There are other things here that have not been judged or deleted
DELETE FROM DetailsKeywords WHERE DetailID=@DetailID
SET NOCOUNT OFF
GO
GetRelatedDetails: Obtain relevant articles based on the article ID
Copy the codeThe code is as follows:
CREATE procedure
@DetailID INT
AS
SELECT DISTINCT , FROM Details d, DetailKeywords k, DetailsKeywords s
WHERE = AND = AND <>@DetailID
GO
I'll post a performance comparison. The environment has 300,000 records, the machine has PIII 800+300 memory, and the hard disk has 7,200 revs:
Copy the codeThe code is as follows:
--The first type, two tables
--select a.* from Article a,ArtKey2 b
--where = and ='Technology 9'
--The second type, three tables
--select a.* from Article a,ArtKey1 b,
--(select KeyID from KeyValue where KeyValue='Technology 9') c
--where = and =
--The third type, three table situation
--select * from Article where ArtID in
--(select ArtID from ArtKey1 where KeyID in
--(select KeyID from KeyValue where KeyValue='Technology 9'))
Optimizing the Sql statement mainly analyzes execution efficiency through the IO cost and CPU cost displayed in the "Execution Plan" diagram. In the execution plan of the SQL query analyzer, IO cost + CPU cost = cost of this step.
The execution analysis results of three types of SQL statements in the same batch:
1. Accounts for 14.22%, and the actual execution cost is 2.96.
2. Accounts for 43.08%, and the actual execution cost is 9.96.
3. Accounts for 42.70%, and the actual execution cost is 8.88.
The first one is much less expensive than the latter, and the execution plan is much simpler than the latter. Obviously, the first one is better than the other two. Concise execution plan is not a superior standard, but SQL statements are not written concise enough. They usually have to write complex statements to meet business needs. The second and three types of execution plans are only different in the last step. The former is an inner join and the latter is a Right Semi Join.