SQL Server Studio 2005 slow down.

• 
  I forgot to say that my large scripts are 3500 lines or more.

  unionguy March 14, 2007 12:42

  - 0 +
• 
  Hi there,
  
  
  Sorry to see you're having difficulties. Unfortunately SQL Prompt has problems coping with large scripts that contain few GO blocks since it uses these for accurately scoping variables etc. We've had to be quite focussed about what goes into the 3.1 release so we've not been able to look at this problem, however we intend to address it for the next 3.x release of SQL Prompt.
  
  If you can somehow add a whole bunch of GO batch markers into your scripts you should find that the situation improves significantly. You might try turning off support for variables (you'll find this on the Candidates tab in the options dialog at the bottom) however I suspect that on a script of the size you're editing you'll experience only a marginal improvement. As I say, the best option is to get regular GO markers in there if at all possible.
  
  Hope that helps.
  
  
  Thanks,
  Bart

  Bart Read March 14, 2007 12:46

  - 0 +
• 
  The word "refactor" comes to mind. A 3500 line script is madness.
  
  My $0.02

  VisualDragon March 20, 2007 15:50

  - 0 +
• 
  My "slowdown", i.e., take over 90% of the CPU is with simply using the tab key to indent on a new line. The script is a well-commented 620 lines right now but the problem starts to manifest around 400 lines (a guess from what I've added in the last hour or so).
  
  This is hauntingly similar to the old Prompt SQL 2.1 "spin the CPU like crazy" era - I hope it doesn't last...
  
  By the way, I'm using the 3.1 beta...
  
  In case I should attract a similar "refactor" comment from VisualDragon, the code is clearly laid out, comprising a load of differing DML statements around two table-variables, with one line per column, per standards, with error-trapping logic thrown in... Refactoring opportunities minimal

  PDinCA March 20, 2007 18:06

  - 0 +
• 
  Any GO blocks in there? We use them for scoping purposes but we are coming up with something a bit more bullet-proof for the release after 3.1 because at the moment if you only use GO markers sparsely it can get pretty sluggish.
  Thanks,
  Bart

  Bart Read March 21, 2007 07:22

  - 0 +
• 
  It's one data-verification SP, so, no, there aren't any GO markers

  PDinCA March 21, 2007 12:19

  - 0 +
• 
  :shock: A 620 line stored proc? Ouch. I tend to think of 100 lines as a getting a bit on the large side.
  
  To be honest you're probably best off disabling SQL Prompt when you're working on that proc, at least for now, however I'm hoping we'll have a dev build of the next version of SQL Prompt available in a month or so that will address this issue. If you're feeling brave you might try refactoring and decomposing it into a number of smaller procedures and/or functions, but that's potentially a little risky so I could certainly understand if you were a bit reticent about doing that.
  
  Sorry I can't be more immediate help.
  
  
  Thanks,
  Bart

  Bart Read March 21, 2007 12:24

  - 0 +
• 
  Trouble with refactoring is that in place of a cursor I use a table-variable, which, in SS2K, cannot be passed to a child SP. As it chugs along verifying the physical presence and characteristics of downloadable files on a media server outside the SQL Server domain, via http request, it stows its results in another table variable and gives the set of exceptions back to the caller. I'm redesigning to persist the result as it takes over a hour on our Production server and Reporting Services will timeout way before even a tenth of the files are checked. That will reduce the number of INSERT statements by using a simple CRUD-style INSERT SP, but that's about all the refactoring I can do while using table variables (about 160 lines saved).
  
  I turned SQL Prompt off yesterday as I was waiting 30 seconds to apply an indent to 15 lines of code...
  
  Definitely looking forward to the 3.2 beta

  PDinCA March 21, 2007 12:44

  - 0 +
• 
  Sounds fair enough to me. It's one of those things I'd like to sort out sooner rather than later: we just had a mountain of work to do for 3.0 and 3.1 is mainly intended to fix some hangs and exceptions that people were experiencing, but there are new features and some performance improvements to get into the next version.
  Cheers,
  Bart

  Bart Read March 21, 2007 12:47

  - 0 +
• 
  I guess this would be a good time to put in for another menu item - SQL Prompt On/Off. Sure we can set it in options, but just like the cache refresh, we have to open another window to do that when what we really need is a quick-hit to just toggle something. I realize that we can't necessarily add a button easily to the toolbars to do that or define hotkeys (which may be in use already), but these sorts of items directly on the main SQL Prompt menu would be really helpful. I get hit by doing a REPLACE on a relatively large script (replacing an key value because the script runs piecemeal and I need to replace that value everywhere it appears). Have to wait 30+seconds to do the update regularly.
  
  -Pete

  paschott March 26, 2007 12:26

  - 0 +
• 
  Hello there,
  
  
  Our support for large SQL scripts is significantly improved in SQL Prompt 3.5. The release candidate is now available for download:
  
  http://www.red-gate.com/messageboard/vi ... php?t=5263
  
  I'd recommend you download it and give it a try.
  
  
  Thanks,
  Bart

  Bart Read July 18, 2007 09:51

  - 0 +

Add comment

Please sign in to leave a comment.