Dovetail SchemaEditor vs. ddcomp – or – why is ddcomp so slow?
While doing some prep for my upcoming webinar on Clarify/Dovetail Schema 101, I started doing some closer looking into what SchemaEditor and ddcomp actually do under the hood.
We’ve had some good reports from customers on how much faster schema changes get applied when using Dovetail SchemaEditor. I knew it as well, but I didn’t really know why. So I put on my Encyclopedia Brown hat and started my investigation.
The scenario
My test case was pretty simple: add a new column to the case table. A pretty common scenario.
Dovetail SchemaEditor
I ran SchemaEditor, adding a new x_test_integer column. I examined the logs emitted by SchemaEditor, as well as the SQL Profiler trace.
Here are the changes SchemaEditor made to the database:
1. Add the new column to the metadata (adp_sch_info table)
INSERT INTO adp_sch_info (…) VALUES (…) @p10 = ‘x_test_integer’
Note: I’m snipping the actual SQL statements for brevity throughout this post, but the core of the SQL statement should be clear.
2. Add the new column to the actual case table
ALTER TABLE table_case ADD x_test_integer integer
3. Drop and re-create the csp_get_case stored procedure
This is a baseline Clarify Stored Procedure for retrieving a case and all its data. In order to include the new column, this procedure must be recreated.
IF EXISTS (…) DROP PROC csp_get_case
CREATE PROCEDURE csp_get_case …
4. Give execute rights to this stored procedure
GRANT EXEC ON csp_get_case…
That’s it. That’s the changes SchemaEditor makes to the database.
Clarify ddcomp
Next up, try the same test case using ddcomp.
It does the same 4 things as above:
1. Add the new column to the metadata (adp_sch_info table)
2. Add the new column to the actual case table
3. Drop and re-create the csp_get_case stored procedure
4. Give execute rights to this stored procedure
But then it does a bunch more…
5. Drop and then re-create all of the views that the case table is involved in
In my database, there were 71 views. So I saw:
DROP VIEW… (71 of these)
CREATE VIEW… (71 of these)
Give proper grants on all of the view that the case table is involved in (71 of each of these):
grant SELECT, UPDATE, INSERT, DELETE on table_… to cl_user
grant ALL on table_… to administrator
grant SELECT on table_… to reporter
6. Drop and then re-create all of the csp_ stored procs that the case table is involved in
drop procedure csp_… (72 of these)
create procedure csp_… (72 of these)
grant execute on csp_… (72 of these)
7. Delete and then insert all of the ADP data for the case table
8. Delete and then insert all of the ADP data for all of the views that the case table is involved in
For steps 7 and 8, this resulted in:
289 individual “delete from adp” statements
2812 individual “insert into adp” statements
9. Drop all case indexes
DROP INDEX table_case.objindex
DROP INDEX table_case.case_index
DROP INDEX table_case.ind_case_owner2user
DROP INDEX table_case.ind_case_wip2wipbin
DROP INDEX table_case.ind_case_currq2queue
DROP INDEX table_case.ind_entitlement2contract
DROP INDEX table_case.ind_case_reporter2contact
DROP INDEX table_case.ind_case_creation_time
DROP INDEX table_case.ind_case_state2condition
DROP INDEX table_case.ind_case_prod2site_part
DROP INDEX table_case.ind_casests2gbst_elm
DROP INDEX table_case.ind_entitle2contr_itm
DROP INDEX table_case.ind_case_title
DROP INDEX table_case.ind_case_reporter2site
DROP INDEX table_case.ind_case_victim2case
DROP INDEX table_case.co2u0000
DROP INDEX table_case.cs2w0000
DROP INDEX table_case.case_index
10. Re-create all case indexes
create UNIQUE index case_index on table_case (id_number)
create UNIQUE index ind_case_wip2wipbin on table_case (case_wip2wipbin, objid)
create UNIQUE index ind_case_currq2queue on table_case (case_currq2queue, objid)
create index ind_case_owner2user on table_case (case_owner2user, case_wip2wipbin)
create index ind_entitlement2contract on table_case (entitlement2contract)
create index ind_case_reporter2contact on table_case (case_reporter2contact)
create index ind_case_creation_time on table_case (creation_time)
create index ind_case_state2condition on table_case (case_state2condition)
create index ind_case_prod2site_part on table_case (case_prod2site_part)
create index ind_casests2gbst_elm on table_case (casests2gbst_elm)
create index ind_entitle2contr_itm on table_case (entitle2contr_itm)
create index ind_case_title on table_case (title)
create index ind_case_reporter2site on table_case (case_reporter2site)
create index ind_case_victim2case on table_case (case_victim2case)
create index co2u0000 on table_case (case_originator2user, case2address)
create index cs2w0000 on table_case (case_soln2workaround
Dropping and recreating all of that ADP data seems like a lot, but it’s not really *that* bad. Same with all of the views.
But once I saw steps 9 and 10, I understand the slowness. ddcomp was dropping and recreating all of the indexes on the case table. Even though the new column being added didn’t have anything to do with any of these indexes.
Now, if you have a small development database with 100 rows in the case table, this isn’t a big deal. But in real-world scenarios, there are millions of rows in the case table. And re-creating all of those indexes can take a lot of time. Mention to your DBA that you want to drop and recreate all of the indexes on the case table – see what she says.
Don’t do unnecessary work
Basically, Dovetail SchemaEditor is faster because it doesn’t do unnecessary work – especially long-running, expensive work such as recreating indexes.
Built into SchemaEditor is a differencing engine. Its job is to go through and compare the current database schema with the change you’re making, and build a dependency tree of changes that need to be made (and no more). As in the test case above, the differencing engine knows that if you add a new field to a table, you have to rebuild the csp stored proc, but you don’t have to rebuild any indexes (unless this column is involved in the index, which, in our test case, it isn’t). This is not a trivial chunk of code, but it’s what helps SchemaEditor be efficient and complete.
Amdocs SchemaManager
As of Amdocs 6 (Clarify 13), ddcomp was replaced by SchemaManager. I haven’t done much in the way of in-depth analysis of it yet, but perhaps I’ll try to tackle that soon. I’ve heard mixed reports form the field. Sounds like some things with SchemaManagerare better, but I’ve also heard some reports that it’s still much slower than expected. Sounds like a topic for a future post.
Do your own tests
I encourage you to use enable SQL logging next time you make a schema change to your database. I have a hunch you’ll be surprised to see what is actually happening.
Learn More
Want to learn more about your Clarify/Dovetail schema, including tools and tips? Sign up for my upcoming Schema 101 webinar.
Interested in a more advanced session? I’m considering doing a Schema 201 session, including items such as SQL Views, Database Flags, Change Fields, Pseudo Relations, MTM tables, ADP in more detail, etc. If this is something you’re interested in – please let me know! I’ll probably only do this if there’s enough interest up front. You can leave a comment here, or drop me an email at gary@dovetailsoftware.com
