Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Question Unanswered: Stopping a stored procedure

    What is the command to stop a stored procedure from continuing to run? I have a procedure that has a problem, and due to a bug it runs wild in some scenarios, so I need to be able to stop it.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Look up the kill command in BOL. It should give you what you need.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Don't "kill" it in production. In fact, you should remove the offending code from prod env all together, until you've proven to yourself and your dba that the code is safe to be redeployed. Is it "running wild" because you have a WHILE loop and are missing adequate conditions to properly end it?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Code

    The offending code is only in our development environment. There is no loop / while statement. We're still trying to figure out why it is running so long in some scenarios, and we'd like to be able to stop it.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    where is it running from?

    Query Analyzer?

    Just hit stop

    If it's from an application, find the spid of the procedure (look at sp_who2 active) and then kill spid

    And why don't you post the sproc...we could all use a good laugh...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    It's running from an application. I was hoping for a different solution than killing the spid, but that's the one we'll go with.

    Here's the SP; it's used to put results into a table that is then used by a reporting tool, and has two validations that return results to the app if they are violated.

    CREATE procedure [dbo].[st_report_store_trade_area] @VSearch as integer, @userTableID as integer
    as
    /*Generates results for Vendor Search: Store Trade Area Report */

    /*Declare Variables */

    declare @count as integer

    set @Count = (select count(vsr.zip_code)
    from vendor_search_Trade_area vsta
    join org_model_data om on om.org_id = vsta.org_id
    left outer join
    (select distinct vn.vendor_number, vn.vendor_id, vn.vendor_name, vdz.zip_code
    from vendor_search_results vsr
    join vendor_distribution_Zip_codes vdz on vdz.vendor_id = vsr.vendor_id
    join view_primary_vendor_name vn on vn.vendor_id = vdz.vendor_id
    where vsr.vendor_search_id = @VSearch
    and select_vendor = 1) as VSR
    on om.postalcode = vsr.zip_code
    join distance_type dt on vsta.model_Criteria_type_id = dt.distance_type_id
    where vsta.vendor_search_id = @VSearch
    and om.model_criteria_id = 30
    and om.org_criteria_value <= dt.distance)

    /*clear table */

    delete report_store_trade_area
    where user_table_id = @UserTableID

    /*Validate Parameters */
    -- Validation verifies if the search result contains stores. If no stores, then report is useless
    if not exists (Select * from vendor_search where vendor_search_id = @VSearch and is_store = 1)
    begin
    Return 20
    end

    -- Validation verifies the size of output. If it will exceed the max allowed, the report will stop

    if @Count > 10000
    begin
    Return 19
    end



    insert into report_store_trade_area
    select @UserTableId,
    o.org_number,
    o.org_name,
    om.postalcode,
    z.zip_location_name,
    z.zip_state_code,
    hh.hh,
    om.org_criteria_value as distance,
    vsr.vendor_number,
    vsr.vendor_name,
    vsr1.weekday_circ,
    case when hh.hh is null then 0
    when hh.hh = 0 then 0
    when vsr1.weekday_circ is null then 0
    else vsr1.weekday_circ/hh.hh end as weekday_cov,
    vsr2.weekend_circ,
    case when hh.hh is null then 0
    when hh.hh = 0 then 0
    when vsr2.weekend_circ is null then 0
    else vsr2.weekend_circ/hh.hh end as weekend_cov,
    case when a.address_zip = om.postalcode then '*'
    else null end as store_zip
    from vendor_search_Trade_area vsta
    join org_model_data om on om.org_id = vsta.org_id
    left outer join
    (select distinct vn.vendor_number, vn.vendor_id, vn.vendor_name, vdz.zip_code
    from vendor_search_results vsr
    join vendor_distribution_Zip_codes vdz on vdz.vendor_id = vsr.vendor_id
    join view_primary_vendor_name vn on vn.vendor_id = vdz.vendor_id
    where vsr.vendor_search_id = @VSearch
    and select_vendor = 1) as VSR
    on om.postalcode = vsr.zip_code
    left outer join
    (select vn.vendor_id, vdz.zip_code, vd.distribution_quantity as weekday_circ
    from vendor_search_results vsr
    join vendor_distribution_Zip_codes vdz on vdz.vendor_id = vsr.vendor_id
    join vendor_distribution vd on vd.vendor_distribution_zip_code_id = vdz.vendor_distribution_zip_code_id
    join view_primary_vendor_name vn on vn.vendor_id = vdz.vendor_id
    where vsr.vendor_search_id = @VSearch
    and select_vendor = 1
    and vd.circ_type_id = 1) as VSR1
    on om.postalcode = vsr1.zip_code and vsr.vendor_id = vsr1.vendor_id
    left outer join
    (select vn.vendor_id, vdz.zip_code, vd.distribution_quantity as weekend_circ
    from vendor_search_results vsr
    join vendor_distribution_Zip_codes vdz on vdz.vendor_id = vsr.vendor_id
    join vendor_distribution vd on vd.vendor_distribution_zip_code_id = vdz.vendor_distribution_zip_code_id
    join view_primary_vendor_name vn on vn.vendor_id = vdz.vendor_id
    where vsr.vendor_search_id = @VSearch
    and select_vendor = 1
    and vd.circ_type_id = 8) as VSR2
    on om.postalcode = vsr2.zip_code and vsr.vendor_id = vsr2.vendor_id
    join org o on vsta.org_id = o.org_id
    left outer join address a on a.address_id = o.org_address_id
    join distance_type dt on vsta.model_Criteria_type_id = dt.distance_type_id
    left outer join zip_code z on z.zip_code = om.postalcode
    left outer join
    (select postalcode, postal_Criteria_value as hh
    from postalcode_model_data pmd where model_Criteria_id = 1) as hh
    on hh.postalcode = om.postalcode
    where vsta.vendor_search_id = @VSearch
    and om.model_criteria_id = 30
    and om.org_criteria_value <= dt.distance

  7. #7
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    In the version of the SP I just posted, I had removed what we believed was an error. In the initial version (that was failing) the Validation step for # of records was inadvertantly hardcoded to a specific vendor_search_id, instead of the variable @VSearch. This was allowing results too large for the reporting tool to pass through (its a web application, so we have file size restrictions for performance reasons).

    It appears to be working fine now, though any suggestions for improved performance are always welcomed.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The final INSERT is based on SELECT that utilizes 16 JOINs. Have you analyzed the execution plan of that SELECT?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Question Execution Plan

    I have run an execution plan against the Select query. I understand the results, and see that there is a 30% cost associated with my Left Outer Join to the following:

    left outer join
    (select postalcode, postal_Criteria_value as hh
    from postalcode_model_data pmd where model_Criteria_id = 1) as hh
    on hh.postalcode = om.postalcode

    However, while knowing I have 16 joins and the cost associated with each is valuable information, I don't see how I can improve that query (since all the information is necessary). My understanding is incorporating views wouldn't improve the performance.

    If you have any suggestions, I would definitely like to hear them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •