Performance

• mtz676

  SSCrazy Eights

  Points: 9233

  More actions

  February 18, 2010 at 2:10 pm

  #211296

  I have a stored procedure where

  First : I drop a table table1...with 50000 rows(col 1 int(5), col2 varchar(8000)...col2 has delimited data with 26 '|' so it has 27 cloumns .

  Second : SELECT IDENTITY(INT,1,1) AS col1,'|' + col2 + '|' AS col2 INTO table1 FROM table2(50000 rows)

  Third : delete from table2

  Fourth : BULK INSERT(50000rows) table2 FROM 'textfile.txt' WITH

  Fifth : drop table table3(50000 rows)(27 columns in this table)

  Sixth : select clause where I split up the table1(col2) data into 27 columns into table3.

  This stored procedure takes between 1 to 2 minutes to execute .

  All the 3 tables have 50000+ rows.The rows will increase every day at around 50 additional rows per day. How do I get this to execute faster .

  Thanks

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  February 18, 2010 at 3:10 pm

  #1121425

  You are going to have to be a lot more specific about which statement is causing your performance problem. Check the links in my sig below for the best way to help us to help you

  ---

  
  Clear Sky SQL
  My Blog[/url]

• Jeff Moden

  SSC Guru

  Points: 1004439

  More actions

  February 18, 2010 at 6:45 pm

  #1121515

  sqlserver12345 (2/18/2010)

  ---

  I have a stored procedure where

  First : I drop a table table1...with 50000 rows(col 1 int(5), col2 varchar(8000)...col2 has delimited data with 26 '|' so it has 27 cloumns .

  Second : SELECT IDENTITY(INT,1,1) AS col1,'|' + col2 + '|' AS col2 INTO table1 FROM table2(50000 rows)

  Third : delete from table2

  Fourth : BULK INSERT(50000rows) table2 FROM 'textfile.txt' WITH

  Fifth : drop table table3(50000 rows)(27 columns in this table)

  [font="Arial Black"][highlight]Sixth : select clause where I split up the table1(col2) data into 27 columns into table3.[/highlight][/font]

  This stored procedure takes between 1 to 2 minutes to execute .

  All the 3 tables have 50000+ rows.The rows will increase every day at around 50 additional rows per day. How do I get this to execute faster .

  Thanks

  Credits to Navy Beans it's the step highlighted above... my recommendation would be to post that code, the table definition, and some sample data IAW the methods found in the article at the first link in my signature below.

  --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)