I have a query using outer apply on the same temporary table again(written by someone long back). It is performing very slow.Please suggest ways to improve. Thanks in advance.

            SET eval_value = cv.contract_value
            FROM #formula_breakdown fb
                    OUTER APPLY(
                    SELECT SUM(cast(eval_value as numeric(28,10))) contract_value FROM #formula_breakdown WHERE fb.func_name = 'ContractValue'  
                        --AND CONVERT(VARCHAR(10),NULLIF(fb.arg1,'NULL'),120) = CONVERT(VARCHAR(10),final_date,120)                     
                        AND CAST(CONVERT(VARCHAR(10),fb.arg1,120)+' '+RIGHT(CAST('00'+CASE WHEN fb.granularity IN(987,989,982,994,995) THEN fb.[Hour]-1 ELSE fb.[Hour] END AS VARCHAR),2)+':'+RIGHT('00'+CAST(CASE WHEN fb.granularity IN(987) THEN fb.[mins]-15 WHEN fb.granularity IN(989) THEN fb.[mins]-30  WHEN fb.granularity IN(994) THEN fb.[mins]-10  WHEN fb.granularity IN(995) THEN fb.[mins]-5 ELSE fb.[mins] END AS VARCHAR),2)+':00.000' AS DATETIME)
                            = CAST(CASE WHEN fb.granularity IN(987,989,982,994,995) THEN final_date WHEN fb.granularity IN(981) THEN CONVERT(VARCHAR(10),final_date,120)+ ' '+'00:00.000' ELSE CONVERT(VARCHAR(7),final_date,120)+'-01' + ' '+'00:00.000' END AS DATETIME)  
                        AND CAST(counterparty_id AS VARCHAR) = (NULLIF(fb.arg2,'NULL'))
                        AND CAST(contract_id AS VARCHAR) = (NULLIF(fb.arg3,'NULL'))
                        AND CAST(invoice_line_item_id AS VARCHAR) = (NULLIF(fb.arg4,'NULL'))
                        AND CAST(nested_id AS VARCHAR) = (NULLIF(fb.arg5,'NULL'))
                        --AND CAST(NULLIF(fb.arg6,'NULL') AS INT) = 0
                        AND arg_no_for_next_func IS NULL
                    ) cv
                formula_level= @i AND isnull(nested_id,0)=@j AND parent_nested_id is NULL --AND eval_value IS null 
                AND invoice_line_item_seq = @invoice_line_item_seq
                AND func_name <> 'UDSql'
                AND fb.func_name = 'ContractValue' 
                AND ISNULL(arg9,1) in(1, 0)

Indexes available on temp table as follows(some are used in other queries):

CREATE INDEX indx_formula_breakdown_1 ON #formula_breakdown(source_id)
CREATE INDEX indx_formula_breakdown_2 ON #formula_breakdown(formula_id)
CREATE INDEX indx_formula_breakdown_5 ON #formula_breakdown(nested_id)
CREATE INDEX indx_formula_breakdown_4 ON #formula_breakdown(formula_level)
CREATE INDEX indx_formula_breakdown_3 ON #formula_breakdown(level_func_sno)
CREATE INDEX indx_formula_breakdown_6 ON #formula_breakdown(parent_nested_id)
CREATE INDEX indx_formula_breakdown_formula_next_arg ON #formula_breakdown([arg_no_for_next_func])
CREATE INDEX indx_formula_breakdown_formula_rowid ON #formula_breakdown([rowid])
CREATE INDEX indx_formula_breakdown_7 ON #formula_breakdown(prod_date,[Hour],[mins])
CREATE INDEX indx_formula_breakdown_8 ON #formula_breakdown(source_deal_detail_id)
CREATE INDEX indx_formula_breakdown_9 ON #formula_breakdown(final_date)
CREATE INDEX indx_formula_breakdown_10 ON #formula_breakdown(granularity)
CREATE INDEX [IX_PT_#formula_breakdown_func_name_11] ON #formula_breakdown ([func_name]) INCLUDE ([rowid], [source_id], [formula_id], [parent_nested_id], [arg2], [arg3], [granularity], [prod_date], [Hour], [source_deal_detail_id], [source_deal_header_id], [mins], [is_dst], [final_date], [final_offset_date])    
CREATE INDEX [IX_PT_#formula_breakdown_nested_id_arg_no_for_next_func_12] ON #formula_breakdown ([nested_id], [arg_no_for_next_func]) INCLUDE ([source_id], [formula_id], [eval_value], [prod_date], [Hour], [source_deal_detail_id], [source_deal_header_id], [mins], [is_dst], [final_date], [counterparty_id], [contract_id], [invoice_line_item_id], [generator_id])    
  • 3
    Posting the ACTUAL execution plan would be extremely helpful. – Scott Hodgin Jun 13 '17 at 12:41

First Advice, most of the condition in where clause is not SARGABLE so no point in creating so many index.may be 1 or 2 index is being use but its not guaranteed.Don't use function in where condition.

Your main problem is query itself.Since it is temp table that means you must be inserting right before update.So you can write the same logic while inserting.

I am sure there is no need of update part.Or don't disturb old column,add few column and store the calculated value.

Either provide script of insert or provide 20 sample data of temp table with data type which include all columns mention above.

For example, CAST(counterparty_id AS VARCHAR) = (NULLIF(fb.arg2,'NULL'))

Here even data type are not equal .In case fb.arg2 is null then it will evaluate ounterparty_id =null,which will never be true.

declare @t table(col varchar(30))
insert into @t values ('1'),(null)
select * from @t where col=null
| improve this answer | |
  • Thanks Harsh.I am in a production system, which has many more similar codes.Once the data grew and system started performing slow,then only company bothered about databases and performance issues.I am in dilemma how to tune such queries whithout understanding business logics. – Madhu Jun 14 '17 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.