Help
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 67
- Joined: Wed Apr 14, 2004 11:33 am
Help
I have 2 similar ds jobs, same structure but touch different file/tables. Both are almost identical and update/insert about 400 records. One takes about a few seconds and the other runs for 12 minutes. Any suggestions on what to look for? Indexing is similar. Any help is much appreciated.
1. Are both tables same size? (number of rows, columns, data types)
2. Are you talking pure inserts, updates, or mixture?
3. What about triggers, any doing behind the scenes work?
4. What about foreign key constraints?
5. Have you divided inserts from updates and can you benchmark how long insert-only processing takes versus update-only processing?
6. Have you looked at where the physical tables reside, are they on the same filesystems, etc?
2. Are you talking pure inserts, updates, or mixture?
3. What about triggers, any doing behind the scenes work?
4. What about foreign key constraints?
5. Have you divided inserts from updates and can you benchmark how long insert-only processing takes versus update-only processing?
6. Have you looked at where the physical tables reside, are they on the same filesystems, etc?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 67
- Joined: Wed Apr 14, 2004 11:33 am
Ken,kcbland wrote:1. Are both tables same size? (number of rows, columns, data types)
2. Are you talking pure inserts, updates, or mixture?
3. What about triggers, any doing behind the scenes work?
4. What about foreign key constraints?
5. Have you divided inserts from updates and can you benchmark how long insert-only processing takes versus update-only processing?
6. Have you looked at where the physical tables reside, are they on the same filesystems, etc?
Tables are almost same size in terms of rows & data types (about 400 records)
2. The job does mixture or inserts and updates, but they are separated.
3. No triggers
4. The PK of each of the 2 tables is a FK in 1 other table for each.
5. Yes, I have a stage to take care of inserts and 1 for updates.
6. Same filesystem.
I'm considering removing the PK/FK on the long running update table, the FK table is a helper table and gets completely refreshed regularly and the build process uses the table in question via hash file, so by default ref. integrity is being enforced.