Databases
StackOverflow2013
Postsdbo
POSlow stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSlow stored procedure
    primarykey
    data
    text
    <p>I currently have a stored procedure that copies content from one table to another.</p> <p>However when it is trying to only insert 27 new rows it continues on for over 12 minutes (after which point I stopped it) it said Affected 27 rows 4 times, however changes were not made.</p> <p>Can you spot any reason the following SP would be slow?</p> <pre><code>SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[sp_CopyCompanyContent] ( @intCopyFromCompanyID Int, @intNewCompanyID Int ) As Begin /* RaisError If any of Odl/New Company ID's are 0 */ If (@intCopyFromCompanyID = 0 Or @intNewCompanyID = 0) Begin RaisError('New Company ID or Old Company ID can not be 0', 16, 1) Return End /* Create Temp Table For the Content Sections */ If Object_ID('tempdb..#ContentSections') IS Not Null Begin Drop Table dbo.#ContentSections End /* Have to get all the existing data for the Old company we are copying from. Have to also add the Max(ContentSectionID) From ContentSection. Max(ContentSectionID) + The Identity (Added further down due to laziness) will be our seed for the ContentID */ Select CS.ContentID, CS.SectionID, CS.MenuOrder, @intNewCompanyID NewCompanyID, CS.CompanyID OldCompanyID, CS.SubMenu, CS.Link, CS.HeaderMenu, CS.ParentContentID, CRS.* Into dbo.#ContentSections From dbo.Company COMP Join dbo.ContentSection CS On COMP.Company_id = CS.CompanyID Join dbo.Content CONT On CONT.ContentID = CS.ContentID Cross Join ( Select MAx(ContentSectionID) MaxContentSectionID From dbo.ContentSection CONT ) crs Where COMP.Company_id = @intCopyFromCompanyID Order By COMP.Company_id /* We now need to create a table for the existing content for the old company. Also have to create the seed. Same principle as above. */ If Object_ID('tempdb..#Content') IS Not Null Begin Drop Table dbo.#Content End Select CONT.*, CRS.* Into dbo.#Content From dbo.Company COMP Join dbo.ContentSection CS On COMP.Company_id = CS.CompanyID Join dbo.Content CONT On CONT.ContentID = CS.ContentID Cross Join ( Select MAx(ContentID) MaxContentID From dbo.Content CONT ) crs Where COMP.Company_id = @intCopyFromCompanyID Order By COMP.Company_id /* Add Identity to each of the tables we have created above. The ID fields will add to the Max of each table to mimic what the future seeds will be. */ exec('Alter table #ContentSections Add ID Int Identity(1,1)') exec('Alter table #Content Add ID Int Identity(1,1)') /* Add content data from the temp table. */ Insert Into dbo.Content ( Title, Content ) Select Title, Content From dbo.#Content /* Have to the Content table up to the content sections table as this contains what ID has been add to the Content Table. */ Insert Into dbo.ContentSection ( ContentID, SectionID, MenuOrder, CompanyID, SubMenu, Link, HeaderMenu, ParentContentID ) Select C.MaxContentID + C.ID, CS.SectionID, CS.MenuOrder, CS.NewCompanyID, CS.Submenu, CS.Link, CS.HEaderMEnu, CS.ParentContentID From dbo.#Content C Join dbo.#ContentSections CS On C.ID = CS.ID End </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRyan McDonough
    1. USRyan McDonough
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSlow stored procedure
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSlow stored procedure
      1. This table or related slice is empty.
      1. VTModeratorReview
    1. COHave you used Query Analyzer to display an execution plan?
      singulars
      1. POSlow stored procedure
      1. USDominic Zukiewicz
    2. COA good place to start would be to run the stored proc with SET STATISTICS IO ON and get the execution plan. Might give you a better understanding of where the problem lies?
      singulars
      1. POSlow stored procedure
      1. USFred
    3. COFor some reason, after adding that the query ran perfectly (Not sure if that is possible)
      singulars
      1. POSlow stored procedure
      1. USRyan McDonough
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload