SQL Server Large Table Query Optimization

Question

I'm currently trying to perform some SAP analysis regarding licensing. I'd like to link users to transactions through roles they have and get the required licenses based on their transactions. This analysis is performed across multiple SAP Servers with each server containing multiple SAP instances. At one point of this analysis, I struggle with large tables and query optimization since the query is never ending (run more that 10 hours (though I have 128 GB of RAM and big CPU performance).

Here are some details about the tables I based my query on:

SAP_LICENSING.DBO.calc_2_USER_ROLES which contains 1M rows and is defined by those columns: DTT_ID (SAP Server Name), MANDT (SAP instance name), UNAME (user name), ASSIGNED_ROLE (role he is assigned to, for example : accoutant). This table stores the roles assigned to users
SAP_LICENSING.DBO.raw_AGR_1251 which contains 2M rows and is defined by those columns : DTT_ID (SAP Server Name), MANDT (SAP instance name), AGR_NAME (role), LOW (transaction range start), HIGH (transaction range end)
SAP_LICENSING.DBO.param_LICENSING_RULES contains 140K rows and is defined by those columns : TRANSAC (transaction), LICENSE (required license for the related transaction)

I've set clustered index on each of those tables:

SAP_LICENSING.DBO.calc_2_USER_ROLES: Index on DTT_ID, MANDT, UNAME, ASSIGNED_ROLE
SAP_LICENSING.DBO.raw_AGR_1251: Index on DTT_ID, MANDT, AGR_NAME, LOW, HIGH
SAP_LICENSING.DBO.param_LICENSING_RULES: Index on TRANSAC

Here are my response times for a select on each sub query:

Subquery regarding the UR alias: 30 seconds
Subquery regarding the T alias: 1 minute and 30 seconds
Subquery regarding the LR alias: 5 seconds

Please note the following points :

when running this query, I see a max usage of 10% of CPU and 30% of RAM
the query works quickly on a subset of SAP servers (filter on 1 SAP server name)
the second subquery is used to transform SAP transaction range into searchable transactions compatible with a JOIN with the BETWEEN keyword
the LEFT JOIN is used because I want to keep unmatched transactions for further analysis

Here is the related query I'm struggling with :

INSERT INTO SAP_LICENSING.DBO.calc_3_USER_ROLES_TRANSACTIONS
SELECT UR.DTT_ID,UR.MANDT,UR.UNAME,UR.ASSIGNED_ROLE,
TRANSAC_FROM,TRANSAC_TO,SAP_TRANSAC_RANGE,LR.TRANSAC,LR.LICENSE
--Get User Role Assignments
FROM 
    (
    SELECT DISTINCT UR.DTT_ID,UR.MANDT,UR.UNAME,UR.ASSIGNED_ROLE
    FROM SAP_LICENSING.DBO.calc_2_USER_ROLES UR
    )UR
--Join on transactions and Convert SAP Transaction ranges into SQL searchable ranges
JOIN 
    (
    SELECT T.DTT_ID,T.MANDT,T.AGR_NAME,T.AUTH,
    replace(replace(T.LOW,'*',' '),'$',' ') AS TRANSAC_FROM,
    replace(replace(coalesce(T.HIGH,T.LOW),'*',left('ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ',41-len(coalesce(T.HIGH,T.LOW)))),'$','Z') AS TRANSAC_TO,
    CONCAT(T.LOW,' TO ', isnull(T.HIGH,'')) as SAP_TRANSAC_RANGE
    FROM SAP_LICENSING.DBO.raw_AGR_1251 T
    )T
ON UR.ASSIGNED_ROLE=T.AGR_NAME and UR.MANDT=T.MANDT and UR.DTT_ID=T.DTT_ID
--Join on transactions requiring a license
LEFT JOIN SAP_LICENSING.DBO.param_LICENSING_RULES LR
ON LR.TRANSAC between T.TRANSAC_FROM and T.TRANSAC_TO;

Thanks a lot for any help you would give. Kindest Regard

when posting queries which require tuning,it would be good(can be answerable ) only,when you post minimal repro for others to test.Here is a good example of how to generate table schema,stats for others to repro.Take a look at my question at DBA.SE for more info:dba.stackexchange.com/questions/157353/… — TheGameiswar
– TheGameiswar, Commented Mar 2, 2017 at 11:16

KumarHarsh · Accepted Answer · 2017-03-02 11:42:52Z

1

i) Is it possible to remove distinct ?
ii) go for batch insert
iii)Use covering index / filter index
iv)Comment insert and manipulate with select
v) if index are not being use then you hv to use Hint
You forgot to use with (nolock)

answered Mar 2, 2017 at 11:42

KumarHarsh

5,0941 gold badge22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Y.B. · Accepted Answer · 2017-03-02 11:42:29Z

0

You perform some costly string transformations on your biggest table and use the result in JOIN condition. That is bound to be inefficient. By default SQL Server Optimiser would put the biggest table the last, so T would get JOINed to UR and LR through those transformed strings.

One way would be to first create a temporary table T, the other - create an indexed view on SAP_LICENSING.DBO.raw_AGR_1251 with TRANSAC_FROM and TRANSAC_TO calculated fields as part of the clustered index.

You also might get lucky with the optimiser if you move T to the front of the FROM clause and JOIN the rest of the tables to it. Worth trying OPTION (FORCE ORDER) with it too.

Posting current execution plan would also help to give a proper advice.

edited Mar 2, 2017 at 11:42

answered Mar 2, 2017 at 11:19

Y.B.

3,58617 silver badges24 bronze badges

Collectives™ on Stack Overflow

SQL Server Large Table Query Optimization

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related