action queries running slow - 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 2008 Forums

Transact-SQL (2008)

action queries running slow

Author

Topic

slimjen
Yak Posting Veteran

61 Posts

Posted - 2012-06-29 : 22:04:45

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:

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 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-29 : 22:43:12

did you check execution plan and identified costly steps? also is function scalar or table valued?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

slimjen
Yak Posting Veteran

61 Posts

Posted - 2012-06-29 : 22:49:00

No; I didnt. I am still running through function from the access front end. Havent tried running thru pass thur query if this makes difference.

slimjen
Yak Posting Veteran

61 Posts

Posted - 2012-07-02 : 12:01:59

All; I really need help on this. Let me start from the beginning. Before I upsized to sql server 2008 this was and in fact still the scenirio. User import an excel file into a temporary table in access. The reason for the temp table is because the excel file they import isn't complete. They wanted fields to autofil based on data from other fields which are in another table in the database. So, when they import the excel spreadsheet, the field they want autopopulated is already a column in the spreadsheet. Some of the records already has data but the majority of them do not. This spreadsheet also contains address fields which have zipcodes and they wanted to have the city and state populate based on the zipcode field. The zipcode table was pulled from the internet and updated periodically by the admin.
I know the rules of normalization but this spreadsheet is the way the users get it from different companies they deal with and they wanted it in the database as is. So; I accomodated them. I just created a function in Access using docmd transferspreadsheet and update queries to update all the records and called the it from a command button. It didnt take no time at all. The problem is now that the tables are in sql linked to access frontend; this function takes about 20mins to run! Its updated all records for each query. I just need advice on what to change so this can run efficiently.
Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources