MSSQL High IO caused by Suite

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?

I don’t know the answer to your question about literals, but you can start by hunting down overgrown tables in the database.

You’ll find some tips here

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 AS id, AS N’accounts_id’ FROM
contacts LEFT JOIN accounts_contacts ON AND
LEFT JOIN accounts accounts ON 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 AND
aow_processed.status = N’Complete’ AND aow_processed.deleted =
0) AND contacts.deleted = 0

Ok, I see what you mean now.

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.

Thanks for reporting