Optimizing this slow, brute force code

• starwiz

  Newbie

  Points: 1

  More actions

  December 24, 2003 at 10:10 am

  #161277

  I've got a huge inefficiency in my code that I'm trying to fix. I’m coding in VB.NET using ASP.NET and an MSSQL 2000 server, but I'm just concerned here with optimizing my SQL.

  I’m working in a temporary table that has an identical layout as another, non-temporary table in my database. Once I get the temp table how I want it, I need to insert everything from that table into my main table. Before I can do that, however, I need to delete all the records in the main table with certain fields that match a record’s in the temporary table.

  Right now, I have a method that builds one delete statement per record in the temporary table and then runs those statements on the main table. Since I’m dealing with the order of 50,000 records (at least) here, building and sending those statements takes forever.

  Is there a way I can accomplish the same thing without building and sending such a huge SQL call to the server? If so, how would I go about doing that? Is there some stored proc or syntax trick I could use?

  Thanks in advance for whatever help you can give,

  -Starwiz

• jxflagg

  Hall of Fame

  Points: 3817

  More actions

  December 24, 2003 at 11:48 am

  #487787

  Delete MainTable

  From MainTable, #TempTable

  Where MainTable.Column = #TempTable.Column