The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Index Selection
Posted: 08 Jul 08 8:32 AM
|
| It appears that the indexes on our SLX db need reconfigured. Numerous scans are being performed, and in turn, lots of reads are occurring. Almost 8,000 times more reads than writes are occurring on the db. SLX db's seem a little trickier to select indexes for given the primary keys are not sequential, so clustered indexes will not be as effective there. I have taken traces to use with the index tuning wizard, but I have witnessed little or no performance gains. I am at the point where I am fine with manually selecting indexes for highly queried tables. Does anyone have any suggestions for index selection on SLX db's? I realize that everyone's setup is different, but any suggestions would be appreciated. |
|
|
|
Re: Index Selection
Posted: 09 Jul 08 1:00 PM
|
I have spent numerous hours on this !
(a) Drop ALL indexes on your database IF it did not start life as a 7.2 Database
(b) Re-create all indexes as per a 7.2 Database
(c) Add the following helper indexes:
CREATE INDEX [IX_USEROPTIONS3] ON [SYSDBA].[USEROPTIONS]([NAME], [CATEGORY], [USERID]) ON [PRIMARY]
CREATE INDEX [IX_PROGRAM] ON [SYSDBA].[SECFUNCTIONS]([PROGRAM]) ON [PRIMARY]
CREATE INDEX [PICKLIST_ID] ON [sysdba].[PICKLIST]([ID] ASC) ON [PRIMARY]
(d) Ensure you add indexes for AccountManagerID for A/C/O tables (which also still do not exist in 7.2!)
(e) Re-examine the indexes created after (b) and drop any unnecessary ones (like the ticket ones in A/C/O/History) if you aren't using Support functionality
(f) Re-examine what is left and create any other additional indexes for custom cols/tables as appropriate for the app.
Thx
Mike
|
|
|
|
Re: Index Selection
Posted: 09 Jul 08 1:03 PM
|
I have spent numerous hours on this !
(a) Drop ALL indexes on your database IF it did not start life as a 7.2 Database
(b) Re-create all indexes as per a 7.2 Database
(c) Add the following helper indexes:
CREATE INDEX [IX_USEROPTIONS3] ON [SYSDBA].[USEROPTIONS]([NAME], [CATEGORY], [USERID]) ON [PRIMARY]
CREATE INDEX [IX_PROGRAM] ON [SYSDBA].[SECFUNCTIONS]([PROGRAM]) ON [PRIMARY]
CREATE INDEX [PICKLIST_ID] ON [sysdba].[PICKLIST]([ID] ASC) ON [PRIMARY]
(d) Ensure you add indexes for AccountManagerID for A/C/O tables (which also still do not exist in 7.2!)
(e) Re-examine the indexes created after (b) and drop any unnecessary ones (like the ticket ones in A/C/O/History) if you aren't using Support functionality
(f) Re-examine what is left and create any other additional indexes for custom cols/tables as appropriate for the app.
Thx
Mike
|
|
|
|
Re: Index Selection
Posted: 09 Jul 08 1:04 PM
|
| Sorry about double-post! Also, forgot to mention - once you have indexes sorted out - then use SLXProfiler to login to SLX, run a few ordinary user activities (especially where you are seeing slow performance) and run the same queries in QA and find the query plan (ctrl-L, which I am sure you know) and then add further indexes as necessary. |
|
|
|
Re: Index Selection
Posted: 09 Jul 08 1:24 PM
|
| I should have mentioned that we are running SLX 6.2.6. I was about 2/3 there with the helper indexes you had suggested. I have been watching profiler the last few days, checking for long running queries, running them in QA, adding and/or deleting indexes where appropriate, and then rerunning the queries in QA to verify. So it appears we are on the same page for the most part on how to attack this. |
|
|
|
Re: Index Selection
Posted: 09 Jul 08 1:28 PM
|
Nope, that's still absolutely fine and definitely what you want to do !! The 7.2 indexes are much more efficient/appropriate than older ones.
You will notice that there are a LOT of compound indexes that SLX simply doesn't use (if your DB started out as v3/4/5 - these were created then) and simply move up through and upgrade for no purpose. By doing this - I save on average around 1-2 Gb of wasted space !! Also, check INDEXUPDATES - if you are NOT using Speedsearch then you'll want to disable those triggers and truncate this table (bet it's huge !!).
So, in summary, still drop all indexes, and re-create them from the 7.2 eval/blank database. These create new constraints (and, therefore, primary keys) and also use clustered indexes.
Mike
|
|
|
|
Re: Index Selection
Posted: 09 Jul 08 2:04 PM
|
| We do not use SpeedSearch, and you are correct, there are 1.3 million rows in INDEXUPDATES. With which tables are the triggers located? I thought they would have been associated with the corresponding tables, either TICKET, ACTIVITY, HISTORY (from SLXTRIGGERS)... but I cannot locate any. |
|
|
|
Re: Index Selection
Posted: 09 Jul 08 2:08 PM
|
Wow !!
They are not "true" triggers. They fire as part of the provider. Do this:
update slxtriggers set enabled = 'F' where path like 'slxsearch%'
That'll stop them occuring (you will need to stop/start slxserver).
Mike
|
|
|
|
Re: Index Selection
Posted: 09 Jul 08 2:24 PM
|
| Thanks Mike, it will be interesting to see how much space I save when I truncate INDEXUPDATES. Not trigger triggers, SLX would be great like that, wouldn't they. |
|
|
| |
| |
|