So, after digging a lot in my db and testing i’ve found some “bugs” in the executionplan of the query that uses the table security_groups_record.
first problem: the type of the columns.
In sql server the best way to use strings is the nvarchar type. Everytime the crm queries the db it CONVERT_IMPLICIT every “id” in the table in nvarchar format. So, i’ve renamed securitygroup and securitygroups_records in securitygroup_old and securitygroups_records, then i launch this 4 queries:
CREATE TABLE [dbo].[securitygroups_records](
[id] [char](36) NOT NULL,
[securitygroup_id] [nvarchar](36) NULL,
[record_id] [nvarchar](36) NULL,
[module] [nchar](36) NULL,
[date_modified] [datetime] NULL,
[modified_user_id] [nvarchar](36) NULL,
[created_by] [nvarchar](36) NULL,
[deleted] [bit] NULL,
CONSTRAINT [securitygroups_recordspk] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[securitygroups_records] ADD DEFAULT ('0') FOR [deleted]
GO
INSERT INTO securitygroups_records SELECT * from ssecuritygroups_records_old
CREATE TABLE [dbo].[securitygroups](
[id] [nvarchar](36) NOT NULL,
[name] [nvarchar](255) NULL,
[date_entered] [datetime] NULL,
[date_modified] [datetime] NULL,
[modified_user_id] [varchar](36) NULL,
[created_by] [varchar](36) NULL,
[description] [nvarchar](max) NULL,
[deleted] [bit] NULL,
[assigned_user_id] [varchar](36) NULL,
[noninheritable] [bit] NULL,
CONSTRAINT [securitygroupspk] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[securitygroups] ADD DEFAULT ('0') FOR [deleted]
GO
INSERT INTO securitygroups SELECT * from securitygroups_old
this 4 queries recreate the 2 tables with the right datatype format and this increased the speed of the query by 3-4 seconds.
the second problem are the indexes:
The query that uses securitygroups_records uses everytime securitygroup_id, record_id, module, deleted but there arent indexes linked at them, so i’ve made the missing indexes NONCLUSTERED
CREATE NONCLUSTERED INDEX securitygrouprecord_recordid
ON securitygroups_records (record_id);
GO
CREATE NONCLUSTERED INDEX securitygrouprecord_module
ON securitygroups_records (module);
GO
CREATE NONCLUSTERED INDEX securitygrouprecord_deleted
ON securitygroups_records (deleted);
GO
this increased the speed of the query by 6-7 second. with a total decrease of the script from 12 sec to 4 with a table of 210000 records.
I wish this will help to provide a good support of the sql server version.
Reguards