0

In my code, I have several SQL scripts that I want to parse using TSql130Parser in order to refactor them. But this process takes a long time - is there a way to increase the speed of this?

Also I have another question that TSql130Parser can get bulk as parameter? If it is possible, how can I do it?

This is my method that I use for parsing procedures by passing content of procedure as query in method parameter:

public string ParseProcedureQuery(string query, out string procedureName)
{
    procedureName= "";

    try
    {
        TSql130Parser parser = new TSql130Parser(true);

        IList<ParseError> errors;

        var fragments = parser.Parse(new System.IO.StringReader(query), out errors);
        var proc = fragments.ScriptTokenStream.Where(x => x.TokenType == TSqlTokenType.Proc || x.TokenType == TSqlTokenType.Procedure).Select(x => x.Text).FirstOrDefault();

        if (proc == null) 
            return string.Empty;

        var index = fragments.ScriptTokenStream.FindIndex(c => c.Text == proc);
        var name = fragments.ScriptTokenStream
                            .Select((value, ind) => new { value, index = ind })
                            .Where(pair => pair.index > index && pair.value.TokenType != TSqlTokenType.WhiteSpace)
                            .Select((value, ind) => new { value.value.Text, index = ind }).Where(y => y.index < 3).Select(p => p.Text);

        procedureName = string.Join(String.Empty, name);

        var processedQuery = string.Join(" ", fragments.ScriptTokenStream
                                   .Where(x => x.TokenType != TSqlTokenType.MultilineComment)
                                   .Where(x => x.TokenType != TSqlTokenType.SingleLineComment)
                                   .Where(x => x.TokenType != TSqlTokenType.WhiteSpace)
                                   .Select(x => x.Text));
        return processedQuery;
    }
    catch
    { }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Maryam
  • 53
  • 8
  • See my comment for another posting this morning : https://stackoverflow.com/questions/73000984/why-does-this-query-take-a-long-time-to-run#comment128937397_73000984 – jdweng Jul 16 '22 at 14:59
  • What's your definition of "a long time", and why does it matter? – Ira Baxter Jul 16 '22 at 18:09
  • @jdweng `TSqlParser` is a client-side parser, it does not use `SqlClient` or connect to a server – Charlieface Jul 17 '22 at 03:19
  • Seems like you could optimize some of this. Remove `proc` and just do `var index = fragments.ScriptTokenStream.FindIndex(x => x.TokenType == TSqlTokenType.Proc || x.TokenType == TSqlTokenType.Procedure);` Also `var name = fragments.ScriptTokenStream.Skip(index + 1).Where(token => token.TokenType != TSqlTokenType.WhiteSpace).Take(2).Select(p => p.Text);` Try also setting `SqlEngineType.Standalone` – Charlieface Jul 17 '22 at 03:21
  • @IraBaxter I call this method in a loop and this method in sql parser takes time so I want to make this process faster – Maryam Jul 17 '22 at 04:53
  • now I use parallel.foreach for calling this method but I want to improve it – Maryam Jul 17 '22 at 05:34
  • And the TSQLParser doesn't offer a feature like "Prepare" (pre-compile a SQL string) like ODBC/JDBC? – Ira Baxter Jul 17 '22 at 16:12

0 Answers0