March 17, 2023 at 9:26 pm
Hello.
We have inherited a system and some code and this has been flagged as a performance issue. In fact the query times out and never completes and running it in SSMS shows it took 52 minutes to complete.
We have pretty much narrowed the issue down to the use of a TVF, and the resultant variable, in a query - but no idea why it is such a problem, as the code as written is cited in examples as a 'correct' solution on some web sites.
We tries changing the code to insert a temp table after the TVF and then use the Temp table in the query and the runtime drops from 52 minutes to sub-second.
Also changing the original code from an IN (using the table variable) to an Exists (using the table variable) causes the runtime to drop from 52 minutes to 8 seconds.
I have never used TVFs before and am also doubtful that the IN is working as expected - the TVF gnerates a 'table' with only 3 6-digit numbers in it - it is not about indexes or statistics or cardinality estimation.
AccountID
111889
111891
111899
Where the code appears to be using the Table variable as a filter predicate using IN() I do not believe it is.
Here is the original query that was provided that ran for 52 minutes.
Removing the IN statement completely cause it to run sub-second and return 521 rows (for all accountids)
Hardcoding the 3 Accountids causes it to run sub-second and return the expected 5 rows.
DECLARE @Accounts TABLE (AccountID INT);
INSERT INTO @Accounts
SELECT DISTINCT Value FROM [RSSQLDB_Support].[dbo].[fn_tblListToTableInt]('111889,111891,111899', ',');
SELECT AccountID FROM @Accounts
SELECT I.ItemID, I.AccountID, I.RecordSeriesID
FROM RSSQLDB.dbo.RSITEM I (NOLOCK) INNER JOIN @Accounts Acc ON I.AccountID = Acc.AccountID
WHERE
I.BaseObjectID = 3
AND I.AccountID IN (SELECT AccountID FROM @Accounts)
AND CONTAINS((I.ItemCode, I.AlternateCode, I.SeqBegin, I.SeqEnd, I.UserField1, I.UserField2, I.UserField3, I.UserField4, I.ItemDesc), '12181');
Any idea why using the table variable in the IN() causes this behaviour as I have never used TVF in the past - only Temp tables.?
The INNER JOIN appears to be redundant in the original query - and in fact I removed it and it made no difference. The NOLOCK I guess is in recognition that the query causes Blocking for the 30 seconds before it times out.
Regards
Steve O.
March 18, 2023 at 1:18 pm
DECLARE @Accounts Table (accountid INT)
INSERT INTO @Accounts
VALUES( '111889' ), ( '111891' ), ('111899')
SELECT AccountID FROM @Accounts
SELECT Distinct I.ItemID, I.AccountID, I.RecordSeriesID
FROM RSSQLDB.dbo.RSITEM I (NOLOCK) cross apply @Accounts
WHERE
I.BaseObjectID = 3
AND I.AccountID IN (SELECT AccountID FROM @Accounts)
AND CONTAINS((I.ItemCode, I.AlternateCode, I.SeqBegin, I.SeqEnd, I.UserField1, I.UserField2, I.UserField3, I.UserField4, I.ItemDesc), '12181');
The above works (subsecond duration with only 1500 rows passed rather then 2.4 milion).
I could not figure what the function added so I removed it and just create a table variable (?).
I used Cross Apply rather than Inner Join (did not seem to work in prior testing but now does).
Lastly I had to a A Distinct to the Select to remove duplicate (triplicated) results (3 per accountid i.e. 15 in total vs 5 of which each result appeared 3 times).
Actually now I think about it the purpose of the function is probably that the number of variables passed is not known and cannot be hardcoded so this comes down to Cross Apply and Distinct from the original code.
Every day is a school day.
March 19, 2023 at 12:37 am
Would help to see the DDL for the Tables, TVF and the indexes defined with some sample data to reproduce the behavior. If it is not inline then a new query plan generated to process for each parameter.
=======================================================================
March 19, 2023 at 9:20 am
Hello Emperor100 and thanks for the reply.
Having fed back my findings last night I was informed that the INNER JOIN was never in fact in the original query, and was added during fault finding (I guess I was right that it appeared to be redundant).
I removed the Cross Apply and retested and it returned the required results (and allowed me to remove the Distinct as I no longer had duplicate results). Odd that I am sure that this was the first thing I tried and it seemed to make no difference. I just tried this again and it works so I am waiting to hear back from the original submitter as to exactly what I am testing.
My reading of Cross Apply suggested that it would behave like an Inner Join (but for TVF) but that is not what I am seeing from what I can tell - if I add it back and remove the IN statement it returns 524 matches against the search criteria and does not filter down to 5 matches based on the 3 Accountids specified (519 do not match on Accountid). No idea why this would be,
As I said, removing the InnerJoin and not replacing with a Cross Apply seems to work anyway - which makes sense as all the query needs to do is filter by an Accountid.
When I hear more I will revert - but either the query behaviour is inconsistent or something else is at play.
Thanks
Steve O.
March 19, 2023 at 9:40 am
did you try and change it from TVF to ITVF? that can make a significant difference to most queries.
even if you don't wish to put the whole code it may be good if you at least put some sample code of how that function is used - and its parameters
March 23, 2023 at 9:38 pm
Well having come up with all sorts of 'issues' and various workarounds (including trace flags and db compatibility levels) it transpired that this code is just an extract from a function. Regardless of what we try to do to make the query sub-second as a discrete piece of SQL (as above) - when the changes are applied to the function the runtime reverts to the same problem - extended runtime. The real issue here is that there are 3 queries in the function that have a combined execution time >30 seconds and the query times out. Assuming that extending the timeout could cause Blocking issues, and knowing that the combined runtime for all 3 queries could be around 1 second we are trying to figure out how to fix this. We know that the 3 queries can all run sub-second if we pass the 3 Accountids directly to the IN (or an Exists statement) - however when the same 3 Accountids are passed via a Table Variable (@Accounts) the same 3 queries take @10 seconds each. We have tried hard-coding the Accountids into the 3 queries in the function and the runtime is reduced - so it is not the use of a function per se - it is the passing of a table variable.
Federico mentioned ITVF (inlining?) but I am unsure how to proceed - we are literally talking about changing this :
AND I.AccountID IN (SELECT AccountID FROM @Accounts)
to this :
AND I.AccountID IN (111889,111891,111899)
or similar to somehow fix this (removing the Table Variable from the comparison) and replacing it.
The execution plan was meaningless but we found a query (maybe from Grant Fritchey?) on UDFs that allowed us to pull more realistic plan from the plan cache - but this did not ring true either in terms of attributing proportion to the steps in the batch.
It seems like something really simple to fix but restrictions in functions (with temp tables for one) seem to make it difficult. As I said I can see why the Accountids are in a Table Variable rather than parameters because the number is unknown/unfixed and it seems a logical way to deal with this.
I can provide whatever extra info is required - although may need some assistance with presentation.
Thanks
Steve O.
March 23, 2023 at 10:03 pm
This is how the main function is called ;
SELECT * FROM
dbo.fn_GetMatchingItemsForGlobalSearchSOC('111889,111891,111899','12181', 3)
and this is the main function
ALTER FUNCTION [dbo].[fn_GetMatchingItemsForGlobalSearchSOC] (
@strAccounts NVARCHAR(MAX),
@strSearchString NVARCHAR(MAX),
@objectType INT )
RETURNS @MatchedItems TABLE (ItemID INT NOT NULL, AccountID INT NOT NULL, RecordSeriesID INT NULL)
AS
BEGIN
SET @strSearchString = REPLACE(@strSearchString, '*', '%');
DECLARE @FullTextSearchCondition NVARCHAR(4000) = dbo.fn_GetFullTextSearchConditionFromUserInput(@strSearchString);
DECLARE @Accounts TABLE (AccountID INT);
INSERT INTO @Accounts
SELECT DISTINCT Value FROM fn_tblListToTableInt(@strAccounts, ',');
--Full text searching
IF @FullTextSearchCondition != ''
BEGIN
--Search all items fields
INSERT INTO @MatchedItems
SELECT I.ItemID, I.AccountID, I.RecordSeriesID
FROM RSSQLDB_Test.dbo.RSITEM I (NOLOCK)
WHERE
I.BaseObjectID = @objectType
AND I.AccountID IN (SELECT AccountID FROM @Accounts)
AND CONTAINS((I.ItemCode, I.AlternateCode, I.SeqBegin, I.SeqEnd, I.UserField1, I.UserField2, I.UserField3, I.UserField4, I.ItemDesc), @FullTextSearchCondition);
END
RETURN;
END
GO
The function to build the Table Variable @Accounts just returns 3 values and works fine outside of the main function - even as the comparison operator in the query
CREATE FUNCTION [dbo].[fn_tblListToTableInt](@list AS NVARCHAR(MAX), @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
Value INT
)
AS
BEGIN
--Make sure trailing spaces don't cause problems with LEN function
SET @list = RTRIM(@list);
--Declare helper to identify the position of the delim
DECLARE @DelimPosition INT;
--Prime the loop, with an initial check for the delim
SET @DelimPosition = CHARINDEX(@delim, @list);
--Loop through, until we no longer find the delimiter
WHILE @DelimPosition > 0
BEGIN
--Add the item to the table
INSERT INTO @listTable(Value)
VALUES(CAST(RTRIM(LEFT(@list, @DelimPosition - 1)) AS INT));
--Remove the entry from the List
SET @list = right(@list, len(@list) - @DelimPosition);
--Perform position comparison
SET @DelimPosition = CHARINDEX(@delim, @list);
END
--If we still have an entry, add it to the list
IF LEN(@list) > 0
INSERT INTO @listTable(Value)
VALUES(CAST(RTRIM(@list) AS INT));
RETURN
END
GO
March 23, 2023 at 10:08 pm
Here is the output from the query invoked as above :
ItemID AccountID RecordSeriesID
163093810 111889 NULL
163840777 111889 NULL
163876831 111889 NULL
153853120 111889 NULL
153220815 111889 NULL
The FTS finds the query string in the stated fields in the contains, then restricts the results (524) by the AccountID to arrive at the above resultset.
March 23, 2023 at 11:25 pm
you are missing the definition of "fn_GetFullTextSearchConditionFromUserInput"
looks like this function is called upon input from user - not based on joins to other tables, but straight as you have on example. Is this a correct assumption?
If not please give full and better examples of how it is used.
with regards to the invocation - how long are the two input variables on normal use?
@StraCcOunts nvarchar and @StrSearchString although defined as varchar(max) that may just be a bullet proof definition instead of reflecting the reality of the search.
placed the code below formatted so its easier for others to look at and comment
some aspects that may be changed.
replace your split string with DelimitedSplit8K_LEAD (see https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2) - this is dependent on the max size of your @StraCcOunts string.
the above, including the original function by Jeff are must read articles (and functions for that matter). They are also good examples of what a ITVF function is.
original code below
select *
from dbo.fn_GetMatchingItemsForGlobalSearchSOC('111889,111891,111899', '12181', 3)
--and this is the main function
alter function [dbo].[fn_GetMatchingItemsForGlobalSearchSOC]
( @StraCcOunts nvarchar(max)
, @StrSearchString nvarchar(max)
, @ObjectType int
)
returns @MatchedItems table
( ItemID int not null
, AccountID int not null
, RecordSeriesID int null
)
as
begin
set @StrSearchString = replace(@StrSearchString, '*', '%');
declare @FullTextSearchCondition nvarchar(4000) = dbo.fn_GetFullTextSearchConditionFromUserInput(@StrSearchString);
declare @Accounts table
( AccountID int
);
insert into @Accounts
select distinct Value
from fn_tblListToTableInt(@StraCcOunts, ',');
--Full text searching
if @FullTextSearchCondition != ''
begin
--Search all items fields
insert into @MatchedItems
select i.ItemID
, i.AccountID
, i.RecordSeriesID
from RSSQLDB_Test.dbo.RSITEM i (nolock)
where i.BaseObjectID = @ObjectType
and i.AccountID in (select AccountID from @Accounts)
and contains((i.ItemCode, i.AlternateCode, i.SeqBegin, i.SeqEnd, i.UserField1, i.UserField2, i.UserField3, i.UserField4, i.ItemDesc)
, @FullTextSearchCondition
);
end
return;
end
go
--The function to build the Table Variable @Accounts just returns 3 values and works fine outside of the main function - even as the comparison operator in the query
create function [dbo].[fn_tblListToTableInt]
( @List as nvarchar(max)
, @DeLim as varchar(10)
)
returns @ListTable table
( Value int
)
as
begin
--Make sure trailing spaces don't cause problems with LEN function
set @List = rtrim(@List);
--Declare helper to identify the position of the delim
declare @DelImposition int;
--Prime the loop, with an initial check for the delim
set @DelImposition = charindex(@DeLim, @List);
--Loop through, until we no longer find the delimiter
while @DelImposition > 0
begin
--Add the item to the table
insert into @ListTable
(Value
)
values (cast(rtrim(left(@List, @DelImposition - 1)) as int));
--Remove the entry from the List
set @List = right(@List, len(@List) - @DelImposition);
--Perform position comparison
set @DelImposition = charindex(@DeLim, @List);
end
--If we still have an entry, add it to the list
if len(@List) > 0
insert into @ListTable
(Value
)
values (cast(rtrim(@List) as int));
return
end
go
possible replacement - completely untested and I don't even know if the contains works with a column from a table
this uses the splitter mentioned above
create function [dbo].[fn_GetMatchingItemsForGlobalSearchSOC]
( @StraCcOunts nvarchar(max)
, @StrSearchString nvarchar(max)
, @ObjectType int
)
returns table with schemabinding
/* table definition replaced with straigth return of recordset
returns @MatchedItems table
( ItemID int not null
, AccountID int not null
, RecordSeriesID int null
)
*/as
return
with fulltextcon
as (select dbo.fn_GetFullTextSearchConditionFromUserInput(replace(@StrSearchString, '*', '%')) as FullTextSearchCondition
/* replaces old code
set @StrSearchString = replace(@StrSearchString, '*', '%');
declare @FullTextSearchCondition nvarchar(4000) = dbo.fn_GetFullTextSearchConditionFromUserInput(@StrSearchString);
*/)
/*
replaced with using the string split function directly on the main query
declare @Accounts table
( AccountID int
);
insert into @Accounts
select distinct Value
from fn_tblListToTableInt(@StraCcOunts, ',');
*//* condition now placed on the where clause
--Full text searching
if @FullTextSearchCondition != ''
begin
*/ --Search all items fields
/* insert not needed as we are returning the record set directly to the caller
insert into @MatchedItems
*/ select i.ItemID
, i.AccountID
, i.RecordSeriesID
from RSSQLDB_Test.dbo.RSITEM i (nolock)
cross apply fulltextcon t2
inner join ( select distinct convert(int, Item) as accountid
from dbo.DelimitedSplit8K_LEAD('abc,edf', ',')
) split
on split.AccountID = i.AccountID
where i.BaseObjectID = @ObjectType
and t2.FullTextSearchCondition <> ''
and contains((i.ItemCode, i.AlternateCode, i.SeqBegin, i.SeqEnd, i.UserField1, i.UserField2, i.UserField3, i.UserField4, i.ItemDesc)
, t2.FullTextSearchCondition
)
;
/*
end
return;
*/--end
March 25, 2023 at 9:40 am
Hello Federico.
Thanks for the lengthy response.
I don't pretend to understand the proposed solution but I have tried it and it does not like the column in the Contains so I need to try and figure out if I can make that work - as well as trying to understand why this additional coding would make it faster.
Steve O.
March 25, 2023 at 10:12 am
thanks for feedback.
as that does not seem to work I now ask if the calling of the main function
SELECT * FROM
dbo.fn_GetMatchingItemsForGlobalSearchSOC('111889,111891,111899','12181', 3)
can be change so that the search string is defined when calling it, so a variable can be used
so code above would become
SELECT *
FROM dbo.fn_GetMatchingItemsForGlobalSearchSOC('111889,111891,111899', dbo.fn_GetFullTextSearchConditionFromUserInput(replace('12181', '*', '%')), 3)
and my proposed function would become
create function [dbo].[fn_GetMatchingItemsForGlobalSearchSOC]
( @StraCcOunts nvarchar(max)
, @StrSearchString nvarchar(max)
, @ObjectType int
)
returns table with schemabinding
return
select i.ItemID
, i.accountid
, i.RecordSeriesID
from RSSQLDB_Test.dbo.RSITEM i (nolock)
inner join (select distinct convert(int, Item) as accountid
from dbo.DelimitedSplit8K_LEAD(@StraCcOunts, ',')
) split
on split.accountid = i.accountid
where i.BaseObjectID = @ObjectType
and @StrSearchString <> ''
and contains((i.ItemCode, i.AlternateCode, i.SeqBegin, i.SeqEnd, i.UserField1, i.UserField2, i.UserField3, i.UserField4, i.ItemDesc)
, @StrSearchString
)
;
one other option for you to test is keeping most of your existing function, but replace the @accounts with a direct join to the ITVF I mentioned
alter function [dbo].[fn_GetMatchingItemsForGlobalSearchSOC]
( @StraCcOunts nvarchar(max)
, @StrSearchString nvarchar(max)
, @ObjectType int
)
returns @MatchedItems table
( ItemID int not null
, AccountID int not null
, RecordSeriesID int null
)
as
begin
set @StrSearchString = replace(@StrSearchString, '*', '%');
declare @FullTextSearchCondition nvarchar(4000) = dbo.fn_GetFullTextSearchConditionFromUserInput(@StrSearchString);
--declare @Accounts table
-- ( AccountID int
-- );
--insert into @Accounts
-- select distinct Value
-- from fn_tblListToTableInt(@StraCcOunts, ',');
--Full text searching
if @FullTextSearchCondition != ''
begin
--Search all items fields
insert into @MatchedItems
select i.ItemID
, i.AccountID
, i.RecordSeriesID
from RSSQLDB_Test.dbo.RSITEM i (nolock)
inner join (select distinct convert(int, Item) as accountid
from dbo.DelimitedSplit8K_LEAD(@StraCcOunts, ',')
) split
on split.accountid = i.accountid
where i.BaseObjectID = @ObjectType
--and i.AccountID in (select AccountID from @Accounts)
and contains((i.ItemCode, i.AlternateCode, i.SeqBegin, i.SeqEnd, i.UserField1, i.UserField2, i.UserField3, i.UserField4, i.ItemDesc)
, @FullTextSearchCondition
);
end
return;
end
March 25, 2023 at 11:05 am
Thanks Frederico - I don;t even know how you construct your responses so quickly, never mind understanding them fully.
Using a combination of the below I seem to have something that runs sub-second:
Importantly without 3 it never seems to end the query - probably takes 52 minutes or more.
I will post back after speaking to the Developers on Monday if not before showing the working solution.
N.B. the code I posted is a stripped down version of the actual funtion - I need to apply your resolution to the actual full function and retest - but it looks like this could reduce the execution time significantly.
We have had a lot of false dawns internally - so may be back to look at your later suggestion.
Steve O.
March 25, 2023 at 11:37 am
would be good if you could supply the full code of all required functions - as well as part of what the invocation of it is - even the invocation may also need changes to improve overall performance
May 8, 2023 at 10:29 am
Hello Federico - still waiting (impatiently) for feedback from the Business despite repeated requests. Once I get a definitive reply I will revert. Suffice to say that we implemented the change as above and our own testing said it worked around the problem so thanks a lot for your assistance.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply