QUERY taking too much time in execution

  • 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/

  • 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/

  • Avoid cursor and try use while..loop

  • 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/

  • vyas (10/16/2012)


    Avoid cursor and try use while..loop

    Why?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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/

  • 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


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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

  • thanks Chris it works fast now

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • 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/

  • 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/

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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/

  • 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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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