Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » QUERY taking too much time in execution
Add to briefcase
17 posts, Page 1 of 2
12»»

QUERY taking too much time in execution Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
kapil_kk
Posted Tuesday, October 16, 2012 7:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151
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
Post #1373217
kapil_kk
Posted Tuesday, October 16, 2012 7:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151
i forget to paste the last lines of close and deallocate cursor plzz make them include
Post #1373226
vyas
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: Thursday, May 16, 2013 4:02 AM
Points: 3,131, Visits: 1,056
Avoid cursor and try use while..loop


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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151
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?
Post #1373231
ChrisM@home
Posted Tuesday, October 16, 2012 7:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 921, Visits: 3,743
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
kapil_kk
Posted Tuesday, October 16, 2012 7:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151
bcoz cursor works on row-by-row basis taht why it takes long time to execute if there are execess rows in your table...
Post #1373239
ChrisM@home
Posted Tuesday, October 16, 2012 7:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 921, Visits: 3,743
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
John Mitchell-245523
Posted Tuesday, October 16, 2012 7:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 4,428, Visits: 7,207
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
kapil_kk
Posted Tuesday, October 16, 2012 7:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151
thanks Chris it works fast now
Post #1373256
kapil_kk
Posted Tuesday, October 16, 2012 7:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151
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...
Post #1373257
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.