Slow query advise

Topics: General, Troubleshooting, Writing modules
Nov 11, 2011 at 8:11 AM

I have i query which takes almost 7 seconds in ms sql server.

var retValFromCache = _orchardServices.ContentManager.Query(VersionOptions.Published, "CommunityEstablishment").Slice(startIndex, pageSize);

this results in the following query:

exec sp_executesql N'SELECT top 10 this_.Id as Id120_2_, this_.Number as Number120_2_,
this_.Published as Published120_2_, this_.Latest as Latest120_2_, this_.Data as Data120_2_,
this_.ContentItemRecord_id as ContentI6_120_2_, contentite1_.Id as Id117_0_, contentite1_.Data as Data117_0_,
contentite1_.ContentType_id as ContentT3_117_0_,
contenttyp2_.Id as Id119_1_, contenttyp2_.Name as Name119_1_
FROM Orchard_Framework_ContentItemVersionRecord this_
inner join Orchard_Framework_ContentItemRecord contentite1_ on this_.ContentItemRecord_id=contentite1_.Id
inner join Orchard_Framework_ContentTypeRecord contenttyp2_ on contentite1_.ContentType_id=contenttyp2_.Id
WHERE contenttyp2_.Name in (@p0) and this_.Published = @p1',
N'@p0 nvarchar(22),@p1 bit',@p0=N'CommunityEstablishment',@p1=1

what i'm doing wrong here?

Nov 11, 2011 at 8:33 AM

Update: i run the SQL Database Engine Tuning Advisor and gave me the following database improvements which maked my query amazing fast! (Estimated improvment 90%!)

CREATE NONCLUSTERED INDEX [_dta_index_Orchard_Framework_ContentItemVer_60_869578136__K3_K6_1_2_4_5] ON [dbo].[Orchard_Framework_ContentItemVersionRecord]
(
    [Published] ASC,
    [ContentItemRecord_id] ASC
)
INCLUDE ( [Id],
[Number],
[Latest],
[Data]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE STATISTICS [_dta_stat_869578136_6_3] ON [dbo].[Orchard_Framework_ContentItemVersionRecord]([ContentItemRecord_id], [Published])
go

CREATE STATISTICS [_dta_stat_805577908_1_3] ON [dbo].[Orchard_Framework_ContentItemRecord]([Id], [ContentType_id])
go

Nov 11, 2011 at 12:21 PM

Also see the following thread about indexing for ContentItemVersionRecord: http://orchard.codeplex.com/discussions/275152

Indexing on the published field also looks like a very good idea for typical queries (I'll try it myself)

Nov 14, 2011 at 8:16 AM

another great improvement:

CREATE NONCLUSTERED INDEX [_dta_index_Orchard_Framework_ContentItemVer_60_869578136__K3_K1_2_4_5_6] ON [dbo].[Orchard_Framework_ContentItemVersionRecord]
(
    [Published] ASC,
    [Id] ASC
)
INCLUDE ( [Number],
[Latest],
[Data],
[ContentItemRecord_id]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE STATISTICS [_dta_stat_869578136_1_3] ON [dbo].[Orchard_Framework_ContentItemVersionRecord]([Id], [Published])
go