Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    10

    Unanswered: Update and Subqueries

    Hi there

    We have an update program that we send periodically to users with a batch of new data. The program simply compares the new data with the old data and updates certain fields in the users database. (I am keeping it real simple).

    Recently I have rewritten the program to speed it up. I load the data into a temporary table using bulk insert and then run an SQL query to update the relevant table.

    The problem is that when this SQL is executed within a transaction it locks up and times out. Outside of a transaction it takes less than 20 seconds. There are about 40000 records.

    It locks up doing the UPDATE SET command. I suspect it is because the transaction locks the current record in the UPDATE, and when the subquery in the SET clause tries to reference that record it hangs.

    Here is what that SQL looks like (it is actually executed within VB code, but this pure sql code generates the same result):

    ##########################################

    BEGIN TRANSACTION

    SELECT TOP 1 * INTO #tempWBranch FROM wBranch
    Go

    DELETE FROM #tempWBranch
    Go

    BULK INSERT #tempWBranch FROM 'd:\union\update\wBranch.txt'
    Go


    UPDATE wBranch SET sBrcNm = (Select #tempWBranch.sBrcNm FROM #tempWBranch WHERE #tempWBranch.sBnkCd = wBranch.sBnkCd AND #tempWBranch.sBrcCd = wBranch.sBrcCd), sStnKn = (Select #tempWBranch.sStnKn FROM #tempWBranch WHERE #tempWBranch.sBnkCd = wBranch.sBnkCd AND #tempWBranch.sBrcCd = wBranch.sBrcCd)
    Go


    DROP TABLE #tempWBranch
    Go

    END TRANSACTION

    ##########################################
    I am sure that I am making a real bozo design error.

    Can anyone help me resolve this?

    Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    10

    Work Around

    for the record:

    It seems that there is something going on with the temporary table thing.

    I moved the bulk insert (into the temporary table) outside of the transaction and the problem seems to be resolved.

Posting Permissions

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