"Timeout expired" error, when executing view in SQL Server 2008 -
i've written query in sql server 2008. query takes 4 minutes execute.
need query view
. so, i've created view query , when try execute view creation script, shows following error:
timeout expired.
timeout period elapsed prior completion of operation or server not responding.
the query is:
select t.jrnno, (select sum(t1.amount) dbo.t_sh t1 (t1.b_or_s = '1') , (t1.jrnno = t.jrnno)) buy, (select sum(t2.amount) dbo.t_sh t2 (t2.b_or_s = '2') , (t2.jrnno = t.jrnno)) sale, sum(t.amount) total, sum(t.h_crg) howla, sum(t.l_crg) laga, sum(t.taxamt) tax, sum(t.commsn) commission dbo.t_sh t (t.tran_type = 's') , (t.jrnno not in (select distinct jrnno dbo.t_ledger)) group t.jrnno
t_sh
, t_ledger
both tables have 100k rows. possible reason , how can overcome this?
update:
select t.jrnno, sum(case when t.b_or_s = 1 t.amount else null end) buy, sum(case when t.b_or_s = 2 t.amount else null end) sale, sum(t.amount) total, sum(t.h_crg) howla, sum(t.l_crg) laga, sum(t.taxamt) tax, sum(t.commsn) commission dbo.t_sh t t.tran_type = 's' , not exists(select 1 dbo.t_ledger x x.jrnno = t.jrnno) group t.jrnno
it solved problem. quick response.
try query:
select t.jrno, sum(case when t1.b_or_s = 1 t.amount else null end) buy, sum(case when t1.b_or_s = 2 t.amount else null end) sale, sum(t.amount) total, sum(t.h_crg) howla, sum(t.l_crg) laga, sum(t.taxamt) tax, sum(t.commsn) commission dbo.t_sh t t.tran_type = 's' , not exists(select 1 dbo.t_ledger x x.jrno = t.jrno)
Comments
Post a Comment