Cursor replacement suggestions

  • I typically use SSIS to do that, since it has the inherent capability to run multiple imports async.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Does not matter what possible options we give you to process from the queue - unless you tells with great detail what you are doing with each row on the queue, what is the performance issue you are getting (is it locks? dead locks? plain slowness?) and a lot more detail, giving you a option to process with a Queue or with SSIS is not necessarily going to fix you issue - and in the meantime you lost time implementing a solution of which the only benefit you got is that you learn another way of doing a particular type of processing.

    if you are so keen on cursors then it is likely that your remaining code is also done in a "row by row" approach - and if this is the case then that is likely your biggest problem and that is what you should be addressing first.

     

  • Each one of the import records from the queue are processed with the cursor, as the count from the capture is associated with a TEST. I went ahead and created a sql agent job for a given oven\wireline so I could multi-process from the Import Table. Thinking this would speed up processing.

    ex.

    usp_process_line '205e01%'

    usp_process_line '205e02%'

    There are 5 different test per data record that can be pass to procedure "Capture_Inline_Quality". I have included one of those TESTs in the code snippet below.  I tried to bypass the call to procedure if the counts where zero(0) meaning no alarming needed, but found I needed those records for a reporting process where it performs counts and averages on the data received. If I excluded those records it threw off the counts. Is there a better way to process the data.

    Thanks.

    -- Declare cursor to read inserted records.
    EXECUTE Write_Error_Log 'INFORMATION','Declaring quality cursor','Import_Smartkatt_3_Data'-- Revision 1 - Added
    DECLARE Quality_Cursor CURSOR LOCAL DYNAMIC READ_ONLY FOR
    SELECT DISTINCT [Key], [Date], [Time], Small_Beads, Large_Beads, Continuity, Speed, Continuity_Voltage,
    Continuity_Amp, Small_Bead_Threshold, Large_Bead_Threshold, Equip_ID,XLarge_Beads,XLarge_Bead_Threshold
    FROM Import_Smartkatt_3
    --WHERE Processed = N'FALSE'-- Revision 1 - Removed
    WHERE (Processed = N'FALSE') AND (Equip_ID LIKE @Equipment)-- Revision 1 - Added
    ORDER BY [Date],[Time]

    -- Open cursor with the records.
    EXECUTE Write_Error_Log 'INFORMATION','Opening quality cursor','Import_Smartkatt_3_Data'-- Revision 1 - Added
    OPEN Quality_Cursor

    -- Read first record from cursor and assign values into internal variables.
    EXECUTE Write_Error_Log 'INFORMATION','Reading first record from quality cursor','Import_Smartkatt_3_Data'-- Revision 1 - Added
    FETCH NEXT FROM Quality_Cursor
    INTO @Key, @Date, @Time, @SmallBeads, @LargeBeads, @Continuity, @Speed, @ContinuityVoltage, @ContinuityAmp,
    @SmallBeadThreshold, @LargeBeadThreshold, @EquipID,@XLargeBeads,@XLargeBeadThreshold

    -- Loop while the cursor is active.
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Mark import row as processed.
    SET @Error_Log_Desc = 'Updating import record "' + CAST(@Key AS nvarchar(MAX)) + '" as processed'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Import_Smartkatt_3_Data'-- Revision 1 - Added

    BEGIN TRY-- Revision 1 - Added
    UPDATE Import_Smartkatt_3
    SET Processed = @Passed, Comments = @Comments
    WHERE [Key] = @Key
    END TRY-- Revision 1 - Added

    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot update import record "' + CAST(@Key AS nvarchar(MAX)) + '" as processed'
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Import_Smartkatt_3_Data'
    SET @Error = 1
    END CATCH-- Revision 1 - Added (End)

    -- Check if a PLC id is provided as the equipment.
    IF (SELECT COUNT(*) FROM Equip_Attribute WHERE Attribute = N'PLC ID' AND Attribute_Value = @EquipID) > 0-- Revision 1 - Added (Begin)
    BEGIN
    -- Retrieve equipment id for specified PLC id.
    SET @Error_Log_Desc = 'Retrieving equipment id associated with PLC id "' + @EquipID + '"'
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Import_Smartkatt_3_Data'
    SELECT TOP 1 @EquipID = Equip_ID
    FROM Equip_Attribute
    WHERE Attribute = N'PLC ID' AND Attribute_Value = @EquipID
    END-- Revision 1 - Added (End)

    SET @Oven = ISNULL(dbo.ParseText(@EquipID,'-',1),'')
    SET @WireLine = ISNULL(dbo.ParseText(@EquipID,'-',2),'')

    -- Save continuity test data.
    IF ISNULL(@ContinuityTestID,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'No "' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + '" test id available'-- Revision 1 - Added (Begin)
    EXECUTE Write_Error_Log 'WARNING',@Error_Log_Desc,'Import_Smartkatt_3_Data'
    SET @Comments += 'WARNING - No ' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + ' test id available' + CHAR(13) + CHAR(10)-- Revision 1 - Added (End)
    --SET @Comments += 'ERROR - No ' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + ' test id available' + CHAR(13) + CHAR(10)-- Revision 1 - Removed
    END
    BEGIN
    SET @Error_Log_Desc = 'Executing CAPTURE_INLINE_QUALITY with parameters ''' + @ContinuityTestID + ''',''' + CAST(@Date AS nvarchar(MAX)) + ''','''-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc += CAST(@Time AS nvarchar(MAX)) + ''',''' + @Oven + ''',''' + @Wireline + ''',''' + CAST(@Continuity AS nvarchar(MAX)) + ''',@Result OUT'
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Import_Smartkatt_3_Data'-- Revision 1 - Added (End)

    --EXECUTE Capture_Inline_Quality @ContinuityTestID,@Date,@Time,@Oven,@WireLine,@Continuity,@Result OUT-- Revision 1 - Removed
    BEGIN TRY-- Revision 1 - Added (Begin)
    EXECUTE @Error = Capture_Inline_Quality @ContinuityTestID,@Date,@Time,@Oven,@WireLine,@Continuity,@Result OUT
    END TRY

    -- Log errors.
    BEGIN CATCH
    SET @Error_Log_Desc = 'Cannot save "' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + '" test data for ' + @Oven + '-' + @WireLine
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Import_Smartkatt_3_Data'
    SET @Error = ERROR_NUMBER()
    END CATCH-- Revision 1 - Added (End)

    --IF @Result = 'FAIL'-- Revision 1 - Removed
    IF @Result = 'FAIL' OR @Error <> 0-- Revision 1 - Added
    BEGIN
    SET @Error_Log_Desc = 'Cannot execute CAPTURE_INLINE_QUALITY with parameters ''' + @ContinuityTestID + ''',''' + CAST(@Date AS nvarchar(MAX)) + ''','''-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc += CAST(@Time AS nvarchar(MAX)) + ''',''' + @Oven + ''',''' + @Wireline + ''',''' + CAST(@Continuity AS nvarchar(MAX)) + ''',@Result OUT'
    SET @Error = 2
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Import_Smartkatt_3_Data',@Error-- Revision 1 - Added (End)
    SET @Comments += 'ERROR - Cannot save ' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + ' test data for ' + @Oven + '-' + @WireLine + CHAR(13) + CHAR(10)
    END
    END

     

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply