October 16, 2012 at 7:00 am
I have a scenario in which I have a table citymaster(cityid,cityname,stateid) which needs to be getupdate from statemaster (stateid,statename,statecode). I dont have common fields fields in both these tables so used another table Zipcode (cityname,statecode,zipcode,zipcodeid).
I created a cursor through which I make join on tables (state,zipcode) and (city,zipcode).
when i ran this its taking a too much time around 15 minutes and still executing...
this is the cursor below -
DECLARE @fcIty varchar(40)
DECLARE @fstate varchar(20)
declare db_cursor cursor for select fcity,fstate from WCDentalSQL_TOR..ZipCode
open db_cursor
fetch next from db_cursor into @fcity,@fstate
while @@FETCH_STATUS=0
begin
UPDATE C
SET C.STATEID =S.STATEID
FROM StateMaster S
INNER JOIN
WCDentalSQL_TOR..ZipCode Z ON Z.fState COLLATE Latin1_General_CI_AI = S.StateCode COLLATE Latin1_General_CI_AI
INNER JOIN
CityMaster C ON C.CityName COLLATE Latin1_General_CI_AI = Z.fCity COLLATE Latin1_General_CI_AI
WHERE Z.fState=@fstate
end
please suggest any suggestion on this so that it takes less time to execute..
waiting for your replies
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 7:06 am
i forget to paste the last lines of close and deallocate cursor plzz make them include
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 7:13 am
Avoid cursor and try use while..loop
October 16, 2012 at 7:16 am
Hi,
as in to fetch every row value we use statement-
fetch next from db_cursor into @fcity,@fstate
How using while loop I fetch every row value?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 7:20 am
vyas (10/16/2012)
Avoid cursor and try use while..loop
Why?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 16, 2012 at 7:23 am
bcoz cursor works on row-by-row basis taht why it takes long time to execute if there are execess rows in your table...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 7:28 am
kapil_kk (10/16/2012)
bcoz cursor works on row-by-row basis taht why it takes long time to execute if there are execess rows in your table...
Your cursor block is running slowly because you are scanning through city by city as well as state by state. Here's a set-based equivalent which doesn't:
UPDATE C
SET C.STATEID = S.STATEID
FROM CityMaster C
INNER JOIN WCDentalSQL_TOR..ZipCode Z
ON C.CityName COLLATE Latin1_General_CI_AI = Z.fCity COLLATE Latin1_General_CI_AI
INNER JOIN StateMaster S
ON Z.fState COLLATE Latin1_General_CI_AI = S.StateCode COLLATE Latin1_General_CI_AI
INNER JOIN (select fstate from WCDentalSQL_TOR..ZipCode GROUP BY fstate) d ON d.fstate = Z.fState
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 16, 2012 at 7:30 am
Why are you getting a value for @fctity and then not using it? More importantly, why are you using a cursor at all? Get rid of everything up to and including "begin" and from "WHERE" onwards. You'll find it does exactly the same, only much faster.
John
October 16, 2012 at 7:34 am
thanks Chris it works fast now
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 7:37 am
previously I fetching statecode rows from zipcode tables which contains about 45k rows due to that also cursor was taking too much time so i replaced with state table and fetch statecode from statetable which has about 100 rows and there is great improvement in performance..
DECLARE @fcIty varchar(40)
DECLARE @fstate varchar(20)
declare db_cursor cursor for select StateCode from StateMaster
open db_cursor
fetch next from db_cursor into @fstate
while @@FETCH_STATUS=0
begin
UPDATE C
SET C.STATEID =S.STATEID
FROM StateMaster S
INNER JOIN
WCDentalSQL_TOR..ZipCode Z ON Z.fState COLLATE Latin1_General_CI_AI = S.StateCode COLLATE Latin1_General_CI_AI
INNER JOIN
CityMaster C ON C.CityName COLLATE Latin1_General_CI_AI = Z.fCity COLLATE Latin1_General_CI_AI
WHERE Z.fState=@fstate
fetch next from db_cursor into @fstate
end
close db_cursor
deallocate db_cursor
but yes always try to minimize the use of cursor so will use Chris query... :-):-P
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 7:40 am
hey Chris,
can you plz tell me why you placed this line -
INNER JOIN (select fstate from WCDentalSQL_TOR..ZipCode GROUP BY fstate) d ON d.fstate = Z.fState
I am not clear with the use of this...
thanks in advance
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 7:45 am
vyas (10/16/2012)
Avoid cursor and try use while..loop
This is actually a really bad recommendation. A well written cursor "firehose" cursor will be just as fast as a While loop. It's a myth that While loops are better than cursors.
What really needs to be done is to avoid RBAR altogether.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2012 at 7:48 am
i have one more scenario similar to this...
I want to update city id in ziptable but as a city can contain different zip codes so I dont know how to do that...
can anyone help me this too...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 7:51 am
kapil_kk (10/16/2012)
hey Chris,can you plz tell me why you placed this line -
INNER JOIN (select fstate from WCDentalSQL_TOR..ZipCode GROUP BY fstate) d ON d.fstate = Z.fState
I am not clear with the use of this...
thanks in advance
This is the set-based equivalent of the cursor loop.
Unless you are 'using' city (as John Mitchell pointed out), you have to aggregate to the same level as the rest of the query - which happens to be state.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 16, 2012 at 7:54 am
I am a newbie in DBA so can you plz explain me about this set based equivalent approach to cursor..
I dont have any idea about this
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply