SQL Query optimization - Long running query

Question

I have a database with the following tables :

CREATE TABLE [Document](
    [IdDocument] [int] NOT NULL,
    [DocumentType] [int] NOT NULL
    CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)
)

CREATE TABLE [ExternalKey](
    [IdExternalKey] [int] NOT NULL,
    [RefDocument] [int] NOT NULL,
    [EntityType] [int] NOT NULL,
    [Value] [int] NOT NULL, 
    CONSTRAINT PK_ExternalKey PRIMARY KEY CLUSTERED (IdExternalKey)
    CONSTRAINT FK_RefDocument FOREIGN KEY (RefDocument) REFERENCES Document(IdDocument),
    CONSTRAINT UC_ExternalKey UNIQUE (RefDocument, EntityType, Value)
)

Documents are mapped to physical files on a drive. Each document has a type (eg : 2 = IDENTITY_CARD)
Those documents are linked with external entities with a one to many relation. Each external key has an entity type (eg : 50 = PERSON) and a ID (eg : 213235)

Database is quite big : Document is 10M records. ExternalKey 40M.

Example of data inside ExternalKey:

IdExternalKey  RefDocument EntityType  Value
1              1           50          3421
2              1           50          9524
3              1           60          7893
4              2           50          1752
5              2           50          8979

I want to filter documents based on their type and the entities they are linked with (simplified query) :

WHERE (DocumentType IN (10, 20, ...) 
       AND ExternalKeys ANY (EntityType = 50 AND Value IN (4,5,6,7,8,9,...)))    
   OR (DocumentType IN (80, 90, ...) 
       AND ExternalKeys ANY (EntityType = 60 AND Value IN (110,120,130,...)))

The best i could come up is this:

SELECT TOP 50 IdDocument
FROM Document d     
WHERE
    (d.DocumentType IN (SELECT Id FROM @listA1) AND d.IdDocument IN (SELECT ex.RefDocument FROM ExternalKey ex WHERE ex.EntityType = 60 AND ex.Value IN (SELECT Id FROM @listB1))) OR
    (d.DocumentType IN (SELECT Id FROM @listA2) AND d.IdDocument IN (SELECT ex.RefDocument FROM ExternalKey ex WHERE ex.EntityType = 61 AND ex.Value IN (SELECT Id FROM @listB2))) OR
    ...
    (d.DocumentType IN (SELECT Id FROM @listA3) AND d.IdDocument IN (SELECT ex.RefDocument FROM ExternalKey ex WHERE ex.EntityType = 59 AND ex.Value IN (SELECT Id FROM @listB3)))
ORDER BY IdDocument

Table value parameters contains a lot of IDs (about 100K in total) Table definition is:

CREATE TYPE int_list_type AS TABLE(Id int NOT NULL PRIMARY KEY)

The query is slow and performance vary a lot depending the sort (from a dozen of seconds to several minutes). For example, sorting documents on IdDocument is way faster than sorting them on DocumentType. What I have tried so far:

Creating indexes on RefDocument, EntityType, Value (mulitple column index). I have tried different column orders.
Replacing the table value parameters (eg : @listA1) by hardcoded values in the query : parsing time explode (as expected).
Replacing IN with EXISTS : performance is similar. Replace IN with JOIN : a lot slower

Is there some more efficient ways to perform the filter, any tips ?

Here is the query plan for 2 OR clauses (each OR clause is in RED)

What's the primary key for your tables? Do you have foreign keys? What's the link between the Document table and the ExternalKeys table? Does "RefDocument" = "IdDocument"? — Danielle Paquette-Harvey
– Danielle Paquette-Harvey, Commented Dec 5, 2017 at 15:10
Can ExternalKeys have many entry for the same "RefDocument","EntityType","Value"? What gets this table unique? — Danielle Paquette-Harvey
– Danielle Paquette-Harvey, Commented Dec 5, 2017 at 15:18
What happens if you add a RECOMPILE hint? Can you add the output of SET STATISTICS IO, TIME ON for your query? Are you able to post an actual plan to pastetheplan.com? — Joe Obbish
– Joe Obbish, Commented Dec 10, 2017 at 5:31

Danielle Paquette-Harvey · Accepted Answer · 2017-12-05 19:28:51Z

EDITED after question got more details.
I get a Clustered Index Scan and an Index Scan. But I don't have a lot of rows in my table.

CREATE TABLE [Document](
[IdDocument] [int] NOT NULL,
[DocumentType] [int] NOT NULL,
CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)
)

CREATE TABLE [ExternalKey](
[IdExternalKey] [int] NOT NULL,
[RefDocument] [int] NOT NULL,
[EntityType] [int] NOT NULL,
[Value] [int] NOT NULL, 
CONSTRAINT PK_ExternalKey PRIMARY KEY CLUSTERED (IdExternalKey),
CONSTRAINT FK_RefDocument FOREIGN KEY (RefDocument) REFERENCES Document(IdDocument),
CONSTRAINT UC_ExternalKey UNIQUE (RefDocument, EntityType, Value)
)

-- Insert a few values
DECLARE @IdDocument INT, @DocumentType INT, @IdExternalKey INT;
Set @IdDocument=0;
SET @DocumentType = 10;
select @IdExternalKey=MAX(IdExternalKey)+1 from ExternalKey ;


set nocount on
WHILE (@IdDocument < 10000)
BEGIN
    INSERT INTO Document(IdDocument, DocumentType) VALUES (@IdDocument, @DocumentType);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey, @IdDocument, 50, 3421);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+1, @IdDocument, 50, 9524);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+2, @IdDocument, 60, 7893);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+3, @IdDocument, 50, 1752);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+4, @IdDocument, 50, 8979);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+5, @IdDocument, 60, 7822);

    SET @DocumentType=CASE WHEN @DocumentType > 100 THEN 10 ELSE @DocumentType + 10 END;
    SET @IdDocument = @IdDocument + 1;
    SET @IdExternalKey = @IdExternalKey+ 6;
END

SELECT TOP 50 IdDocument
FROM Document d
JOIN ExternalKey e on e.RefDocument=d.IdDocument 
WHERE
(d.DocumentType IN (10,20,30) AND e.EntityType = 60 AND e.Value IN (4,5,6,7,8)) OR
(d.DocumentType IN (50) AND e.EntityType = 61 AND e.Value IN (110,120,130) OR
(d.DocumentType IN (80,90) AND e.EntityType = 59 AND e.Value IN (1,2,3)))
ORDER BY IdDocument

I would add that if the ORDER BY is not necessary and can be done in the program instead, it can help accelerate the query.

What you really want to do to improve your query would be to fix the "Index Scan" of the ExternalKey table or the scan of the Document table. For example, since the ExternalKey is unique by RefDocument, entityType, Value, maybe consider using it as clustered index?

The thing is right now, with the keys and indexes, SQL Server has no choice but to scan the whole table to get the rows you need. So if you have a million row, it has to scan every row even if you only get 10 as a result.

Thanks for answer. I will try suggestions and see if it improve performance. — tigrou
– tigrou, Commented Dec 5, 2017 at 16:51
It also depends on the size of the table and the data in the table. Have you heard of FILTERED INDEX? Depending on your data, it could help you optimize the query. learn.microsoft.com/en-us/sql/relational-databases/indexes/… — Danielle Paquette-Harvey
– Danielle Paquette-Harvey, Commented Dec 5, 2017 at 17:17
By doing a left join we will get duplicates, since RefDocument column is not unique, is it right ? Also why not using a INNER JOIN ? — tigrou
– tigrou, Commented Dec 5, 2017 at 18:20
Yes you are right, do an INNER JOIN, I did a mistake, I will edit my answer — Danielle Paquette-Harvey
– Danielle Paquette-Harvey, Commented Dec 5, 2017 at 19:28

KumarHarsh · Accepted Answer · 2017-12-20 10:58:38Z

Main problem is query itself.

if @listA1,@listB1 table type are mandatory search condition then they can be put into only table type @listA,@listB

SELECT TOP 50 IdDocument
FROM Document d     
WHERE EXISTS
    ( SELECT Id FROM @listA A1 where a1.Id= d.DocumentType)
     AND EXISTS(
    SELECT ex.RefDocument FROM ExternalKey ex WHERE  ex.RefDocument=d.IdDocument and  ex.EntityTypein(60,61,62) AND ex.Value IN 
    (SELECT Id FROM @listB)) 
ORDER BY IdDocument

Explain your search requirement and parameter that you and fix your query.

Stack Exchange Network

SQL Query optimization - Long running query

2 Answers 2

Your Answer

Hot Network Questions

SQL Query optimization - Long running query

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions