4

I have a table that contain transactions like this:

table view

I get the required Data by using scalar function, but it is executing very long time to get the query results. Can any one help me how to get the required data faster?

I read it can be done using a table function, but I didn't know how to or how to use a join with the same table.

For context, TransactionType 1 = debit and TransactionType 2 = credit.

Here is the function I created:

ALTER FUNCTION [dbo].[CardBalanceByDate_FN] 
(
    @studentID int ,
    @CurrancyID int,
    @BalanceDate Date
)
RETURNS decimal(18, 2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result decimal(18, 2)

    -- Add the T-SQL statements to compute the return value here

    select @Result =  ISNULL((((dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,1,@BalanceDate) + (dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,3,@BalanceDate))) )-(dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,2,@BalanceDate))) ,0)


    --******************************************************************************************


    -- Return the result of the function
    RETURN ISNULL(@Result,0)

END

and

ALTER FUNCTION [dbo].[PeriodTotalTransactions] 
(
    -- Add the parameters for the function here
    @studentID int,
    @CurrancyID int,
    @TransactionTypeID int,
    @StarteDate Date,
    @EndDate Date
)
RETURNS decimal(18, 2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result decimal(18, 2)

    -- Add the T-SQL statements to compute the return value here

    select @Result =  ISNULL(SUM(TransactionAmount) ,0)
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=@studentID
    and ct.IsDeleted=0   
    and CONVERT(date, ct.TransactionDate) between @StarteDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=@CurrancyID
    and ct.TransactionTypeID=@TransactionTypeID

--******************************************************************************************


    -- Return the result of the function
    RETURN ISNULL(@Result,0)

END

and the select query is

SELECT sp.studentcode, 
       sp.studentnameen, 
       sp.flag, 
       dbo.Cardbalancebydate_fn(sp.serial, 1, '2017-12-15') 
       StartBalance, 
       dbo.Periodtotaltransactions(sp.serial, 1, 2, '2017-12-15', '2018-01-15') 
       DebitTransaction, 
( 
dbo.Periodtotaltransactions(sp.serial, 1, 1, '2017-12-15', '2018-01-15') 
+ dbo.Periodtotaltransactions(sp.serial, 1, 3, '2017-12-15', '2018-01-15') ) CreditTransaction, 
dbo.Cardbalancebydate_fn(sp.serial, 1, '2018-01-15') 
EndBalance 
FROM   student_profile sp, 
student_cards SC 
WHERE  sp.serial = sc.studentid 
AND sp.isdeleted = 0 
AND sc.isdeleted = 0 

the transaction Table is

CREATE TABLE [dbo].[Student_Card_Transaction](
    [Serial] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NOT NULL,
    [CardID] [int] NOT NULL,
    [TransactionTypeID] [int] NOT NULL,
    [CurrencyID] [int] NOT NULL,
    [TransactionAmount] [decimal](18, 2) NOT NULL,
    [TransactionDate] [datetime] NULL,
    [UserID] [int] NULL,
    [Notes] [nvarchar](2000) NULL,
    [IsDeleted] [bit] NOT NULL CONSTRAINT 
)    

and the table involved

CREATE TABLE [dbo].[Student_Profile](
    [Serial] [int] IDENTITY(1,1) NOT NULL,
    [StudentCode] [nvarchar](20) NOT NULL,
    [StudentNameAR] [nvarchar](500) NOT NULL,
    [StudentNameEN] [nvarchar](500) NOT NULL,
    [StudentImagePath] [nvarchar](2000) NULL,
    [StudentEmail] [nvarchar](1000) NULL,
    [Flag] [int] NOT NULL,
    [IsDeleted] 
)
  • 3
    I doubt anyone could help you on "how to get the required data much faster", if you don't show them the query you're currently using. Also consider providing DDL for that table (possibly including indexes), sample data and maybe a description of the meaning of the columns. – sticky bit May 1 '18 at 12:38
  • 1
    @doaa - take a look at how to create an MCVE - a minimally complete verifiable example. Add the appropriate CREATE TABLE statements, sample data, and desired result to your question with the edit link below your question. – Max Vernon May 1 '18 at 13:07
  • 2
    Thanks for adding those details to your question! Can you script the tables involved and add the CREATE TABLE statements to your question? – Max Vernon May 1 '18 at 14:35
  • 1
    @doaa we reopened the question but please follow MaxVernon's advice and add the details requested. – ypercubeᵀᴹ May 1 '18 at 17:55
  • 1
    why question is upvoted so many times when nobody is satisfied and question is incomplete.Main problem appear to be using 3 UDF.write UDF logic in cross apply or sub query.Beside few condition are not SARgable. – KumarHarsh May 2 '18 at 8:19
0

I solved the problem using CROSS APPLY. The performance is so much better the code is below:

select sp.StudentCode,sp.StudentNameEN,sp.Flag,DST.DTAmount-CST.CTAmount StartBalance,TDTrans.DTran  DreditTransactions,TCTrans.CTran Credittransactions,DET.DTAmount-CET.CTAmount EndBalance,@StartDate StartDate,@EndDate EndDate
from Student_Profile sp  cross apply 
(
    select DTAmount = ISNULL(SUM(TransactionAmount) ,0)    --Charge & Refund
    FROM  dbo.Student_Card_Transaction CT 
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  @StartDate
    and ct.CurrencyID=1
    and ct.TransactionTypeID!=2 
    and ct.StudentID=sp.Serial
)as DST cross apply 
(
    select CTAmount = ISNULL(SUM(TransactionAmount) ,0)     --orders
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  @StartDate
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2 
    and ct.StudentID=sp.Serial
)as CST cross apply 
(
    select DTAmount = ISNULL(SUM(TransactionAmount) ,0)     
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID !=2 
    and ct.StudentID=sp.Serial
)as DET cross apply 
(
    select CTAmount = ISNULL(SUM(TransactionAmount) ,0)
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) <  DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2 
    and ct.StudentID=sp.Serial
)as CET  cross apply 
(
select DTran =  ISNULL(SUM(TransactionAmount) ,0)    --charge & Refund
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=sp.Serial
    and ct.IsDeleted=0   
    and CONVERT(date, ct.TransactionDate) between @StartDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID!=2
)as TDTrans cross apply 
(
select CTran =  ISNULL(SUM(TransactionAmount) ,0)     --order
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=sp.Serial
    and ct.IsDeleted=0   
    and CONVERT(date, ct.TransactionDate) between @StartDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2
)as TCTrans 
inner join  Student_Cards SC on sp.Serial=sc.StudentID
where sp.IsDeleted=0
and sc.IsDeleted=0
and sc.IsActive=1
and (sp.Flag=1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.