"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

Popular posts from this blog

delphi - How to convert bitmaps to video? -

jasper reports - Fixed header in Excel using JasperReports -

python - ('The SQL contains 0 parameter markers, but 50 parameters were supplied', 'HY000') or TypeError: 'tuple' object is not callable -