locked
problem with call function and split the data into columns and rows RRS feed

  • Question

  • User1948389125 posted

    Hi all

    I working on a stored procedure to generate report.

    This is the source data

    tbl_Order

    OrderNo

    OrderDate

    ProductNo

    Metadata

    A210515-01

    15/05/2021

    SW01-B05

    Customer Name : Wesley | Transaction Date : 31/05/2021 |

    A210515-02

    15/05/2021

    SW01-D01

    Customer Name : David | Transaction Date : 02/06/2021 | Remark : Testing |

    A210516-01

    16/05/2021

    SW05-B01

    Customer Name : Bruce | Transaction Date : 31/05/2021 | Customer Name :  Michael | Transaction Date : 08/06/2021 |

    A210516-01

    16/05/2021

    SW01-B05

    Customer Name : Zoe | Transaction Date : 30/05/2021 | Remark : Test |

     

    Report will be

    OrderNo

    OrderDate

    ProductNo

    Customer Name

    Transaction Date

    Remark

    A210515-01

    15/05/2021

    SW01-B05

    Wesley

    31/05/2021

     

    A210515-02

    15/05/2021

    SW01-D01

    David

    02/06/2021

    Testing

    A210516-03

    16/05/2021

    SW05-B01

    Bruce

    31/05/2021

     

    A210516-03

    16/05/2021

    SW05-B01

    Michael

    08/06/2021

     

    A210516-01

    16/05/2021

    SW01-B05

    Zoe

    30/05/2021

    Test

     

    As can see, the metadata contain all the information needed, but unfortunately it contain different fields for different products.

    And also 1 metadata possible contain 2 records example the 3rd Order.

    I have make a function to split the Metadata to few columns, I use substring and charindex to split the metadata into columns.

    But I need to pass the metadata into the function 1 by 1 looping the tbl_Order table. This table will have more and more data coming in, to generate it took me more than 45 mins for 11k records.

    This is very slow, I wish to modify the function and stored procedure to make it faster.

    This is the function, this functon will split the metadata into few rows depends on the title.

    ALTER FUNCTION [dbo].[fn_split_string_to_column] (

        @string NVARCHAR(MAX),

        @delimiter CHAR(1)

        )

    RETURNS @out_put TABLE (

        [column_id] INT IDENTITY(1, 1) NOT NULL,

        [value] NVARCHAR(MAX)

        )

    AS

    BEGIN

        DECLARE @value NVARCHAR(MAX),

            @pos INT = 0,

            @len INT = 0

     

        SET @string =CASE

                WHEN RIGHT(@string, 1) != @delimiter

                    THEN @string + @delimiter

                ELSE @string

                END

     

        WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0

        BEGIN

            SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos

            SET @value = SUBSTRING(@string, @pos, @len)

     

            INSERT INTO @out_put ([value])

            SELECT LTRIM(RTRIM(@value)) AS [column]

     

            SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1

        END

     

        RETURN

    END

     

    This is the SP calling the function, after calling the function, it insert into a tmp table and then update the field and value

    DECLARE CUR_TEST CURSOR FAST_FORWARD FOR

    SELECT METADATA

    FROM   [99_ORDERS]

     

    DECLARE @TMP_SPLIT_STRING TABLE

    (

            METADATA_NO                     NVARCHAR(5),

            METADATA                        NVARCHAR(MAX),

            METADATA_TITLE                  NVARCHAR(255),

            METADATA_DATA                   NVARCHAR(MAX)

    )

     

     

    OPEN CUR_TEST

    FETCH NEXT FROM CUR_TEST INTO @METADATA

     

    WHILE @@FETCH_STATUS = 0

    BEGIN

     

            INSERT INTO @TMP_SPLIT_STRING (METADATA_NO, METADATA)

            SELECT *

            FROM   fn_split_string_to_column(@METADATA, '|')

     

            UPDATE @TMP_SPLIT_STRING

            SET    METADATA_TITLE = TRIM(LEFT(METADATA, CHARINDEX(':', METADATA, 0) + 1)),

                   METADATA_DATA = TRIM(SUBSTRING(METADATA, CHARINDEX(':', METADATA, 0) + 1, LEN(METADATA)))

            FETCH NEXT FROM CUR_TEST INTO @METADATA

     

    END

     

    CLOSE CUR_TEST

    DEALLOCATE CUR_TEST

    GO

     

    What I wish to do is to have 1-2 SQL script to handle he report column and also split the metadata to different columns and rows.

    I really no clue how to continue on this, please help me on this.

     

     

     

     

     

     

    Sunday, May 16, 2021 3:47 PM

Answers

  • User1535942433 posted

    Hi arctura,

    I think,when you loop the query,it will do these operations again and again. So,I suggest you split your string before the update.

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 21, 2021 6:32 AM

All replies

  • User475983607 posted

    A far better solution is fixing the table design.  Rather than metadata you should have separate columns Customer Name, Transaction Date and Remark.  The current design approach will create very complex code that is difficult to maintain and error prone.

    Sunday, May 16, 2021 8:44 PM
  • User1535942433 posted

    Hi arctura,

    As far I think,you could get full data from table order into result and then split the string using STRING_SPLIT if your sql server is 2016.

    Syntax:

    STRING_SPLIT ( string , separator )  

    More detail,you could refer to below articles:

    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

    https://tecloger.com/string-split-function-in-sql-server/

    Best regards,

    Yijing Sun

    Monday, May 17, 2021 2:33 AM
  • User1948389125 posted

    Hi

    Thanks for the replies, really happy to receive your suggestion.

    But i think i know why the SP slow performance, it is because of I keep update the data in the loop.

    BEGIN

     

            INSERT INTO @TMP_SPLIT_STRING (METADATA_NO, METADATA)

            SELECT *

            FROM   fn_split_string_to_column(@METADATA, '|')

     

            UPDATE @TMP_SPLIT_STRING

            SET    METADATA_TITLE = TRIM(LEFT(METADATA, CHARINDEX(':', METADATA, 0) + 1)),

                   METADATA_DATA = TRIM(SUBSTRING(METADATA, CHARINDEX(':', METADATA, 0) + 1, LEN(METADATA)))

            FETCH NEXT FROM CUR_TEST INTO @METADATA

     

    END

    Once i move this code after the loop, it become alot faster.

    Anyway thanks...

    Will try your suggestion to improve my SP.

    Thursday, May 20, 2021 3:49 AM
  • User1535942433 posted

    Hi arctura,

    I think,when you loop the query,it will do these operations again and again. So,I suggest you split your string before the update.

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 21, 2021 6:32 AM