Sql-server – OPENJSON running once per row of an INNER JOIN unnecessarily

azure-sql-databaseexecution-planperformancesql server

I have a table MP_User with 147 entries, and in order to query it, I am passing in JSON containing an array of "query objects", each of which has a unique ID and either an email address or a mobile number to match to user(s). These get converted to one row each and matches with the users able through a JOIN operation. User results need to be returned along with the unique ID. Although the following query does work for this:

WITH QryCte AS (
    SELECT * FROM OPENJSON('[{"UniqueId":"0xy-5244249247924974924C","MobileNo":null,"Email":"joe.bloggs@domain.co.uk"},{...},...]') WITH (UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255))
)
SELECT
    usr.[user_id],
    usr.[firstname],
    usr.[surname],
    qry.[UniqueId] AS [temp_user_match_unique_id]
FROM
    [dbo].[mp_user] usr
    INNER JOIN QryCte qry ON (qry.[MobileNo] IS NOT NULL AND usr.[mobile_no] = qry.[MobileNo]) OR (qry.[Email] IS NOT NULL AND usr.[email] = qry.[Email])

… it seems to run very inefficiently on our Azure SQL database, taking 5-10 seconds to run (it's much quicker on my local SQL Server DB, but that's probably because of increased resources as the execution plan is the same). The execution plan reveals:

query execution plan

… that the OPENJSON has been run 147 times, once for each row of the MP_User table, presumably because of the INNER JOIN. However, I don't understand why SQL Server does this. The result of OPENJSON isn't going to change and you'd think that making it the table in a CTE would give SQL Server the hint that it just needs to execute it once and cache the result table that can then be joined to in the CTE's SELECT statement.

How can I make SQL Server just run the OPENJSON once, put the result into a memory table, and join to that? Is it possible to do in a CTE or do I need to use procedural code where I explicitly define the table variable and assign it? I'd rather avoid that if possible as the CTE form looks more elegant I think.

Best Answer

I ended up having to solve this by creating a table variable to hold the results of OPENJSON and joining on that variable. This forces SQL Server to actually create a temp table containing the query data rather than repeatedly running OPENJSON. Apparently a CTE doesn't do that, even though I thought it did. Presumably this applies to any table-valued function, actually.

This works, and results in OPENJSON only being run once (when deriving the data to fill the table variable with):

DECLARE @jsonQueryText nvarchar(max) = '[{"UniqueId":"0xy-5244249247924974924C","MobileNo":null,"Email":"joe.bloggs@domain.co.uk"},{...},...]';
DECLARE @Qry TABLE(UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255));
INSERT INTO @Qry SELECT * FROM OPENJSON(@jsonQueryText) WITH (UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255));

SELECT
    usr.[user_id],
    usr.[firstname],
    usr.[surname],
    qry.[UniqueId] AS [temp_user_match_unique_id]
FROM
    [dbo].[mp_user] usr
    INNER JOIN @Qry qry ON (qry.[MobileNo] IS NOT NULL AND usr.[mobile_no] = qry.[MobileNo]) OR (qry.[Email] IS NOT NULL AND usr.[email] = qry.[Email])
Related Question