Jaystonian
November 24th, 2006, 05:47 PM
Hi,
I'm at a loss for troubleshooting this issue. I have a report that grabs data from two views, by calling one view. This view would take around 16 seconds to return a table. This is what it looked like:
create view view_whatever AS
select A.*, B.Name, C.Name
from primarytable as A
inner join firstView as B ON A.ID = B.PID
inner join secondView as C ON A.ID = C.PID
I'm just typing from memory, and simplifying names used... The important part is we're grabbing one column from each view. So this consumes too much time. The views each concatenate about a dozen fields into one string, specifically one for english and one for a secondary language.
To speed this up, I wrapped each view into a function:
CREATE FUNCTION fn_whatever1( @PID as int ) RETURNS varchar(250) AS
Select Name from firstView where ID=@PID
CREATE FUNCTION fn_whatever2( @PID as int ) RETURNS varchar(250) AS
Select Name from secondView where ID=@PID
Now, because functions only are called when necessary (sometimes once per query, sometimes once per row, depending on how u use it..) there are some obvious improvements to speed.. The original view then looks like:
create view view_whatever AS
select A.*, fn_whatever1(A.ID), fn_whatever2(A.ID)
from primarytable as A
I am REALLY simplifyng this as much as I can to convey exactly what the issue is. For all intensive purposes, presume this all works, because it does. The query (select * from view_whatever where OrderID=82047) only takes around 0.8 seconds, compared to 16-20 times that time.
OK so now for the meat of the problem. The report is using fields from the view, and it takes 15-20 seconds for the view to open. When I use the new view, which should take about a second to open, it instead hangs the process. When I analyze the database (MS 2005), I see the processing running (sp_who2) and I have to kill it.
I can grab the data from the view any number of ways, but Crystal Reports doesn't want to swallow. Is this a bug in CR?
I'm at a loss for troubleshooting this issue. I have a report that grabs data from two views, by calling one view. This view would take around 16 seconds to return a table. This is what it looked like:
create view view_whatever AS
select A.*, B.Name, C.Name
from primarytable as A
inner join firstView as B ON A.ID = B.PID
inner join secondView as C ON A.ID = C.PID
I'm just typing from memory, and simplifying names used... The important part is we're grabbing one column from each view. So this consumes too much time. The views each concatenate about a dozen fields into one string, specifically one for english and one for a secondary language.
To speed this up, I wrapped each view into a function:
CREATE FUNCTION fn_whatever1( @PID as int ) RETURNS varchar(250) AS
Select Name from firstView where ID=@PID
CREATE FUNCTION fn_whatever2( @PID as int ) RETURNS varchar(250) AS
Select Name from secondView where ID=@PID
Now, because functions only are called when necessary (sometimes once per query, sometimes once per row, depending on how u use it..) there are some obvious improvements to speed.. The original view then looks like:
create view view_whatever AS
select A.*, fn_whatever1(A.ID), fn_whatever2(A.ID)
from primarytable as A
I am REALLY simplifyng this as much as I can to convey exactly what the issue is. For all intensive purposes, presume this all works, because it does. The query (select * from view_whatever where OrderID=82047) only takes around 0.8 seconds, compared to 16-20 times that time.
OK so now for the meat of the problem. The report is using fields from the view, and it takes 15-20 seconds for the view to open. When I use the new view, which should take about a second to open, it instead hangs the process. When I analyze the database (MS 2005), I see the processing running (sp_who2) and I have to kill it.
I can grab the data from the view any number of ways, but Crystal Reports doesn't want to swallow. Is this a bug in CR?