Need a suggestion and solution
-
Do you have a while - end clause or a recursive?
-
No zubeyir...the syntax do not have a recursive...
this is the syntax which i mean, maybe u will get the better picture:
create view tso_cust_profile
as
select a.memberid as accountid,
a.srvlast, dbo.general_counter('m', a.srvlast, getdate()) as mthsrvlast,
a.srvtotal, dbo.srv_freq_categorizer(a.srvtotal) as srvfreq,
a.amttotal, dbo.amount_trans_categorizer(a.amttotal) as amttotalcatg,
b.title, b.account, b.gender,
dbo.general_counter('y',b.birthdate,getdate()) as age, b.maritalstatus,
b.employtype, b.livingstatus, b.industrialtype, b.education, b.jobtitle,
b.approvaldate, b.bankrefid, b.cardtype, b.cardlevel, b.validfrom,
b.validto, b.creditlimit, b.annualincome, b.monthlyexp, b.hobby,
c.type1total,
d.type2total,
e.type3total,
f.type4total,
g.type5total,
h.type6total,
i.type7total,
j.type8total,
k.type9total,
l.type10total,
m.type11total,
n.type12total,
o.type13total,
p.type14total,
q.type15total,
r.type16total,
s.type17total,
t.type18total,
u.type19total,
v.type20total
from tso_cust_service_profile a
left outer join cust_sapcic b
on a.memberid = b.accountid
left outer join tso_cust_service_type1 c
on a.memberid = c.memberid
left outer join tso_cust_service_type2 d
on a.memberid = d.memberid
left outer join tso_cust_service_type3 e
on a.memberid = e.memberid
left outer join tso_cust_service_type4 f
on a.memberid = f.memberid
left outer join tso_cust_service_type5 g
on a.memberid = g.memberid
left outer join tso_cust_service_type6 h
on a.memberid = h.memberid
left outer join tso_cust_service_type7 i
on a.memberid = i.memberid
left outer join tso_cust_service_type8 j
on a.memberid = j.memberid
left outer join tso_cust_service_type9 k
on a.memberid = k.memberid
left outer join tso_cust_service_type10 l
on a.memberid = l.memberid
left outer join tso_cust_service_type11 m
on a.memberid = m.memberid
left outer join tso_cust_service_type12 n
on a.memberid = n.memberid
left outer join tso_cust_service_type13 o
on a.memberid = o.memberid
left outer join tso_cust_service_type14 p
on a.memberid = p.memberid
left outer join tso_cust_service_type15 q
on a.memberid = q.memberid
left outer join tso_cust_service_type16 r
on a.memberid = r.memberid
left outer join tso_cust_service_type17 s
on a.memberid = s.memberid
left outer join tso_cust_service_type18 t
on a.memberid = t.memberid
left outer join tso_cust_service_type19 u
on a.memberid = u.memberid
left outer join tso_cust_service_type20 v
on a.memberid = v.memberid
the source of the data are coming from 2 tables only...but I have to make until 22 views to bring up together all the info that i need...
one of the table is containing at least two million records....the other only three hundred records
Is there any solution to running query above??
-
No zubeyir...the syntax do not have a recursive...
this is the syntax which i mean, maybe u will get the better picture:
create view tso_cust_profile
as
select a.memberid as accountid,
a.srvlast, dbo.general_counter('m', a.srvlast, getdate()) as mthsrvlast,
a.srvtotal, dbo.srv_freq_categorizer(a.srvtotal) as srvfreq,
a.amttotal, dbo.amount_trans_categorizer(a.amttotal) as amttotalcatg,
b.title, b.account, b.gender,
dbo.general_counter('y',b.birthdate,getdate()) as age, b.maritalstatus,
b.employtype, b.livingstatus, b.industrialtype, b.education, b.jobtitle,
b.approvaldate, b.bankrefid, b.cardtype, b.cardlevel, b.validfrom,
b.validto, b.creditlimit, b.annualincome, b.monthlyexp, b.hobby,
c.type1total,
d.type2total,
e.type3total,
f.type4total,
g.type5total,
h.type6total,
i.type7total,
j.type8total,
k.type9total,
l.type10total,
m.type11total,
n.type12total,
o.type13total,
p.type14total,
q.type15total,
r.type16total,
s.type17total,
t.type18total,
u.type19total,
v.type20total
from tso_cust_service_profile a
left outer join cust_sapcic b
on a.memberid = b.accountid
left outer join tso_cust_service_type1 c
on a.memberid = c.memberid
left outer join tso_cust_service_type2 d
on a.memberid = d.memberid
left outer join tso_cust_service_type3 e
on a.memberid = e.memberid
left outer join tso_cust_service_type4 f
on a.memberid = f.memberid
left outer join tso_cust_service_type5 g
on a.memberid = g.memberid
left outer join tso_cust_service_type6 h
on a.memberid = h.memberid
left outer join tso_cust_service_type7 i
on a.memberid = i.memberid
left outer join tso_cust_service_type8 j
on a.memberid = j.memberid
left outer join tso_cust_service_type9 k
on a.memberid = k.memberid
left outer join tso_cust_service_type10 l
on a.memberid = l.memberid
left outer join tso_cust_service_type11 m
on a.memberid = m.memberid
left outer join tso_cust_service_type12 n
on a.memberid = n.memberid
left outer join tso_cust_service_type13 o
on a.memberid = o.memberid
left outer join tso_cust_service_type14 p
on a.memberid = p.memberid
left outer join tso_cust_service_type15 q
on a.memberid = q.memberid
left outer join tso_cust_service_type16 r
on a.memberid = r.memberid
left outer join tso_cust_service_type17 s
on a.memberid = s.memberid
left outer join tso_cust_service_type18 t
on a.memberid = t.memberid
left outer join tso_cust_service_type19 u
on a.memberid = u.memberid
left outer join tso_cust_service_type20 v
on a.memberid = v.memberid
the source of the data are coming from 2 tables only...but I have to make until 22 views to bring up together all the info that i need...
one of the table is containing at least two million records....the other only three hundred records
Is there any solution to running query above??
-
No zubeyir...the syntax do not have a recursive...
this is the syntax which i mean, maybe u will get the better picture:
create view tso_cust_profile
as
select a.memberid as accountid,
a.srvlast, dbo.general_counter('m', a.srvlast, getdate()) as mthsrvlast,
a.srvtotal, dbo.srv_freq_categorizer(a.srvtotal) as srvfreq,
a.amttotal, dbo.amount_trans_categorizer(a.amttotal) as amttotalcatg,
b.title, b.account, b.gender,
dbo.general_counter('y',b.birthdate,getdate()) as age, b.maritalstatus,
b.employtype, b.livingstatus, b.industrialtype, b.education, b.jobtitle,
b.approvaldate, b.bankrefid, b.cardtype, b.cardlevel, b.validfrom,
b.validto, b.creditlimit, b.annualincome, b.monthlyexp, b.hobby,
c.type1total,
d.type2total,
e.type3total,
f.type4total,
g.type5total,
h.type6total,
i.type7total,
j.type8total,
k.type9total,
l.type10total,
m.type11total,
n.type12total,
o.type13total,
p.type14total,
q.type15total,
r.type16total,
s.type17total,
t.type18total,
u.type19total,
v.type20total
from tso_cust_service_profile a
left outer join cust_sapcic b
on a.memberid = b.accountid
left outer join tso_cust_service_type1 c
on a.memberid = c.memberid
left outer join tso_cust_service_type2 d
on a.memberid = d.memberid
left outer join tso_cust_service_type3 e
on a.memberid = e.memberid
left outer join tso_cust_service_type4 f
on a.memberid = f.memberid
left outer join tso_cust_service_type5 g
on a.memberid = g.memberid
left outer join tso_cust_service_type6 h
on a.memberid = h.memberid
left outer join tso_cust_service_type7 i
on a.memberid = i.memberid
left outer join tso_cust_service_type8 j
on a.memberid = j.memberid
left outer join tso_cust_service_type9 k
on a.memberid = k.memberid
left outer join tso_cust_service_type10 l
on a.memberid = l.memberid
left outer join tso_cust_service_type11 m
on a.memberid = m.memberid
left outer join tso_cust_service_type12 n
on a.memberid = n.memberid
left outer join tso_cust_service_type13 o
on a.memberid = o.memberid
left outer join tso_cust_service_type14 p
on a.memberid = p.memberid
left outer join tso_cust_service_type15 q
on a.memberid = q.memberid
left outer join tso_cust_service_type16 r
on a.memberid = r.memberid
left outer join tso_cust_service_type17 s
on a.memberid = s.memberid
left outer join tso_cust_service_type18 t
on a.memberid = t.memberid
left outer join tso_cust_service_type19 u
on a.memberid = u.memberid
left outer join tso_cust_service_type20 v
on a.memberid = v.memberid
the source of the data are coming from 2 tables only...but I have to make until 22 views to bring up together all the info that i need...
one of the table is containing at least two million records....the other only three hundred records
Is there any solution to running query above??
-
It may help to look at the views and tables being used. As the SQL for these may be extensive, if you send me a private email (PM), I can tell you where to send me the scripts so I can look at them and try to help. I have a feeling that you may not need all the views you are using but can't tell for sure until I see them.
Thanks,
Lynn
-
Hi Lynn,
sorry, I forgot to copy the syntax for the UDFs
by the way, I've tried your SQL code and it works fine ...it took less time to run than the original code...the difference is until 6 minutes to retrieve the result...
thanx again for the help...
regards,
Ady
-
Hi Lynn,
sorry, I forgot to copy the syntax for the UDFs
by the way, I've tried your SQL code and it works fine ...it took less time to run than the original code...the difference is until 6 minutes to retrieve the result...
thanx again for the help...
regards,
Ady
-
Hi Lynn,
sorry, I forgot to copy the syntax for the UDFs
by the way, I've tried your SQL code and it works fine ...it took less time to run than the original code...the difference is until 6 minutes to retrieve the result...
thanx again for the help...
regards,
Ady
-
Hi Lynn,
sorry, I forgot to copy the syntax for the UDFs
by the way, I've tried your SQL code and it works fine ...it took less time to run than the original code...the difference is until 6 minutes to retrieve the result...
thanx again for the help...
regards,
Ady
-
If it is running 6 minutes, you may still want to do some more work. I would suggest looking at your current indexes and perhaps running SQL Profiler to see if there are some changes that can be made to improve the performance of the query.
I am glad to hear that I was able to help.
Lynn
Viewing 10 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply