CREATE PROCEDURE MANYSELECT_Int_COUNT_test
                 @str    nvarchar(MAX),
                 @retdata  bit = 1,
                 @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)

CREATE TABLE #manyintjoin (number int NOT NULL)

SELECT @start = sysdatetime()

EXEC unpack_with_manyselect @str, '#manyintjoin'

DECLARE @cnt bigint
SELECT @cnt = SUM(len(word))
FROM   usrdictwords u
JOIN   #manyintjoin m ON u.wordno = m.number


SELECT @tookms = datediff(ms, @start, sysdatetime());


go

CREATE PROCEDURE MANYSELECT_Int_EXISTS_test
                 @str    nvarchar(MAX),
                 @retdata  bit = 1,
                 @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)

CREATE TABLE #manyintjoin (number int NOT NULL)

SELECT @start = sysdatetime()

EXEC unpack_with_manyselect @str, '#manyintjoin'

INSERT #Int_JOIN (word)
SELECT u.word
FROM   usrdictwords u
WHERE  EXISTS (SELECT * FROM #manyintjoin m WHERE u.wordno = m.number)


SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT word FROM #Int_JOIN
TRUNCATE TABLE #Int_JOIN

go

CREATE PROCEDURE MANYSELECT_Int_JOIN_test
                 @str    nvarchar(MAX),
                 @retdata  bit = 1,
                 @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)

CREATE TABLE #manyintjoin (number int NOT NULL)

SELECT @start = sysdatetime()

EXEC unpack_with_manyselect @str, '#manyintjoin'

INSERT #Int_JOIN (word)
SELECT u.word
FROM   usrdictwords u
JOIN   #manyintjoin m ON u.wordno = m.number


SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT word FROM #Int_JOIN
TRUNCATE TABLE #Int_JOIN

go

CREATE PROCEDURE MANYSELECT_Int_UNPACK_test
                 @str    nvarchar(MAX),
                 @retdata  bit = 1,
                 @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)

CREATE TABLE #manyintunpack (number int NOT NULL)

SELECT @start = sysdatetime()

EXEC unpack_with_manyselect @str, '#manyintunpack'

INSERT #Int_UNPACK (number)
SELECT number
FROM   #manyintunpack


SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT number FROM #Int_UNPACK
TRUNCATE TABLE #Int_UNPACK

go

CREATE PROCEDURE MANYSELECT_Str_COUNT_test
                 @str    nvarchar(MAX),
                 @retdata  bit = 1,
                 @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)

CREATE TABLE #manystrjoin (str nvarchar(50) COLLATE database_default NOT NULL)

SELECT @start = sysdatetime()

EXEC unpackstr_with_manyselect @str, '#manystrjoin'

DECLARE @cnt bigint
SELECT @cnt = SUM(len(guid))
FROM   usrdictwords u
JOIN   #manystrjoin l ON l.str = u.word

SELECT @tookms = datediff(ms, @start, sysdatetime());



go

CREATE PROCEDURE MANYSELECT_Str_EXISTS_test
                 @str    nvarchar(MAX),
                 @retdata  bit = 1,
                 @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)

CREATE TABLE #manystrjoin (str nvarchar(50) COLLATE database_default NOT NULL)

SELECT @start = sysdatetime()

EXEC unpackstr_with_manyselect @str, '#manystrjoin'

INSERT #Str_JOIN (wordno, guid)
SELECT u.wordno, u.guid
FROM   usrdictwords u
WHERE  EXISTS (SELECT * FROM #manystrjoin l WHERE l.str = u.word)

SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT wordno FROM #Str_JOIN

TRUNCATE TABLE #Str_JOIN

go

CREATE PROCEDURE MANYSELECT_Str_JOIN_test
                 @str    nvarchar(MAX),
                 @retdata  bit = 1,
                 @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)

CREATE TABLE #manystrjoin (str nvarchar(50) COLLATE database_default NOT NULL)

SELECT @start = sysdatetime()

EXEC unpackstr_with_manyselect @str, '#manystrjoin'

INSERT #Str_JOIN (wordno, guid)
SELECT u.wordno, u.guid
FROM   usrdictwords u
JOIN   #manystrjoin l ON l.str = u.word

SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT wordno FROM #Str_JOIN

TRUNCATE TABLE #Str_JOIN

go

CREATE PROCEDURE MANYSELECT_Str_UNPACK_test
                 @str    nvarchar(MAX),
                 @retdata  bit = 1,
                 @tookms   int = NULL OUTPUT AS

DECLARE @start datetime2(3)

CREATE TABLE #manystrunpack (str nvarchar(50) COLLATE database_default NOT NULL)

SELECT @start = sysdatetime()

EXEC unpackstr_with_manyselect @str, '#manystrunpack'

INSERT #Str_UNPACK (word)
SELECT str
FROM   #manystrunpack

SELECT @tookms = datediff(ms, @start, sysdatetime());

IF @retdata = 1 SELECT word FROM #Str_UNPACK

TRUNCATE TABLE #Str_UNPACK

go