Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Parsing Character String

    I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.



    Here are the function and the stored procedure wher eit occurs:




    Code:
    CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
    
    RETURNS @Results TABLE (Item nvarchar(4000))
    
    AS
    
    
    
    BEGIN
    
    DECLARE @INDEX INT
    
    DECLARE @SLICE nvarchar(4000)
    
    -- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    
    -- ERO FIRST TIME IN LOOP
    
    SELECT @INDEX = 1
    
    WHILE @INDEX !=0
    
    
    
    BEGIN 
    
    -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
    
    SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
    
    -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
    
    IF @INDEX !=0
    
    SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
    
    ELSE
    
    SELECT @SLICE = @STRING
    
    -- PUT THE ITEM INTO THE RESULTS SET
    
    INSERT INTO @Results(Item) VALUES(@SLICE)
    
    -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
    
    SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
    
    -- BREAK OUT IF WE ARE DONE
    
    IF LEN(@STRING) = 0 BREAK
    
    END
    
    RETURN
    
    END
    Procedure:

    Code:
    Code SnippetCREATE PROCEDURE [dbo].[RPTPatientAnalysis]
    
    (
    
    @stateList CHAR(2),
    
    @employerIdList VARCHAR(4000),
    
    @payerIdList VARCHAR(4000) 
    
    )
    
    AS 
    
    SELECT 
    
    p.PAT_ID,
    
    p.PAT_FirstName,
    
    ISNULL(p.PAT_MiddleName,'') AS PAT_MiddleName,
    
    p.PAT_LastName,
    
    p.PAT_Gender,
    
    CONVERT(VARCHAR(10),p.PAT_DOB,101) AS DOB,
    
    p.PAT_AddressStreet1,
    
    ISNULL(p.PAT_AddressStreet2,'') AS PAT_AddressStreet2,
    
    p.PAT_AddressCity,
    
    p.PAT_AddressStateProvince,
    
    p.PAT_AddressPostalCode,
    
    ISNULL(p.PAT_EmailAddress,'') AS PAT_EmailAddress,
    
    p.PAT_PhoneNumber,
    
    ISNULL(e.EMPLOYER_Name,'<Unknown>') AS EMPLOYER_Name,
    
    ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,
    
    ISNULL(p.PAT_Comment,'') AS PAT_Comment,
    
    ISNULL(p.PAT_PrimCareProv_PRIMCP_ID,'') AS PAT_PrimCareProv_PRIMCP_ID,
    
    ISNULL(p.PAT_PrimCareProvAllowNotification,0) AS PAT_PrimCareProvAllowNotification,
    
    ISNULL(p.PAT_PrimCareProvFullName,'') AS PAT_PrimCareProvFullName,
    
    ISNULL(p.PAT_DoNotMail,0) AS PAT_DoNotMail,
    
    ISNULL(p.PAT_UnderAgePermission,0) AS PAT_UnderAgePermission,
    
    p.PAT_LastEandMCodingDateTime,
    
    p.PAT_Desceased,
    
    p.PAT_PCP_ID,
    
    p.PAT_LastUpdatedDateTime,
    
    ISNULL(p.PAT_PCPRecordType,0) AS PAT_PCPRecordType,
    
    ISNULL(p.PAT_EnableEmailMarketing,0) AS PAT_EnableEmailMarketing,
    
    ISNULL(p.PAT_EnablePortal,0) AS PAT_EnablePortal,
    
    ISNULL(p.PAT_PortalID,0) AS PAT_PortalID,
    
    ISNULL(e2.EMPLOYER_Name,'') AS EMPLOYER_Name,
    
    ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,
    
    pcp.PRIMCP_ID,
    
    ISNULL(pcp.PRIMCP_ADDR_ID,'') AS PRIMCP_ADDR_ID,
    
    ISNULL(pcp.PRIMCP_ClinicName,'') AS PRIMCP_ClinicName,
    
    ISNULL(pcp.PRIMCP_PhysicianFullname,'') AS PRIMCP_PhysicianFullname,
    
    pcp.PRIMCP_DateDeactivated,
    
    ISNULL(pcp.PRIMCP_Phone_MedicalRecordFax,'') AS PRIMCP_Phone_MedicalRecordFax,
    
    ISNULL(pcp.PRIMCP_Phone_Voice,'') AS PRIMCP_Phone_Voice,
    
    ISNULL(pcp.PRIMCP_MedicalRecords_Street1,'') AS PRIMCP_MedicalRecords_Street1,
    
    ISNULL(pcp.PRIMCP_MedicalRecords_Street2,'') AS PRIMCP_MedicalRecords_Street2,
    
    ISNULL(pcp.PRIMCP_MedicalRecords_City,'') AS PRIMCP_MedicalRecords_City,
    
    ISNULL(pcp.PRIMCP_MedicalRecords_State,'') AS PRIMCP_MedicalRecords_State,
    
    ISNULL(pcp.PRIMCP_MedicalRecords_Zip,'') AS PRIMCP_MedicalRecords_Zip,
    
    ISNULL(pcp.PRIMCP_Street1,'') AS PRIMCP_Street1,
    
    ISNULL(pcp.PRIMCP_Street2,'') AS PRIMCP_Street2,
    
    ISNULL(pcp.PRIMCP_City,'') AS PRIMCP_City,
    
    ISNULL(pcp.PRIMCP_State,'') AS PRIMCP_State,
    
    ISNULL(pcp.PRIMCP_Zip,'') AS PRIMCP_Zip,
    
    ISNULL(pcp.PRIMCP_DoNotFax,0) AS PRIMCP_DoNotFax,
    
    pati.PATINS_InsuranceTypeID,
    
    ISNULL(pati.PATINS_Account,'') AS PATINS_Account,
    
    ISNULL(pati.PATINS_Group,'') AS PATINS_Group,
    
    ISNULL(pati.PATINS_CopayType,'') AS PATINS_CopayType,
    
    ISNULL(pati.PATINS_CopayAmount,0) AS PATINS_CopayAmount,
    
    ISNULL(pati.PATINS_CollectFullAmount,0) AS PATINS_CollectFullAmount,
    
    ISNULL(pati.PATINS_EmployerPays,0) AS PATINS_EmployerPays,
    
    ISNULL(pati.PATINS_ZeroScreenCopay,0) AS PATINS_ZeroScreenCopay,
    
    ISNULL(pati.PATINS_ZeroVaccineCopay,0) AS PATINS_ZeroVaccineCopay,
    
    ISNULL(pati.PATINS_NonPar,0) AS PATINS_NonPar,
    
    ISNULL(pati.PATINS_MedicarePlan,0) AS PATINS_MedicarePlan,
    
    ISNULL(ipcl.INSPCAT_Description,'') AS INSPCAT_Description,
    
    ISNULL(ip.INSP_Name,'') AS INSP_Name,
    
    ISNULL(ip.INSP_ChargeFullPrice,0) AS INSP_ChargeFullPrice,
    
    ISNULL(ip.INSP_CopayApplies,0) AS INSP_CopayApplies,
    
    CONVERT(VARCHAR(10),ip.INSP_DeactivatedDate,101) AS INSP_DeactivatedDate,
    
    ISNULL(ip.INSP_EligibilityActive,0) AS INSP_EligibilityActive,
    
    CONVERT(VARCHAR(10),ip.INSP_PromoStartDate,101) AS INSP_PromoStartDate,
    
    CONVERT(VARCHAR(10),ip.INSP_PromoEndDate,101) AS INSP_PromoEndDate
    
    
    FROM dbo.patient AS p 
    
    LEFT JOIN dbo.Employer AS e ON p.PAT_EMPLOYER_ID = e.EMPLOYER_ID
    
    LEFT JOIN dbo.Employer AS e2 ON p.PAT_SecondaryEMPLOYER_ID = e2.EMPLOYER_ID
    
    LEFT JOIN dbo.PrimaryCareProvider AS pcp ON p.PAT_PCP_ID = pcp.PRIMCP_ID
    
    LEFT JOIN dbo.PatientInsurance AS pati ON p.PAT_ID = pati.PATINS_PAT_PERS_ID AND PATINS_InsuranceTypeID = 1
    
    LEFT JOIN dbo.InsurancePayer AS ip ON pati.PATINS_INSP_ID = ip.INSP_ID
    
    LEFT JOIN dbo.InsurancePayerCategoryLookup AS ipcl ON ip.INSP_INSPCAT_ID = ipcl.INSPCAT_ID
    
    WHERE p.PAT_AddressStateProvince IN (SELECT Item FROM dbo.SplitVarcharMax(@stateList,',')) 
    
    AND PAT_EMPLOYER_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@employerIdList,','))
    
    AND pati.PATINS_INSP_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@payerIdList,','))

    Is there a faster / more efficient way to accomplish the above?

    Any insight would be appreciated!!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What version of SQL Server are you using?
    Can you post one or two examples of the data?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The first question that jumps to my mind is why can you have arguments that are over 8000 characters long? In the case of a comma delimited list, this is just a disaster in search of its next victim.

    You are limited in how to get past the 8000 character limit in Transact SQL. If you are running SQL 2005 or later, you can use VARCHAR(MAX). If you are using a version of SQL before SQL 2005, you'd have to use TEXT.

    I'd approach this problem from the perspective of "WHY" long before I worried about "HOW" to handle arguments that size. Someone would have to find a very good reason before I'd move from the why to the how... I wouldn't be easy to move because this is fundamentally a bad idea.

    -PatP

  4. #4
    Join Date
    Sep 2005
    Posts
    161
    Pat is right. This would be a piece of cake for bcp or even an SSIS package.... and MUCH faster.

    Quote Originally Posted by Pat Phelan
    I'd approach this problem from the perspective of "WHY" long before I worried about "HOW" to handle arguments that size. Someone would have to find a very good reason before I'd move from the why to the how... I wouldn't be easy to move because this is fundamentally a bad idea.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pass XML, and inside the function parse it into a table. Just don't use table-valued function for it, but rather an inline function.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    ...as to the "why"...

    We have a list of employers - over 2000. Each of those employer id's can be anywhere from one to five characters. If someone chooses to search on all emploers, there's your more than 8000 characters...

    And the issue with the size is no longer an issue - I forgot that with SQL 2K5 the MAX option allows up to 2GB for a VARCHAR.

    With the XML option, that creates more work on my end. I would still have to take the list, convert it to XML, and then read it out again. These procedure is being invoked via Reporting Services, so no way to "natively" pass XML.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sounds like a bad reporting design approach. Why (and mostly HOW) do you plan to "search" for employers by passing their ID's as a parameter? If you already know their ID's, why are you searching for them? If you allow a check-box against each employer's record to be checked, then you need to limit that to a page level (no more than 50 to be checked). In short, the report architecture has flaws that lead to necessity to pass 8000+ character list. So, the GUI needs to be reviewed and changed to make more sense. It also need to be structured so that users expected to work with such report did not feel overwhelmed with 2000+ records to be reviewed. Remember, computer system is expected to be easier to use than a paper-based system. Don't try to negate its advantages by avoiding proper business analysis and adequate design.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    Trust me...I am all too aware of the flaws with this approach.

    And as far as "searching" for them, you can see in the SQL that there is a WHERE EMP_ID in (.........)

    I didn't design the report, but it's waht the business wants - to be able to select any number of employers...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So now with varchar(max) you can pass up to 2GB worth of employer ID's. Isn't that great?!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ansonee
    I didn't design the report, but it's waht the business wants - ...
    What the hell do they know about what they need?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •