Need a suggestion and solution
-
Hi guys,
Please help to solve this problem, I have a query which involved at least 22 views from 2 tables. After a long time running, the syntax generates an error like this:
Server: Msg 8630, Level 17, State 98, Line 1
Internal Query Processor Error: The query processor encountered an unexpected error during execution.
I don't know what to do...because there is no other better syntax to generate the data that my boss wanted...
Need your suggestions and problem solving guys...a.s.a.p.....thx
-adyseven-
-
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply