using stored procedures in crystal

[JuniorNowSr]

JuniorNowSr (Programmer) 10 Aug 07 16:06
I have created a report using a stored procedure.
When I run the procedure via SQL analyzer it takes about 7 minutes to run.

When the procedure is used in crystal it displays "Accessing Database" on the lower leftside of the screen for over an hour, then it reads the records and that takes about another 10 minutes.


Here is the procedure I am using?
What am I doing wrong?
What is crystal doing that takes over an hour to access the database.

--drop procedure USP_RPT_WHLSLR_DAILY_GLANCE_SUMMARY
create procedure USP_RPT_WHLSLR_DAILY_GLANCE_SUMMARY (@end_date datetime, @channel varchar(3), @compensation varchar(3))
as

/*
declare @end_date datetime, @channel varchar(3), @compensation varchar(3)
select @end_date = '8/7/2007', @channel = 'NBD', @compensation = 'Y'
*/

--setting functional variables and values to be used throughout the procedure
declare @begin_year datetime, @month datetime,@bus_days int
select @begin_year = '1' + '/'+ '1' + '/' + convert(varchar(4),datepart(yy,@end_date))
select @month = convert(varchar(4),datepart(mm,@end_date)) + '/' + '1' + '/' + convert(varchar(4),datepart(yy,@end_date))
select @bus_days = (datediff(dd, @begin_year, @end_date)/1.46)

select Group_Level= vw.description, Group_Type = 'TER',SalesDaily = sum(s_day_tot),SalesMTD = sum(s_mtd_tot),SalesYTD = sum(s_ytd_tot),SaleYTDAvg = (sum(s_ytd_tot)/@bus_days),RedsDaily = sum(r_day_tot),RedsMTD = sum(r_mtd_tot),RedsYTD = sum(r_ytd_tot),RedsYTDAvg = (sum(r_ytd_tot)/@bus_days)
from vw_territory_external_wholesaler vw,terr_firm_console fc,firm f
where vw.terr1 = fc.terr1
and f.firm_id = fc.firm_id
and f.channel = @channel
group by vw.description
union
--by mutual fund
SELECT fu.portfolio,'MF',
sum(case when posting_date = @end_date and trade_class = 'S' then t.gross_amount else 0 end),
sum(case when posting_date between @month and @end_date and trade_class = 'S' then t.gross_amount else 0 end),
sum(case when posting_date between @begin_year and @end_date and trade_class = 'S' then t.gross_amount else 0 end),
sum(case when posting_date between @begin_year and @end_date and trade_class = 'S' then t.gross_amount else 0 end)/@bus_days ,
sum(case when posting_date = @end_date and trade_class = 'R' then t.gross_amount else 0 end),
sum(case when posting_date between @month and @end_date and trade_class = 'R' then t.gross_amount else 0 end) ,
sum(case when posting_date between @begin_year and @end_date and trade_class = 'R' then t.gross_amount else 0 end) ,
sum(case when posting_date between @begin_year and @end_date and trade_class = 'R' then t.gross_amount else 0 end)/@bus_days
FROM TRANSACTION_HISTORY t, FIRM f, FUNDS fu,vw_territory_external_wholesaler vw
WHERE t.POSTING_DATE between @begin_year AND @end_date
AND t.trade_class in ('R','S')
AND f.CHANNEL = @channel
and t.terr1 = vw.terr1
AND t.SALES_COMPENSATION = @compensation
AND fu.PRODUCT_TYPE = 'Mutual Fund'
and t.firm_id = f.firm_id
and t.fund = fu.fund
group by fu.portfolio
union
--by sma
SELECT fu.portfolio,'SMA',
sum(case when posting_date = @end_date and trade_class = 'S' then t.gross_amount else 0 end),
sum(case when posting_date between @month and @end_date and trade_class = 'S' then t.gross_amount else 0 end),
sum(case when posting_date between @begin_year and @end_date and trade_class = 'S' then t.gross_amount else 0 end),
sum(case when posting_date between @begin_year and @end_date and trade_class = 'S' then t.gross_amount else 0 end)/@bus_days,
sum(case when posting_date = @end_date and trade_class = 'R' then t.gross_amount else 0 end),
sum(case when posting_date between @month and @end_date and trade_class = 'R' then t.gross_amount else 0 end),
sum(case when posting_date between @begin_year and @end_date and trade_class = 'R' then t.gross_amount else 0 end),
sum(case when posting_date between @begin_year and @end_date and trade_class = 'R' then t.gross_amount else 0 end)/@bus_days
FROM TRANSACTION_HISTORY t, FIRM f, FUNDS fu,vw_territory_external_wholesaler vw
WHERE t.POSTING_DATE between @begin_year AND @end_date
AND t.trade_class in ('R','S')
AND f.CHANNEL = @channel
and t.terr1 = vw.terr1
AND t.SALES_COMPENSATION = @compensation
AND fu.PRODUCT_TYPE = 'SMA'
and t.firm_id = f.firm_id
and t.fund = fu.fund
group by fu.portfolio
union
--by dealer
select f.c_name,'DLR', sum(s_day_tot),sum(s_mtd_tot),sum(s_ytd_tot),(sum(s_ytd_tot)/@bus_days), sum(r_day_tot),RedsMTD = sum(r_mtd_tot),sum(r_ytd_tot),(sum(r_ytd_tot)/@bus_days)
from vw_territory_external_wholesaler vw,terr_firm_console fc,firm f
where vw.terr1 = fc.terr1
and f.firm_id = fc.firm_id
and f.channel = @channel
group by f.c_name

grant exec on USP_RPT_WHLSLR_DAILY_GLANCE_SUMMARY to public

 

[synapsevampire]

What version of Crystal and SQL Server are being used?

What connectivity did you use from Crystal?

In general you'll receive better responses if you supply the above when you post.

-k

 

[JuniorNowSr]

I am using crystal version 9(yes, I know...no longer supported, that's why I am in this form).

I am connecting via to crstal via odbc