I have a function that is performing very slow.I am working a database that I need to migrate data and I have NO control over!. Ideally I would like to use a view directly since this function is called by a view ,but I could only seem to be able to do it by calling a function.
=== A view should return whatever is in the dummytable by orderNo.If an orderNo has a paymenttype of "Interest" than the balance should be interest,if "tax" should be tax
In real life I will have 200000 rows and more,by using cross apply it seems to slow down quite a lot.
Is there a better way to get the data rather than using CrossApply?
Noddy Sample here(data and datatypes are just fictious for semplicity of the example)
CREATE DATABASE DummyDB
GO
use DummyDB
IF object_id(N'DummyTable', 'U') IS NOT NULL
DROP TABLE DummyTable
GO
CREATE TABLE DummyTable
(
Id int,
OrderNo varchar(255),
PaymentType varchar(255),
Credit varchar(255),
Debit varchar(255),
Balance varchar(255)
)
GO
INSERT INTO [dbo].[DummyTable]([Id], [OrderNo], [PaymentType], [Credit], [Debit], [Balance])
SELECT 1, N'200', N'Interest', N'10', N'5', N'5' UNION ALL
SELECT 2, N'201', N'Deposit', N'400', N'30', N'370' UNION ALL
SELECT 3, N'202', N'Tax', N'20', N'10', N'10' UNION ALL
SELECT 4, N'202', N'Tax', N'50', N'10', N'10'
--my sample attempt not performing
use DummyDB
select * from DummyTable
Declare @OrderNo int
set @OrderNo=202
SELECT
Tax=tx.Tax,
Interest=tx1.Interest,
Deposit=tx2.Deposit
FROM DummyTable T1
CROSS APPLY(SELECT
Tax=sum(cast(T2.Balance as money))
FROM DummyTable T2
WHERE T2.OrderNo=@OrderNo
AND PaymentType='Tax')as tx
CROSS APPLY(select
Interest=sum(cast(T2.Balance as money))
FROM DummyTable T2
WHERE T2.OrderNo=@OrderNo
AND PaymentType='Interest')as tx1
CROSS APPLY(select
Deposit=sum(cast(T2.Balance as money))
FROM DummyTable T2
WHERE T2.OrderNo=@OrderNo
AND PaymentType='Deposit')as tx2
WHERE T1.OrderNo=@OrderNo
Any Suggestion of using something more efficient than cross apply?
Many thanks
This will do almost the same as your sample query. It will give you one row with the result where your query will repeat the values for all rows that match @OrderNo.
select sum(case when T1.PaymentType = 'Tax' then cast(T1.Balance as money) else 0 end) as Tax,
sum(case when T1.PaymentType = 'Interest' then cast(T1.Balance as money) else 0 end) as Interest,
sum(case when T1.PaymentType = 'Deposit' then cast(T1.Balance as money) else 0 end) as Deposit
from DummyTable as T1
where T1.OrderNo = @OrderNo
BTW, you should make sure that the data type for OrderNo in the table is the same as the variable @OrderNo. It looks like you are dealing with integers so you should change the table. If that is not possible for you then you need to change @OrderNo to varchar(255) if you want to use an index on OrderNo.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments