Link to home
Start Free TrialLog in
Avatar of MIGINC
MIGINC

asked on

UPS Worldship Automatic Import/Export Painfully Slow - SQL Process Duration Extremely High

We recently upgraded our SQL server from v2000 to v2005. A side-effect of that upgrade was that our UPS Worldship software's Import/Export functionality started taking an extremely long time to launch and process transactions.

The issue we have is very similar to the issue described here, though our setup is different: http://gps678.com/21/62328ef335fee782.html

We have UPSWS configured to Import data from our ERP application's DB on the SQL server using a key we manually enter (Packing Slip ID). We then populate the rest of the shipment details in UPSWS. Once this step is completed, the shipment is processed in UPSWS. At that time, UPSWS automatically Exports the shipment details back into our ERP DB.

Either process (import/export) should be almost instantaneous. However, each transaction is taking upwards of 5 full minutes to complete. When you're processing hundreds of shipments per day, this becomes impossible to live with.

We've had mixed results in dealing with this issue. UPS has not been able to provide any information in dealing with the problems we're experiencing, so we've found what we thought to be an acceptable workaround. For a solid month or two, any time this issue creeped up, we'd detach and reattach our ERP database. For whatever reason, that seemed to do the trick. UPSWS behaved as it should until the next UPSWS update came down the pipeline.

However, after this most recent update (delivered Tuesday), our workaround is no longer working.

When we perform a trace on the SQL server, we're seeing that UPSWS is performing an 'exec sp_columns' and 'exec sp_tables' on the entire ERP database. This is fine. The problem is that each event is taking > 1000ms to complete. When working properly, these events take approximately 5ms to complete.

We've tried reindexing, detaching/reattaching, switching compatibility modes, dropping the SQL account and recreating, remapping the ODBC connection on the UPSWS client, and probably some things I'm forgetting.

What can I do to get these process duration times down - and keep them down permanently?

I'm attaching samples of a good trace along with a bad one.
upsws-traces.zip
Avatar of Mark Wills
Mark Wills
Flag of Australia image

When For starters, have you got all the latest Service packs ? For example SP2 does show some fixes for slow performance on catalogue stored procedures (see http://support.microsoft.com/kb/921896 ).

The catalogues for 2005 are different from 2000, and there is a bit of a "hit", but as your "good" example suggests, should be considerably quicker (my 2005 certainly is a lot quicker).

How did upgrade to 2005 ? When you changed compatibility modes did you do UPDATE_STATISTICS and a re-index. Have you run an update usage ?

Basically when upgrading to 2005, I did the following...

1) restore database
2) change compatability mode to 90
3) dbcc checkdb
4) dbcc redindex on every table
5) update_statistics on every table
6) dbcc updateusage
7) maintenance plan for DB - log files backup, full nightly backups, set sizes, weekly optimize

Ideally, stop the procedures going through the miriad of views which have little or no reference to your app database. Do not know of a way off the top of my head to stop that behaviour, but then again, it should be qorking a lot quicker than you have shown in the "bad" example.
Avatar of MIGINC
MIGINC

ASKER

9.00.3068.00    SP2    Standard Edition

How did upgrade to 2005?
It's been about a six months, so I honestly don't recall the steps we followed.

When you changed compatibility modes did you do UPDATE_STATISTICS and a re-index. Have you run an update usage?
I did the re-index, but did not run UPDATE_STATISTICS or usage. We're currently in compatibility mode for SQL 2000 (80).

If I'm understanding correctly, I think your last suggestion would require getting into the code behind the UPS app itself. That's something the UPS developers would have to do, so it's not really an option for me. You know how it is: developers don't write bad code. Ever. So it must be a problem on my end....

In all seriousness, the more I play with this, the more I become convinced that it's a bug in their code - or it's simply non-optimized code - and that SQL 2000 was just more forgiving than 2005.

Is there a fast way to dbcc reindex and update_statistics on every table? There are 645 tables in this particular DB.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MIGINC

ASKER

I get a ton of these in return:

Check Catalog Msg 3853, State 1: Attribute (object_id=5952189) of row (object_id=5952189,column_id=2) in sys.columns does not have a matching row (object_id=5952189) in sys.objects.

Msg 8986, Level 16, State 1, Line 16

Too many errors found (201) for object ID 0. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".

CHECKDB found 0 allocation errors and 67434 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 67434 consistency errors in database 'iERP81'.
well now, that was a bit unexpected, but, probably explains part of the problem. Make sure you have a backup of the database and you sould run the REPAIR option with the CHECKDB - and I would do it interactively...
How did you go with the Repair ? just in case, the correct syntax would be :

ALTER DATABASE iERP81 SET single_user

DBCC CHECKDB ('iERP81', REPAIR_REBUILD  )   -- or, if more serious, might need :  REPAIR_ALLOW_DATA_LOSS

ALTER DATABASE iERP81 SET multi_user
Avatar of MIGINC

ASKER

I couldn't run it till after hours, so it's going right now. I'll post back after completion.
Avatar of MIGINC

ASKER

It ran for about 10 hours overnight and was still going this morning. Since you have to put the DB in single user mode, I had to kill the repair so people could work this morning.

*sigh*

I'm going to try getting an earlier start on it tonight. Hopefully it'll have enough time to complete this time.
REPAIR_REBUILD is the most extensive and time consuming... If you can fit it in overnight, would be the best - but again, make sure you do backups first...
Avatar of MIGINC

ASKER

Well, problem is gone for now. I don't think it's fixed at this point, so I'm going to have to wait till it rears its ugly head again.

You did help me to uncover some other issues with my databases. Even after running REPAIR_ALLOW_DATA_LOSS, I'm still getting those consistency errors. However, I'm going to have to treat that as a separate issue to keep this posting on topic.

Thanks for your help.
Avatar of MIGINC

ASKER

I'm awarding a 'B' only because the solution provided didn't actually resolve my issue, though it was very informative and helped to uncover additional issues.