Performance Improvement Tips

[opensource]

Hello Team,

Wondering if any of you had any Performance Improvement Tips

I know moving from windows to linux for the database server might improve performance but before I take that step, I wanted to see if I have exhausted all other ways in which I could improve performance. BTW, how fast is the response between windows and linux. Is it 3 times faster, 5 times or does it depend

I also see that the emailmessage and and securitylog tables are increasing in size exponentially. My entire database is at 34MB or so but the size of just the two above tables is more than 10MB. Does that impact performance ?. Is there is a way or a plan to purge/archive that data ?

Are all the indexes already in place to ensure optimal performance or has anyone created un-documented indexes which might improve performance ?

Any and every other Performance Improvement tips is much appreciate. There are times when I run two instances of opendental on one machine does that impact the performance adversely.

Thanks in advance for sharing your thoughts / ideas.

Cheers,

OpenSource

[jordansparks]

Moving to a Linux server does not increase performance afaik. Response time seems to be the same. Sure, securitylog table will increase. That's the downside of keeping a history of all changes. 10MB isn't really that big of a deal when you think about it. But emailmessage? I can't imagine for the life of me why that table would be big. Are you sending lots and lots of email? It's just simple text storage, so I don't know. Anyway, the size should not affect performance. We have already put all indexes in place that users seem to need. I would not recommend adding indexes, because it is likely to cause an upgrade failure later if we try to add the same index or something similar.

[opensource]

Firstly, As always....appreciate the quick response from you Dr. Jordan

The emailmessage table has 5000+ records. First message was created on 02/08/05. The securitylog too has 80,000+ records. First message around the same date as above.

since none of the above impact performance, I guess I will not bother about it until I have space problems but will there be any downside if I delete data that is older than 1 year or 2 based on what the office would like to keep.

Thanks for the headsup on the impact of creating additional indexes and also your thoughts on migration to linux server. BTW, I am currently on 5.3.14

Cheers,

OpenSource

[jordansparks]

Well, I really don't think it's a good idea to do any manual manipulation of the database, and that includes adding indexes, deleting old data, or whatever. Having said that, I think it's probably safe to delete old securitylog entries. It's also safe to clear out the signal table. But do this at your own risk.

[V Suite]

Aren't the securitylog entries used in Audit Trail? The securitylog entries cannot be edited from within OpenDental, many other edits within OpenDental are actually copied and then edited, and the permissions for making changes to the database (editing or entering data) can be set separately. While they are good for internal checking, aren't they important to be able to 'guarantee' that the data has not been tampered with?

If it is possible to edit the database outside of OpenDental, wouldn't that create worse headaches for a systems admin - if anyone could do it? I guess this is why the passwords need to be set on the mysql side as well as OpenDental side. And then, wouldn't someone be able to argue that the database is not secure. HIPAA, etc compliance?

If your office staff are not careful during data entry, and need to later correct mistakes, this will increase the securitylog entries. If you disallow editing completed payments/procedures, then they will have to double check before clicking okay or require to call the systems/admin or whatever each time. Soon there will be fewer mistakes. (And no chance for cover ups).

[mowgli]

A partial deletion of "old" entries wouldn't work too well right now, because when the lower-numbered entries are missing, the others get pulled into the audit trail screen sorted by securitylognum "alphabetically," instead of by date. Entry number 1053 would appear before number 171, for example--the 2nd character being lower in the first number. So you would get more recent entries appearing before older ones and the dates would be mixed up. All the entries would need to be deleted, if any.