May 7, 2015 at 11:38 am
I like to DISTINCT my column facc_bkey but that take a lot of time. This is very, very slow 🙁
Select DISTINCT CAST(a.DATAAREAID AS NVARCHAR(4)) + '_' + CAST(a.ACCOUNTNUM AS NVARCHAR(20)) AS FACC_BKEY
,a.ACCOUNTNUM AS FACC_CODE
,a.ACCOUNTNAME AS FACC_DESC
,CAST(a.ACCOUNTNUM as NVARCHAR(20))+ ' - ' + CAST(a.ACCOUNTNAME as NVARCHAR(60)) AS [FACC_CODE_DESC]
,CAST(a.ACCOUNTNAME as NVARCHAR(60))+ ' (' + CAST(a.ACCOUNTNUM as NVARCHAR(20)) + ')' AS [FACC_DESC_CODE]
,ISNULL(f.ACCOUNTNUM, a.ACCOUNTNUM) AS FACC_H1_L5_CODE
,ISNULL(f.ACCOUNTNAME, a.ACCOUNTNAME) AS FACC_H1_L5_DESC
,CAST(ISNULL(f.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(f.ACCOUNTNAME,A.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L5_CODE_DESC]
,CAST(ISNULL(f.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(f.ACCOUNTNUM,A.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L5_DESC_CODE]
,ISNULL(e.ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L4_CODE
,ISNULL(e.ACCOUNTNAME,a.ACCOUNTNAME) AS FACC_H1_L4_DESC
,CAST(ISNULL(e.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(e.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L4_CODE_DESC]
,CAST(ISNULL(e.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(e.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L4_DESC_CODE]
,ISNULL(d.ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L3_CODE
,ISNULL(d.ACCOUNTNAME,a.ACCOUNTNAME) AS FACC_H1_L3_DESC
,CAST(ISNULL(d.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(d.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L3_CODE_DESC]
,CAST(ISNULL(d.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(d.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L3_DESC_CODE]
,ISNULL(c.ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L2_CODE
,ISNULL(c.ACCOUNTNAME,a.ACCOUNTNAME) AS FACC_H1_L2_DESC
,CAST(ISNULL(c.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(c.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L2_CODE_DESC]
,CAST(ISNULL(c.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(c.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L2_DESC_CODE]
,ISNULL(b.ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L1_CODE
,ISNULL(b.ACCOUNTNAME,a.ACCOUNTNUM) AS FACC_H1_L1_DESC
,CAST(ISNULL(b.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(b.ACCOUNTNAME,a.ACCOUNTNUM) as NVARCHAR(60)) AS [FACC_H1_L1_CODE_DESC]
,CAST(ISNULL(b.ACCOUNTNAME,a.ACCOUNTNUM) as NVARCHAR(60))+ ' (' + CAST(ISNULL(b.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L1_DESC_CODE]
,a.ACCOUNTPLTYPE AS FACC_TYPE_CODE
,CAST(CASE a.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
END as NVARCHAR(25))AS FACC_TYPE_DESC
,CAST(a.ACCOUNTPLTYPE as NVARCHAR(20))+ ' - ' +
CAST(CASE a.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
END as NVARCHAR(25))AS FACC_TYPE_CODE_DESC
,CAST(CASE a.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
END as NVARCHAR(25)) + ' (' + CAST(a.ACCOUNTPLTYPE as NVARCHAR(20)) + ')' AS FACC_TYPE_DESC_CODE
,g.ACCOUNTCATEGORY AS FACC_CATEGORY_CODE
,g.[DESCRIPTION] as FACC_CATEGORY_DESC
,CAST(g.ACCOUNTCATEGORY as NVARCHAR(20))+ ' - ' + CAST(g.[DESCRIPTION] as NVARCHAR(60)) AS FACC_CATEGORY_CODE_DESC
,CAST(g.[DESCRIPTION] as NVARCHAR(60))+ ' (' + CAST(g.ACCOUNTCATEGORY as NVARCHAR(20)) + ')' AS FACC_CATEGORY_DESC_CODE
,a.DATAAREAID AS FACC_ENTITIY_CODE
,h.NAME AS FACC_ENTITY_DESC
,CAST(a.DATAAREAID as NVARCHAR(4))+ ' - ' + CAST(h.[NAME] as NVARCHAR(40)) AS FACC_ENTITIY_CODE_DESC
,CAST(h.[NAME] as NVARCHAR(40))+ ' (' + CAST(a.DATAAREAID as NVARCHAR(4)) + ')' AS FACC_ENTITIY_DESC_CODE
FROM
HST_AX.LEDGERACCOUNTCATEGORY g
INNER JOIN HST_AX.LEDGERTABLE a
ON a.DATAAREAID=g.DATAAREAID
INNER JOIN HST_AX.DATAAREA h
ON g.DATAAREAID=h.ID
LEFT OUTER JOIN HST_AX.LEDGERTABLE b
on SUBSTRING(a.ACCOUNTNUM,1,1)= b.ACCOUNTNUM
LEFT OUTER JOIN HST_AX.LEDGERTABLE c
ON SUBSTRING(a.ACCOUNTNUM,1,2)= c.ACCOUNTNUM
LEFT OUTER JOIN HST_AX.LEDGERTABLE d
on SUBSTRING(a.ACCOUNTNUM,1,3) = d.ACCOUNTNUM
LEFT OUTER JOIN HST_AX.LEDGERTABLE e
on SUBSTRING(a.ACCOUNTNUM,1,4) = e.ACCOUNTNUM
LEFT OUTER JOIN HST_AX.LEDGERTABLE f
on SUBSTRING(a.ACCOUNTNUM,1,5)=f.ACCOUNTNUM
Is there a other syntax to fix my qeury?
May 7, 2015 at 12:19 pm
GG_BI_GG (5/7/2015)
I like to DISTINCT my column facc_bkey but that take a lot of time. This is very, very slow 🙁
Select DISTINCT CAST(a.DATAAREAID AS NVARCHAR(4)) + '_' + CAST(a.ACCOUNTNUM AS NVARCHAR(20)) AS FACC_BKEY,a.ACCOUNTNUM AS FACC_CODE
,a.ACCOUNTNAME AS FACC_DESC
,CAST(a.ACCOUNTNUM as NVARCHAR(20))+ ' - ' + CAST(a.ACCOUNTNAME as NVARCHAR(60)) AS [FACC_CODE_DESC]
,CAST(a.ACCOUNTNAME as NVARCHAR(60))+ ' (' + CAST(a.ACCOUNTNUM as NVARCHAR(20)) + ')' AS [FACC_DESC_CODE]
,ISNULL(f.ACCOUNTNUM, a.ACCOUNTNUM) AS FACC_H1_L5_CODE
,ISNULL(f.ACCOUNTNAME, a.ACCOUNTNAME) AS FACC_H1_L5_DESC
,CAST(ISNULL(f.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(f.ACCOUNTNAME,A.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L5_CODE_DESC]
,CAST(ISNULL(f.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(f.ACCOUNTNUM,A.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L5_DESC_CODE]
,ISNULL(e.ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L4_CODE
,ISNULL(e.ACCOUNTNAME,a.ACCOUNTNAME) AS FACC_H1_L4_DESC
,CAST(ISNULL(e.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(e.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L4_CODE_DESC]
,CAST(ISNULL(e.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(e.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L4_DESC_CODE]
,ISNULL(d.ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L3_CODE
,ISNULL(d.ACCOUNTNAME,a.ACCOUNTNAME) AS FACC_H1_L3_DESC
,CAST(ISNULL(d.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(d.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L3_CODE_DESC]
,CAST(ISNULL(d.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(d.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L3_DESC_CODE]
,ISNULL(c.ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L2_CODE
,ISNULL(c.ACCOUNTNAME,a.ACCOUNTNAME) AS FACC_H1_L2_DESC
,CAST(ISNULL(c.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(c.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L2_CODE_DESC]
,CAST(ISNULL(c.ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(c.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L2_DESC_CODE]
,ISNULL(b.ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L1_CODE
,ISNULL(b.ACCOUNTNAME,a.ACCOUNTNUM) AS FACC_H1_L1_DESC
,CAST(ISNULL(b.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(b.ACCOUNTNAME,a.ACCOUNTNUM) as NVARCHAR(60)) AS [FACC_H1_L1_CODE_DESC]
,CAST(ISNULL(b.ACCOUNTNAME,a.ACCOUNTNUM) as NVARCHAR(60))+ ' (' + CAST(ISNULL(b.ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L1_DESC_CODE]
,a.ACCOUNTPLTYPE AS FACC_TYPE_CODE
,CAST(CASE a.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
END as NVARCHAR(25))AS FACC_TYPE_DESC
,CAST(a.ACCOUNTPLTYPE as NVARCHAR(20))+ ' - ' +
CAST(CASE a.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
END as NVARCHAR(25))AS FACC_TYPE_CODE_DESC
,CAST(CASE a.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
END as NVARCHAR(25)) + ' (' + CAST(a.ACCOUNTPLTYPE as NVARCHAR(20)) + ')' AS FACC_TYPE_DESC_CODE
,g.ACCOUNTCATEGORY AS FACC_CATEGORY_CODE
,g.[DESCRIPTION] as FACC_CATEGORY_DESC
,CAST(g.ACCOUNTCATEGORY as NVARCHAR(20))+ ' - ' + CAST(g.[DESCRIPTION] as NVARCHAR(60)) AS FACC_CATEGORY_CODE_DESC
,CAST(g.[DESCRIPTION] as NVARCHAR(60))+ ' (' + CAST(g.ACCOUNTCATEGORY as NVARCHAR(20)) + ')' AS FACC_CATEGORY_DESC_CODE
,a.DATAAREAID AS FACC_ENTITIY_CODE
,h.NAME AS FACC_ENTITY_DESC
,CAST(a.DATAAREAID as NVARCHAR(4))+ ' - ' + CAST(h.[NAME] as NVARCHAR(40)) AS FACC_ENTITIY_CODE_DESC
,CAST(h.[NAME] as NVARCHAR(40))+ ' (' + CAST(a.DATAAREAID as NVARCHAR(4)) + ')' AS FACC_ENTITIY_DESC_CODE
FROM
HST_AX.LEDGERACCOUNTCATEGORY g
INNER JOIN HST_AX.LEDGERTABLE a
ON a.DATAAREAID=g.DATAAREAID
INNER JOIN HST_AX.DATAAREA h
ON g.DATAAREAID=h.ID
LEFT OUTER JOIN HST_AX.LEDGERTABLE b
on SUBSTRING(a.ACCOUNTNUM,1,1)= b.ACCOUNTNUM
LEFT OUTER JOIN HST_AX.LEDGERTABLE c
ON SUBSTRING(a.ACCOUNTNUM,1,2)= c.ACCOUNTNUM
LEFT OUTER JOIN HST_AX.LEDGERTABLE d
on SUBSTRING(a.ACCOUNTNUM,1,3) = d.ACCOUNTNUM
LEFT OUTER JOIN HST_AX.LEDGERTABLE e
on SUBSTRING(a.ACCOUNTNUM,1,4) = e.ACCOUNTNUM
LEFT OUTER JOIN HST_AX.LEDGERTABLE f
on SUBSTRING(a.ACCOUNTNUM,1,5)=f.ACCOUNTNUM
Is there a other syntax to fix my qeury?
This is slow because of all those functions in your joins. It has to calculate the value for every single row for every single one of your joins to the same table. It looks like the biggest problem here is normalization. You have a bunch of values shoved into a single column (ACCOUNTNUM) which forces you to constantly split it apart. I doubt there is much of anything you can do to improve this if you can't fix the underlying cause (the table structures).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2015 at 12:27 pm
In your JOIN statements, try using the LEFT function instead of SUBSTRING to see if it will run any faster.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 7, 2015 at 12:33 pm
Alvin Ramard (5/7/2015)
Try using the LEFT function instead of SUBSTRING to see if it will run any faster.
The problem is for the first join they want the first character. For the second join they want the second character etc...:w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2015 at 12:40 pm
Sean Lange (5/7/2015)
Alvin Ramard (5/7/2015)
Try using the LEFT function instead of SUBSTRING to see if it will run any faster.The problem is for the first join they want the first character. For the second join they want the second character etc...:w00t:
Actually, for the first join they want the first character, the second the first two characters, the third the first three characters, etc.
May 7, 2015 at 12:44 pm
Lynn Pettis (5/7/2015)
Sean Lange (5/7/2015)
Alvin Ramard (5/7/2015)
Try using the LEFT function instead of SUBSTRING to see if it will run any faster.The problem is for the first join they want the first character. For the second join they want the second character etc...:w00t:
Actually, for the first join they want the first character, the second the first two characters, the third the first three characters, etc.
Ahh yes...my eyes started crossing at some point. Thanks for the correction. I think Alvin's recommendation will likely help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2015 at 12:49 pm
Sean Lange (5/7/2015)
Lynn Pettis (5/7/2015)
Sean Lange (5/7/2015)
Alvin Ramard (5/7/2015)
Try using the LEFT function instead of SUBSTRING to see if it will run any faster.The problem is for the first join they want the first character. For the second join they want the second character etc...:w00t:
Actually, for the first join they want the first character, the second the first two characters, the third the first three characters, etc.
Ahh yes...my eyes started crossing at some point. Thanks for the correction. I think Alvin's recommendation will likely help.
I wouldn't put money on it, unfortunately.
May 7, 2015 at 12:53 pm
Lynn Pettis (5/7/2015)
Sean Lange (5/7/2015)
Lynn Pettis (5/7/2015)
Sean Lange (5/7/2015)
Alvin Ramard (5/7/2015)
Try using the LEFT function instead of SUBSTRING to see if it will run any faster.The problem is for the first join they want the first character. For the second join they want the second character etc...:w00t:
Actually, for the first join they want the first character, the second the first two characters, the third the first three characters, etc.
Ahh yes...my eyes started crossing at some point. Thanks for the correction. I think Alvin's recommendation will likely help.
I wouldn't put money on it, unfortunately.
It certainly isn't going to make it any worse and might help, albeit slightly. Of course the best improvement would be to modify the structures so it isn't required to do such a strange set of joins. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2015 at 12:58 pm
Tnx for the quick responds.
Ye it still dont work.. I think Alvin's recommendation makes my qeury not quicker..
Is there a better way to make my query syntax, on which way.
May 7, 2015 at 1:01 pm
GG_BI_GG (5/7/2015)
Tnx for the quick responds.Ye it still dont work.. I think Alvin's recommendation makes my qeury not quicker..
Is there a better way to make my query syntax, on which way.
There is no amount of syntax that will "fix" this. The problem is your table structures. See the comments already made about this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2015 at 1:05 pm
Functions against columns lead to scans. Period. End of story. You can't have the functions there AND get good performance. No matter what.
Also, DISTINCT is an aggregation function suggesting that you have duplicate data further suggesting there's something up with your data design besides needing functions on columns.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2015 at 1:24 pm
Ok. I will look..
But is there any option for a subqeury (COALESCE), CROSS APPLY...
May 7, 2015 at 1:38 pm
GG_BI_GG (5/7/2015)
Ok. I will look..But is there any option for a subqeury (COALESCE), CROSS APPLY...
COALESCE is just another function and is not at all relevant here. Not sure how a CROSS APPLY would help either and that isn't any different than a subquery. You really don't have much option here because of the way you have to retrieve portions of a column over and over.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2015 at 4:25 pm
Sean Lange (5/7/2015)
GG_BI_GG (5/7/2015)
Ok. I will look..But is there any option for a subqeury (COALESCE), CROSS APPLY...
COALESCE is just another function and is not at all relevant here. Not sure how a CROSS APPLY would help either and that isn't any different than a subquery. You really don't have much option here because of the way you have to retrieve portions of a column over and over.
Agreed. Rearchitecting this database design is the correct answer. What you have is unworkable. There are no tips or tricks or special settings that will get around it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2015 at 5:02 pm
The table design is probably less than ideal/perfect.
However, the query can still be improved, hopefully dramatically. Keep in mind that I don't have sample data to test it ahead of time.
For best speed, you'll need one of these index(es) on HST_AX.LEDGERTABLE:
(1) the table clustered on ACCOUNTNUM
(2) has an index keyed on ( ACCOUNTNUM ) that includes ACCOUNTNAME
(3) [less efficient but still useful]: has an index that contains both ACCOUNTNUM and ACCCOUTNAME.
SELECT CAST(a.DATAAREAID AS NVARCHAR(4)) + '_' + CAST(a.ACCOUNTNUM AS NVARCHAR(20)) AS FACC_BKEY
,a.ACCOUNTNUM AS FACC_CODE
,a.ACCOUNTNAME AS FACC_DESC
,CAST(a.ACCOUNTNUM as NVARCHAR(20))+ ' - ' + CAST(a.ACCOUNTNAME as NVARCHAR(60)) AS [FACC_CODE_DESC]
,CAST(a.ACCOUNTNAME as NVARCHAR(60))+ ' (' + CAST(a.ACCOUNTNUM as NVARCHAR(20)) + ')' AS [FACC_DESC_CODE]
,ISNULL(b5_ACCOUNTNUM, a.ACCOUNTNUM) AS FACC_H1_L5_CODE
,ISNULL(b5_ACCOUNTNAME, a.ACCOUNTNAME) AS FACC_H1_L5_DESC
,CAST(ISNULL(b5_ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(b5_ACCOUNTNAME,A.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L5_CODE_DESC]
,CAST(ISNULL(b5_ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(b5_ACCOUNTNUM,A.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L5_DESC_CODE]
,ISNULL(b4_ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L4_CODE
,ISNULL(b4_ACCOUNTNAME,a.ACCOUNTNAME) AS FACC_H1_L4_DESC
,CAST(ISNULL(b4_ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(b4_ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L4_CODE_DESC]
,CAST(ISNULL(b4_ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(b4_ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L4_DESC_CODE]
,ISNULL(b3_ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L3_CODE
,ISNULL(b3_ACCOUNTNAME,a.ACCOUNTNAME) AS FACC_H1_L3_DESC
,CAST(ISNULL(b3_ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(b3_ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L3_CODE_DESC]
,CAST(ISNULL(b3_ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(b3_ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L3_DESC_CODE]
,ISNULL(b2_ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L2_CODE
,ISNULL(b2_ACCOUNTNAME,a.ACCOUNTNAME) AS FACC_H1_L2_DESC
,CAST(ISNULL(b2_ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(b2_ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60)) AS [FACC_H1_L2_CODE_DESC]
,CAST(ISNULL(b2_ACCOUNTNAME,a.ACCOUNTNAME) as NVARCHAR(60))+ ' (' + CAST(ISNULL(b2_ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L2_DESC_CODE]
,ISNULL(b1_ACCOUNTNUM,a.ACCOUNTNUM) AS FACC_H1_L1_CODE
,ISNULL(b1_ACCOUNTNAME,a.ACCOUNTNUM) AS FACC_H1_L1_DESC
,CAST(ISNULL(b1_ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20))+ ' - ' + CAST(ISNULL(b1_ACCOUNTNAME,a.ACCOUNTNUM) as NVARCHAR(60)) AS [FACC_H1_L1_CODE_DESC]
,CAST(ISNULL(b1_ACCOUNTNAME,a.ACCOUNTNUM) as NVARCHAR(60))+ ' (' + CAST(ISNULL(b1_ACCOUNTNUM,a.ACCOUNTNUM) as NVARCHAR(20)) + ')' AS [FACC_H1_L1_DESC_CODE]
,a.ACCOUNTPLTYPE AS FACC_TYPE_CODE
,CAST(CASE a.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
END as NVARCHAR(25))AS FACC_TYPE_DESC
,CAST(a.ACCOUNTPLTYPE as NVARCHAR(20))+ ' - ' +
CAST(CASE a.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
END as NVARCHAR(25))AS FACC_TYPE_CODE_DESC
,CAST(CASE a.ACCOUNTPLTYPE
WHEN 0 THEN 'Income Statement'
WHEN 3 THEN 'Balance Sheet'
END as NVARCHAR(25)) + ' (' + CAST(a.ACCOUNTPLTYPE as NVARCHAR(20)) + ')' AS FACC_TYPE_DESC_CODE
,g.ACCOUNTCATEGORY AS FACC_CATEGORY_CODE
,g.[DESCRIPTION] as FACC_CATEGORY_DESC
,CAST(g.ACCOUNTCATEGORY as NVARCHAR(20))+ ' - ' + CAST(g.[DESCRIPTION] as NVARCHAR(60)) AS FACC_CATEGORY_CODE_DESC
,CAST(g.[DESCRIPTION] as NVARCHAR(60))+ ' (' + CAST(g.ACCOUNTCATEGORY as NVARCHAR(20)) + ')' AS FACC_CATEGORY_DESC_CODE
,a.DATAAREAID AS FACC_ENTITIY_CODE
,h.NAME AS FACC_ENTITY_DESC
,CAST(a.DATAAREAID as NVARCHAR(4))+ ' - ' + CAST(h.[NAME] as NVARCHAR(40)) AS FACC_ENTITIY_CODE_DESC
,CAST(h.[NAME] as NVARCHAR(40))+ ' (' + CAST(a.DATAAREAID as NVARCHAR(4)) + ')' AS FACC_ENTITIY_DESC_CODE
FROM
HST_AX.LEDGERACCOUNTCATEGORY g
INNER JOIN HST_AX.LEDGERTABLE a
ON a.DATAAREAID=g.DATAAREAID
INNER JOIN HST_AX.DATAAREA h
ON g.DATAAREAID=h.ID
OUTER APPLY (
SELECT
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 1 THEN b1_ACCOUNTNUM END) AS b1_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 1 THEN b1_ACCOUNTNAME END) AS b1_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 2 THEN b1_ACCOUNTNUM END) AS b2_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 2 THEN b1_ACCOUNTNAME END) AS b2_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 3 THEN b1_ACCOUNTNUM END) AS b3_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 3 THEN b1_ACCOUNTNAME END) AS b3_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 4 THEN b1_ACCOUNTNUM END) AS b4_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 4 THEN b1_ACCOUNTNAME END) AS b4_ACCOUNTNAME,
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 5 THEN b1_ACCOUNTNUM END) AS b5_ACCOUNTNUM,
MAX(CASE WHEN LEN(b.ACCOUTNUM) = 5 THEN b1_ACCOUNTNAME END) AS b6_ACCOUNTNAME
FROM HST_AX.LEDGERTABLE b
WHERE
b1_ACCOUNTNUM IN ( LEFT(a.ACCOUNTNUM, 1), LEFT(a.ACCOUNTNUM, 2), LEFT(a.ACCOUNTNUM, 3),
LEFT(a.ACCOUNTNUM, 4), LEFT(a.ACCOUNTNUM, 5) )
) AS outer_apply_1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply