Slow

• 
  That's interesting, we've not seen this. Usually it performs very well, and much better than version 2, especially in SSMS. QA is generally a bit slower though. It may be because we have a significant amount of debug output/logging going on, in which case the situation will improve for the final release. It may also be that you have insufficient physical memory and the poor performance is being caused by swapping.
  
  Finally it might be that SQL Prompt is taking a while to load your meta-data: this would particularly be the case for a large database.
  
  
  Thanks,
  Bart

  Bart Read September 08, 2006 10:13

  - 0 +
• 
  We have a fairly complex database (1200 tables, 4200 SPs), and it takes a good minute or so for SQL Prompt to start up. It would be nice if SQL Prompt kept the snapshot of a DB across instances of SSMS. I typically open and close different projects to work on different sections of code, and each time I do this, SQL Prompt needs to snapshot the database. This results in about 1-2 minutes of time that I have to wait to start using SSMS on startup.
  
  Thanks
  Rick

  rptodd September 13, 2006 11:28

  - 0 +
• 
  It creates a snapshot the first time, but then every subsequent time you access the same database it pulls that (encrypted) snapshot back from the local disk. The UI may well not explain it in quite that way. However, from the snapshot it then creates a number of complex data structures to provide code completion and this takes some time, which is probably what you're seeing. For larger databases merely loading the snapshot from disk can take a little time as well.
  
  For example, I have one database I use for occassional testing that has 25,000 tables, 25,000 views and about the same number of SPs, and in total including columns and everything else there are over 500,000 objects in the database. Simply building up the data structures used for the code completion takes 90 seconds, and this doesn't include the time to load the snapshot, which even with compression weighs in at a whopping 70MB. To be honest I'd need to measure the amount of time it takes to load the snapshot as I can't remember exactly how long it is off the top of my head. What I can tell you is that to initially pull the meta-data from the database and create the snapshot takes 15 - 20 minutes, and that loading the snapshot is significantly quicker than this: I have 3 minutes floating around in my head, but as I say, I'd need to measure it again to be sure.
  
  So 1 - 2 minutes to reload the snapshot and rebuild the data structures is probably not unrealistic. However you need to weigh this up against the time to initially load the meta-data from the database (you can clear the cache if you want to measure this), and against the responsiveness of the application once the snapshot has been pulled back off the disk. Essentially the cost of this is amortized across every keystroke when you're working with your database in SSMS or QA, so the best thing to do is keep SSMS/QA open for as long as possible rather than restarting often if you are able to avoid it.
  
  
  HTH,
  Bart

  Bart Read September 13, 2006 11:40

  - 0 +

Add comment

Please sign in to leave a comment.