Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: action queries running in sql

    Hi all,
    I upsized an access 2003 db to sql server 2008. I am using access 2003 as front end. I have the update queries that I run in a function as follows(sql view) I am using the docmd open query:

    Code:
    UPDATE tblDTF_SHIPMENT_TEMP INNER JOIN dbo_tblZipcode ON tblDTF_SHIPMENT_TEMP.RECIPIENTZIP = dbo_tblZipcode.txtZIPCODE SET tblDTF_SHIPMENT_TEMP.RECIPIENTCITY = [dbo_tblZipcode].[txtCity], tblDTF_SHIPMENT_TEMP.RECIPIENTSTATE = [dbo_tblZipcode].[txtState];
    Code:
    UPDATE tblDTF_SHIPMENT_TEMP INNER JOIN dbo_tblCarrierID ON tblDTF_SHIPMENT_TEMP.CARRIERID = dbo_tblCarrierID.txtCARRIERID SET tblDTF_SHIPMENT_TEMP.CARRIERNAME = [dbo_tblCARRIERID].[txtCARRIERNAME];
    Code:
    UPDATE tblDTF_SHIPMENT_TEMP INNER JOIN qryCOUNTRYREGION ON tblDTF_SHIPMENT_TEMP.RECIPIENTSTATE = qryCOUNTRYREGION.txtSTATE SET tblDTF_SHIPMENT_TEMP.COUNTRYREGRECIPIENT = [qryCOUNTRYREGION].[txtREGIONDESC];
    I also have an append query in this function that runs after these update queries.

    When I run the function like normal from the access front end; it takes a very long time.

    Do I need to convert this to a stored procedure or what do I need to do to cut this time down.

    Please help! Thank you.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Given your code:
    Code:
    UPDATE tblDTF_SHIPMENT_TEMP
       INNER JOIN dbo_tblZipcode
          ON tblDTF_SHIPMENT_TEMP.RECIPIENTZIP = dbo_tblZipcode.txtZIPCODE
       SET
          tblDTF_SHIPMENT_TEMP.RECIPIENTCITY = [dbo_tblZipcode].[txtCity]
    ,     tblDTF_SHIPMENT_TEMP.RECIPIENTSTATE = [dbo_tblZipcode].[txtState];
    
    UPDATE tblDTF_SHIPMENT_TEMP
       INNER JOIN dbo_tblCarrierID
          ON tblDTF_SHIPMENT_TEMP.CARRIERID = dbo_tblCarrierID.txtCARRIERID
       SET tblDTF_SHIPMENT_TEMP.CARRIERNAME = [dbo_tblCARRIERID].[txtCARRIERNAME];
    
    UPDATE tblDTF_SHIPMENT_TEMP
       INNER JOIN qryCOUNTRYREGION
          ON tblDTF_SHIPMENT_TEMP.RECIPIENTSTATE = qryCOUNTRYREGION.txtSTATE
       SET tblDTF_SHIPMENT_TEMP.COUNTRYREGRECIPIENT = [qryCOUNTRYREGION].[txtREGIONDESC];
    I'd suggest that you check to be sure that you have appropriate indexes. Specifically, you need:

    Code:
    Table Name         Index Starts With
    ----------------   -----------------
    dbo_tblZipcode     txtZIPCODE
    dbo_tblCarrierID   txtCARRIERID
    qryCOUNTRYREGION   txtSTATE
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Ok. All of these tables have id fields as primary keys clustered. In the region table, txtState is unique, nonclustered. What should I change. Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are there indexes on the dbo_tblZipcode.txtZIPCODE and the dbo_tblCarrierID.txtCARRIERID columns (so that these columns are the first column listed in at least one index)?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2004
    Posts
    214
    txtZipcode-dbo.tblZipcode$Zipcode(Non-Unique, Non-Clustered)

    dbo.tblCarrierID$PrimaryKey(Clustered)
    no index on txtCarrierID

    How should this be changed pls?

Posting Permissions

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