4

I'm using the Microsoft.Data.Schema.ScriptDom and Microsoft.Data.Schema.ScriptDom.Sql libraries to parse SQL and add "TOP #" to SQL select statements. We do this to limit the result set in certain situations.

I just getting started using the library and I've noticed that parsing the SQL is not exactly fast. I've not instrumented the code to actually measure the speed but it is noticeable, between half to a whole second. Which is not terrible but I would not call it fast either.

My code looks like this:

  var Parser = new TSql100Parser(true);      
  TextReader reader = new StringReader(sql);
  IList<ParseError> errors;
  var fragments = Parser.Parse(reader, out errors);

I've googled this and not found any complaints about speed so I'm wondering if I'm doing something wrong.

Craig Jensen
  • 95
  • 11
  • Is there any chance you could fix the piece that produces the sql? Otherwise Instrumenting the assembly is your best bet. – Ryan Gates Mar 05 '13 at 16:27
  • Just to clarify; the last line in the code above is noticeably slower. Everything else, even the code that rewrites the SQL, seems to perform just fine. I'll see if I can post additional code later today that better illustrates what I'm seeing. – Craig Jensen Mar 05 '13 at 16:33
  • 1
    Using the Stopwatch class I was able to capture the time needed to parse my test SQL vs the time needed to rewrite the SQL after parsing. If my math is correct the parse took 516.83ms and rewriting the SQL took 1.93ms. The test environment is Windows 7 64bit so Stopwatch is high resolution. – Craig Jensen Mar 06 '13 at 20:38
  • Parsing is expensive. Sticking some text into a string isn't. These are facts of life. –  Aug 06 '16 at 16:43

1 Answers1

3

It seems that Microsoft's library is not what you would call fast. I've done some more google searches and I'm not the only one to see this issue. There are some alternatives out there if you really need to parse SQL. They don't have the features of Microsoft's library but they seem to perform well.

Gold Parsing System

Poor Man's T-SQL Formatter

Craig Jensen
  • 95
  • 11
  • I don't know what you're expecting from parsing, one of the key methods of avoiding bottlenecks in database code is avoiding parses to the greatest extent possible. If you just accept that parsing is slow and minimize your use of it you'll make your life easier. –  Aug 06 '16 at 16:41