Performance Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Performance Issue

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2006-10-31 : 06:32:02

I have one table as follow

Id Id1 Amt Ind
1 2 100 A
1 2 200 A
1 2 300 B
1 2 400 X
1 2 500 P
1 2 600 F
1 2 700 D

I want the sum of amount as per the Ind (the result should be)

Adj AMt = 300 where Ind = 'A'
Bal Amt = 300 where Ind ='B'
Pay Amt = 1800 where Ind not in ('A','B','X')

for this I have written two view

create view vcm1
as

select
Id,
Id1,
Ind,
Pay Amt =
case Ind
when 'A' then null
when 'B' then null
when 'X' then null
else SUM(Amt)
end,
Adj AMt =
case Ind
when 'A' then SUM(Amt)
else null
end,
Bal Amtt =
case Ind
when 'B' then SUM(Amt)
else null
end

from tab1
group by Id, Id1,Ind

and thie view is called by 2nd view

create view vcm2
as
select
Id,
Id2
sum(vcm.Pay Amt) Pay Amt,
sum(vcm.Adj Amt) Adj Amt,
sum(vcm.Bal Amt) Bal Amt

from vcm1
group by Id,Id1

these views are called in main view which is called by crystal report to display the report.

I want to reduce the no of views for better performance.

How I can perform the above functionality in one view and have any other approch.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-31 : 06:36:44

Crystal Reports can do the sum for you. Make a Stored Procedure that returns all of your base data, and Crystal Reports can call that. CR can then do the summing for you.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-31 : 07:22:51

Well, you haven't told us much about indexes or about your data, so it is pretty hard to gauge performance ... however, I believe this is a bit of a simpler query that has the potential to give you a more favorable plan.


create view dbo.vcm
as
select
    ID,
    ID1,
    sum(case when Ind not in ('A','B','X') then Amt else 0 end) as 'Pay',
    sum(case when Ind = 'A' then Amt else 0 end) as 'Adj',
    sum(case when Ind = 'B' then Amt else 0 end) as 'Bal'
from
    tab1
group by
    ID,
    ID1

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources