SQL Prompt
-
Does anyone have anything to say about this product? I won a copy from SSC and I've given it a little go over the last few weeks, but it's not lived up to my expectations.
Simple things don't seem to work consistently and this is very frustrating. For example, the software regularly 'forgets' that I have told it not to auto-start when QA opens. As it takes too long to initialise a database, this gets very annoying as I keep having to reach for the off button.
The software also has trouble remembering my preferences for formatting etc, so when I do use it I find that all my T-SQL appears in upper case and I then have to trawl the menu to sort that out.
As this is such basic functionality, I'm wondering if there is a problem with my PC not remembering these changes, but no other software has this trouble.
The performance of SQL Prompt is what has prompted me to finally stop using the software. It takes an age to initialise, even if I open a new QA window that is connecting to an already initialised DB, that I'd rather switch it off than wait. I've also found that when switched on it interfers with the movement of the cursor, as it jerks about when scrolling/moving through T-SQL queries. Perhaps I can overcome this by more careful tuning of the options? But, if it isn't going to remember those options then I can't see me using it again.
Thoughts anyone?
EDIT: Re-reading this, I've made it sound like SQL Prompt is rubbish and doesn't work: This isn't the case. It works very well when initialised, but the simple problems highlighted above are enough to put me off using it.
-
A bunch of at work downloaded the 14 day trial yesterday to play with. The idea in theory is great but the implimentation is poor.
I didn't notice any issues with it forgetting settings. Our main beef is the performance.
It seems that it was constantly "indexing" itself. Every time i opened a new query window it would pop up and start "indexing" and it usually took 5 to 10 minutes to do that. durring the time my systems was being bogged down and SSMS would lock up ocasionally in the middle of me typing. Not to mention the 15-20mb of ram it was constantly using.
I'll be the first to admit out database structure needs a good cleaning up. We have over 3500 tables, 3000 views and 2000 stored procedures in our main production database.
I think that perhaps we have a bit too large of a system for it to efficently manage all the data it needs to work with.
We did have high hopes and were planing to buy a 10 license pack but i don't think we will now. We still might get 1 to give to new programmers so they have a nice quick reference to help learn our system.
Maybe they'll work on the performance and we'll come back and take another look at version 4.
-
My experience with SQL Prompt has been nothing but excellent. Granted our production DB has about 500 procedures and only 100 tables and 50 views, but it works very well.
I'm sure it has limitations with large DBs but, then again this is the first release is it not? I mean, how many versions/updates of windows are there? And they still don't have it right.
All in all, I think it's a great tool. The time benefit of writing code is awesome.
Just my humble opinion. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Hi everyone,
Sorry for the delay in responding to you, although in my defence I will say this is my second attempt. My first attempt was swallowed up this morning by the ether when I clicked the “Post Reply” button and at that point I decided that for the good of humanity, and particularly my colleagues, I should walk away from the computer and get some lunch.
I want to deal with Michael’s concerns first since there are a couple of problems he’s had that have left us scratching our heads: forgetting options and re-enabling itself in Query Analyzer after it’s been switched off.
I actually think these problems are related because all of this information is stored in a few different files in a subdirectory in your local profile. So for example on my machine everything sits in “C:\Documents and settings\bart.read\Local Settings\Application Data\Red Gate\SQL Prompt 3”. My questions to you are therefore:
(1) Is there any reason that SQL Prompt would not be able to access this area of your local profile?
(2) Are you running Query Analyzer using “Run As…” with a user account that, for whatever reason, does not have a local profile?
(3) Is there anything that happens when you log in/out that would modify or clean up your local profile for some reason?
(4) Can you verify that files are update if you modify settings in the Options dialog and then click OK? E.g. Check that EngineOptions.xml is updated if you change the completion popup delay or the qualification options.
As I say, these issues have left us scratching our heads: they’re not something we’ve seen before nor had anyone else report them so at the moment I’m afraid I’m at a bit of a loss. It does seem to me like it’s more likely to be an issue with the local profile on your machine than with SQL Prompt, however since you don’t experience this with any other software I can’t be sure about that.
On the subject of SQL Prompt taking ages to initialise when you open Query Analyzer, this is something we’ve had people experience where they connect to SQL Server instances over a VPN, or if there’s some other reason that some/all of the instances they work with are periodically unavailable, e.g. using a laptop with a wireless connection that is sometimes plugged into different networks. The reason this happens is that there is a bug in SQL Prompt 3.0 that means it tries to unnecessarily query server instances to find out their version. We’ve fixed this in SQL Prompt 3.1, which should be released at the end of next week or early the week after. If you’re particularly keen to find out an early access release is available at
http://www.red-gate.com/MessageBoard/viewtopic.php?t=4487
SQL Prompt 3.1 will also fix a number of other problems, mainly exceptions that a few users have reported, but also:
- It now prefers objects that exactly match the current filter string and pulls them to the top of the completion list.
- It allows overtyping of closing characters, such as parentheses, and provides much smarter support for automatically inserting and deleting them.
On the subject of “indexing” mentioned by both Michael and abarber, there are a few things to say.
The first time you connect to a database SQL Prompt will load the meta-data and cache it on disk, which for a large database could take a long time. SQL Prompt will only reload the meta-data if you manually refresh it in the Cache Management dialog (click on the SQL Prompt > Cache Management menu item), or automatically the next time you start an editor session and access that database if the “Refresh the cache automatically” option is set in the same dialog. So initially I’d say if you’re having problems with excessive loading of meta-data that you uncheck this option since it’s most useful only if you’re working with databases whose schema is frequently modified.
Once the data is loaded, either directly from the database or by pulling it back from the cache on disk, SQL Prompt will build up data structures for code completion that allow fast filtering, switching between lists etc. In both 3.0 and 3.1 whilst the meta-data itself is shared between all editor windows accessing a particular database the completion structures are not because there are generally differences between them for each editor window based on the current context. This has advantages in that it keeps things relatively simple, however the downside when working with larger databases is that there is some overhead when opening a new editor window, even on to a database that you’re already working on, and of course it uses more memory.
Therefore in the next 3.x release we are going to be removing these separate data structures and sharing them between each editor window onto a particular database. This makes things trickier behind the scenes because we have to do some extra work by modifying the data structures when you switch editor window, but the pay-off is better performance because the number of changes is small relative to the amount of work involved in rebuilding the data structures from scratch, and also lower memory usage. The amount of memory saved will obviously be very dependent upon the database you’re working with, but the performance improvement will be significant.
Having said all of that I’m somewhat surprised to see that abarber’s database is causing so many problems, although obviously I don’t know the spec of the machine you’re running. Sure, 3500 tables etc is a big database, but it’s still an order of magnitude smaller than one of the databases we test on which has around 30000 tables, 30000 views, 30000 stored procs etc, and I should point out that this is a real database from Peoplesoft or SAP (can’t remember which offhand). To give you an idea with 3.1 that’s taking about 25 minutes to load from scratch on my development machine, and about 12 minutes to load from cache. This machine has a decent amount of memory but is by no means top of the range; it’s a three year old 3GHz P4 HT with 2GB of RAM.
Most of the time taken to load it from cache seems to be used building up the columns data structure (there are somewhere between 3 and 400,000 columns in this database – it’s truly nutty) that’s used for the “all columns” list. Now the thing is that we’ve found that for a lot of people this list is of limited utility so in the next 3.x release it’ll be switched off by default (you’ll be able to get it back easily enough), which drops the time taken to pull the data back from disk down to about 2 minutes, most of which is taken up by loading and decompressing the data. It’s still not rocket ship fast, but it’s a truly gargantuan database so it’s about as good as it’s going to get. Based on that I reckon abarber’s database would take about 20-30 seconds, which is certainly no worse than the delay that I experience when I open a project in Visual Studio 2005.
In a month or so we should have a reasonably stable development build available with all these changes in and you’d be more than welcome to give it a whirl if you fancied it. If you are interested just drop me a pm.
On the memory usage front I have to say I think you’re getting off pretty lightly if SQL Server Management Studio is using only 15 – 20MB. I’m not sure how that’s even possible since on my machine and without SQL Prompt or any other add-ins installed it uses a good 60MB before I even do anything, but then it is basically Visual Studio 2005 minus Intellisense and some of the other bits. Once you add auto-completion into this and open a few query windows this value is only going to go upwards. By comparison, when I’ve got the SQL Prompt project open Visual Studio 2005 routinely uses 500-600MB, and I might only have a couple of source files open, and bear in mind that I don’t tend to use the more advanced features of VS 2005. I’m not trying to make excuses for using memory inefficiently, but I am saying if you want this extra functionality on top of that provided by QA or SSMS then there is some cost involved, particularly when you’re talking about an application that might have to deal with an awful lot of data and still remain responsive as you type. SSMS (and to a lesser extent QA) + SQL Prompt basically gives you a SQL IDE and so you’re going to end up with something that uses an IDE-like quantity of memory.
Finally I just want to say thanks to Jason for the kind comments. I’m really glad you like SQL Prompt, and we’ll obviously keep making it better as time goes on.
Thanks,
Bart
-
Thanks for the update Bart!
FYI, my system a 2.2ghz p4 (original p4s) with a gig of ram. not a barnstormer by any means but i like to think no slouch either.
I'll put a note in my calendar to check back and retry the new build in a month or so. Like I said the idea is great and I'd love to have a tool to help me write some nice clean SQL.
-
Thanks for the excellent response, Bart.
I've checked the UIOptions.xml and EngineOptions.xml files and they do update when I make changes to SQL Prompt options. I'm going to check with the IT guys here to see if there is something wrong with my profile that is making it 'forget' these changes.
The DB I use mostly is over 50GB in size, so it does take a fair amount of time to initialise. However, the things you mentioned for the new version sound like they'll definitely help matters, so I'll be giving that a go.
And just to re-iterate, SQL Prompt works very well apart from the niggles I have mentioned, and the WOW factor when I showed it to a colleague was most impressive
-
Thanks for the positive feedback. I'll keep you guys updated with what's happening. I guess it probably makes sense for me to post announcements here since there seem to be a few members who are interested.
I must admit I'm still baffled by your lost settings problem Michael, and the fact that it's writing the files correctly only makes it stranger. Within a given session when you're logged in, if you exit your SQL editor and the reopen it does SQL Prompt remember its settings, or is it still forgetting them in that situation as well?
You'll also be glad to know that we don't attempt to load the entire 50GB of your database: we're just interested in the schema and don't look at the data at all, but still if a database is that large it usually suggests a large and/or complex schema.
If anything else comes up please feel free to come back to me here. I'll be checking this forum fairly regularly from now on.
Thanks,
Bart
-
Well, it now gets more or less bizarre, depending on your view, because this morning SQL Prompt has remembered all my settings from yesterday.
I'll keep monitoring it each day and see if I can pin down the exact scenario that settings are lost.
-
Hmm... well I *think* that's probably good, but on the other hand if it's intermittently misbehaving that's not good. Thanks for keeping me up to date. Let me know if it starts playing up again.
Cheers,
Bart
-
Hi again,
I just wanted to let you know that SQL Prompt 3.1 has been released. You'll find improvements in a number of areas, however we're still scratching our heads over why QA integration doesn't remain disabled. You should find this is less of an issue in 3.1 anyway since it shouldn't significantly slow down QA startup because we've stopped it trying to connect to databases unnecessarily.
I've posted a more complete list of fixes and improvements at:
http://www.simple-talk.com/community/blogs/bart/archive/2007/03/23/21197.aspx
As suggested in this post, even if you're not interested in the new Management Studio Express support, I'd definitely recommend the upgrade.
Thanks,
Bart
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply