A half-megabyte script made our editor lag. Here's why.

I opened a data-import script this morning. Nothing exotic: one transaction, a dozen temp tables, sixty-odd INSERT … VALUES blocks with a few thousand rows of reference data, some UPDATE … JOIN clean-up between them. About 3,900 lines and 0.43 MB. That is a reasonably sized script, not a big one. People paste bigger ones into SQL editors every day.

The editor started lagging badly. Every character I typed showed up a beat late, and holding a key down turned into a slideshow. A SQL editor that can't keep up with typing is broken, whatever else it does well, so I stopped what I was doing and dug in.

Step one: reproduce it without the real data

The script was real data, so it couldn't go into the repository as a test. Instead I measured its shape: line count, average and longest line, how many string literals, how many statements of each kind, how much accented text. Then I wrote a small generator that produces a script with the same shape from made-up names and departments, the same every time. It comes out at about 3,500 lines and 0.46 MB, and it lives in the test suite next to the editor.

Next came a timing harness. It loads a script into the real editor component in a test window, puts the caret in the middle, types a few dozen characters one at a time, and times each keystroke from input to finished frame. It runs against the synthetic script by default. An environment variable points it at a local file instead, so I could check that the synthetic script behaves like the real one without the real one ever leaving my machine.

Both scripts were slow in the same way, so the synthetic one was a fair stand-in.

Step two: let the profiler point

I ran the harness under a sampling profiler. It wasn't subtle: 99.6% of the time was in one place, the code that works out where each statement starts. The editor needs that list for the ▶ run arrows in the gutter, the frame around the statement under the caret, and the Run all (224) count on the toolbar. The list is cached, but every keystroke changes the text, so it is rebuilt after every edit. Rebuilding it once per edit is fine. The trouble was how it was rebuilt.

What was happening

The editor already had a well-tested helper that answers one question: which statement contains this position? Run uses it to decide what to execute when you press Run with no selection. To answer, it tokenizes the whole script, splits it into statements, and returns the one that contains the position.

The gutter needed the full list of statements, so it was built from that helper in a loop, in pseudo-code:

list every statement in the script:
    position = start of the script
    while position is before the end:
        statement = which statement contains position?
            (tokenize the WHOLE script, split it into statements,
             return the one that contains position)
        add statement to the list
        position = end of that statement

Each call to which statement contains position? is linear: it reads the whole script once. Calling it once per statement makes the loop quadratic. The script had 224 statements, so every keystroke tokenized and split the full 0.46 MB script 224 times, and threw away all but one statement each time.

For a script of a few dozen lines the extra work is invisible, which is why nobody noticed. It grows with the square of the script: twice the statements means twice as many passes, each over twice as much text. Somewhere around a few hundred statements it crosses the line from "fine" to "I can see every letter land".

The fix

Split the script once and walk the result:

list every statement in the script:
    pieces = tokenize the script once, split it into statements once
    position = start of the script
    while position is before the end:
        statement = the piece that contains position   (no re-reading)
        add statement to the list
        position = end of that statement

I kept the loop's shape on purpose. Every rule that decides which statement contains a position (where a GO line belongs, trailing whitespace, the last statement in the file) still comes from the same code that Run uses, so the gutter arrows and Run can't disagree. The only change is that the expensive part happens once instead of once per statement.

Two tests come with the fix. The first runs the old walk and the new one side by side, on small edge cases and a slice of the synthetic script in all five SQL dialects, and requires the results to be identical. The second counts how many times the script gets split while building the list, and fails if it is ever more than once. A timing threshold would be flaky. Counting the passes is exact and catches the problem directly.

The same problem, twice more

Once you've seen this pattern, it's easy to spot. Oracle-family connections (Oracle, Db2, Teradata, Informix, SAP HANA) use an older statement scanner with the same shape: find every boundary, keep the one you asked about, throw the rest away, repeat for the next statement. Same fix, scan once. On the synthetic script that went from 204 ms to under a millisecond.

The harness covers the editor on its own, so I also profiled the full app while typing into the same script. That turned up one more whole-script pass. Signature help, the tooltip that shows a function's parameters while you are inside its parentheses, tokenized the entire script on every keystroke to find the nearest open parenthesis, and did it twice whenever there was no call to show:

on every edit:
    tokens = tokenize the WHOLE script
    walk backwards from the caret to the nearest open parenthesis

    if there is no call here:
        tokens = tokenize the WHOLE script again   (to manage the Esc dismissal)

A function call never spans two statements, and the editor already has every statement's range from the step above. Now signature help reads only the statement under the caret:

on every edit:
    statement = the cached statement range under the caret
    tokens = tokenize just that statement
    walk backwards from the caret to the nearest open parenthesis
    report positions relative to the whole script, as before

After

That's roughly 130 times faster per keystroke on the same script, comfortably inside a single 60 Hz frame. The remaining 5 ms is the one pass per edit that genuinely has to read the whole script: re-splitting the statements, the fold regions, and the comment state for syntax colors. Those grow linearly with the script, the way they should.

What I'm taking from it

The bug wasn't careless code. Each piece was reasonable and well tested on its own. A helper that answers "which statement am I in?" by reading the script is the simplest correct version, and it is exactly right when you call it once, as Run does. It went wrong when someone, quite reasonably, built a list by calling it in a loop. Code review won't catch that if every test script is twenty lines long.

So the synthetic half-megabyte script stays in the test suite, along with the harness that types into it and the test that counts passes over the script. The next time something in the editor starts doing work per statement, or per line, on every keystroke, a test will say so long before a user feels it.