06-30-2014 02:07 AM
Having just done a v7.5.3 to v8.1.0 SNC02 everything went well on the client side. They had replaced the SQL Server and SLX Server with brand new equipment.
However, I had written some VB exe's some years previously that clear, update stats, just various housekeeping things. I had already re-compiled and tested for 64bit and the new SQL Native Client. I left the site and assumed all was working - but came back next day to find that some exes were still running and had not completed.
Looking at where in the source it was quite a simple cmd:
UPDATE CONTACT SET Q_COL1 = NULL
Confused, I tested here on my system (previously, I did the typical test - "it runs, it works" !) and got the same result. I then used SQL profiler and could see a lot of unexpected updates occurring. This lead me to triggers and, disabling the ones on the contact table meant my entire app ran in just over 6 mins. Previously, this had started at 19:45 and not completed by the next day (this, it turns out wasn't because it was running - but because the update had timed out.
Anyway, just to say two things - watch the triggers added as part of the upgrade and, to Swiftpage, this is going to cause a lot of issues. Try the above syntax on any DB with around 250,000 contacts and you'll get similar results. It will timeout, due to the triggers. Without triggers, the update will take just under a second or two. With the triggers, SQL Management Studio will happily run the above statement (for over 11 mins before I got bored and killed it). Also note, this is not an SLX column either, it's a custom one.
Now, I run this:
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
Prior to the evening overnight updates. And then re-enable after. We don't use the integration nor the Exchange Link parts so it would be safe to leave disabled.
06-30-2014 06:02 AM
Great catch Miike!
To add.. Liek many of you, I've used triggers in the past to do things.. but put significant constrains on the trigger and it's useage in general. EX: If you have TaskCentre on your system there's a possibility you used TC's SQL triggers... or you nay have put an "InsteadOF" trigger on something to protect it (Pingserver in the SYSTEMINFO table.
Saleslogix/Swiftpage has added a LOT of triggers to the system lately and I've yet to see a comprehensive (or even official list) of their triggers as well as why (each one) exists.
Slx Dev/QA/Support - We need a document that tells us in which versions/updates, etc a trigger was added as well as why it is there.
All we need is "one-liners" listing the table, field (a great way to constrain a trigger), What version/sp/update it was added (or dropped) and what it's for.
Trigger conflict as well as multiple triggers on a table can easily bring a very well tuned SLX db to it's knees quickly. Having these un-documented items in the system is a real problem.
Anyone else feel the same way?
06-30-2014 11:47 PM
Completely agree, we need to have documnetation detailing these triggers, also consideration given for upgrade documentation, i.e. disable XX trigger or all triggers.
07-01-2014 07:33 AM - edited 07-01-2014 07:34 AM
I also agree.
As a developer in a large organization in the fledgling stages of migrating from Oracle to SQL Server (with the main driver being that there are just too many Oracle issues that we have uncovered that negatively impact performance with basic functions), knowing this would help tremendously. We have a large user base who isn't happy with DB and app performance as it is (and we fall under the > 250k Contacts category)
07-01-2014 08:00 AM
Elliot - I take it your Oracle is setup w/an instance that JUST has SLX - right?.... If not, you MUST do this to get around some significant pitfalls (of Oracle).
Between Views and Trigggers you must go nutz ;-) Just glad I no longer have any Oracle based SLX customers anymore!
I had (and will if one pops up) charge them at least a 20% premium or more (per hour/project) to work on their systems.
07-01-2014 08:02 AM
It has been difficult, yes. We have uncovered a lot of issues for SP specific to Oracle. So much so that we've decided the effort isn't worth it anymore and we are aggressively looking into SQL Server. Seeing things like this though give me reason to pause for sure.
07-01-2014 08:11 AM
SQL server is SO MUCH easier to deal with....
A - (modern) SLX was really designed w/SQL server in mind and is developed on it..
B - Backups - what can I say! .. haveing "live" builtin-backup capability in SQL Server w/ZERO third party tools needed makes it a breeze!
C - .. on and on.. and on......
IMHO - Saleslogiox should ONLY support/run on SQL server - I've said this on-line/face-to-face/etc.. many.. many.. many times over the years w/customers and SLX development/program management, etc - all the way back to when SLX first supported SQL 7 (SQL 6.5 was garbage.. it was a port/hack.. and a BAD hack of Sybase).
.. and I'm not alone on this point...
In the beginning we had scaling issues w/SQL server - MicroSquish solved that problem and there's really NO SALESOGIX db in the world that cannot run reliably and VERY FAST on SQL server - BUT you better do your indexing, tuning, etc.. and stay away from RAID5! - The worst RAID configuration out there.. Full Mirror (w/overlapped reads) is the way to go!
07-01-2014 08:16 AM
I'll give you BIG credit on the JBOD.. I tend to forget that one... Only seen it on "file servers" that were not into performance..
07-01-2014 01:59 PM
Mike, the database triggers were created to replace the provider triggers for integration (X3, Outlook, Google, and soon Exchange).
They track any change to tables we sync with integrations. The reason a statement like “UPDATE CONTACT SET Q_COL1 = NULL” has such a large performance impact is because the triggers have to process each row of the update individually. If this type of change is not required to be tracked for Integration purposes then it would be best to disable the triggers as you have done before applying the update.
Here is a complete list of all the triggers and their function as well as a script for disabling/enabling all SLX triggers on MSSQL.
ACCOUNT_INTEGRATION_INSERT - Captures change info for Integration. Updates ErpEmailAddress and ErpPhoneNumber tables if phones/emails have a value.
ACCOUNT_INTEGRATION_CHANGE - Captures change info for Integration. Updates ErpEmailAddress and ErpPhoneNumber tables if phones/emails have changed.
ACCOUNT_TOMBSTONE – Records the deletion of the account record to the GlobalChangeTracking table. (Currently for Integration only)
ACTIVITY_INTEGRATION_CHANGE - Captures change info for Integration.
ACTIVITYATTENDEE_COUNT – Updates the Activity AttendeeCount field for any insert or delete. (This trigger has nothing to do with Integrations)
ACTIVITYATTENDEE_INTEGRATION_CHANGE - Captures change info for Integration.
ADDRESS_INTEGRATION_CHANGE - Captures change info for Integration.
ADHOCGROUP_INTEGRATION_INSERT - Captures change info for Integration. Only tracks Contact additions for the specific user.
ADHOCGROUP_INTEGRATION_TOMBSTONE – Records the contact removal from the AdhocGroup as a deletion for that user in the GlobalChangeTracking table.
CONTACT_INTEGRATION_INSERT - Captures change info for Integration. Updates ErpEmailAddress and ErpPhoneNumber tables if phones/emails have a value.
CONTACT_INTEGRATION_CHANGE - Captures change info for Integration. Updates ErpEmailAddress and ErpPhoneNumber tables if phones/emails have changed.
CONTACT_TOMBSTONE - Records the deletion of the contact record to the GlobalChangeTracking table. (Currently for Integration only)
USERACTIVITY_INTEGRATION_CHANGE - Captures change info for Integration.
USERACTIVITY_TOMBSTONE - Records the UserActivity deletion as an Activity deletion for that user in the GlobalChangeTracking table.
ALTER TABLE SYSDBA.ACCOUNT DISABLE TRIGGER ALL
ALTER TABLE SYSDBA.ACTIVITY DISABLE TRIGGER ALL
ALTER TABLE SYSDBA.ACTIVITYATTENDEE DISABLE TRIGGER ACTIVITYATTENDEE_INTEGRATION_CHANGE
ALTER TABLE SYSDBA.ADHOCGROUP DISABLE TRIGGER ALL
ALTER TABLE SYSDBA.CONTACT DISABLE TRIGGER ALL
ALTER TABLE SYSDBA.USER_ACTIVITY DISABLE TRIGGER ALL
ALTER TABLE SYSDBA.ACCOUNT ENABLE TRIGGER ALL
ALTER TABLE SYSDBA.ACTIVITY ENABLE TRIGGER ALL
ALTER TABLE SYSDBA.ACTIVITYATTENDEE ENABLE TRIGGER ACTIVITYATTENDEE_INTEGRATION_CHANGE
ALTER TABLE SYSDBA.ADHOCGROUP ENABLE TRIGGER ALL
ALTER TABLE SYSDBA.CONTACT ENABLE TRIGGER ALL
ALTER TABLE SYSDBA.USER_ACTIVITY ENABLE TRIGGER ALL