Optimize Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Optimize Query

Author

Topic

tpavan7329
Starting Member

18 Posts

Posted - 2011-03-11 : 01:45:02

Hi,
I want to optimize below Store Procedure and try to make it run faster and minimize of using temporary tables. Can any one help me please?

Alter proc [dbo].[bsp_DRProcess_1GetData]
As
declare @startDate datetime
declare @endDate datetime
set @startDate= getdate() - 1
set @endDate= getdate()

select @startdate, @enddate

/*** START.1: Combine AFP & CORE Records ****/
--AFP records
select
m.myphoneid as afp_myphoneid
,m.destination as afp_destination
,mh.id as afp_mhid
,mh.statusCode as afp_statusCode
,mh.reasoncode as afp_reasonCode
,mh.statusText as afp_statusText
,mh.aggregatorStatusCode as afp_aggregatorStatusCode
,mh.aggregatorReasonCode as afp_aggregatorReasonCode
,mh.aggregatorStatusText as afp_aggregatorStatusText
,mh.created as afp_CreationDate
into #temp_afp_all_history
from [ATLANTIS].[AFP].[dbo].[messageHistory] mh, [ATLANTIS].[AFP].[dbo].[message] m with (nolock)
where mh.msgTableId=m.id and responsetype=7 and m.created between @startDate and @endDate

--AFP records with most recent messages (id) per myphoneid
select afp.*
into #temp_afp_all
from #temp_afp_all_history afp
inner join (select afp_myphoneid,max(afp_mhid) as afp_mhid from #temp_afp_all_history group by afp_myphoneid) afp_last
on afp.afp_mhid=afp_last.afp_mhid

--AFP+CORE.BCN through myphoneid

Select
bcn.destinator as msisdn,bcn.mcc, bcn.BillingCycleNotificationId,bcn.NotificationType
,bcn.Myphoneid,bcn.BillingRequestStateId, bcn.status as bcn_Status
,bcn.creationdate as bcn_CreationDate, bcn.CreatedBy as bcn_CreatedBy
,bcn.UpdatedBy as bcn_UpdatedBy --SH100610
,bcn.DrReturnCode as bcn_DrReturnCode, bcn.DrReasonText as bcn_DrReasonText
,afp.*
into #temp_bcn_afp_all_history
from CORE..BillingCycleNotification bcn with (nolock)
inner join #temp_afp_all afp on convert(nvarchar,afp.afp_myphoneid)=convert(nvarchar,bcn.myphoneid)

--Most recent BCNs per myphoneid
select ba.*
into #temp_bcn_afp_all
from #temp_bcn_afp_all_history ba
inner join
(SELECT max(billingCycleNotificationId) as billingCycleNotificationId FROM #temp_bcn_afp_all_history group by myphoneid) bamax
on (ba.billingCycleNotificationId=bamax.billingCycleNotificationid)

--Comments
--1. Combine above quries into one

--BCN+BR
select
br.BillingRequestId, br.status as br_status
,br.creationdate as br_CreationDate, br.CreatedBy as br_CreatedBy --SH100610
,br.UpdatedBy as br_UpdatedBy --SH100610
,br.LastRetryDate, br.RetryScheduleLock, br.mnc, br.RetryCount
,bre.termId, bre.CustomerId, bre.BillingRequestEventId, bre.RequestType
,ptr.sessionId,ptr.CommandType,ptr.PersistentTaskRecordId --SH100621 added commandType
,ba.*
into #temp_brbcn_status_all_history
from #temp_bcn_afp_all ba
inner join CORE..BillingRequestState brs with (nolock) on brs.BillingRequestStateId=ba.BillingRequestStateId
inner join CORE..BillingRequest br with (nolock) on br.BillingRequestId = brs.BillingRequestId
inner join CORE..BillingRequestEvent bre with (nolock) on bre.BillingRequestEventId = br.BillingRequestEventId
inner join CORE..persistentTaskRecord ptr with (nolock) on ptr.billingRequestId=br.billingRequestId --3PG sessionId

--Most recent myphoneid per BR
select bb.*
into #temp_brbcn_status_all_history2
from #temp_brbcn_status_all_history bb
inner join (select max(Myphoneid) Myphoneid from #temp_brbcn_status_all_history group by BillingRequestId,NotificationType) bbmax
on bb.Myphoneid=bbmax.Myphoneid

--Most recent PersistentTaskRecordId per BillingRequestId 3PG persistent issue
select bb.*
into #temp_brbcn_status_all
from #temp_brbcn_status_all_history2 bb
inner join (select max(PersistentTaskRecordId) PersistentTaskRecordId from #temp_brbcn_status_all_history2 group by BillingRequestId) bbmax
on bb.PersistentTaskRecordId=bbmax.PersistentTaskRecordId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources