SQL select command consume 100% CPU while executing

Question

In my query I'm Selecting row by row closing balance. It's consume 100% CPU while executing is sql server 2014. Here is my query:

;WITH summary(id,reference_id,entry_date,particular,remarks,debit,credit,balance)AS(
SELECT 
id,
reference_id,
entry_date,
particular,
remarks,
debit,
credit,
(credit-debit)+(SELECT ISNULL(SUM(l.credit-l.debit) ,0) FROM member_transaction l WHERE l.entry_date<a.entry_date AND l.member_id=@mId AND is_succeed=1 AND isnull(l.reference_id,0) NOT IN(SELECT user_reference_id FROM recharge_request WHERE status='Failure'))AS balance 
FROM  member_transaction a 
WHERE member_id=@mId AND is_succeed=1 
    AND isnull(reference_id,0) NOT IN(SELECT user_reference_id FROM recharge_request WHERE status='Failure')),
    openingbalance(
    id,
    reference_id,
    entry_date,
    particular,
    remarks,
    debit,
    credit,
    balance
    )AS(SELECT TOP 1 0,'','','OPENING BALANCE','',0,0,balance FROM summary WHERE entry_date<'2017/03/10' ORDER BY entry_date DESC
)SELECT * FROM openingbalance UNION SELECT * FROM summary ORDER BY entry_date DESC

Is there any other way to calculate row by row closing balance on every transaction? Please help me to solve this problem.

HERE is table structure:

CREATE TABLE [member_transaction](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [member_id] [int] NULL,
    [t_type] [varchar](50) NULL,
    [debit] [decimal](12, 2) NOT NULL,
    [credit] [decimal](12, 2) NOT NULL,
    [particular] [varchar](100) NULL,
    [remarks] [varchar](150) NULL,
    [reference_id] [varchar](50) NULL,
    [entry_date] [datetime] NOT NULL,
    [is_succeed] [bit] NOT NULL
)

CREATE TABLE [recharge_request](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [mobile_no] [varchar](50) NULL,
    [amount] [decimal](12, 0) NULL,
    [user_reference_id] [varchar](50) NULL,
    [uid] [int] NULL,
    [rdate] [datetime] NOT NULL,
    [status] [varchar](50) NOT NULL
)

What is your actual question? You need to know SQL Server is greedy on all the resources it can get. — MK_, Mar 10 '17 at 08:26
@AmitMishra, Which version of SQL Server you are using? Please also post the table structure, sample data and expected results. You should be able to join the same table again to match previous record for calculating row wise closing balance. This [post](http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) may help you. — Venu, Mar 10 '17 at 08:48
@Amit Mishra, Please add sample data also. Please refer to https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Venu, Mar 10 '17 at 09:13
@Venu, Its large amount of data. If executing with 10 or 50 records it's working fine. — Amit Mishra, Mar 10 '17 at 11:41
@Amit Mishra, I understand you have huge amount of data which is giving you problems. However we need only few number of records to really work on these tables to see what could go wrong. There are multiple areas which can contribute to performance issues. — Venu, Mar 10 '17 at 11:56

score 1 · Accepted Answer · answered Mar 10 '17 at 10:56

Assuming you're in SQL Server 2012+, you should try something like this:

SELECT 
  id,
  reference_id,
  entry_date,
  particular,
  remarks,
  debit,
  credit,
  sum(isnull(credit,0)-isnull(debit,0)) over (order by entry_date asc) AS balance 
FROM
  member_transaction a 
WHERE 
  member_id=@mId AND 
  is_succeed=1 AND 
  not exist (select 1 FROM recharge_request r WHERE r.user_reference_id = a.reference_id and r.status='Failure')

If you want to fetch more than one member, then you should have partition by in the over part of the sum.

SQL select command consume 100% CPU while executing

1 Answers1