This is the result using ElasticSearch integrated with T-SQL:
For that solution, I used C# + ElasticSearch and Nest libraries + CLR.
The execution plans are very fast and light:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(9 rows affected)
Table 'marsweb_CarIndex'. Scan count 11, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B97405BA'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 58 ms.
The source for that solution is that:
I had set up in VS 2017 the references:
My SqlFunction is that:
===========================================================================
using System;
using System.Data.SqlTypes;
using Elasticsearch.Net;
using Nest;
public class DocumentAttributes
{
public string id { get; set; }
public string model { get; set; }
}
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SqlFunction(string strSearch)
{
ConnectionSettings connectionSettings;
ElasticClient elasticClient;
StaticConnectionPool connectionPool;
//Multiple node for fail over (cluster addresses)
var nodes = new Uri[]
{
new Uri("https://myelasticsearchserver:9200/"),
//new Uri("Add server 2 address") //Add cluster addresses here
//new Uri("Add server 3 address")
};
connectionPool = new StaticConnectionPool(nodes);
connectionSettings = new ConnectionSettings(connectionPool);
elasticClient = new ElasticClient(connectionSettings);
var response = elasticClient.Search(s => s
.Index("standard_web_index")
.Type("car")
.Query(q => q.QueryString(qs => qs.Query(strSearch))));
string idReturn = "";
foreach (var hit in response.Hits)
{
idReturn += hit.Source.id.ToString().Trim() + ",";
}
return new SqlString (idReturn);
}
My SQL SqlFunction is that:
===========================================================================
drop FUNCTION [dbo].[fnElasticSearch]
go
drop ASSEMBLY [SearchES]
GO
CREATE ASSEMBLY [SearchES]
AUTHORIZATION [dbo]
FROM 'C:\ElasticSearch\DLL\SearchES.dll'
WITH PERMISSION_SET = UNSAFE;
GO
ALTER ASSEMBLY [SearchES]
DROP FILE ALL
ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A00000000000000000000000000000000000 AS N'SearchES.pdb';
GO
PRINT N'Creating [dbo].[SqlFunction]...';
GO
CREATE FUNCTION [dbo].[fnElasticSearch]
(@strSearch NVARCHAR (MAX) NULL)
RETURNS NVARCHAR (MAX)
AS
EXTERNAL NAME [SearchES].[UserDefinedFunctions].[SqlFunction]
GO
PRINT N'Update complete.';
USE DBA
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create or ALTER function [dbo].[fnElasticSearchSplitList](
@String varchar(max)
)
returns @Split table (Value varchar(1000))
as
begin
declare @Pos int
declare @Piece varchar(1000)
set @String = replace(@String,'''''','''') + ','
set @Pos = charindex(',' , @String)
while @Pos <> 0
begin
set @Piece = left(@String, @Pos - 1)
set @Piece = ltrim(rtrim(@Piece))
insert into @Split(Value) values (@Piece)
set @String = stuff(@String, 1, @Pos, '')
set @Pos = charindex(',' , @String)
end
return
end
CREATE FUNCTION [dbo].[fnElasticSearchList]
(@strSearch NVARCHAR (MAX) NULL)
returns @Split table (Value varchar(1000))
as
begin
insert into @Split(Value)
Select value from dbo.fnElasticSearchSplitList(dbo.fnElasticSearch(@strSearch))
return
end
My T-SQL instegrated with CLR as a function is that:
===========================================================================
select
*
from marsweb_CarIndex
where goodNumber in (Select value from dbo.fnElasticSearchList('4wd'))