0

I'm in a situation where I'm being submitted a (potentially quite long) list of "match entities", each of which contains user data to match, along with a unique ID for that match information. Actual user data from matched users, along with the unique ID for that match, needs to be returned from my SQL query. So, let's say I were given 2 entities to try and match users against, both of which were trying to match phone numbers against a user's phone number; I could match any users associated with the entities submitted along with the "match entity"'s unique ID by using a union like this (client_handle is the submitted unique ID):

SELECT
    [client_handle] = 'axtwe-wasst',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7747 122123', '+44 7904 223323')

UNION

SELECT
    [client_handle] = 'zjfft-albwq',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7758 444111', '+44 7758 444222', '+44 7758 444333')

The trouble with this method is that it could potentially result in a very large number of UNIONs if large numbers of match entities are submitted to me. 1000 submitted match entities would result in 999 UNIONs. Is this actually an issue performance-wise, and is there a better way to achieve the result I want? Alternatively I could just loop through each submitted match entity and run a query to match each, but then I'd have 1000 separate queries if 1000 match entities are submitted, which seems even worse.

2
  • Save your parameters into temporary table of structure ([parameters group] [client_handle], [mobile_no]) (one phone number per row, more than one row per group) and join it to data table. – Akina Apr 6 at 19:22
  • Could you put that in an answer with example TSQL? – Jez Apr 6 at 19:26
2

Schematically:

CREATE TABLE tmp ([parameters group] INT, [client_handle] CHAR(11), [mobile_no] CHAR(15));

INSERT INTO tmp VALUES
(1,'axtwe-wasst','+44 7747 122123'),
(1,'axtwe-wasst','+44 7904 223323'),
(2,'zjfft-albwq','+44 7758 444111'),
(2,'zjfft-albwq','+44 7758 444222'),
(2,'zjfft-albwq','+44 7758 444333');

SELECT DISTINCT
    [client_handle] = tmp.[parameters group],
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive] t
JOIN 
    tmp ON t.[mobile_no] = tmp.[mobile_no];

PS. [parameters group] is not used - on shown source data it is excess, but may be useful in real task (if so then GROUP BY instead of DISTINCT must be used).

6
  • Would this actually be better performance than a bunch of UNIONs? You have to create a temp table with a bunch of INSERTs into it... not sure that would be any better? – Jez Apr 6 at 20:02
  • @Jez The best criteria is practice.. test. – Akina Apr 6 at 20:33
  • @Jez Some good things to note about this solution is you're only doing one INSERT, just the number of values is a large batch for that one INSERT, also 1,000 values to be inserted is actually a rather tiny amount of data. Also the single JOIN clause will likely be a lot more relationally efficient than SQL Server needing to process 999 UNION clauses. – J.D. Apr 6 at 23:49
  • @Jez You can also do a bulk insert to the temp table, or use a Table Valued Parameter, both of which are very fast – Charlieface Apr 7 at 2:03
  • 1
    @DavidBrowne-Microsoft Interesting you should say that as that's exactly the solution I've decided on in the end. I'll probably post it as the answer later. – Jez Apr 7 at 16:46
0

What I ended up doing in the end was passing in JSON representing the different set of queries, and breaking them into a table using OPENJSON. This actually makes the query simple enough that it doesn't even need to be in a stored procedure or create a temp. table, as OPENJSON can be used directly in a JOIN query, being a table-valued function:

SELECT
    *,
    qry.[UniqueId] AS [temp_user_match_unique_id]
FROM
    [dbo].[vAPP_UsersActive] usr
    INNER JOIN (
        SELECT * FROM OPENJSON(@jsonQuery) WITH (UniqueId nvarchar(max), MobileNo nvarchar(255), Email nvarchar(255))
    ) qry ON (qry.[MobileNo] IS NOT NULL AND usr.[mobile_no] = qry.[MobileNo]) OR (qry.[Email] IS NOT NULL AND usr.[email] = qry.[Email])

... where the @jsonQuery variable is passed in with a value like:

[{"UniqueId":"123aaa","MobileNo":null,"Email":"joanna@t.com"},{"UniqueId":"123aaa","MobileNo":null,"Email":"james@dmn.co.uk"},{"UniqueId":"234bbb","MobileNo":"+44 7121 234588","Email":null},{"UniqueId":"234bbb","MobileNo":"+44 7121 234599","Email":null},{"UniqueId":"234bbb","MobileNo":"+44 7759 112233","Email":null}]

The calling code first has to flatten out the queries with the same UniqueId into multiple "query rows" as seen in the JSON above, and then unflattens the matches returned with a dictionary whose key is the UniqueId.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.