Jag har under en tid funderat på hur man kan sortera ett sökresultat utifrån dess träff-freksens. Så idag när jag hade lite tid så gjorde jag ett litet snabbt script för SQL Server.

Jag är dock inte nöjd med hur scriptet är skrivet. Det känns inte alls optimalt men det fungerar. I denna tråd skulle jag gärna vilja att vi försöker förbättra scriptet och hur det ska användas.

Scriptet är en funktion som returnerar träff-frekvensen. Vi antar att @SearchCriteria är satt till "SQL performance counters" och @Data har värdet "Bla bla SQL bla bla permonce bla bla" då får man en träfffrekvens på 50%. Anledningen för att man får 50% är för att jag klassar hela strängen som ett sökkriterie samt varje ord i strängen. Så sammanlagt blir det fyra kriterier och man får träff på två. Så här ser funktionen ut:


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SearchRanking]
(
@SearchCriteria varchar(4000),
@Data text
)
RETURNS int
AS
BEGIN

DECLARE @ReturnValue int
DECLARE @SplitOn nvarchar(5)
DECLARE @Counter int
DECLARE @TempTable table
(
Id int identity(1,1),
SearchWord varchar(4000)
)

SET @SplitOn = ' '
SET @Counter = 1

-- Insert the whole search criteria to the temp table
INSERT INTO @TempTable (SearchWord) VALUES (@SearchCriteria)

-- Split the search criteria just to prepare the insert to the temp table
WHILE (CHARINDEX(@SplitOn,@SearchCriteria)>0)
BEGIN
INSERT INTO @TempTable (SearchWord)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@SearchCriteria,1,CHARINDEX(@SplitOn,@SearchCriteria)-1)))

SET @SearchCriteria = SUBSTRING(@SearchCriteria,CHARINDEX(@SplitOn,@SearchCriteria)+1,LEN(@SearchCriteria))
SET @Counter = @Counter + 1
END

-- Insert search criterias to temp table
INSERT INTO @TempTable (SearchWord)
SELECT Data = LTRIM(RTRIM(@SearchCriteria))

-- Check how many hits we can find
SELECT @ReturnValue = CAST(CAST(COUNT(Id) AS decimal(18,2)) /
CAST((SELECT COUNT(Id) FROM @TempTable) AS decimal(18,2)) AS decimal(18,2)) * 100
FROM @TempTable
WHERE @Data LIKE '%' + SearchWord + '%'

RETURN @ReturnValue
END


Jag tror som sagt inte att scriptet är optimalt. Det var något jag skrev ihop relativt snabbt. Where-satsen är nog inte den bästa. Nedan följer lite sql för att använda funktionen.


DECLARE @SearchCriteria nvarchar(4000)
SET @SearchCriteria = 'SQL performance'

DECLARE @TestTable table
(
Id int identity(1,1),
Data text
)


INSERT INTO @TestTable (Data) VALUES ('Bla bla SQL bla bla performance bla bla')
INSERT INTO @TestTable (Data) VALUES ('Bla bla bla bla')
INSERT INTO @TestTable (Data) VALUES ('SQL')
INSERT INTO @TestTable (Data) VALUES ('Bla bla performance')


SELECT Data, dbo.SearchRanking (@SearchCriteria, Data) AS SearchHitPercent
FROM @TestTable
WHERE dbo.SearchRanking (@SearchCriteria, Data) <> 0
ORDER BY dbo.SearchRanking (@SearchCriteria, Data) DESC


Det som jag inte gillar är att funktionen anropas tre gånger. Man kan få ner det till två genom att göra så här. Det bästa vore att bara anropa det en gång men det är nog inte möjligt.


SELECT TestTable.Data, TestTable.SearchHitPercent FROM
(SELECT Data, dbo.SearchRanking (@SearchCriteria, Data) AS SearchHitPercent
FROM @TestTable
WHERE dbo.SearchRanking (@SearchCriteria, Data) <> 0) AS TestTable
ORDER BY TestTable.SearchHitPercent DESC


Har ni några förslag på hur man kan optimera scriptet eller hur man kan göra på ett helt annat sätt så får ni gärna skriva ett inlägg.

Jag fick några tips kring förbättring av scriptet av en kollega. Det första var att göra om funktionen till en tabell-funktion och sedan använda mig av cross apply. Scriptet ser ut så här för tillfället.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[SearchRanking]
(
@SearchCriteria varchar(4000),
@Data text
)
RETURNS @RetTable table
(
SearchRanking int
)
AS
BEGIN

DECLARE @SplitOn nvarchar(5)
DECLARE @Counter int
DECLARE @TempTable table
(
SearchWord varchar(4000)
)

SET @SplitOn = ' '
SET @Counter = 1

-- Insert the whole search criteria to the temp table
INSERT INTO @TempTable (SearchWord) VALUES (@SearchCriteria)

-- Split the search criteria just to prepare the insert to the temp table
WHILE (CHARINDEX(@SplitOn,@SearchCriteria)>0)
BEGIN
INSERT INTO @TempTable (SearchWord)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@SearchCriteria,1,CHARINDEX(@SplitOn, @SearchCriteria)-1)))

SET @SearchCriteria = SUBSTRING(@SearchCriteria,CHARINDEX(@SplitOn,@SearchCriteria )+1,LEN(@SearchCriteria))
SET @Counter = @Counter + 1
END

-- Insert search criterias to temp table
INSERT INTO @TempTable (SearchWord)
SELECT Data = LTRIM(RTRIM(@SearchCriteria))

-- Insert search criterias to return table
INSERT INTO @RetTable (SearchRanking)
SELECT CAST(CAST(COUNT(SearchWord) AS decimal(18,2)) /
CAST((SELECT COUNT(SearchWord) FROM @TempTable) AS decimal(18,2)) AS decimal(18,2)) * 100
FROM @TempTable
WHERE @Data LIKE '%' + SearchWord + '%'

RETURN
END


Anropet ser ut så här:

DECLARE @SearchCriteria varchar(4000)
SET @SearchCriteria = 'SQL performance counters'

DECLARE @TestTable table
(
Data text
)


INSERT INTO @TestTable (Data) VALUES ('Bla bla SQL bla bla performance bla bla')
INSERT INTO @TestTable (Data) VALUES ('Bla bla bla bla')
INSERT INTO @TestTable (Data) VALUES ('SQL')
INSERT INTO @TestTable (Data) VALUES ('Bla bla performance')
INSERT INTO @TestTable (Data) VALUES ('Bla bla sql performance counters bla')


SELECT Data, SearchHitPercent.SearchRanking
FROM @TestTable
CROSS APPLY dbo.SearchRanking (@SearchCriteria, Data) AS SearchHitPercent
WHERE SearchHitPercent.SearchRanking <> 0
ORDER BY SearchHitPercent.SearchRanking DESC

... ska forska i detta också lite. Har ju jobbat med sökmotorer en del under åren. Ska bli kul å damma av kunskaperna lite :D

Återkommer!

//henke