Index on pr_operators.pyuseridentifier
Hi
We quite often open Operator by pyuseridentifier in activities. Just tinking about create index on pyuseridentifier column. Is it ok? Why this kind of index is not OOTB?
Regards,
Adam
**Updated by moderator: Marissa. Removed user added Ask the Expert tag. Apologies for confusion, shouldn't have been an end-user option***
Accepted Solution
Baxter
US
Hi all,
pyUserIdentifier is key property of Data-Admin-Operator-ID class.
When we use Obj-Open by pyuseridentifier SQL is:
select * from DEVGBLD.pr_operators where pzInsKey = ?
pzInsKey is PK on this table and by default there is a index on it. That's why there is no index on pyuseridentifier column. IMHO in same cases we can create index on pyusername. Be cerful of it - if you use autocomplite component with report that "contains" filter criteria on this property SQL is:
select * from DEVGBLD.pr_operators where pzusername like %?%
and index is useless (not used)
Regards,
Adam
Virtusa
US
you should open a SR for a traceability on modifying a pega shipped DB table- for future tracking and consultation. I have personally done indexing on the pyuseridentifier on pr_operators; it may affect performance (positively)- if there is an extensive usage/manipulation on the operator ID table or you have reporting capabilities - it definitely will help in getting speed up.
Thanks !
Pegasystems Inc.
IN
Hi Adam,
Let us know if you do log an SR with Pega Support for this and its ID. That way we can track it and follow-up this discussion with the resolution.
Hope to see you around the Community!
Lochan | Community Moderator | Pegasystems Inc.
Pegasystems Inc.
AU
Any addition indexes that you create in the Pega schema, other than that comes with OOTB scripts, we call them as custom index’s. We (Pega) don’t restrict you from creating new index’s, you can always evaluate the necessary of creating new index’s whenever required to address the performance issue.
Please note that it’s not advisable to create any custom index’s in production environment until and unless your DBA justifies the index requirement. Such changes should b tested in the lower environment before going to the production.
Accepted Solution
Baxter
US
Hi all,
pyUserIdentifier is key property of Data-Admin-Operator-ID class.
When we use Obj-Open by pyuseridentifier SQL is:
select * from DEVGBLD.pr_operators where pzInsKey = ?
pzInsKey is PK on this table and by default there is a index on it. That's why there is no index on pyuseridentifier column. IMHO in same cases we can create index on pyusername. Be cerful of it - if you use autocomplite component with report that "contains" filter criteria on this property SQL is:
select * from DEVGBLD.pr_operators where pzusername like %?%
and index is useless (not used)
Regards,
Adam