Performance and loading large files...

dbawhite

Installed Prompt 3 and had a couple of issues
Just wondered if anybody else has had any similar issues:

I am testing using ISQL against SQL 2005.

Connected to server instance , and cached master , then selected my user database and it cached that database also.
Checked the memory used for ISQLW was up to 232MB :?

So I exited QA and back in again... and happily running at "normal" levels.

Another problem I encountered is I tried to open in Query Analyser our Database creation script - size is 7 MB with over 220,000 lines of code.
And Query Analyser hangs.. unsure what is happening... So killed off ISQLW - disabled SQL prompt and everything back to normal it loads the file and I can execute it.
So in the interim I have switched SQL prompt off, so I can test my scripts.

Overall, I did try earlier versions but it wasn't scalable .. initial testing it does look useable against a larger database (200 + tables) as opposed to using Northwind!

Bart Read

Hi there,


We are aware of some problems when working with large scripts. SQL Prompt basically uses the GO batch markers for scoping, but it still sometimes seems to parse too much, which I suspect is why it's bugging out. We'll be addressing these issues for the patch release, but in the meantime I'd suggest you avoid working with large scripts.

I'm interested in the memory usage though. Can you reproduce this? Does it happen every time you work with the databases, or just when you cache them for the first time. Some of the data structures SQL Prompt uses are pretty memory hungry (it's that old performance/memory usage trade-off again), however you should see roughly the same memory usage every time you use Prompt against a particular database. One other thing that occurs with SSMS, which may also occur with QA is that if you minimise and then maximise the application the memory usage rapidly decreases and then stabilises at a much lower level. This peculiarity seems to be particular to SSMS regardless of whether Prompt is running or not, so I wonder if perhaps this might also help for QA.

Thanks for posting this, I'm sorry I can't be more directly helpful now, but rest assured we'll look in to the performance issues on large scripts.


Thanks,
Bart

dbawhite

I think it was a first time cache.. but will do more testing next week.

Thanks for the tip on min. and maxing the window for memory releasing, that seems to work a treat.



regards,

Bart Read

Hmm, that's really interesting. I wonder if it's a result, in both cases of the CLR being hosted within an unmanaged application. It's not something I've investigated in great deal so I might have a chat with my colleague Lionel about it.
Thanks,
Bart