From my DBA:
performance hit is getting very severe because of more rows now in the table. Two queries against suiteCrm are now the two highest-I/O consuming queries on the entire this SQL instance. 51M I/O and 585 secs/9+ mins of CPU and 33M I/Os and 484 secs/6 mins of CPU. At times, I’ve seen suiteCrm accounting for up to 43% of SQL’s CPU usage.
Using plain literals, the 33M I/O query goes down to 4,366 I/Os and no more than 10 secs of CPU.
Is there any way to get suiteCrm to gen queries using only non-Unicode literals for all values?
From our DBA
I took a look, but those aren’t our issues. It’s only the use of Unicode literals – N’…’ rather than just ‘…’ – that is causing our performance issue. The irony is that the app has the correct indexes in place, but they can’t be used because of the bad literal specification.
The query being run by the application in question is
Specifically, this query:
SELECT contacts.id AS id, accounts.id AS N’accounts_id’ FROM
contacts LEFT JOIN accounts_contacts ON
contacts.id=accounts_contacts.contact_id AND
accounts_contacts.deleted=0
LEFT JOIN accounts accounts ON
accounts.id=accounts_contacts.account_id AND accounts.deleted=0
WHERE accounts.account_type = N’Prospect’ AND NOT EXISTS
(SELECT * FROM aow_processed WHERE
aow_processed.aow_workflow_id=N’7f0e4728-96ec-7a65-7544-57e587065ea2’ AND aow_processed.parent_id=contacts.id AND
aow_processed.status = N’Complete’ AND aow_processed.deleted =
0) AND contacts.deleted = 0
Normally I use SuiteCRM only on Linux, and I have never noticed those N’…’ on the queries, so it must be something specific to the MSSQL engine.
Can you please open an issue with this on GitHub, with your delay measurements? I think it’s worth an effort to fix, but if you can help with a fix yourself, we would really appreciate it, because testing and developing on Windows is not in the DNA of SalesAgility… we’ll probably need the help from the Community on this one.