--------
-- setup (~2 minutes)
--------

DROP VIEW IF EXISTS vw_entity_attribute_values_aggregated;
DROP TABLE IF EXISTS entity_attribute_values;
DROP TABLE IF EXISTS #base;
GO

CREATE TABLE entity_attribute_values
    (
     entity_id INT NOT NULL
    ,attribute_id INT NOT NULL
    ,value INT NOT NULL
    );
ALTER TABLE entity_attribute_values ADD CONSTRAINT PK_entity_attribute_values PRIMARY KEY (entity_id, attribute_id) WITH (IGNORE_DUP_KEY = ON);


-- generate random data, using itzik ben gan's generator
WITH    L0
          AS ( SELECT   1 C
               UNION ALL
               SELECT   1
             ), -- 2 rows
        L1
          AS ( SELECT   1 AS C
               FROM     L0 AS A
               CROSS JOIN L0 AS B
             ), -- 4 rows
        L2
          AS ( SELECT   1 AS C
               FROM     L1 AS A
               CROSS JOIN L1 AS B
             ), -- 16 rows
        L3
          AS ( SELECT   1 AS C
               FROM     L2 AS A
               CROSS JOIN L2 AS B
             ), -- 256 rows
        L4
          AS ( SELECT   1 AS C
               FROM     L3 AS A
               CROSS JOIN L3 AS B
             ), -- 65,536 rows
        L5
          AS ( SELECT   1 AS C
               FROM     L4 AS A
               CROSS JOIN L4 AS B
             ), -- 4,294,967,296 rows
        Nums
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY ( SELECT   NULL
                                                     ) ) AS N
               FROM     L5
             )
    INSERT  INTO entity_attribute_values
            SELECT TOP 15000000
                    entity_id = ABS(CHECKSUM(NEWID())) % 3000000
                   ,attribute_id = ABS(CHECKSUM(NEWID())) % 10
                   ,value = ABS(CHECKSUM(NEWID())) % 20
            FROM    Nums;
GO

CREATE INDEX ix_entity_attribute_values_attribute_id_value ON dbo.entity_attribute_values (attribute_id, value);
GO


---------
-- search
---------


-- select one entity to search for
DECLARE @entity_id INT;

SELECT TOP 1
        @entity_id = eav.entity_id
FROM    dbo.entity_attribute_values AS eav
GROUP BY eav.entity_id
HAVING  COUNT(*) > 3
ORDER BY NEWID();



SELECT  entity_id_for_search_demo = @entity_id;


DECLARE @search_entity_id INT
   ,@current_count INT;

SET @search_entity_id = @entity_id;

-- get data of the searched entity
SELECT  entity_id
       ,attribute_id
       ,value
INTO    #base
FROM    entity_attribute_values
WHERE   entity_id = @search_entity_id;

SELECT  @current_count = COUNT(*)
FROM    #base;


--
-- find full matches - this is the query that needs to be tuned
--

SELECT  eav.entity_id
       ,count_match_attributes = COUNT(*)
FROM    entity_attribute_values AS eav
INNER JOIN #base AS b
        ON b.attribute_id = eav.attribute_id
           AND b.value = eav.value
WHERE   eav.entity_id <> @search_entity_id
GROUP BY eav.entity_id
HAVING  COUNT(*) = @current_count
ORDER BY eav.entity_id DESC;