I’m doing some tests concerning performance and I see the following situation:
My table looks like this:
CREATE TABLE [dbo].[articletranslations](
[articletranslation_id] [int] NOT NULL,
[entity_type] [char](2) NOT NULL,
[entity_id] [int] NOT NULL,
[language_code] [char](2) NOT NULL,
[description] [nvarchar](400) NULL,
[sort_order] [int] NULL,
[html_description] [text] NULL,
[short_description] [nvarchar](100) NULL,
CONSTRAINT [PK_articletranslations] PRIMARY KEY NONCLUSTERED
(
[articletranslation_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]
And I have an alternate index:
CREATE UNIQUE CLUSTERED INDEX [IX_entitytype_id_language] ON [dbo].[articletranslations]
(
[entity_type] ASC,
[entity_id] ASC,
[language_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
I’m running the following query:
select * from dbo.articletranslations
where language_code = 'NL' AND entity_id = 6727 AND entity_type = 'FD'
So far nothing special. But when I check my SQL Server Profiler I see that Servoy uses stored procedures to retrieve this data when using foundsets in Servoy script.
exec sp_execute 44, N’NL’, 6727, N’FD’
When executing this stored procedure I see more than 100 reads, while I expected very few reads because it returns always 1 record, and all the fields of the alternate index are filled. That is what I was examening. When I check my statistics I see the following:
[attachment=1]CaptureSQL1.JPG[/attachment]
So far, so good, but I see that the stored procedure triggered by Servoy uses N’NL’ and N’FD’ as parameters, so I changed my query like I think Servoy will handle this query:
select * from dbo.articletranslations
where language_code = N'NL' AND entity_id = 6727 AND entity_type = N'FD'
And then I see the following statistics:
[attachment=0]CaptureSQL2.JPG[/attachment]
As you can see is the number of data received from the server almost 77% higher when using N’…’ as parameter for the stored procedure.
It is not just one single situation. I tested it several times and each I see the same result.
When I replace the parameters N’NL’ and N’FD’ again by ‘NL’ and ‘FD’ then I see the nr of bytes again on 14.000
Can this be explained? And is there a way to avoid this performance loss?
Martin
