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_
– MK_, Commented Mar 10, 2017 at 8: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 may help you. — Venu
– Venu, Commented Mar 10, 2017 at 8:48
@Amit Mishra, Please add sample data also. Please refer to spaghettidba.com/2015/04/24/… — Venu
– Venu, Commented Mar 10, 2017 at 9:13
@Venu, Its large amount of data. If executing with 10 or 50 records it's working fine. — Amit Mishra
– Amit Mishra, Commented Mar 10, 2017 at 11:41

James Z · Accepted Answer · 2017-03-10 10:56:03Z

1

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.

answered Mar 10, 2017 at 10:56

James Z

12.3k10 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amit Mishra Over a year ago

Thnaks @James !

Collectives™ on Stack Overflow

SQL select command consume 100% CPU while executing

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related