Query Run time help - SQL Server Forums

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 2005 Forums

SSIS and Import/Export (2005)

Query Run time help

Author

Topic

jmiilman
Starting Member

4 Posts

Posted - 2010-02-01 : 09:24:44

Hello,
I am hoping that someone can help me out here. I have an issue where I am importing data from an access db into an sql db. Some of the fields contain Ascii Nul value characters in them. I tried to find a solution to remove them with no success. I have created a little script to run through the fields and manually remove them. I'm sure there must be a better way to do this but I'm not sure how so for now this is what I have come up with.

The issue I am having is that I put the data into a cursor, go through each cursor line, check each field to see if it contains ascii null values and then remove them. For now I am only looking at 2 fields - AccountNumber and ARAccountNumber.

If I look at AccountNumber and clear out any null values, then look at the ARAccountNumber and clear out any null values (1 cursor line at a time), then update the table with the new values containing no ascii nul values using the AccountNumber as my where.... the query runs for over 2 mins before I stop it.

If I look at AccountNumber and clear out any null values, update the table with my new AccountNumber containing no ascii nulls, then look at the ARAccountNumber and clear out any null values, update the table with the new ARAccountNumber containing no ascii nul values using the AccountNumber as my where for both updates.... the query runs for over 2 mins before I stop it.

If I look at AccountNumber and clear out any null values, update the table with my new AccountNumber containing no ascii nulls, then look at the ARAccountNumber and clear out any null values, update the table with the new ARAccountNumber containing no ascii nul values using the AccountNumber as the where for the AccountNumber update and ARAccountNumber as the where for the ARAccountNumber update .... the query completes in 33 seconds.

I only want to use the AccountNumber for my where but the running time is just insanely different. I'm not sure what I am missing but there is something I am not doing correctly in order for it to take so long to run.

The folloiwng is my code, if anyone can help me in the least little bit I would greatly appreciate it.....

DECLARE ABNULL CURSOR FOR
SELECT rtrim(AccountNumber),ARAccountNumber,ARCompany,TermsCode,MailInvoiceTo,
LocalWATS,Division,TaxCode,BillReservedField2,BillReservedField4,BankCreditCard,
AssignInvcToAcnt,InvcIncludedInAcnt,BillReservedField1,BillReservedField2,
BillReservedField3,BillReservedField6,[Statement],BillReservedField5,
AccountOnCreditHold,Location,BillingCode1,BillingCode2,Credit,SalesRep,
Country,BranchCreditCard,AccountCreditCard,AccountOriginatedBy,AccountOwnedBy,
BillingFrequency
FROM SIMSIITest.dbo.AccountBillingTbl as AB

OPEN ABNULL

FETCH NEXT FROM ABNULL
INTO @AccountNumber,@ARAccountNumber,@ARCompany,@TermsCode,@MailInvoiceTo,
@LocalWATS,@Division,@TaxCode,@BillReservedField2,@BillReservedField4,@BankCreditCard,
@AssignInvcToAcnt,@InvcIncludedInAcnt,@BillReservedField1,@BillReservedField2,
@BillReservedField3,@BillReservedField6,@Stmnt,@BillReservedField5,
@AccountOnCreditHold,@Location,@BillingCode1,@BillingCode2,@Credit,@SalesRep,
@Country,@BranchCreditCard,@AccountCreditCard,@AccountOriginatedBy,@AccountOwnedBy,
@BillFrequency

WHILE @@FETCH_STATUS = 0
BEGIN

/* ACCOUNT # - REMOVE ASCII NULS FROM THE VALUE */
SET @STARTPNT = 0
SET @ORIGACCT = @AccountNumber
SET @CNTCHAR = LEN(@AccountNumber)
SET @CNTVAR = 1

WHILE @STARTPNT = 0 AND @CNTVAR <= @CNTCHAR
BEGIN

IF ASCII(SUBSTRING(@AccountNumber,@CNTVAR,1)) = 0
BEGIN
SET @STARTPNT = @CNTVAR

SET @ENDPNT = (LEN(@AccountNumber) - @STARTPNT) + 1
SET @AccountNumber = STUFF(@AccountNumber,@STARTPNT,@ENDPNT,'')

/**
UPDATE SIMSIITest.dbo.AccountBillingTbl
SET AccountNumber = rtrim(@AccountNumber)
WHERE AccountNumber like @ORIGACCT
*/

END

SET @CNTVAR = @CNTVAR + 1

END /* WHILE */

/* ARACCOUNT # - REMOVE ASCII NULS FROM THE VALUE */
SET @STARTPNT1 = 0
/*SET @ORIGACCT1 = @ARAccountNumber*/
SET @CNTCHAR1 = LEN(@ARAccountNumber)
SET @CNTVAR1 = 1

WHILE @STARTPNT1 = 0 AND @CNTVAR1 <= @CNTCHAR1
BEGIN

IF ASCII(SUBSTRING(@ARAccountNumber,@CNTVAR1,1)) = 0
BEGIN
SET @STARTPNT1 = @CNTVAR1

SET @ENDPNT1 = (LEN(@ARAccountNumber) - @STARTPNT1) + 1
SET @ARAccountNumber = STUFF(@ARAccountNumber,@STARTPNT1,@ENDPNT1,'')

/**
UPDATE SIMSIITest.dbo.AccountBillingTbl
SET
ARAccountNumber = rtrim(@ARAccountNumber)
WHERE AccountNumber like @ORIGACCT
**/

END

SET @CNTVAR1 = @CNTVAR1 + 1

END /* WHILE */

/*****
UPDATE SIMSIITest.dbo.AccountBillingTbl
SET AccountNumber = rtrim(@AccountNumber),
ARAccountNumber = rtrim(@ARAccountNumber)
WHERE AccountNumber like @ORIGACCT
***/

FETCH NEXT FROM ABNULL
INTO @AccountNumber,@ARAccountNumber,@ARCompany,@TermsCode,@MailInvoiceTo,
@LocalWATS,@Division,@TaxCode,@BillReservedField2,@BillReservedField4,@BankCreditCard,
@AssignInvcToAcnt,@InvcIncludedInAcnt,@BillReservedField1,@BillReservedField2,
@BillReservedField3,@BillReservedField6,@Stmnt,@BillReservedField5,
@AccountOnCreditHold,@Location,@BillingCode1,@BillingCode2,@Credit,@SalesRep,
@Country,@BranchCreditCard,@AccountCreditCard,@AccountOriginatedBy,@AccountOwnedBy,
@BillFrequency

END /** WHILE END **/

CLOSE ABNULL
DEALLOCATE ABNULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 09:30:41

why are you doing this via t-sql query? isnt it enough to do this via conditional transform in data flow task?