August 23, 2017 at 1:33 pm
Hi Experts,
Can this below sql statement be optimized? There are some 40 lakh odd records in the table.
So checking , Is there a better way to replace/avoid CASE in GROUP BY Clause with an expression and other output columns in the SELECT list which contains CASE Expression. Is there any problem with the below query?
Basically query is taking long time 6-7 mins to return the resultset. That's why checking something can be done to this sql stmt. There are other joins involved but I dont want to get into that aspect and just want to seek help to see if below stmt can be rewritten or optimized in a better way?
SELECT
c1,
c2,
c3,
c4,
c5,
,SUM (CASE
WHEN cpcode IN (
6, 8, 9, 10, 11, 12, 13, 14, 15, 16,
17, 18, 19, 20, 21, 22, 23, 60, 62, 72,
76, 78, 80, 87, 25, 26, 27, 31, 32, 75
)
THEN txn_amt
ELSE 0
END ) AS "Tran_Credit_Amount"
,SUM(CASE
WHEN cpcode NOT IN (
6, 8, 9, 10, 11, 12, 13, 14, 15, 16,
17, 18, 19, 20, 21, 22, 23, 60, 62, 72,
76, 78, 80, 87, 25, 26, 27, 31, 32, 75
)
THEN txn_amt
ELSE 0
END ) AS "Tran_Debit_Amount"
from table_1
group by c1,
c2,
c3,
c4,
c5,
WHEN cpcode IN (
6, 8, 9, 10, 11, 12, 13, 14, 15, 16,
17, 18, 19, 20, 21, 22, 23, 60, 62, 72,
76, 78, 80, 87, 25, 26, 27, 31, 32, 75
)
THEN 'Credit'
ELSE 'Debit'
END
Any help would be appreciated
Thanks you.
August 23, 2017 at 1:54 pm
vsamantha35 - Wednesday, August 23, 2017 1:33 PMHi Experts,Can this below sql statement be optimized? There are some 40 lakh odd records in the table.
So checking , Is there a better way to replace/avoid CASE in GROUP BY Clause with an expression and other output columns in the SELECT list which contains CASE Expression. Is there any problem with the below query?
Basically query is taking long time 6-7 mins to return the resultset. That's why checking something can be done to this sql stmt. There are other joins involved but I dont want to get into that aspect and just want to seek help to see if below stmt can be rewritten or optimized in a better way?SELECT
c1,
c2,
c3,
c4,
c5,
,SUM (CASE
WHEN cpcode IN (
6, 8, 9, 10, 11, 12, 13, 14, 15, 16,
17, 18, 19, 20, 21, 22, 23, 60, 62, 72,
76, 78, 80, 87, 25, 26, 27, 31, 32, 75
)
THEN txn_amt
ELSE 0
END ) AS "Tran_Credit_Amount"
,SUM(CASE
WHEN cpcode NOT IN (
6, 8, 9, 10, 11, 12, 13, 14, 15, 16,
17, 18, 19, 20, 21, 22, 23, 60, 62, 72,
76, 78, 80, 87, 25, 26, 27, 31, 32, 75
)
THEN txn_amt
ELSE 0
END ) AS "Tran_Debit_Amount"from table_1
group by c1,
c2,
c3,
c4,
c5,
WHEN cpcode IN (
6, 8, 9, 10, 11, 12, 13, 14, 15, 16,
17, 18, 19, 20, 21, 22, 23, 60, 62, 72,
76, 78, 80, 87, 25, 26, 27, 31, 32, 75
)
THEN 'Credit'
ELSE 'Debit'
ENDAny help would be appreciated
Thanks you.
Please post an actual execution plan for your query.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 23, 2017 at 2:17 pm
Personally, I would create an exceptions table to be joined to your main query. It would make your code cleaner. Try something like this and left join to it:
Declare @t table (cpcode int not null primary key, iscredit bit not null);
August 23, 2017 at 2:18 pm
Attached the actual plan from sql sentry plan explorer.
August 24, 2017 at 6:38 am
The first thing I notice is that there seems to be a big problem with statistics here... there are nodes that say estimated rows in single digits but actual rows in millions such as scans on PKCAPP1 and Name_To_All_Acct. This query also seems to be very complex, with many joins and subqueries. It may be worth considering building some sort of datamart or data warehouse, or at the very least break this down into some smaller pieces to build some of the data before trying to produce this complex result.
For your original question, the best way to avoid the big CASE statements is to join table_1 to some kind of code table on cpcode, then in your code table you can have a column that categorizes each cpcode as a Debit or Credit.
As I hover over the SELECT node (in the far upper left) I see that Degree of Parallelism is 16, which seems a bit high, Microsoft recommends usually no more than 8:
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi
It also says ARITHABORT = False. Microsoft recommends that this always be True:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql
You can configure an instance to default all connections to ARITHABORT ON using the server properties, connections page, arithmetic abort setting:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-properties-connections-page
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply