Optimized way to transfer from String to StringBuilder

Plateforme Level Extreme

Abonnement

Profil corporatif

Produits & Services

Support

Légal

English

Optimized way to transfer from String to StringBuilder

Message

05/02/2014 14:06:08

Thomas Ganss
Main Trend
Frankfurt, Allemagne

05/02/2014 13:36:50

Michel Fournier
Level Extreme Inc.
Petit-Rocher, Nouveau-Brunswick, Canada

Information générale

Forum:

ASP.NET

Catégorie:

Code, syntaxe and commandes

Titre:

Re: Optimized way to transfer from String to StringBuilder

Versions des environnements

Environment:

VB 9.0

OS:

Windows 7

Network:

Windows 2003 Server

Database:

MS SQL Server

Application:

Web

Divers

Thread ID:

01593079

Message ID:

01593316

Vues:

MIchel,

I skipped quite a few posts of this thread so I am unsure of yout intentions or the job needing to be done ;-) But I wonder if you could not parse the stringsof the different SQL segments into separate strings first and do the actual work on the shorter sub-strings (provided that some operations are not needed on all segments and then concat again? Probably already a few SQL parsers in exitance in Dotnet as well....

>Don't understand. What doesn't my StringBuilder() do that requires more work ?
>
>This is the actual method:
>
>

>    ' Parse a string for SQL Server
>    ' expC1 String
>    Public Function ParseForSQLServer(ByVal tcString As String) As String
>        Dim laReservedWord(28) As Object
>        Dim laReservedWordForFieldOnly(1) As Object
>        Dim lnCounter As Integer = 0
>        Dim lcString As String = ""
>        Dim lcWord As String = ""
>
>        ' Define the reserved words
>        laReservedWord(1) = "Order"
>        laReservedWord(2) = "All"
>        laReservedWord(3) = "Table"
>        laReservedWord(4) = "From"
>        laReservedWord(5) = "To"
>        laReservedWord(6) = "Delete"
>        laReservedWord(7) = "Insert"
>        laReservedWord(8) = "Default"
>        laReservedWord(9) = "End"
>        laReservedWord(10) = "Index"
>        laReservedWord(11) = "File"
>        laReservedWord(12) = "Current"
>        laReservedWord(13) = "Expand"
>        laReservedWord(14) = "Image"
>        laReservedWord(15) = "Read"
>        laReservedWord(16) = "View"
>        laReservedWord(17) = "Desc"
>        laReservedWord(18) = "Update"
>        laReservedWord(19) = "User"
>        laReservedWord(20) = "In"
>        laReservedWord(21) = "Public"
>        laReservedWord(22) = "Unique"
>        laReservedWord(23) = "Read"
>        laReservedWord(24) = "Site"
>        laReservedWord(25) = "Full"
>        laReservedWord(26) = "Key"
>        laReservedWord(27) = "Group"
>        laReservedWord(28) = "Like"
>
>        ' Define the reserved words for fields only
>        laReservedWordForFieldOnly(1) = "As"
>
>        lcString = tcString
>
>        ' Make sure we have a value
>        If lcString.Length = 0 Then
>            Return ""
>        End If
>
>        ' Add a space at the end to make sure to include the last word
>        lcString = lcString + " "
>
>        ' Scan all reserved words
>        For lnCounter = 1 To 28
>            lcWord = laReservedWord(lnCounter)
>
>            ' Field
>            lcString = oApp.StrTran(lcString, "," + lcWord + ".", ",[" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ",", ".[" + lcWord + "],")
>            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, " " + lcWord + ",", " [" + lcWord + "],")
>            lcString = oApp.StrTran(lcString, "," + lcWord + ")", ",[" + lcWord + "])")
>
>            ' INNER JOIN or AS clause in the fields
>            lcString = oApp.StrTran(lcString, " " + lcWord + " ", " [" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, " " + lcWord + ".", " [" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
>            lcString = oApp.StrTran(lcString, "=" + lcWord + ".", "=[" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
>
>            ' Where
>            lcString = oApp.StrTran(lcString, "(" + lcWord + ".", "([" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ")", ".[" + lcWord + "])")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ">", ".[" + lcWord + "]>")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "<", ".[" + lcWord + "]<")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
>
>            ' Update
>            lcString = oApp.StrTran(lcString, " " + lcWord + "=", " [" + lcWord + "]=")
>
>            ' Order
>            lcString = oApp.StrTran(lcString, "," + lcWord + " ", ",[" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, "," + lcWord + ",", ",[" + lcWord + "],")
>
>            ' Check for the last occurrence
>            If Mid(lcString, lcString.Length - lcWord.Length) = "." + lcWord Then
>                lcString = Mid(lcString, 1, lcString.Length - lcWord.Length) + "[" + lcWord + "] "
>            End If
>
>            ' Check for the first occurrence
>            If Mid(lcString, 1, lcWord.Length + 1) = lcWord + "." Then
>                lcString = "[" + Mid(lcString, 1, lcWord.Length) + "]" + Mid(lcString, lcWord.Length + 1)
>            End If
>
>            ' Field in update
>            lcString = oApp.StrTran(lcString, "," + lcWord + "=", ",[" + lcWord + "]=")
>
>        Next
>
>        ' Scan all reserved words for fields only
>        For lnCounter = 1 To 1
>            lcWord = laReservedWordForFieldOnly(lnCounter)
>
>            ' Field
>            lcString = oApp.StrTran(lcString, "," + lcWord + ".", ",[" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ",", ".[" + lcWord + "],")
>            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, " " + lcWord + ",", " [" + lcWord + "],")
>            lcString = oApp.StrTran(lcString, "," + lcWord + ")", ",[" + lcWord + "])")
>
>            ' INNER JOIN or AS clause in the fields
>            lcString = oApp.StrTran(lcString, " " + lcWord + ".", " [" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
>            lcString = oApp.StrTran(lcString, "=" + lcWord + ".", "=[" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + " ", ".[" + lcWord + "] ")
>
>            ' Where
>            lcString = oApp.StrTran(lcString, "(" + lcWord + ".", "([" + lcWord + "].")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ")", ".[" + lcWord + "])")
>            lcString = oApp.StrTran(lcString, "." + lcWord + ">", ".[" + lcWord + "]>")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "<", ".[" + lcWord + "]<")
>            lcString = oApp.StrTran(lcString, "." + lcWord + "=", ".[" + lcWord + "]=")
>
>            ' Update
>            lcString = oApp.StrTran(lcString, " " + lcWord + "=", " [" + lcWord + "]=")
>
>            ' Order
>            lcString = oApp.StrTran(lcString, "," + lcWord + " ", ",[" + lcWord + "] ")
>            lcString = oApp.StrTran(lcString, "," + lcWord + ",", ",[" + lcWord + "],")
>
>            ' Check for the last occurrence
>            If Mid(lcString, lcString.Length - lcWord.Length) = "." + lcWord Then
>                lcString = Mid(lcString, 1, lcString.Length - lcWord.Length) + "[" + lcWord + "] "
>            End If
>
>            ' Check for the first occurrence
>            If Mid(lcString, 1, lcWord.Length + 1) = lcWord + "." Then
>                lcString = "[" + Mid(lcString, 1, lcWord.Length) + "]" + Mid(lcString, lcWord.Length + 1)
>            End If
>
>            ' Field in update
>            lcString = oApp.StrTran(lcString, "," + lcWord + "=", ",[" + lcWord + "]=")
>
>        Next
>
>        ' Trim everything
>        lcString = Trim(lcString)
>
>        Return lcString
>    End Function
>

>
>This is an attempt I made with StringBuilder but it was slower:
>
>

>    ' Parse a string for SQL Server
>    ' expC1 String
>    Public Function ParseForSQLServer(ByVal tcString As String) As String
>        Dim laReservedWord(28) As Object
>        Dim laReservedWordForFieldOnly(1) As Object
>        Dim lnCounter As Integer = 0
>        Dim lcWord As String = ""
>        Dim loStringBuilder As StringBuilder = New StringBuilder
>
>        ' Define the reserved words
>        laReservedWord(1) = "Order"
>        laReservedWord(2) = "All"
>        laReservedWord(3) = "Table"
>        laReservedWord(4) = "From"
>        laReservedWord(5) = "To"
>        laReservedWord(6) = "Delete"
>        laReservedWord(7) = "Insert"
>        laReservedWord(8) = "Default"
>        laReservedWord(9) = "End"
>        laReservedWord(10) = "Index"
>        laReservedWord(11) = "File"
>        laReservedWord(12) = "Current"
>        laReservedWord(13) = "Expand"
>        laReservedWord(14) = "Image"
>        laReservedWord(15) = "Read"
>        laReservedWord(16) = "View"
>        laReservedWord(17) = "Desc"
>        laReservedWord(18) = "Update"
>        laReservedWord(19) = "User"
>        laReservedWord(20) = "In"
>        laReservedWord(21) = "Public"
>        laReservedWord(22) = "Unique"
>        laReservedWord(23) = "Read"
>        laReservedWord(24) = "Site"
>        laReservedWord(25) = "Full"
>        laReservedWord(26) = "Key"
>        laReservedWord(27) = "Group"
>        laReservedWord(28) = "Like"
>
>        ' Define the reserved words for fields only
>        laReservedWordForFieldOnly(1) = "As"
>
>        loStringBuilder.Append(tcString)
>
>        ' Make sure we have a value
>        If loStringBuilder.Length = 0 Then
>            Return ""
>        End If
>
>        ' Add a space at the end to make sure to include the last word
>        loStringBuilder.Append(" ")
>
>        ' For each reserved word
>        For lnCounter = 1 To 28
>            lcWord = laReservedWord(lnCounter)
>
>            ' Field
>            loStringBuilder.Replace("," + lcWord + ".", ",[" + lcWord + "].")
>            loStringBuilder.Replace("," + lcWord + ".", ",[" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + ",", ".[" + lcWord + "],")
>            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
>            loStringBuilder.Replace(" " + lcWord + ",", " [" + lcWord + "],")
>            loStringBuilder.Replace("," + lcWord + ")", ",[" + lcWord + "])")
>
>            ' INNER JOIN or AS clause in the fields
>            loStringBuilder.Replace(" " + lcWord + " ", " [" + lcWord + "] ")
>            loStringBuilder.Replace(" " + lcWord + ".", " [" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
>            loStringBuilder.Replace("=" + lcWord + ".", "=[" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
>
>            ' Where
>            loStringBuilder.Replace("(" + lcWord + ".", "([" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + ")", ".[" + lcWord + "])")
>            loStringBuilder.Replace("." + lcWord + ">", ".[" + lcWord + "]>")
>            loStringBuilder.Replace("." + lcWord + "<", ".[" + lcWord + "]<")
>            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
>
>            ' Update
>            loStringBuilder.Replace(" " + lcWord + "=", " [" + lcWord + "]=")
>
>            ' Order
>            loStringBuilder.Replace("," + lcWord + " ", ",[" + lcWord + "] ")
>            loStringBuilder.Replace("," + lcWord + ",", ",[" + lcWord + "],")
>
>            ' Check for the last occurrence
>            If Mid(loStringBuilder.ToString, loStringBuilder.Length - lcWord.Length) = "." + lcWord Then
>                loStringBuilder.Remove(loStringBuilder.Length - 1 - lcWord.Length, lcWord.Length)
>                loStringBuilder.Append("[" + lcWord + "] ")
>            End If
>
>            ' Check for the first occurrence
>            If Mid(loStringBuilder.ToString, 1, lcWord.Length + 1) = lcWord + "." Then
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, 1, 1, "[")
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, lcWord.Length + 2, 1, "]")
>            End If
>
>            ' Field in update
>            loStringBuilder.Replace("," + lcWord + "=", ",[" + lcWord + "]=")
>
>        Next
>
>        ' For each reserved words for fields only
>        For lnCounter = 1 To 1
>            lcWord = laReservedWordForFieldOnly(lnCounter)
>
>            ' Field
>            loStringBuilder.Replace("," + lcWord + ".", ",[" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + ",", ".[" + lcWord + "],")
>            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
>            loStringBuilder.Replace(" " + lcWord + ",", " [" + lcWord + "],")
>            loStringBuilder.Replace("," + lcWord + ")", ",[" + lcWord + "])")
>
>            ' INNER JOIN or AS clause in the fields
>            loStringBuilder.Replace(" " + lcWord + ".", " [" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
>            loStringBuilder.Replace("=" + lcWord + ".", "=[" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + " ", ".[" + lcWord + "] ")
>
>            ' Where
>            loStringBuilder.Replace("(" + lcWord + ".", "([" + lcWord + "].")
>            loStringBuilder.Replace("." + lcWord + ")", ".[" + lcWord + "])")
>            loStringBuilder.Replace("." + lcWord + ">", ".[" + lcWord + "]>")
>            loStringBuilder.Replace("." + lcWord + "<", ".[" + lcWord + "]<")
>            loStringBuilder.Replace("." + lcWord + "=", ".[" + lcWord + "]=")
>
>            ' Update
>            loStringBuilder.Replace(" " + lcWord + "=", " [" + lcWord + "]=")
>
>            ' Order
>            loStringBuilder.Replace("," + lcWord + " ", ",[" + lcWord + "] ")
>            loStringBuilder.Replace("," + lcWord + ",", ",[" + lcWord + "],")
>
>            ' Check for the last occurrence
>            If Mid(loStringBuilder.ToString, loStringBuilder.Length - lcWord.Length) = "." + lcWord Then
>                loStringBuilder.Remove(loStringBuilder.Length - 1 - lcWord.Length, lcWord.Length)
>                loStringBuilder.Append("[" + lcWord + "] ")
>            End If
>
>            ' Check for the first occurrence
>            If Mid(loStringBuilder.ToString, 1, lcWord.Length + 1) = lcWord + "." Then
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, 1, 1, "[")
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, lcWord.Length + 2, 1, "]")
>            End If
>
>            ' Field in update
>            loStringBuilder.Replace("," + lcWord + "=", ",[" + lcWord + "]=")
>
>        Next
>
>        Return loStringBuilder.ToString
>    End Function
>

>
>The part I was wondering about to see if it can be optimized was:
>
>

>            ' Check for the last occurrence
>            If Mid(loStringBuilder.ToString, loStringBuilder.Length - lcWord.Length) = "." + lcWord Then
>                loStringBuilder.Remove(loStringBuilder.Length - 1 - lcWord.Length, lcWord.Length)
>                loStringBuilder.Append("[" + lcWord + "] ")
>            End If
>
>            ' Check for the first occurrence
>            If Mid(loStringBuilder.ToString, 1, lcWord.Length + 1) = lcWord + "." Then
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, 1, 1, "[")
>                loStringBuilder = oApp.StuffFromStringBuilder(loStringBuilder, lcWord.Length + 2, 1, "]")
>            End If
>

Répondre

Fil

Voir

Click here to load this message in the networking platform