SQL Query Optimization

Question

I have this query which takes about 52 second to be executed I have been meaning to use Cursors or anyway that could help me to optimize the execution time

select * from (
select distinct 'Net Sales' Class,  SUM ( CAST(ROUND(ABS(dbo.Trial_Balance_Variances.PTD_ACTUAL), 0) AS float)) over (partition by  CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) ) 
- (select distinct sum( CAST(ROUND(ABS(v.PTD_ACTUAL), 0) AS float)) over (partition by  CONVERT(int,CONVERT(varchar(10), v.PERIOD_DATE, 112)) )  from Trial_Balance_Variances v
inner join 
dbo.[P&L] p ON v.GL_ACCOUNT_NUMBER = p.Code  where p.Category in('Sales Rebates')
and v.PERIOD_NAME = Trial_Balance_Variances.PERIOD_NAME
   ) Total,CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) AS DateKey
from Trial_Balance_Variances
inner join 
dbo.[P&L] ON dbo.Trial_Balance_Variances.GL_ACCOUNT_NUMBER = dbo.[P&L].Code LEFT OUTER JOIN
dbo.Location AS l ON l.Code = SUBSTRING(CONVERT(varchar(50), dbo.Trial_Balance_Variances.ACCOUNT_COMBINATION), 4, 2) where Category = 'Potash Sales (Gross)'

union 

SELECT distinct l.Class,  SUM ( CAST(ROUND(ABS(dbo.Trial_Balance_Variances.PTD_ACTUAL), 0) AS float)) over (partition by  CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) )
+ (select distinct sum( CAST(ROUND(ABS(v.PTD_ACTUAL), 0) AS float)) over (partition by  CONVERT(int,CONVERT(varchar(10), v.PERIOD_DATE, 112)) ) from Trial_Balance_Variances v
inner join 
dbo.[P&L] p ON v.GL_ACCOUNT_NUMBER = p.Code  where p.Category in('Change In Inventory','Freight','Dispatch')
and v.PERIOD_NAME = Trial_Balance_Variances.PERIOD_NAME
   ) Total
,  CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) AS DateKey from Trial_Balance_Variances 
inner join 
dbo.[P&L] ON dbo.Trial_Balance_Variances.GL_ACCOUNT_NUMBER = dbo.[P&L].Code LEFT OUTER JOIN
dbo.Location AS l ON l.Code = SUBSTRING(CONVERT(varchar(50), dbo.Trial_Balance_Variances.ACCOUNT_COMBINATION), 4, 2)
where Class='Cost of goods sold'  and Category not in ('Sales Rebates','Royalties','Potash Sales (Gross)','Interest Expense LTL','Interest Expense LTMed','Interest Expense Others'
,'Interest Income','(Gain)/Loss from Investment Jor','(Gain)/Loss from Investment N+B','Gain from Investment', 'EXC Gain from Investment' ,'D Gain from Investment ', 'Loss from Investment Jormag','Bank Charges','Other Revenues','Change In Inventory','Freight','Dispatch') 

union 

SELECT distinct l.Class,  SUM ( CAST(ROUND(ABS(dbo.Trial_Balance_Variances.PTD_ACTUAL), 0) AS float)) over (partition by  CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) ) Total
,  CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) AS DateKey from Trial_Balance_Variances 
inner join 
dbo.[P&L] ON dbo.Trial_Balance_Variances.GL_ACCOUNT_NUMBER = dbo.[P&L].Code LEFT OUTER JOIN
dbo.Location AS l ON l.Code = SUBSTRING(CONVERT(varchar(50), dbo.Trial_Balance_Variances.ACCOUNT_COMBINATION), 4, 2)
where Class='Administration'  and Category not in ('Sales Rebates','Royalties','Potash Sales (Gross)','Interest Expense LTL','Interest Expense LTMed','Interest Expense Others'
,'Interest Income','(Gain)/Loss from Investment Jor','(Gain)/Loss from Investment N+B','Gain from Investment', 'EXC Gain from Investment' ,'D Gain from Investment ', 'Loss from Investment Jormag','Bank Charges','Change In Inventory','Freight','Dispatch','Other Revenues') 
union 

SELECT distinct l.Class,  SUM ( CAST(ROUND(ABS(dbo.Trial_Balance_Variances.PTD_ACTUAL), 0) AS float)) over (partition by  CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) ) Total
,  CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) AS DateKey from Trial_Balance_Variances 
inner join 
dbo.[P&L] ON dbo.Trial_Balance_Variances.GL_ACCOUNT_NUMBER = dbo.[P&L].Code LEFT OUTER JOIN
dbo.Location AS l ON l.Code = SUBSTRING(CONVERT(varchar(50), dbo.Trial_Balance_Variances.ACCOUNT_COMBINATION), 4, 2)
where Class='Selling And Distribution Expenses'  and Category not in ('Sales Rebates','Royalties','Potash Sales (Gross)','Interest Expense LTL','Interest Expense LTMed','Interest Expense Others'
,'Interest Income','(Gain)/Loss from Investment Jor','(Gain)/Loss from Investment N+B','Gain from Investment', 'EXC Gain from Investment' ,'D Gain from Investment ', 'Loss from Investment Jormag','Bank Charges','Change In Inventory','Freight','Dispatch','Other Revenues')

union 


SELECT distinct l.Class,  SUM ( CAST(ROUND(ABS(dbo.Trial_Balance_Variances.PTD_ACTUAL), 0) AS float)) over (partition by  CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) )
+ (select distinct sum( CAST(ROUND(ABS(v.PTD_ACTUAL), 0) AS float)) over (partition by  CONVERT(int,CONVERT(varchar(10), v.PERIOD_DATE, 112)) ) from Trial_Balance_Variances v
inner join 
   dbo.[P&L] p ON v.GL_ACCOUNT_NUMBER = p.Code  where p.Category in('Interest Expense LTL','Interest Expense LTMed','Interest Expense Others'
,'Interest Income','(Gain)/Loss from Investment Jor','(Gain)/Loss from Investment N+B','Gain from Investment', 'EXC Gain from Investment' ,'D Gain from Investment ', 'Loss from Investment Jormag','Bank Charges','Other Revenues')
    and v.PERIOD_NAME = Trial_Balance_Variances.PERIOD_NAME and SUBSTRING(CONVERT(varchar(50), v.ACCOUNT_COMBINATION), 4, 2) not in ('00','05')
   )
,  CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112)) AS DateKey from Trial_Balance_Variances 
inner join 
dbo.[P&L] ON dbo.Trial_Balance_Variances.GL_ACCOUNT_NUMBER = dbo.[P&L].Code LEFT OUTER JOIN
dbo.Location AS l ON l.Code = SUBSTRING(CONVERT(varchar(50), dbo.Trial_Balance_Variances.ACCOUNT_COMBINATION), 4, 2)
where Class='Others'  and
 Category not in ('Sales Rebates','Royalties','Potash Sales (Gross)','Change In Inventory','Freight','Dispatch')) x order by x.DateKey

Could anyone help me to solve this problem please?

Adding cursors to this will definitely ***NOT*** improve performance - quite the contrary! Avoid cursors if you ever can ..... — marc_s, Oct 19 '15 at 19:35

score 1 · Answer 1 · edited Oct 20 '15 at 07:18

SUBSTRING() is a scalar function, which means they need to run on every row that they get called for. If you can, either omit them or run them after you've reduced your recordset.

I don't believe you need to convert to an int to get this ordering to work the way you want it to in this SQL:

CONVERT(int,CONVERT(varchar(10), dbo.Trial_Balance_Variances.PERIOD_DATE, 112))

remove the convert(int,....) part and see what happens.

Distinct is pretty inefficient at what it does. I'd recommend changing them all to group by. To help with that I'd suggest putting everything into a temp table, then doing your final select so you only need to do it once.

If you'd like any specific stuff, giving us some test data on a Fiddle would help!

omg ! that helped like crazy . the execution time is only 10 seconds now .. thaks alot !! — Noor Mohammed, Oct 19 '15 at 20:39

SQL Query Optimization

1 Answers1