Tuning a SQL query

Question

I am trying to tune the following query which is got 500k IOs against the INVENTTABLE which I thought would be a good place to start. The complexity of all the joins has beaten me though and I have been unable to wrap round where a good start would be with this?

Thanks in advance for any tips.

SET STATISTICS IO ON

DECLARE
@paramCompany       varchar(3),
@paramCreatedBy     varchar(8000),
@paramCustomer      varchar(100),
@paramBlanketId     varchar(20)

SET @paramCompany = 'adf'
SET @paramCreatedBy = 'All'
SET @paramCustomer = NULL
SET @paramBlanketId = NULL

SELECT
 un.MAINSALESID,
 un.DATAAREAID,
 Sum(un.Quantity) as 'Quantity',
 Sum(un.SalesValue) as 'SalesValue'
INTO #desprel
 FROM
 (SELECT 
  stl.MAINSALESID,
  st.DATAAREAID,
  sl.SALESQTY as 'Quantity',
  sl.SALESQTY * sl.SALESPRICE as 'SalesValue'
 FROM
  DynamicsV5Realtime.dbo.SALESTABLE st
 INNER JOIN
  DynamicsV5Realtime.dbo.SALESLINE sl
 ON
  sl.SALESID = st.SALESID
  and sl.DATAAREAID = st.DATAAREAID
 INNER JOIN
  DynamicsV5Realtime.dbo.INVENTTABLE it
 ON
  it.ITEMID = sl.ITEMID
  and it.DATAAREAID = sl.DATAAREAID
 INNER JOIN
  DynamicsV5Realtime.dbo.SALESTABLELINKS stl
 ON
  stl.SUBSALESID = st.SALESID
  and stl.DATAAREAID = st.DATAAREAID
 INNER JOIN
  DynamicsV5Realtime.dbo.SALESTABLE st1
 ON
  st1.SALESID = stl.MAINSALESID
  and st1.SALESTYPE = 5
 --to get Order created by
 inner JOIN
  --TR  
  vw_R000_EmployeeList pm
 ON 
  --st1.SALESTAKER = pm.emplid
  CASE WHEN st1.SALESTAKER = 'balla' THEN 'gende' ELSE st1.SALESTAKER END = pm.emplid
  and (pm.[NAME] in (SELECT * FROM       udf_MultiValueParameterHandlingString(@paramCreatedBy)) or @paramCreatedBy = 'All')
 WHERE
  st.DATAAREAID = 'adf'
  and st.SALESTYPE = 3 -- Release Order
  and st.SALESSTATUS in (2,3)
  and sl.SALESSTATUS <> 4
  and it.ITEMGROUPID <> 'G0022A'
  and sl.SALESQTY > 0
  and st1.CUSTACCOUNT = IsNull(@paramCustomer,st1.CUSTACCOUNT)
  and st1.SALESID = IsNull(@paramBlanketId,st1.SALESID)
 UNION ALL
 SELECT 
  stl.MAINSALESID,
  st.DATAAREAID,
  sl.SALESQTY as 'Quantity',
  sl.SALESQTY * sl.SALESPRICE as 'SalesValue'
 FROM
  DynamicsV5Realtime.dbo.SALESTABLE st
 INNER JOIN
  DynamicsV5Realtime.dbo.SALESLINE sl
 ON
 sl.SALESID = st.SALESID
 and sl.DATAAREAID = st.DATAAREAID
 INNER JOIN
  DynamicsV5Realtime.dbo.INVENTTABLE it
 ON
  it.ITEMID = sl.ITEMID
  and it.DATAAREAID = sl.DATAAREAID
 INNER JOIN
  DynamicsV5Realtime.dbo.SALESTABLELINKS stl
 ON
  stl.SUBSALESID = st.MARIMSSALESID
  and stl.DATAAREAID = st.DATAAREAID
 INNER JOIN
  DynamicsV5Realtime.dbo.SALESTABLE st1
 ON
  st1.SALESID = stl.MAINSALESID
  and st1.SALESTYPE = 5
 --to get Order created by
 inner JOIN
 --TR  
  vw_R000_EmployeeList pm
 ON 
 --st1.SALESTAKER = pm.emplid
  CASE WHEN st1.SALESTAKER = 'balla' THEN 'gende' ELSE st1.SALESTAKER END = pm.emplid
 and (pm.[NAME] in (SELECT * FROM   udf_MultiValueParameterHandlingString(@paramCreatedBy)) or @paramCreatedBy = 'All')
 WHERE
  st.DATAAREAID = 'adf'
  and st.SALESTYPE = 3 -- Release Order
  and st.SALESSTATUS in (2,3)
  and sl.SALESSTATUS <> 4
  and it.ITEMGROUPID <> 'G0022A'
  and sl.SALESQTY < 0
  and st1.CUSTACCOUNT = IsNull(@paramCustomer,st1.CUSTACCOUNT)
  and st1.SALESID = IsNull(@paramBlanketId,st1.SALESID)     
            ) un
GROUP BY
 un.MAINSALESID,
 un.DATAAREAID

You may want to tag as SQL Server, since tuning may be pretty RDBMS specific. — Joachim Isaksson, Jan 23 '14 at 11:28
Yep. Assuming that this IS Sql Server, run the query in SSMS and tell it to produce the Query Plan. What I do then it look for the operation which has the highest % and try to optimise it. Also SSMS will sometimes suggest new indexes which will help: applying them is generally a good thing! — simon at rcl, Jan 23 '14 at 11:44
Yes I am looking at the execution plan and one of the largest index seeks is against a table DIRPARTYCOMMUNICATIONRELA - which must be some kind of internal work table so unsure how I can index that. I was hoping for some help with how to rewrite the query to bring the total IOs down, such I can apply that technique elsewhere. — Tom, Jan 23 '14 at 11:57

score 1 · Answer 1 · answered Jan 23 '14 at 12:46

Is it just me or is this particular section a bit confusing? I'd tackle this first.

inner JOIN
  --TR  
  vw_R000_EmployeeList pm
 ON 
  --st1.SALESTAKER = pm.emplid
  CASE WHEN st1.SALESTAKER = 'balla' THEN 'gende' ELSE st1.SALESTAKER END = pm.emplid
  and (pm.[NAME] in (SELECT * FROM udf_MultiValueParameterHandlingString(@paramCreatedBy)) or @paramCreatedBy = 'All')

Can you trying using a temp table to figure out the SALESTAKER ID's beforehand and then join to that? Using a udf and case statement in a join condition is probably not the best idea.

Yes I should probably split that out – Tom Jan 23 '14 at 12:51 — Tom, Jan 23 '14 at 12:51

score 0 · Accepted Answer · answered Jan 23 '14 at 12:10

0

Your idea to have a look at the inventtable is good. It is evident, that none of its fields are shown in the results and it is not needed as a link to join two other tables. So it is only there to guarantee that an appropriate record in inventtable exists. This can be done in an EXISTS clause.

Then check why there are two almost similar joins glued with UNION ALL. All the tables are accessed twice. Is this necessary? One time you want all sl.salesqty < 0 and you link via stl.subsalesid = st.marimssalesid. The other time you want all sl.salesqty > 0 and you link via stl.subsalesid = st.salesid. Maybe you can do this one select statement and access each table only once.

The result has one field of salestable (st). This is st.DATAAREAID. But this is a search and join criteria actually, so you can replace it with either sl.DATAAREAID or even with the literal 'adf'. So no field from table st. Maybe also only an EXISTS thing?

Same again, when salestable is read again as st1. It is not part of the selected columns, so maybe it can be converted into an EXISTS clause.

This is just on first glimpse, so I may be mistaken here and there. It is just to give you a something to start with.

answered Jan 23 '14 at 12:10

Thorsten Kettner

89,309
7
49
73

Thanks for that. On the EXISTS pointwould I replace the join with an EXISTS clause? I don't see how I could else how can I filter for the record. Would you provide an example of how to do this? I promise I am trying to understand this and not just get you to re-write it for me. – Tom Jan 23 '14 at 12:20
You remove the join completely and add the accordant exists clause to the where clause instead: ... and exists (select * from DynamicsV5Realtime.dbo.INVENTTABLE it where it.ITEMID = sl.ITEMID and it.DATAAREAID = sl.DATAAREAID and and it.ITEMGROUPID <> 'G0022A') – Thorsten Kettner Jan 23 '14 at 14:07
OK I have tried that, unfortunately this increased the amount of IO and also the time taken to run :( – Tom Jan 23 '14 at 14:38
Sorry about that. This happens when the result set contains too many rows where a lookup is needed. I was hoping this was not the case. An IN clause might help then: ... and sl.ITEMID in (select it.ITEMID from DynamicsV5Realtime.dbo.INVENTTABLE it where it.DATAAREAID = 'adf' and it.ITEMGROUPID <> 'G0022A'). However, probably this will lead to about the same execution plan as your original query. Well, you can try. – Thorsten Kettner Jan 23 '14 at 14:51
If you are lucky, you can invert the query and ask for NOT In or NOT EXISTS where it.DATAAREAID = 'adf' and it.ITEMGROUPID = 'G0022A'. Be aware: This is definitly asking for something different, but _could_ be the same in this particular case. This depends on what data is actually stored in the tables. – Thorsten Kettner Jan 23 '14 at 14:57
OK I have actually commented out the extra join criteria for and stl.DATAAREAID = st.DATAAREAID. As this was filtered in the where clause anyway this has made a big difference. Is my thinking right here, or have I fundamentally changed the query? – Tom Jan 23 '14 at 15:22
You must make sure of course that both stl.DATAAREAID and st.DATAAREAID are equal to 'adf'. Your original join does so by st.DATAAREAID = 'adf' plus stl.DATAAREAID and st.DATAAREAID. You can replace it by st.DATAAREAID = 'adf' plus stl.DATAAREAID = 'adf' for instance, but you cannot remove the restriction on one of the tables completely. (If you did so and the query got _faster_ in spite of more records, this may indicate that before an index was used when a full table scan would have been more appropriate.) – Thorsten Kettner Jan 23 '14 at 15:56
hmm this is where my coding falls down, I am going to have to go away and think about that one. – Tom Jan 23 '14 at 16:36

Tuning a SQL query

2 Answers2