SQL Server – Query to Return a Unique ID for Each Different Clause Matched

query-performancesql server

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.

Best Answer

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).

Related Solutions

Sql-server – Replace cursor with set-based approach

I would probably just do this the brute force way, and add indexes to support these joins where they don't exist. Not much gain to treating new customers and old customers any different once you've inserted all the customers that don't already exist:

INSERT dbo.Customer(fname, lname, address, city, state, zip, email)
    SELECT fname, lname, address, city, state, zip, email
     FROM dbo.job AS j
     WHERE job_no = @job_no
     AND NOT EXISTS
     (
        SELECT 1 FROM dbo.Customer
        WHERE fname = j.fname
        AND lname = j.lname
        AND (address = j.address OR email = j.email)
     );

INSERT INTO dbo.personal_code (customer_id, mailing_id, personal_code, email)
SELECT c.customer_id, j.mailing_id, j.personal_code, c.email
  FROM dbo.Customer AS c
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

INSERT dbo.personal_code_extra(personal_code_id, extra)
SELECT pc.personal_code_id, j.extra
  FROM dbo.personal_code AS pc
  INNER JOIN dbo.Customer AS c
  ON pc.customer_id = c.customer_id
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

Sql-server – Separate archive tables or soft delete for inventory database

I would say that if your users are going to need to query the Archive data, then using the bit flag or soft delete is easier. If the users don't need the data any longer, then I would go with the archive tables.

Based on your description above, I would suggest going with the Soft Delete version. I can tell you from experience in one of our systems, we went with an archive schema to move older data to and it lead to nothing but issues because the users needed access to the data. So it lead to using UNION ALL on every query we had to run.

As a result of the issues, we stopped that route and moved to the soft delete, which is much easier.

We added a bit flag to all of the tables it was needed and then we just included this in the WHERE clause when querying the data.

A suggestion would be to make sure that this field has a default value when you INSERT data. If you are using IsArchived then the default value on the column would be false since you do not want it archived immediately.

Best Answer

Related Solutions

Sql-server – Replace cursor with set-based approach

Sql-server – Separate archive tables or soft delete for inventory database

Related Question