How can we help you today? How can we help you today?

Performance Issue with semi large scripts.

I'm dealing with semi large scripts on occasion (in the order of 600-800 lines) and have come across 2, slightly annoying, performance issues. The first happens when you attempt to paste a semi large script the whole application hangs until, I assume, Sql prompt completes parsing the pasted data.

The other time I run into this issue is if I grab the top slider window in SSMS to look at two portions of the same file it will again hang as it parses the new split window on top.

I'm not certain if there really is anything you can do about that as you need to parse the contents but maybe have it be a background type process so the UI does not appear to be hung.


Travis DeAngelis
Sr. Software Engineer
AdvancedMD Software
ikatsu
0

Comments

1 comment

Sort by
  • Bart Read
    Actually we have a fix for this in the offing. The performance hit is partially down to parsing the file, but that generally doesn't take too long. Mostly it's due to SQL Prompt scanning the file for aliases which it will then use if you've got automatic alias assignment switched on. There is a maximum time limit applied to this however, it's possible that this, plus parsing, plus Management Studio overhead can run to 10 - 15 seconds, and this can sometimes lead to a worse problem where Management Studio hangs for 1 - 3 minutes whilst chewing CPU time for no good reason. This is actually nothing to do with SQL Prompt, but is a bug in Management Studio that is apparently a workaround for a race condition in the application (it's great).

    Anyway, back to Prompt, there are a couple of things we've done since the beta to address this:

    (1) If automatic alias assignment is switched off we no longer scan for aliases, since they'll never be used anyway.

    (2) We've added a couple of options related to alias learning:

    - Learn aliases as I type. Does exactly what it says on the tin, and doesn't incur much of a performance hit doing so. Switched on by default.
    - Learn aliases when I open a file or paste text. Again, fairly self-explanatory. Switched off by default since this is the one that causes the performance hit.

    Neither of these options has any effect if automatic alias assignment is disabled, so you won't get the performance hit from (2) in the final release even if it's switched on when auto-aliasing isn't.

    I may also be able to improve the performance of alias scanning somewhat before the final release, but this is probably less important than doing the right thing in the scenarios I've outlined above, and in any case there will still be a noticeable delay if (2) is switched on when auto-aliasing is enabled. What it means is that within the alloted time I'll be able to scan a bigger chunk of SQL for aliases.

    It wouldn't be so bad if I could do the scan in a background thread but this causes problems with SSMS, and even bigger problems with QA, because you just can't access the editor contents from multiple threads unless you want it all to blow up in your face.

    Hope that explains the situation.


    Thanks,
    Bart
    Bart Read
    0

Add comment

Please sign in to leave a comment.