slow stored procedure

• smknox

  Hall of Fame

  Points: 3275

  More actions

  May 30, 2009 at 8:06 am

  #73711

  I have a stored procedure with a complex select SQL statement that creates a temporary table. The SP takes 1 to 2 seconds to execute when asked simply to create the table.

  If I then want to select the entire or filtered table such as:

  Select n.* FROM NewTable n

  Or

  SELECT n.* FROM NewTable n Where n.ReportID=1

  It takes 30 seconds to execute

  What is the cause for this? The Select line by itself executes in under a second when run alone after the table is created.

• John H Marsh

  SSChampion

  Points: 11671

  More actions

  May 30, 2009 at 8:24 pm

  #1002440

  Hello,

  Are you using SQL Server 7 and are you creating the table via a Select Into statement? If so, may be this article answers your question:-

  http://www.sql-server-performance.com/articles/per/avoid_bottlenecks_locking_tempdb_p1.aspx

  Regards,

  John Marsh

  www.sql.lu
  SQL Server Luxembourg User Group