September 4, 2008 at 6:15 am
I have around 40,000 records in a table and using cursor to traverse through it. Need some way to speed up the processing. I tried using While loop instead, but still don't find any difference in performance. Is there any other way to achieve this?
i have a table where i am inserting data for around 15 columns and then traversing each row using cursor where checking for a value of a particular field, and depending on this value further updating 2 more columns of the same table inside the loop. If i am using some search criteria then it shows the result, but without filtering it just seems like hanged, doesn't show anything and just process something internally.
So its mainly because of no. of records. And this may increase with time. So need some solution.
Can anybody help me how should i achieve this with better performance?
here is the code..
September 4, 2008 at 6:17 am
Could you post you code please
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 4, 2008 at 6:28 am
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
-- to Compute Name and Address on mailing Page for each client
DECLARE @EntityType CHAR(1)
DECLARE @EntityID INT
DECLARE EntityAddress CURSOR FAST_FORWARD FOR SELECT EntityID, EntityType FROM #TempClientSummary
OPEN EntityAddress
FETCH NEXT FROM EntityAddress INTO @EntityID,@EntityType
WHILE @@Fetch_Status=0
BEGIN
DECLARE @PortfolioID int
DECLARE @ClientID int
Declare @vchClientName varchar(100)
Declare @vchClientAddress varchar(5000)
Declare @vchClientAddress1 varchar(150)
declare @vchClientAddress2 varchar(150)
declare @vchClientAddress3 varchar(150)
declare @vchClientAddress4 varchar(150)
declare @vchClientAddress5 varchar(150)
Declare @intAccountID int
Set @vchClientName = ''
set @vchClientAddress = ''
Set @vchClientAddress1 = ''
Set @vchClientAddress2 = ''
Set @vchClientAddress3 = ''
Set @vchClientAddress4 = ''
Set @vchClientAddress5 = ''
IF @EntityType = 'C'
-- Here The Address is computed by taking all the available addresses, state, country info from resp. tables and
-- then combining it to in a format : #Address1 | #Address2 | #City | #State
BEGIN
DECLARE @contacttypeid CHAR(1)
DECLARE @Phone VARCHAR(50)
SET @vchClientAddress = ''
SET @ClientID = @EntityID
SELECT @vchClientName = isnull(ClientName ,'')
FROM Client
WHERE ClientID = @ClientID
IF EXISTS(SELECT contacttypeid FROM Multiaddress WHERE contacttypeid = 'I' AND MultiID = @ClientID AND MultiCode = 'CL')
SET @contacttypeid = 'I'
ELSE IF EXISTS(SELECT contacttypeid FROM Multiaddress WHERE contacttypeid = 'H' AND MultiID = @ClientID AND MultiCode = 'CL')
SET @contacttypeid = 'H'
ELSE IF EXISTS(SELECT contacttypeid FROM Multiaddress WHERE contacttypeid = 'W' AND MultiID = @ClientID AND MultiCode = 'CL')
SET @contacttypeid = 'W'
SELECT @vchClientAddress1 = CASE LTRIM(RTRIM(ISNULL(A.addr1,''))) WHEN '' THEN '' ELSE LTRIM(RTRIM(A.addr1)) + '|' END,
@vchClientAddress2 = CASE LTRIM(RTRIM(ISNULL(A.addr2,''))) WHEN '' THEN '' ELSE LTRIM(RTRIM(A.addr2)) + '|' END,
@vchClientAddress3 = CASE LTRIM(RTRIM(isnull(A.City,''))) WHEN '' THEN '' ELSE LTRIM(RTRIM(isnull(A.City,''))) + '|' END,
@vchClientAddress4 = Case When (C.CountryID=1 or C.CountryID=231) then rtrim(ltrim(S.StateCode)) else rtrim(ltrim(A.StateName)) End + ' ' + ISNULL(dbo.ufn_QPR_FormatZip(A.Zip),''),
@vchClientAddress4 = CASE ISNULL(@vchClientAddress4,'') WHEN '' THEN '' ELSE @vchClientAddress4 + '|' END,
@vchClientAddress5 = ISNULL(C.Country,''),
@Phone = P.Phone
FROM
Address A
LEFT OUTER JOIN Multiaddress MA ON A.AddressID = MA.AddressId AND MA.MultiCode = 'CL'
LEFT OUTER JOIN State S ON A.stateId = S.StateId
LEFT OUTER JOIN Country C ON A.CountryId = C.CountryId
LEFT OUTER JOIN MultiPhone MP ON MP.MultiId = MA.MultiId AND MP.MultiCode = 'CL'
LEFT OUTER JOIN Phone P ON MP.PhoneId = P.PhoneId
WHERE MA.MultiId = @ClientID AND MA.ContactTypeId = @contacttypeid
SET @vchClientName = @vchClientName
SET @vchClientAddress = @vchClientAddress1 + @vchClientAddress2 + @vchClientAddress3 + @vchClientAddress4 + @vchClientAddress5
END
ELSE IF @EntityType = 'P'
-- The Address is taken from QPRMailingAddress in ‘Name|#Address1|#Address2|#City|#State' form
-- and then splitted to get name & address resp.
BEGIN
DECLARE @Addr CHAR(500)
SET @PortfolioID = @EntityID
SET @ClientID = (SELECT ClientID FROM Portfolio WHERE PortfolioID = @PortfolioID)
IF EXISTS (select 1 from account where PortfolioID=@PortfolioID and isnull(SNAM,'') <> '' and isnull(status,'') = 'L')
SET @intAccountID=(SELECT top 1 accountId FROM Account WHERE PortfolioID=@PortfolioID AND isnull(SNAM,'') <> '' and isnull(status,'') = 'L')
ELSE
SET @intAccountID=(SELECT top 1 accountId FROM Account WHERE PortfolioID=@PortfolioID AND isnull(SNAM,'') <> '')
SET @Addr = (SELECT REPLACE(qprmailingaddress,'char(13) + char(10)','|')FROM Account WHERE AccountId = @intAccountID)
DECLARE @pos INT
SET @pos = CHARINDEX('|',@Addr)
IF @pos < 0
BEGIN
SELECT @vchClientName = @Addr
SELECT @vchClientAddress = ''
END
ELSE
BEGIN
SELECT @vchClientName = (SELECT LEFT(@Addr,@pos-1))
SELECT @vchClientAddress = (SELECT SUBSTRING(@Addr,@POS+1,500))
END
SET @Addr = ''
END
UPDATE #TempClientSummary SET ClientMailName = @vchClientName, ClientMailAddress = @vchClientAddress, Phone = @Phone WHERE EntityId = @EntityId
FETCH NEXT FROM EntityAddress INTO @EntityID,@EntityType
END
CLOSE EntityAddress
DEALLOCATE EntityAddress
SET @noRecords = (SELECT COUNT(*) FROM #TempClientSummary )
SET @nvcBaseQuery = N'SELECT '+@nvcNoRecords+' UserName,ClientID,ClientName,PortfolioID,GoalName,AccountID,AccountName,AccountNo,AcntOpeningDate,
QPRReportingLevel,ClientMailName,ClientMailAddress,MarketValue, Phone FROM #TempClientSummary'
IF @sortColumn <> ''
SET @nvcBaseQuery= @nvcBaseQuery+' ORDER BY '+ @sortColumn
--returns the details
EXEC sp_executesql @nvcBaseQuery
September 4, 2008 at 6:32 am
could this not be done just using a select statement rather than a cursor?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 4, 2008 at 6:37 am
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 temp. table. But that will be very difficult to manage since there are many conditions in between, as you see. But still i tried it, but no result, its still taking the same time.
September 4, 2008 at 6:38 am
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 we do it using simple select?
September 4, 2008 at 6:39 am
OK cool,
I assume the query is slow at the processing level and not at the update level?
If so could post an execution plan please, else of the cursor version or the select version.
Thanks
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 4, 2008 at 6:40 am
ranjita.damle (9/4/2008)
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 we do it using simple select?
I would think that a case statement would you to do difference workings for each row dependant on a different entitytype
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 4, 2008 at 6:48 am
if i see execution plan, it is very clear that most of the time it is spending in table scan.
September 4, 2008 at 6:50 am
Ok, have you checked your indexes and stats?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 4, 2008 at 6:50 am
Hey, how can i modify cheking the execution plan. its for thefirst time i am checking this. Thanks to you. 🙂
September 4, 2008 at 6:53 am
WEll you need to check the indexes on your tables
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 4, 2008 at 6:57 am
Is there any other for this (instead of using cursor or loops)way i should try?
September 4, 2008 at 7:02 am
There are so many indexes. Because so many tables are involved each with so many fields.
September 4, 2008 at 7:08 am
AFAIK, Solution for Table Scan is to create proper Indexes... Try atleast to create indexes in the columns which are of most use in your query...
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply