Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

QUERY taking too much time in execution Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 7:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1373217
Posted Tuesday, October 16, 2012 7:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1373226
Posted Tuesday, October 16, 2012 7:13 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 7:16 AM
Points: 3,931, Visits: 1,102
Avoid cursor and try use while..loop


Post #1373230
Posted Tuesday, October 16, 2012 7:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1373231
Posted Tuesday, October 16, 2012 7:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:07 AM
Points: 1,037, Visits: 7,025
vyas (10/16/2012)
Avoid cursor and try use while..loop


Why?



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1373235
Posted Tuesday, October 16, 2012 7:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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/
Post #1373239
Posted Tuesday, October 16, 2012 7:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:07 AM
Points: 1,037, Visits: 7,025
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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1373247
Posted Tuesday, October 16, 2012 7:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 AM
Points: 5,488, Visits: 10,339
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
Post #1373250
Posted Tuesday, October 16, 2012 7:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
thanks Chris it works fast now


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1373256
Posted Tuesday, October 16, 2012 7:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
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...



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1373257
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse