Q: SQL select command consume 100% CPU while executing 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
)


A: 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.
