SQL Server Studio 2005 slow down.

unionguy

I have just installed SQL Prompt and it's great but it does not seems to handle large scripts very well.

The 1st problem I found was that when I arrow down over a PRINT statement is hangs.

Then 2nd problem I found was that if I have a DECALRE that is not at the top of my script and arrow down over it, it hangs.

The 3rd problem I have found is if I comment out code using /*Insert code here*/ and arrow down over this code it hangs.

The 4th problem I have found is the following line of code. The code is commented out so I don't know why it cause the tool to hand but it does.
--SET IDENTITY_INSERT TableName OFF

For small scripts this tool seems to work great but I do alot of data conversions and my scripts tend to be larger, so it is just slowing me down and I had to uninstall it.

Are there any settings that I turn on/off to fix solve my problems?

unionguy

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

Bart Read

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

VisualDragon

The word "refactor" comes to mind. A 3500 line script is madness.

My $0.02

PDinCA

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

Bart Read

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

PDinCA

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

Bart Read

: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

PDinCA

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

Bart Read

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

paschott

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

Bart Read

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