Hi all,
I have a T-Sql procedure that reads through a transaction table, and does
data validation and assigns error_codes.
I have indexes on :  tx_id, Status, Error_code columns

structure of import_tx table is as follows:
tx_id,
error_code,
status,
patient_name,
patient_address,
etc...

procedure is as follows:
----------------- Start import_error_check.sql------------------------
CREATE PROCEDURE usp_import_error_check
AS

DECLARE @tx_id as bigint
DECLARE @nabp as varchar(20) -- necessary
DECLARE @birth_date as varchar(10) -- necessary
DECLARE @sex_code as varchar(10)
DECLARE @Date_filled as varchar(10) --necessary
DECLARE @rx_number as varchar(10) --necessary
DECLARE @new_refill_Code as varchar(10)
DECLARE @metric_quantity as varchar(10)
DECLARE @days_supply as varchar(10)
DECLARE @ndc as varchar(15) --necessary
DECLARE @dea as varchar(10) --necessary
DECLARE @date_rx_written varchar(10)
DECLARE @nof_refills_auth varchar(10)
DECLARE @patient_last_name as varchar(50) --necessary
DECLARE @patient_first_name as varchar(50) --necessary
DECLARE @patient_street_address as varchar(50)
DECLARE @patient_state as varchar(10)
DECLARE @error_code as varchar(50)

DECLARE @error_flag as varchar(50)
DECLARE @count as int
DECLARE @new_errors as int
DECLARE @total_errors as int
DECLARE @previous_errors as int
DECLARE @good_records as int
DECLARE @process_start as varchar(50)

SET @process_start = convert(char,getdate())
SET @error_flag = NULL
SET @count = 0
SET @new_errors = 0
SET @good_records = 0
SET @previous_errors = 0


DECLARE cur_rx CURSOR
    LOCAL FAST_FORWARD
FOR
    SELECT  tx_id,
        NABP,
        Birth_Date,
        Sex_Code,
        Date_Filled,
        Rx_Number,
        New_Refill_Code,
        Metric_Quantity,
        Days_Supply,
        NDC,
        DEA,
        Date_Rx_Written,
        NOF_Refills_Auth,
        Patient_Last_Name,
        Patient_First_Name,
        Patient_Street_Address,
        Patient_State,
        error_code
    FROM tbl_import_tx
    WHERE status = 'Y'


print ('Number of NULL records: ' + convert(char,@@ROWCOUNT))

open cur_rx
fetch NEXT FROM cur_rx into @tx_id, @nabp, @birth_date, @sex_code,
                @Date_filled, @rx_number, @new_refill_Code, @metric_quantity,
                @days_supply, @ndc, @dea, @date_rx_written,
                @nof_refills_auth, @patient_last_name, @patient_first_name,
@patient_street_address,
                @patient_state, @error_code

print ('Fetch Status: ' + convert(char, @@FETCH_STATUS))
print ('Number of Rows currently processing: ' + convert(char, @@CURSOR_ROWS))

While @@FETCH_STATUS = 0
BEGIN
    SET @count = @count + 1

--    print ('counter :' + convert(char, @count))
--    print ('Record : ' + @nabp)

    --========================================================================
    --    Check validity of necessary fields (8)
    --        @birth_date: not null, valid date,  not a future value
    --        @Date_filled: not null and valid date
    --        @nabp: not null
    --        @rx_number: is numeric and not null
    --        @ndc: is numeric and not null
    --        @dea: is not null
    --        @patient_last_name:  is not null
    --        @patient_first_name:  isn not null
    --========================================================================


--    print ('2 Error_flag: ' + isnull(@error_flag, '0'))   

    If ((len(@nabp) <= 2) or (isnull(@nabp, '0') = '0'))
        set @error_flag = '010'
    else
        If (isnull(@dea, '0') = '0')
            set @error_flag = '011'
        else
            If ((isnumeric(@ndc) = 0) or (isnull(@ndc, '0') = '0'))
                set @error_flag = '012'
            else
                If ((isnumeric(@rx_number) = 0) or (isnull(@rx_number, '0') = '0'))
                    set @error_flag = '016'
                else
                    If ( (isnull(@patient_last_name, '0' ) = '0') or
(ltrim(rtrim(@patient_last_name)) = '') )   
                        set @error_flag = '022'
                    else
                        If ( (isnull(@patient_first_name, '0') = '0') or
(ltrim(rtrim(@patient_first_name)) = '') )   
                            set @error_flag = '023'


    If (isnull(@error_flag, '0') = '0')
        Begin
            If (isnull(@birth_date, '0') = '0')
                    set @error_flag = '013'
            else
                Begin
                    set @birth_date = substring(@birth_date,1,4) + '/' +
substring(@birth_date,5,2) + '/' + substring(@birth_date,7,2)
                    if (isdate(@birth_date) = 0)
                        set @error_flag = '013'
                    else
                        if (convert(int, DATEADD(day, DATEDIFF(day, 0, getdate()), 0)) -
convert(int, cast(@birth_date as datetime)) <= 0)
                            set @error_flag = '026'
                End   
        End                               

    If (isnull(@error_flag, '0') = '0')
        Begin
            -- @Date_filled: not null and valid date
            If (isnull(@Date_filled, '0') = '0')
                set @error_flag = '015'
            else
                Begin
                    set @Date_filled = substring(@Date_filled,1,4) + '/' +
substring(@Date_filled,5,2) + '/' + substring(@Date_filled,7,2)
                    if (isdate(@Date_filled) = 0)
                        set @error_flag = '015'
                    else
                        if (convert(int, DATEADD(day, DATEDIFF(day, 0, getdate()), 0)) -
convert(int, cast(@Date_filled as datetime)) <= 0)
                            set @error_flag = '027'
                End   
        End




    --=================================================
    -- Data Validation Section : Non-mandatory fields
    --=================================================

    If (isnull(@error_flag, '0') = '0')
    Begin
        --=========================================================================
        -- sex code : Accepted: null, 1, 2, blank (convert into null during insert)
        --=========================================================================
        If isnull(@sex_code, '0') <> '0'
            If not (@sex_code = '1' or @sex_code = '2' or ltrim(rtrim(@sex_code)) = '')
                SET @error_flag = '014'   
        --===========================================================================
        -- new refill code : Accepted: null, blank(convert into null), numbers         --===========================================================================
        If isnull(@new_refill_code, '0') <> '0'
            If not ((isnumeric(@new_refill_code) = 1) or
(ltrim(rtrim(@new_refill_code)) = ''))
                SET @error_flag = '017'   
        --===========================================================================
        -- metric quantity : Accepted: null, blank(convert into null), numbers         --===========================================================================
        If isnull(@metric_quantity, '0') <> '0'
            If not ((isnumeric(@metric_quantity) = 1) or
ltrim(rtrim(@metric_quantity)) = '')
                SET @error_flag = '018'
        --===========================================================================
        -- days supply : Accepted: null, blank(convert into null), numbers         --===========================================================================
        If isnull(@days_supply, '0') <> '0'
            If not ((isnumeric(@days_supply) = 1) or ltrim(rtrim(@days_supply)) = '')
                SET @error_flag = '019'
        --===========================================================================
        -- Date rx written: null, blank, valid date         --===========================================================================
        If isnull(@date_rx_written, '0') <> '0'
            Begin
                SET @date_rx_written = substring(@date_rx_written, 1, 4) + '/' +
substring(@date_rx_written, 5, 2) + '/' + substring(@date_rx_written, 7, 2)
                If not (isdate(@date_rx_written) = 1 or ltrim(rtrim(@date_rx_written)) =
'')
                    SET @error_flag = '020'
            End       
        --===========================================================================
        -- nof refills auth : Accepted: null, blank(convert into null), numbers         --===========================================================================
        If isnull(@nof_refills_auth, '0') <> '0'
            If not ((isnumeric(@nof_refills_auth) = 1) or
ltrim(rtrim(@nof_refills_auth)) = '')
                SET @error_flag = '021'


    End


    -- update error code, if there is one
    -- if no errors, then set status = G and error_code to NULL
    -- or else set status to N with resply increment
--    print ('printing error flag at the end: -' + isnull(@error_flag, '0') +
'-')
    If (isnull(@error_flag, '0') <> '0')
        Begin
            SET @total_errors = @total_errors + 1

            If (isnull(@error_code, '0') = isnull(@error_flag, '0'))
                Begin   
                    update tbl_import_tx
                    set status = 'N'
                    where tx_id = @tx_id

                    SET @previous_errors = @previous_errors + 1

                End
            else
                Begin
                    update tbl_import_tx
                    set error_code =  @error_flag, status = 'N'
                    where tx_id = @tx_id

                    SET @new_errors = @new_errors + 1
                End
        End
    else
        Begin
            update tbl_import_tx
            set error_code = NULL, status = 'G'
            where tx_id = @tx_id

            SET @good_records = @good_records + 1
        End


    -- reset error_flag for every iteration
    set @error_flag = NULL

fetch NEXT FROM cur_rx into @tx_id, @nabp, @birth_date, @sex_code,
            @Date_filled, @rx_number, @new_refill_Code, @metric_quantity,
            @days_supply, @ndc, @dea, @date_rx_written,
            @nof_refills_auth, @patient_last_name, @patient_first_name,
@patient_street_address,
            @patient_state, @error_code

END

print ('********************************************************************')
print ('********************************************************************')
print ('Summary:')
print ('Run on ' + convert(char, getdate()))
print ('')
print ('Process started at:' + @process_start)
print ('Process ended at:' + convert(char,getdate()))
print ('Number of records processed:: ' + convert(char, @@CURSOR_ROWS))
print ('Number of good records: ' + convert(char, @good_records))
print ('Previous unresolved errors:' + convert(char, @previous_errors))
print ('Total new errors: ' + convert(char, @new_errors))
print ('Total errors found: ' + convert(char, @total_errors))

print ('********************************************************************')
print ('********************************************************************')
CLOSE cur_rx
DEALLOCATE cur_rx
GO

----------------- End import_error_check.sql-------------------------

We have about 2.4 million records in the transaction table. This procedure
takes around 9 days to process.

I even tried writing same procedure without cursors, it took even longer to
run...

Is there a better approach for this..... any optimization techniques on
procedure and on tables....

Any help is greatly, greatly appreciated.......

Thank in advance,
_Uday

How did you do it? Did you use a WHILE loop to go through the data one
row at a time? That's just a cursor in disguise so avoid that approach.
The way to optimize this is to write set based code rather than
row-at-a-time processing.

It looks like you could do the whole thing with an UPDATE statement:

UPDATE tbl_import_tx
SET error_code =
  CASE
    WHEN /* error condition 1 */ THEN /* error code 1 */
    WHEN /* error condition 2 */ THEN /* error code 2 */
    WHEN /* error condition 3 */ THEN /* error code 3 */
  ... etc
    ELSE 0
  END

For very large UPDATEs you can batch it into manageable chunks:

SET ROWCOUNT 100000 -- size of chunk

WHILE 1=1
  BEGIN

   UPDATE SET error_code =
    ...
    WHERE error_code IS NULL

   IF @@ROWCOUNT =0
    BREAK

  END

I actually prefer to log validation errors to a separate table so I
would probably turn the whole thing into an INSERT myself, which may
improve performance somewhat.

Hope this helps.

Good iead.... I'll try this (Not sure if all the cases could be done, but
looks doable).

Batching into manageable chunks is cool idea too...

Could you talk more about
"""I actually prefer to log validation errors to a separate table so I
would probably turn the whole thing into an INSERT myself, """

Thanks a lot for a quick reply,
_Uday

clustered index on a identity field
ROWS with null values getting truncated.
Dynamic order by case expression problem
Generating an Index
complicated update (for me)