starred/SuiteCRM-SuiteCRM

Fork 0

mirror of https://github.com/SuiteCRM/SuiteCRM.git synced 2026-03-02 19:16:58 -05:00

Performance problem due to id being implicitly converted #5108

New issue

Open

opened 2026-02-20 16:29:45 -05:00 by deekerman · 3 comments

deekerman commented

2026-02-20 16:29:45 -05:00

Owner

Originally created by @imens-frankd on GitHub (Oct 2, 2023).

Select statements which retrieve a records based on the id have bad performance on mssql.
This is due tot the fact that the generated sql statement pass the id as a nvarchar
'''
SELECT id ,accounts.name as parent_name , accounts.assigned_user_id owner FROM accounts WHERE deleted=0 AND id=N'14110000-568c-0050-4dbd-08db66e86890'
'''
but the id column in the database is varchar.
Because of this mssql uses a index scan instead of an index seek for the id.
This is much slower and uses up a lot of IO on the sql server.

Expected Behavior

The generated sql statement should use varchar as type for the id value it passes.
'''
SELECT id ,accounts.name as parent_name , accounts.assigned_user_id owner FROM accounts WHERE deleted=0 AND id='14110000-568c-0050-4dbd-08db66e86890'
'''

Actual Behavior

The generated sql statement uses nvarchar as type for the id value it passes.
'''
SELECT id ,accounts.name as parent_name , accounts.assigned_user_id owner FROM accounts WHERE deleted=0 AND id=N'14110000-568c-0050-4dbd-08db66e86890'
'''

Context

This results in overloading of our sql server.
This should be high priority

Originally created by @imens-frankd on GitHub (Oct 2, 2023). Select statements which retrieve a records based on the id have bad performance on mssql. This is due tot the fact that the generated sql statement pass the id as a nvarchar ''' SELECT id ,accounts.name as parent_name , accounts.assigned_user_id owner FROM accounts WHERE deleted=0 AND id=N'14110000-568c-0050-4dbd-08db66e86890' ''' but the id column in the database is varchar. Because of this mssql uses a index scan instead of an index seek for the id. This is much slower and uses up a lot of IO on the sql server. #### Expected Behavior The generated sql statement should use varchar as type for the id value it passes. ''' SELECT id ,accounts.name as parent_name , accounts.assigned_user_id owner FROM accounts WHERE deleted=0 AND id='14110000-568c-0050-4dbd-08db66e86890' ''' #### Actual Behavior The generated sql statement uses nvarchar as type for the id value it passes. ''' SELECT id ,accounts.name as parent_name , accounts.assigned_user_id owner FROM accounts WHERE deleted=0 AND id=N'14110000-568c-0050-4dbd-08db66e86890' ''' #### Context This results in overloading of our sql server. This should be high priority

deekerman added the

Status: Requires Updates

Area: Databases

labels

2026-02-20 16:29:45 -05:00

deekerman commented

2026-02-20 16:29:47 -05:00

Author

Owner

@johnM2401 commented on GitHub (Oct 11, 2023):

Hey!

Thanks for getting in touch!

I've had a test of this on my local environment, (PHP8.2, Suite 7.14.1, Ubuntu 18.04, mariadb 10.4.12)

However, it does not appear to be generating as nvarchar on my system, i'm afraid.

An Example query is:

Wed Oct 11 15:20:52 2023 [478][1][DEBUG] Retrieve Account : SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE accounts.id = '32b6a426-024e-81b0-1326-6511a0873f47' AND accounts.deleted=0

Would you be able to provide some information on your environment?
Such as:

SuiteCRM Version used:
Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)):
Environment name and version (e.g. MySQL, PHP 7):
Operating System and version (e.g Ubuntu 16.04):

Or any specific steps to replicate?
(ie: Changing field types in Studio, Specific Queries [if any] )

Thanks!

@johnM2401 commented on GitHub (Oct 11, 2023): Hey! Thanks for getting in touch! <br> I've had a test of this on my local environment, (PHP8.2, Suite 7.14.1, Ubuntu 18.04, mariadb 10.4.12) However, it does not appear to be generating as nvarchar on my system, i'm afraid. An Example query is: ``` Wed Oct 11 15:20:52 2023 [478][1][DEBUG] Retrieve Account : SELECT accounts.*,accounts_cstm.* FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c WHERE accounts.id = '32b6a426-024e-81b0-1326-6511a0873f47' AND accounts.deleted=0 ``` Would you be able to provide some information on your environment? Such as: * SuiteCRM Version used: * Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): * Environment name and version (e.g. MySQL, PHP 7): * Operating System and version (e.g Ubuntu 16.04): Or any specific steps to replicate? (ie: Changing field types in Studio, Specific Queries [if any] ) Thanks!

deekerman commented

2026-02-20 16:29:47 -05:00

Author

Owner

@imens-frankd commented on GitHub (Oct 12, 2023):

Hi John,

we are using V7.11.12

The code adds the character N in front of all single quoted parts in the SQL. This tells mssql that the value in the SQL statement is off type nvarchar. See : github.com/salesagility/SuiteCRM@c43eaa311f/include/database/MssqlManager.php (L1893)

The columns in our database however are sometimes varchar.

@imens-frankd commented on GitHub (Oct 12, 2023): Hi John, we are using V7.11.12 The code adds the character N in front of all single quoted parts in the SQL. This tells mssql that the value in the SQL statement is off type nvarchar. See : https://github.com/salesagility/SuiteCRM/blob/c43eaa311fb010b7928983e6afc6f9075c3996aa/include/database/MssqlManager.php#L1893 The columns in our database however are sometimes varchar.

deekerman commented

2026-02-20 16:29:47 -05:00

Author

Owner

@ebogaard commented on GitHub (Oct 12, 2023):

The difference results reported here are due to @imens-frankd reporting this issue with Microsoft SQL Server, and @johnM2401 is using MariaDB (=MySQL type server).
This issue is only with SQL Server, not MySQL-type DB servers.

@ebogaard commented on GitHub (Oct 12, 2023): The difference results reported here are due to @imens-frankd reporting this issue with **Microsoft SQL Server**, and @johnM2401 is using MariaDB (=MySQL type server). This issue is only with SQL Server, not MySQL-type DB servers.