Boost MS SQL Transaction Performance

Question

If you do not have enough information, I will attach them if necessary.

Workspace

I have a database running on a standard version of MS SQL 2012 of this kind:

• tables:

  • users (id, softId (not unique), date of birth)

    • rows: 10.5 million
    • indexes: all three columns, date of birth (clustered)

  • docs (docId, userId, createDate, deleteDate, lastname, forename, theoryId)

    • rows: 23 million

    • indexes: lastname, forename, docId, createDate, userID (clustered)

    • Please note: in this particular case, the names are associated with documents, not userId

  • classifications (id, description)

    • rows: 200

  • three data tables

    • lines: 10, 13 and 0.3 million.
    • indexes: docIds

• relationship:

  • users for documents: from 1 to n

  • document classification: from 1 to n

  • docs for data tables: 1 to n

, :

16

SELECT * FROM (
    select * from docs 
    where userID in (
        select distinct userID from users where softId like '...'
    )
) as doc
LEFT JOIN users on users.userID = doc.userId
LEFT JOIN classifications on classifications.id = doc.classificationId
LEFT JOIN data1 on data1.docId = doc.docId
LEFT JOIN data2 on data2.docId = doc.docId
LEFT JOIN data3 on data3.docId = doc.docId;

- 15

SELECT
docID, calssificationId, classificationDescription,
userId, softId, forename, lastname, birthdate,
data1.id, data1.date, data2.id, data2.date, data3.id, data3.date,
FROM docs
JOIN users on users.userID = doc.userId AND softId like '...'
LEFT JOIN classifications on classifications.id = doc.classificationId
LEFT JOIN data1 on data1.docId = doc.docId
LEFT JOIN data2 on data2.docId = doc.docId
LEFT JOIN data3 on data3.docId = doc.docId;

17

DECLARE @userIDs table( id bigint );
DECLARE @docIDs table( id bigint );

insert into @userIDs select userID from users where softId like '...';
insert into @docIDs select docId from docs where userId in ( select id from @userIDs);
SELECT * FROM users where userID in ( select id from @userIDs);
SELECT * FROM docs where docID in (select id from @docIDs);
SELECT * FROM data1 where data1.docId in (select id from @docIDs);
SELECT * FROM data2 where data2.docId in (select id from @docIDs);
SELECT * FROM data3 where data3.docId in (select id from @docIDs);
GO

- 14

DECLARE @userIDs table( id bigint, softId varchar(12), birthdate varchar(8) );
DECLARE @docIDs table( id bigint, classification bigint, capture_date datetime, userId bigint, lastname varchar(50), forename varchar(50) );


INSERT INTO @userIDs select userID, softId, birthdate from users where softId like '...';
INSERT INTO @docIDs select docID, classification, capture_date, userID, lastname, forename from docs where userID in ( select id from @userIDs);

SELECT * FROM @userIDs;
SELECT * FROM @docIDs;

SELECT [only needed fields] FROM data1 where docID in (select id from @docIDs);
SELECT [only needed fields] FROM data2 where docID in (select id from @docIDs);
SELECT [only needed fields] FROM data3 where docID in (select id from @docIDs);

• userIds
• docIds
• userIds output
• 1

@AntonínLejsek docId , pkId - . :

• : -1
• Multi-Select-Statement: -5

, :

• : 4
• Multi-Select-Statement: 6

""

- ?

Answer 1

:

users:

SELECT *
FROM docs JOIN
     users
     ON users.userID = doc.userId AND softId LIKE '...'  LEFT JOIN
     . . .

IN , JOIN.

. , , , , , .

Answer 2

, .

. . , 4B, . , 12B , . , , , . , id.

- -, softIds , .

Answer 3

, .

DECLARE @userIDs table( id bigint primary key, softId varchar(12), birthdate varchar(8) );
DECLARE @docIDs table( id bigint primary key, classification bigint, capture_date datetime, userId bigint, lastname varchar(50), forename varchar(50) );