Optimizing Script for SSRS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Optimizing Script for SSRS

Author

Topic

HelalM
Starting Member

19 Posts

Posted - 2010-10-06 : 15:44:50

I inherited these codes and optimized it to the extent of my SQL knowledge which doesn't go very far! I reduced the temp tables from 9 to 6, but I need further optimization (efficiency/performance) for SSRS report development. Here is the code:

Set NOCOUNT ON
declare @StartDate char(10)
declare @EndDate char(10)

set @StartDate = '09-01-2010'
set @EndDate = '09-30-2010'
-----------------
if object_id('tempdb..#calltrack') is not null begin drop table #calltrack end
SELECT DISTINCT calltr.calldate
,callr.status
,callr.resoldate
,datename(dw, calltr.calldate) as calldate_day
,callt.category
,callt.description as calltypedesc
,calls.description as callsource
,calltr.createid
,callr.createdate
,calltr.memid
,mem.fullname as memname
,CAST(calln.callnote as VARCHAR(500)) as callnote

INTO #calltrack

FROM callreason callr with (nolock)
inner join calltype callt with (nolock) on callr.callcode = callt.callcode
inner join calltrack calltr with (nolock) on calltr.callerid = callr.callerid
inner join callsource calls with (nolock) on calls.callsourceid = calltr.callsourceid
inner join member mem with (nolock) on mem.memid = calltr.memid
inner join callnote calln with (nolock) on calln.callerid = calltr.callerid

Where callr.status in ('X', 'Y')
and calltr.calldate between @StartDate and @EndDate
and calltr.createid in ('X', 'Y' , 'Z')

order by mem.fullname

---------------------------------------------------------------------------------------------
--BROUGHT BACK MAX TERM AND EFFECTIVE DATE FOR MEMBERS IN APPOPRIATE PROGRAM
if object_id('tempdb..#maxterm') is not null begin drop table #maxterm end
select distinct
memid,
max(termdate) as termdate
into #maxterm
from enrollkeys
where segtype='INT'
group by memid

if object_id('tempdb..#maxterm2') is not null begin drop table #maxterm2 end
select distinct
m.memid,
m.termdate,
max(effdate) as effdate
INTO #maxterm2
from #maxterm m
inner join enrollkeys e with (nolock) ON m.memid=e.memid AND m.termdate=e.termdate AND segtype='INT'
group by
m.memid,
m.termdate

--THERE ARE FEW MEMBERS WHO ARE IN TWO DIFFERENT PROGRAMS
if object_id('tempdb..#maxenrollid') is not null begin drop table #maxenrollid end
select distinct
m.memid,
m.termdate,
bp.programid,
p.description as program
into #maxenrollid
from #maxterm2 m
inner join enrollkeys e with (nolock) ON m.memid=e.memid and m.termdate=e.termdate AND m.effdate=e.effdate AND segtype='INT'
inner join benefitplan bp with (nolock) ON e.planid=bp.planid AND bp.programid IN ('X' , 'Y', 'Z')
inner join program p with (nolock) ON bp.programid=p.programid

--------------------------------------------------------------------------------------------
if object_id('tempdb..#calltrack2') is not null begin drop table #calltrack2 end
SELECT DISTINCT
calldate,
status,
resoldate,
calldate_day,
category,
calltypedesc,
callsource,
createid,
createdate,
c.memid,
memname,
program,
callnote
INTO #calltrack2
FROM #calltrack c
INNER JOIN #maxenrollid m with (nolock) ON c.memid=m.memid

if object_id('tempdb..#calltrack3') is not null begin drop table #calltrack3 end
SELECT DISTINCT
calldate,
memid,
callnote,
max(createdate) as createdate
INTO #calltrack3
FROM #calltrack2
GROUP BY
calldate,
memid,
callnote

SELECT DISTINCT
c2.calldate,
c2.status,
c2.resoldate,
c2.calldate_day,
c2.category,
c2.calltypedesc,
c2.callsource,
c2.createid,
c3.createdate,
c2.memid,
c2.memname,
c2.program,
c2.callnote

FROM #calltrack2 c2
INNER JOIN #calltrack3 c3 with (nolock) ON c2.calldate=c3.calldate AND c2.memid=c3.memid
AND c2.callnote=c3.callnote AND c2.createdate=c3.createdate

Since I am here (newbie) please be more specific in your suggestions. Give me examples if possible.

Thanks,
Helal

HM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 04:47:32

without understanding what exactly trying to do in report its hard to suggest. I dont think anybody will take pain of going through whole code and then understand what you want. So please post some sample data from tables and then explain what you're trying achieve from them in report?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

HelalM
Starting Member

19 Posts

Posted - 2010-10-20 : 10:23:09

I total y undrestand and would have if I had errors with the script. My question is rather general. The script runs ok and I get my results ok as well. However, when developing this report for SSRS, then there is a rendering issue as I was told by IT department. Thus, the query needs to be optimized and temp tables be removed as much as possible by using drive queries, etc....as I indicated, I have reduced using temp tables ffrom 9 to 6 but need help to use other methods and perhaps completely remove all temp tables. As I was reviewing these codes, there seem to be some redundancies and those could be optimized. In sum, what I need is to optimize this script. The result should look like:
Columns:
calldate, status, resoldate, calldate_day, category, calltypedesc,
callsource,creatid,createdate,memid,memname,program,callnote

HM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources