The problem is solved, removed the loop, now simply used update statements with derived tables for each operation which were earlier used inside cursor. Thanks to all for there suggestions....

Oh... Ok. Thanks, I need to learn all these things in detail before applying it. But anyways thanks, i will try and let you know the result.

Thanks.

This is what actually happens with Bookmark lookup mechanism:

When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index...

Yeah, it is there, but still the problem exist.

There are so many indexes. Because so many tables are involved each with so many fields.

Is there any other for this (instead of using cursor or loops)way i should try?

Hey, how can i modify cheking the execution plan. its for thefirst time i am checking this. Thanks to you. 🙂

if i see execution plan, it is very clear that most of the time it is spending in table scan.

And how to do it when updating mailingname and mailingaddress fields in turn depends on entitytype of each row. so i need to traverse through it. Isnt it? Or can...

You mean first selecting all the records where entityType is C and updating the fields accordingly, And similar thing if entitytype = P.

And maintaining all these required fields in one...

Here is a code.

Its a huge procedure, so here giving the main portion of processing which is taking time.

INSERT INTO #TempClientSummary

(UserID,UserName,AccountID,AccountNo,AccountName,AcntOpeningDate,ClientID, ClientName,EntityType,EntityID,QPRReportingLevel,MarketValue,PortfolioId, GoalName)

EXEC sp_executesql @nvcBaseQuery

--select * from #TempClientSummary

--...