TuningProc

• Jeff Moden

  SSC Guru

  Points: 1004435

  More actions

  June 20, 2008 at 8:35 am

  #831821

  Just a thought... Sometimes, it's faster to export the data to a file, transmit the file to the other server or put the data in an area accessible to both servers, and import the data on the other side.

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

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 20, 2008 at 9:17 am

  #831881

  Jeff Moden (6/20/2008)

  ---

  Just a thought... Sometimes, it's faster to export the data to a file, transmit the file to the other server or put the data in an area accessible to both servers, and import the data on the other side.

  I actually currently have a situation where having SSIS pull data into ADO recordsets from one server, then push it to the other server, is faster than running a distributed transaction. That's on servers that are in the same room as each other, not something that's transcontinental or anything like that.

  (The plan is to move both databases onto the same server next weekend, which will eliminate that need, but for the last six months, the SSIS package has been doing it quite well.)

  I've also found, in prior years, when moving data between servers in different geographic regions, that using table variables to avoid transactions, can make a huge difference in speed.

  In the rewrite of the proc, all I did was slap together a rough draft. It will need serious tuning and testing. It might need to do something external to SQL Server (like the files you mentioned), or it might not. It might benefit from table variables, or might not. No way to tell without testing.

  I just followed the basic rule that getting rid of a multi-thousand row cursor is almost always a good place to start when performance tuning. 🙂

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Mike Levan

  SSChampion

  Points: 14201

  More actions

  June 20, 2008 at 12:29 pm

  #832007

  Hey I got a new error from your proc.

  Msg 209, Level 16, State 1, Procedure xSynPortalToSolomon_ProgTEST, Line 117

  Ambiguous column name 'programid'.

  I checked at that column everything looks fina in my tables not sure y the colum name is ambiguous

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 20, 2008 at 12:44 pm

  #832014

  Line 117 points at this update:

  update #programs

  set programid = programid

  from #tmpProgMapping

  where LOWER(TaskPrefix) = LOWER(Prefix)

  It's probably supposed to be this

  update #programs

  set #programs.programid = #tmpProgMapping.programid

  from #tmpProgMapping

  where LOWER(#programs.TaskPrefix) = LOWER(#tmpProgMapping.Prefix)

  Oh, as an extra, if your server is not case-sensitive, those LOWERs are superfluous. Consider removing them. You may also want to experiment with adding indexes on the temp tables to support the joins.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass