פיצול מחרוזת (Split)

פיצול מחרוזת של ערכים המופרדים בפסיקים לסט- זה אתגר שיכול לצוץ כשעובדים עם מידע המגיע בקבצי טקסט, או אולי כשרוצים להעביר מערך שנוצר בשפת תכנות כלשהי לתוך טבלה ב-SQL Server: יהיה מאוד לא יעיל לעבור בלולאה על המערך וכל ערך להכניס בנפרד לטבלה, ובמקום זה עדיף לשרשר את כולם למחרוזת אחת, להעביר ל-SQL Server ולתת לו לפצל אותה ולקלוט את הערכים.
להלן מספר "הצעות הגשה", כלומר- מספר פתרונות אפשריים.

קודם כל ניצור מחרוזת מספיק ארוכה שיהיה מה לפצל.
להלן סקריפט קצר שמשרשר 10,000 ערכים אקראיים ושומר אותם בטבלה זמנית בת שורה אחת (שוב- בשלב זה יש לנו מחרוזת אחת אותה יש לפצל בהמשך):

Declare    @I Int;

Declare    @S VarChar(MAX);

Set        @I=1;

Set        @S='';

While    @I<=10000

    Begin

    Set    @S=@S+Cast(Cast(1000000*RAND() As Int) As VarChar)+',';

    Set    @I=@I+1;

    End

Set    @S=LEFT(@S,Len(@S)-1);

Select @S S Into #Tmp;

נתחיל מהפתרון המסובך יותר- שיתגלה בהמשך כיעיל ביותר- על ידי קוד #C, שמתבסס על המאמר T-SQL Split String User-Defined Function באתר The Code Project.

את הקוד הבא נשמור בתור קובץ C:\Tmp\Split.cs:

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class Utils

{

    /// summary>

    /// Splits String at parts and returns as table of splintered

    /// /summary>

    /// param name="line">line to split /param>

    /// param name="separator">separator /param>

    /// returns>list of splitered /returns>

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillBySplintered", Name = "SplitString",

          TableDefinition = "splintered nvarchar(128)", IsDeterministic = true)]

    public static IEnumerable SplitString(string line, string separator)

    {

        return line.Split(new string[] { separator }, StringSplitOptions.RemoveEmptyEntries);

    }

    ///  summary>

    /// Fills row by values

    ///  /summary>

    ///  param name="obj">pointer to one from elements returened by Primary function /param>

    ///  param name="splintered">first column in table /param>

    public static void FillBySplintered(Object obj, out SqlString splintered)

    {

        splintered = new SqlString((string)obj);

    }

};

במסך Cmd נעבור למחיצה C:\Tmp ונריץ שם את הפקודה C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Csc /target:library C:\Tmp\Split.Cs.

כתוצאה מכך יווצר במחיצה הזו קובץ Split.dll (הוא יווצר במחיצה בה מריצים את הפקודה).

ב-SSMS נריץ את הקוד הבא כדי לאפשר שימוש בפונקציות Clr, ולקלוט את ה-DLL שייצרנו:

--create assembly in SQL Server

CREATE ASSEMBLY [SqlFuns] FROM 'C:\Tmp\Split.dll'

GO

--Have a look at the assembly within the database

--SELECT * FROM sys.assemblies

--Utils Function

--Split string at parts using separator

CREATE FUNCTION dbo.SplitString(@string nvarchar(max),@separator nvarchar(5))

RETURNS TABLE (splintered nvarchar(128))

AS EXTERNAL NAME [SqlFuns].Utils.SplitString

GO

sp_configure 'clr enabled', 1

GO

reconfigure with override

GO

ולבסוף נריץ את הפונקציה על המחרוזת ששמרנו בטבלה הזמנית,

כאשר לצורך הבדיקה ננקה את ה-Buffer ואת ה-Cache, ונבדוק את הסטטיסטיקה של זמן ה-CPU:

DBCC DropCleanBuffers

DBCC FreeProcCache

SET STATISTICS Time ON;

Declare    @S VarChar(Max);

Set        @S=(Select S From #Tmp)+',';

Select    *

From    dbo.SplitString(@S, ',');

SET STATISTICS Time Off;

בבדיקה שלי קיבלתי את התוצאות מיידית וזמן ה-CPU היה אפסי.

נעבור כעת לשיטה שחביבה עלי במיוחד- שימוש ב-CTE רקורסיבי: הקוד שולף את ראש המחרוזת ופועל ברקורסיה על הזנב..

להלן הקוד יחד עם ניקוי ה-Buffer וה-Cache וההפעלה והנטרול של איסוף הסטטיסטיקה:

DBCC DropCleanBuffers

DBCC FreeProcCache

SET STATISTICS Time ON;

Declare    @S VarChar(Max);

Set        @S=(Select S From #Tmp)+',';

With T As

(Select    Left(@S,T1.I-1) S1,

        Right(@S,Len(@S)-T1.I) S

From    (Select    CHARINDEX(',',@S) I) T1

Union All

Select    Left(T1.S,T1.I-1) S1,

        Right(T1.S,Len(T1.S)-T1.I) S

From    (Select    S,

                CHARINDEX(',',S) I

        From T) T1

Where    Len(T1.S)>0)

Select    S1

From    T

Option (MaxRecursion 0);

SET STATISTICS Time Off;

אצלי הריצה נמשכה 8 שניות ולפי הסטטיסטיקה זמן ה-CPU היה 6125 אלפיות השניה.

הרבה פחות טוב מהקודם, אך בלי כל "הסמטוכה" של השימוש ב-#C.

עוד דרך טריקית מעט היא להפוך את המחרוזת הארוכה לפקודת SQL דינאמית, והפעלתה (למי שלא הבין- מומלץ לשנות את הקוד כך שיבצע בסוף Select ל-S@ במקום Exec):

DBCC DropCleanBuffers

DBCC FreeProcCache

SET STATISTICS Time ON;

Declare    @S VarChar(Max);

Set        @S=(Select S From #Tmp);

Set        @S='Select '''+REPLACE(@S,',',''' S Union All Select ''')+''' S;';

Exec(@S);

SET STATISTICS Time Off;

אצלי הריצה נמשכה 59 שניות, וזמן ה-CPU היה 50,266 אלפיות שניה.

גרוע מאוד!

ועל חשבון הבית- וריאציה על הטריק הנ"ל שתעבוד על גרסאות 2008 ואילך:

DBCC DropCleanBuffers

DBCC FreeProcCache

SET STATISTICS Time ON;

Declare    @S VarChar(Max);

Set        @S=(Select S From #Tmp);

Set        @S='Select * From (Values('''+Replace(@S,',','''),(''')+''')) T(S);';

Exec(@S);

SET STATISTICS Time Off;

הפעם זה לקח 54 שניות, וזמן ה-CPU היה 50,062 אלפיות שניה.

קצת יותר טוב, אך עדיין- לא מציאה גדולה.

ולבסוף- איך לא – שימוש בפונקציה (UDF):

Create Function dbo.F_Split (@S Varchar(Max))

Returns @Splited Table (S Varchar(Max)) As

Begin

Declare    @I Int;

Set @I=CharIndex(',',@S);

While @I>0

    Begin

    INSERT INTO @Splited Select Left(@S,@I-1);

    Set    @S=Right(@S,Len(@S)-@I);

    SET @I=CharIndex(',',@S);

    End

Return

End

GO

ונפעיל אותה כך-

DBCC DropCleanBuffers

DBCC FreeProcCache

SET STATISTICS Time ON;

Declare    @S VarChar(Max);

Set        @S=(Select S From #Tmp)+',';

Select    *

From    dbo.F_Split(@S);

SET STATISTICS Time Off;

אצלי זה לקח שניה אחת, וזמן ה-CPU 984 אלפיות שניה.

לסיכום: בכל מה שקשור לעיבוד של מחרוזות- אין פונקציות CLR מבחינת היעילות.

מי שזה קצת מפחיד אותו- יסתפק בפונקציה רגילה- קצת פחות יעילה, אך עדיין מחזירה תוצאות כמעט מיידית.

ולבסוף, מי שרוצה להתחכם, ואולי גם להימנע מיצירת אובייקטים חדשים (פונקציות) – ישתמש ב-CTE רקורסיבי: הוא אולי יזכה למחיאות כפיים על הדרך, אך הביצועים לא משהו..

27/06/2010 – מתברר שיש עוד אפשרויות, ואפילו יעילות למדי, למשל- תוך שימוש בטבלת מספרים: בעזרתה נזהה היכן יש פסיקים במחרוזת, ונחפש את מה שבינם לבין הפסיק הבא.
לכאורה גם פתרון זה מחייב יצירת אובייקטים בדטבייס, לפיכך אני אשתמש בטבלת  מספרים On the fly כדי להמחיש כיצד הביצועים של השליפה הבאה הם באותו סדר גודל כמו של קוד CLR:

Declare @S Varchar(Max);

Set @S=(Select ','+S+',' From #Tmp);

With  L0   As (Select 1 As C Union ALL Select 1),

L1   As (Select 1 As C From L0 As A, L0 As B),

L2   As (Select 1 As C From L1 As A, L1 As B),

L3   As (Select 1 As C From L2 As A, L2 As B),

L4   As (Select 1 As C From L3 As A, L3 As B),

L5   As (Select 1 As C From L4 As A, L4 As B),

T As (Select Row_Number() Over (Order By C) As n From L5)

Select SubString(@S,N+1,CharIndex(',',@S,N+1)-N-1)

From T

Where SubString(@S,N,1)=','

And N<Len(@S)

option (MaxRecursion 0);

Go

08/07/2010 – בדיקה קצת יותר יסודית משלי עם מסקנות די דומות-Aaron Bertrand.