Solved

Optimizing TSQL query

Posted on 2006-06-21
5
393 Views
Last Modified: 2012-05-05
Hello,
I am running a query which takes about one hour.Can any one help me optimize it.
Here is the query:

Select * into #TAD  from
OPENQUERY(DWDB, 'select DW_PR_SVC_LOAN_ID,DELQ_PRIN_AMT
as Total_Amt_Due from DW_PR_SVC_LOAN_MOD ')

Update  tbl_MaestroCampaignList set TotalAmountDue = #TAD.Total_Amt_Due
from (tbl_MaestroCampaignList inner join #TAD
on tbl_MaestroCampaignList.DW_PR_SVC_LOAN_ID = #TAD.DW_PR_SVC_LOAN_ID)
where
datediff(day,GetDate(),campaignDate)=0 and CampaignName ='FirstLienPreFCL'

I have to basically calculate Total Amount only for the loans in tbl_MaestroCampaignList .But I have to get these values from Datawarehouse table which has about a million loans. How to optimize it.
Thanks  bunch
0
Comment
Question by:justin11
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16951532
what takes the time
the select into
or the update ?

which version of sql server?

why isn't the maestrocampaignlist in the warehouse?

why do you need to  datediff on campaigndate?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16952152
create this index it will speed upd the process a bit.
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 500 total points
ID: 16952160
create this index it will speed up the process a bit.

create nonclustered index ix_tbl_MaestroCampaignList_01 on tbl_MaestroCampaignList(CampaignName,DW_PR_SVC_LOAN_ID)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now