Data Loading with cursor, help performance tuning

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Data Loading with cursor, help performance tuning

Author

Topic

Zten
Starting Member

2 Posts

Posted - 2009-04-19 : 13:49:54

Hi,

Using SQL SERVER 2000, T-SQL Language

There is a table called retailer which is always up to date containing a list of all the valid retailers.

The data comes in text format
It is being loaded into a temporary table initially and it must be transferred to the holding table, all the rows in the temporary table are flagged 'N' i.e. not been loaded
If they are loaded successfully into the table then they are flagged 'Y' else 'R' rejected.

Before that all the 'N' flagged records are to be checked for valid data like any nulls

Then to load the data into a holding table

Using a script containing stored procedure in which

Cursor is being used for row by row fetching of data

After a row is fetched it will be checked for valid,
i.e whether the retailer id is valid or not,customer name is null, or id is null

all these checks are done inside the cursor after the first row is fetched.

an example of the checking


          --  Fail if Retailer does not exist
  SELECT RETA_SK
    FROM RETA_RETAILER RETA1
   INNER JOIN DERM_DEALER_RETAIL_MANAGER DERM1
      ON RETA1.DERM_SK = DERM1.DERM_SK
   WHERE RETA1.RETAILER_ID = @RETAILER_ID
     AND COUN1.COUNTRY_CODE = 'SE'
     
 	  
          IF NOT EXISTS (SELECT RETA1.RETA_SK
                           FROM RETA_RETAILER RETA1
                          INNER JOIN DERM_DEALER_RETAIL_MANAGER DERM1
                             ON RETA1.DERM_SK = DERM1.DERM_SK
                          INNER JOIN COUN_COUNTRY COUN1
                      ON DERM1.COUN_COUNTRY_CODE = COUN1.COUNTRY_CODE
                          WHERE RETA1.RETAILER_ID = @RETAILER_ID
                            AND COUN1.COUNTRY_CODE = 'SE'
                        )

             BEGIN
     
              INSERT INTO ERLO_ERROR_LOOKUP
                         (CUSTOMER_ID,
                          FINANCE_AGREEMENT_NUMBER,
                          REASON,
                          LOADED_DATETIME
                         )
                  VALUES (@CUSTOMER_ID,
                          @AGREEMENT_NUMBER,
                          'RETAILER ID ('+CONVERT(VARCHAR,@RETAILER_ID) + ') DOES NOT EXIST',
                          GETDATE()
                         )
      
              UPDATE LOAD_DATA_TABLE
                 SET LOADED_INDICATOR = 'R'
               WHERE LOAD_ID = @LOAD_ID
       
              SET @ERROR_IND = 1
      
              GOTO FETCH_NEXT_CURSOR_VALUE
      
             END

/*--------------------------------------------------------------------------------------------------------------------------------*/
  
          --  Fail if the customer name and company name are null
  
          IF  @CUSTOMER_NAME IS NULL
          AND @COMPANY_NAME  IS NULL
   
             BEGIN

              INSERT INTO ERLO_ERROR_LOOKUP
                         (CUSTOMER_ID,
                          FINANCE_AGR_NUMBER,
                          REASON,
                          LOADED_DATETIME
                         )
                  VALUES (@CUSTOMER_ID,
                          @AGREEMENT_NUMBER,
                          'NO CUSTOMER NAME OR COMPANY NAME',
                          GETDATE()
                         )

              UPDATE LOAD_DATA_TABLE
                 SET LOADED_INDICATOR = 'R'
               WHERE LOAD_ID = @LOAD_ID
       
              SET @ERROR_IND = 1
      
              GOTO FETCH_NEXT_CURSOR_VALUE
      
             END

...

If a row passes all the tests then

Then update the tables to insert the new arrived values.


          --  If there hasnt been an error, then load the data

          IF @ERROR_IND = 0
          
           BEGIN
          
             --  If the Customer and Agreement already exist, then update the records

             IF EXISTS (SELECT CUST1.CUSTOMER_ID,
                               FIAG1.FIAG_SK
                          FROM CUST_CUSTOMER CUST1
                         INNER JOIN CUVE_CUSTOMER_VEHICLE CUVE1
                            ON CUST1.CUSTOMER_ID = CUVE1.CUSTOMER_ID
                         INNER JOIN FIAG_FINANCE_AGREEMENT FIAG1
                            ON CUVE1.CUVE_SK = FIAG1.CUVE_SK
                         WHERE CUST1.CUSTOMER_ID = @CUSTOMER_ID
                           AND FIAG1.FIAG_SK = @AGREEMENT_NUMBER
                        )

               BEGIN

                 -- UPDATE CUST_CUSTOMER

                 UPDATE CUST_CUSTOMER
                    SET ADDRESS_LINE_1 = ISNULL(@ADDRESS_LINE_1,ADDRESS_LINE_1),
                        ADDRESS_LINE_2 = ISNULL(@ADDRESS_LINE_2, ADDRESS_LINE_2),
                        ADDRESS_LINE_3 = ISNULL(@ADDRESS_LINE_3, ADDRESS_LINE_3),
                        ADDRESS_LINE_4 = ISNULL(@ADDRESS_LINE_4, ADDRESS_LINE_4),
                        CITY =  ISNULL(@CITY,CITY),
                        POSTCODE =  ISNULL(@POSTCODE,POSTCODE),
                        TELEPHONE_DAY_NUMBER = ISNULL(@DAYTIME_NUMBER, TELEPHONE_DAY_NUMBER),
                        TELEPHONE_EVENING_NUMBER =ISNULL( @EVENING_NUMBER, TELEPHONE_EVENING_NUMBER),
                        TELEPHONE_MOBILE_NUMBER = ISNULL(@MOBILE_NUMBER, TELEPHONE_MOBILE_NUMBER),
                        PREFERRED_TELEPHONE_CONTACT = ISNULL(@PREFERRED_NUMBER, PREFERRED_TELEPHONE_CONTACT),
                        EMAIL = ISNULL(@EMAIL, EMAIL),
                        USER_LAST_UPDATED_BY = 1,
                        LAST_UPDATED_DATE_TIME = GETDATE()
                  WHERE CUSTOMER_ID  = @CUSTOMER_ID

Now this process is taking many hours

Kindly help me out and suggest if you spot any problems in the code or if you can suggest something which would reduce the data loading time that would be great.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-20 : 13:01:32

Without knowing the specifics of your system, I would suggest that you perform these operations in a set based manner instead of using a cursor. That should seep things up drmatically.

Zten
Starting Member

2 Posts

Posted - 2009-04-20 : 15:29:59

Could you elaborate on how this can be implemented in set theory.

Thank you.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-20 : 15:41:36

It would be easier if you could post all the code, you're missing cursor declarations in your first post. You're also missing the code after the last update statement.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-20 : 15:50:10

It's hard to tell from the fragments you've posted or perhaps I didn't spend enough time looked at it. However, here is a small sample that might get you going:

--  Fail if the customer name and company name are null

INSERT 
	ERLO_ERROR_LOOKUP
	(
		CUSTOMER_ID,
		FINANCE_AGR_NUMBER,
		REASON,
		LOADED_DATETIME
	 )
SELECT
	CUSTOMER_ID,
    AGREEMENT_NUMBER,
    'NO CUSTOMER NAME OR COMPANY NAME',
    GETDATE()
FROM
	-- Tables and Joins
WHERE
	Customer_Name IS NULL
	AND Company_Name IS NULL
	AND LOADED_INDICATOR <> 'R'

IF @@ROWCOUNT > 0
BEGIN
	SET @ERROR_IND = 1
END


-- Not sure where LOAD_ID comes from, but you might be able to use that.
--UPDATE 
--	LOAD_DATA_TABLE
--SET 
--	LOADED_INDICATOR = 'R'
--WHERE 
--	LOAD_ID = @LOAD_ID

-- But probably need to do a simialr join as teh INSERT above
UPDATE 
	LoadTable
SET 
	LOADED_INDICATOR = 'R'
FROM
	LOAD_DATA_TABLE AS LoadTable
	-- Joins to other tables
WHERE
	Customer_Name IS NULL
	AND Company_Name IS NULL
	AND LOADED_INDICATOR <> 'R'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources