Slow sous-requête IN clause?

J'ai un certain nombre de requêtes de prélecture lente dans LLBL. Voici une version simplifiée du SQL généré:

SELECT DISTINCT Column1 FROM Table1 WHERE Table1.Table2ID IN ( SELECT Table2.Table2ID AS Table2ID FROM Table2 INNER JOIN Table1 ON Table2.Table2ID=Table1.Table2ID INNER JOIN ( SELECT DISTINCT Table1.Table2ID AS Table2ID, MAX(Table1.EffectiveDate) AS EffectiveDate FROM Table1 WHERE Table1.EffectiveDate <= '2012-01-03 00:00:00:000' GROUP BY Table1.Table2ID ) MaxEffective ON MaxEffective.Table2ID = Table1.Table2ID AND MaxEffective.EffectiveDate = Table1.EffectiveDate )

Ce que je trouve est que la sous-requête s'exécute rapidement et si je remplace cette sous-requête par les résultats réels, la requête externe est rapide. Mais set, ils sont lents.

J'ai couru le conseiller d'optimization de moteur de database qui a aidé un peu, mais c'est encore assez lent.

Je ne suis pas très doué pour comprendre les plans d'exécution, mais il semble que la plus grande partie du time soit consacrée à la search d'index sur Table1.

Je m'attendais à ce que cela fonctionne plus vite puisqu'il s'agit d'une sous-requête non corrélée. Y a-t-il quelque chose que je ne vois pas?

Si ce n'était que du SQL, je réécrivais la requête et faisais une jointure, mais je suis plutôt bloqué avec LLBL. Y a-t-il des parameters que je peux utiliser pour le forcer à faire une jointure? Y a-t-il une raison pour laquelle SQL Server ne génère pas le même plan d'exécution que pour une jointure?

Modifier pour une requête réelle …

 SELECT DISTINCT ResidentialComponentValues.ResidentialComponentValueID AS ResidentialComponentValueId, ResidentialComponentValues.ResidentialComponentTypeID AS ResidentialComponentTypeId, ResidentialComponentValues.Value, ResidentialComponentValues.Story, ResidentialComponentValues.LastUpdated, ResidentialComponentValues.LastUpdatedBy, ResidentialComponentValues.ConcurrencyTimestamp, ResidentialComponentValues.EffectiveDate, ResidentialComponentValues.DefaultQuantity FROM ResidentialComponentValues WHERE ResidentialComponentValues.ResidentialComponentTypeID IN ( SELECT ResidentialComponentTypes.ResidentialComponentTypeID AS ResidentialComponentTypeId FROM ResidentialComponentTypes INNER JOIN ResidentialComponentValues ON ResidentialComponentTypes.ResidentialComponentTypeID=ResidentialComponentValues.ResidentialComponentTypeID INNER JOIN ( SELECT DISTINCT ResidentialComponentValues.ResidentialComponentTypeID AS ResidentialComponentTypeId, MAX(ResidentialComponentValues.EffectiveDate) AS EffectiveDate FROM ResidentialComponentValues WHERE ResidentialComponentValues.EffectiveDate <= '2012-01-03 00:00:00:000' GROUP BY ResidentialComponentValues.ResidentialComponentTypeID ) LPA_E1 ON LPA_E1.ResidentialComponentTypeId = ResidentialComponentValues.ResidentialComponentTypeID AND LPA_E1.EffectiveDate = ResidentialComponentValues.EffectiveDate )

Modifier pour les instructions de création:

 /****** Object: Table [dbo].[ResidentialComponentTypes] Script Date: 01/03/2012 13:49:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ResidentialComponentTypes]( [ResidentialComponentTypeID] [int] IDENTITY(1,1) NOT NULL, [ComponentTypeName] [varchar](255) NOT NULL, [LastUpdated] [datetime] NOT NULL, [LastUpdatedBy] [varchar](50) NOT NULL, [ConcurrencyTimestamp] [timestamp] NOT NULL, [Active] [bit] NOT NULL, CONSTRAINT [PK_ResidentialComponentTypes] PRIMARY KEY CLUSTERED ( [ResidentialComponentTypeID] 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 SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[ResidentialComponentValues] Script Date: 01/03/2012 13:49:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ResidentialComponentValues]( [ResidentialComponentValueID] [int] IDENTITY(1,1) NOT NULL, [ResidentialComponentTypeID] [int] NOT NULL, [Value] [decimal](18, 3) NOT NULL, [Story] [varchar](255) NOT NULL, [LastUpdated] [datetime] NOT NULL, [LastUpdatedBy] [varchar](50) NOT NULL, [ConcurrencyTimestamp] [timestamp] NOT NULL, [EffectiveDate] [datetime] NOT NULL, [DefaultQuantity] [int] NOT NULL, CONSTRAINT [PK_ResidentialComponentPrices] PRIMARY KEY CLUSTERED ( [ResidentialComponentValueID] 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 SET ANSI_PADDING OFF GO CREATE NONCLUSTERED INDEX [_dta_index_ResidentialComponentValues_71_56543435__K1] ON [dbo].[ResidentialComponentValues] ( [ResidentialComponentValueID] 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] GO CREATE NONCLUSTERED INDEX [_dta_index_ResidentialComponentValues_71_56543435__K1_2_3_4_5_6_7_8_9] ON [dbo].[ResidentialComponentValues] ( [ResidentialComponentValueID] ASC ) INCLUDE ( [ResidentialComponentTypeID], [Value], [Story], [LastUpdated], [LastUpdatedBy], [ConcurrencyTimestamp], [EffectiveDate], [DefaultQuantity]) 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] GO CREATE NONCLUSTERED INDEX [_dta_index_ResidentialComponentValues_71_56543435__K2_K1] ON [dbo].[ResidentialComponentValues] ( [ResidentialComponentTypeID] ASC, [ResidentialComponentValueID] 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] GO CREATE NONCLUSTERED INDEX [_dta_index_ResidentialComponentValues_71_56543435__K2_K8_K1] ON [dbo].[ResidentialComponentValues] ( [ResidentialComponentTypeID] ASC, [EffectiveDate] ASC, [ResidentialComponentValueID] 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] GO CREATE NONCLUSTERED INDEX [_dta_index_ResidentialComponentValues_71_56543435__K2_K8_K1_3_4_5_6_7_9] ON [dbo].[ResidentialComponentValues] ( [ResidentialComponentTypeID] ASC, [EffectiveDate] ASC, [ResidentialComponentValueID] ASC ) INCLUDE ( [Value], [Story], [LastUpdated], [LastUpdatedBy], [ConcurrencyTimestamp], [DefaultQuantity]) 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] GO /****** Object: ForeignKey [FK_ResidentialComponentValues_ResidentialComponentTypes] Script Date: 01/03/2012 13:49:06 ******/ ALTER TABLE [dbo].[ResidentialComponentValues] WITH CHECK ADD CONSTRAINT [FK_ResidentialComponentValues_ResidentialComponentTypes] FOREIGN KEY([ResidentialComponentTypeID]) REFERENCES [dbo].[ResidentialComponentTypes] ([ResidentialComponentTypeID]) GO ALTER TABLE [dbo].[ResidentialComponentValues] CHECK CONSTRAINT [FK_ResidentialComponentValues_ResidentialComponentTypes] GO

entrez la description de l'image ici

Ce n'est pas clair pour moi de lire vos questions sur ce que vous essayez réellement d'accomplir. Votre requête externe tente-t-elle de sélectionner uniquement les loggings ResidentialComponentValues les plus récents pour chaque ResidentialComponentType?

DISTINCT sur la requête la plus interne semble inutile et peut provoquer la difficulté de la database dans l'optimization de la requête. Vous ne select que deux colonnes, et vous groupez par un et agrégez l'autre, donc je suis sûr que les résultats seront déjà distincts. Vous n'aidez pas la database à exécuter cette requête plus efficacement en spécifiant DISTINCT , bien que l'optimiseur de requête l'ignore peut-être.

De même, le premier INNER JOIN à ResidentialComponentValues sur la requête interne semble être inutile.

La condition ON de votre deuxième INNER JOIN dans votre sous-requête (ci-dessous) me rend confuse. Il semble que ce soit simplement joindre votre résultat LPA_E1 avec la table ResidentialComponentValues de la première INNER JOIN dans votre sous-requête, mais je pense que ce que vous essayez vraiment de faire est de le joindre avec la table ResidentialComponentValues de la requête externe.

 ON LPA_E1.ResidentialComponentTypeId = ResidentialComponentValues.ResidentialComponentTypeID AND LPA_E1.EffectiveDate = ResidentialComponentValues.EffectiveDate

Ma conjecture est que ci-dessous est la requête que vous voulez vraiment, bien que je ne pense pas qu'elle produit les mêmes résultats que votre original. Cela sélectionne uniquement les loggings ResidentialComponentValue les plus récents pour chaque ResidentialComponentType.

 declare @endDate datetime set @endDate = '2012-01-03 00:00:00:000' SELECT ResidentialComponentValues.ResidentialComponentValueID AS ResidentialComponentValueId, ResidentialComponentValues.ResidentialComponentTypeID AS ResidentialComponentTypeId, ResidentialComponentValues.Value, ResidentialComponentValues.Story, ResidentialComponentValues.LastUpdated, ResidentialComponentValues.LastUpdatedBy, ResidentialComponentValues.ConcurrencyTimestamp, ResidentialComponentValues.EffectiveDate, ResidentialComponentValues.DefaultQuantity FROM ResidentialComponentValues WHERE -- the effective date for this ResidentialComponentValue record has already passed ResidentialComponentValues.EffectiveDate <= @endDate -- and there does not exist any other ResidentialComponentValue record for the same ResidentialComponentType that is effective more recently and not exists ( select 1 from ResidentialComponentValues LPA_E1 where LPA_E1.ResidentialComponentTypeID = ResidentialComponentValues.ResidentialComponentTypeID and LPA_E1.EffectiveDate <= @endDate and LPA_E1.EffectiveDate > ResidentialComponentValues.EffectiveDate )

Side Note: Je suppose que cette requête bénéficierait d'un index à 2 colonnes sur la table ResidentialComponentValues pour les colonnes (ResidentialComponentTypeID, EffectiveDate).

De plus, je pense que cette requête montrée ci-dessous produira probablement les mêmes résultats que votre original, et je pense qu'elle s'exécutera plus rapidement.

 SELECT ResidentialComponentValues.ResidentialComponentValueID AS ResidentialComponentValueId, ResidentialComponentValues.ResidentialComponentTypeID AS ResidentialComponentTypeId, ResidentialComponentValues.Value, ResidentialComponentValues.Story, ResidentialComponentValues.LastUpdated, ResidentialComponentValues.LastUpdatedBy, ResidentialComponentValues.ConcurrencyTimestamp, ResidentialComponentValues.EffectiveDate, ResidentialComponentValues.DefaultQuantity FROM ResidentialComponentValues WHERE -- show any ResidentialComponentValue records where there is any other currently effective ResidentialComponentValue record for the same ResidentialComponentType exists ( select 1 from ResidentialComponentValues LPA_E1 where LPA_E1.ResidentialComponentTypeID = ResidentialComponentValues.ResidentialComponentTypeID and LPA_E1.EffectiveDate <= @endDate )

Compte tenu des données de test suivantes, la première requête renvoie les loggings 2 et 4. La deuxième requête renvoie les loggings 1, 2, 3, 4 et 5.

 insert into ResidentialComponentTypes values (1) insert into ResidentialComponentTypes values (2) insert into ResidentialComponentTypes values (3) insert into ResidentialComponentValues (ResidentialComponentValueID, ResidentialComponentTypeID, Value, Story, LastUpdated, LastUpdatedBy, EffectiveDate, DefaultQuantity) select 1, 1, 'One', 'Blah', getdate(), 'Blah', '2012-01-01', 1 union all select 2, 1, 'Two', 'Blah', getdate(), 'Blah', '2012-01-02', 1 union all select 3, 1, 'Three', 'Blah', getdate(), 'Blah', '2012-01-04', 1 union all select 4, 2, 'Four', 'Blah', getdate(), 'Blah', '2012-01-02', 1 union all select 5, 2, 'Five', 'Blah', getdate(), 'Blah', '2012-01-04', 1 union all select 6, 3, 'Six', 'Blah', getdate(), 'Blah', '2012-01-04', 1

La sous-requête interne n'a pas besoin de DISTINCT comme vous l'avez déjà GROUP BY ResidentialComponentTypeID :

  ( SELECT DISTINCT ResidentialComponentValues.ResidentialComponentTypeID AS ResidentialComponentTypeId, MAX(ResidentialComponentValues.EffectiveDate) AS EffectiveDate FROM ResidentialComponentValues WHERE ResidentialComponentValues.EffectiveDate <= '2012-01-03 00:00:00:000' GROUP BY ResidentialComponentValues.ResidentialComponentTypeID ) LPA_E1

Vous ne savez pas si SQL Server le reconnaîtra et l'optimisera, mais vous pouvez le réécrire pour être sûr:

  ( SELECT rcv.ResidentialComponentTypeID MAX(rcv.EffectiveDate) AS EffectiveDate FROM ResidentialComponentValues AS rcv WHERE rcv.EffectiveDate <= '2012-01-03 00:00:00:000' GROUP BY rcv.ResidentialComponentTypeID ) LPA_E1

Et si je ne me trompe pas, vous n'avez besoin ni de l'autre DISTINCT dans la requête ni de l'imbrication de sous-requête supplémentaire. Vérifiez si cette réécriture donne les mêmes résultats:

 SELECT v.ResidentialComponentValueID, v.ResidentialComponentTypeID, v.Value, v.Story, v.LastUpdated, v.LastUpdatedBy, v.ConcurrencyTimestamp, v.EffectiveDate, v.DefaultQuantity FROM ResidentialComponentTypes AS t INNER JOIN ResidentialComponentValues AS v ON t.ResidentialComponentTypeID=v.ResidentialComponentTypeID INNER JOIN ( SELECT rcv.ResidentialComponentTypeID MAX(rcv.EffectiveDate) AS EffectiveDate FROM ResidentialComponentValues AS rcv WHERE rcv.EffectiveDate <= '2012-01-03 00:00:00:000' GROUP BY rcv.ResidentialComponentTypeID ) LPA_E1 ON LPA_E1.ResidentialComponentTypeId = v.ResidentialComponentTypeID AND LPA_E1.EffectiveDate = v.EffectiveDate

Vous n'avez pas non plus besoin de joindre les ResidentialComponentTypes car il existe une contrainte de Foreign Key de la part de ResidentialComponentValues , mais vous avez peut-être cette jointure à utiliser dans d'autres rapports.

Aucune idée de comment cela se ferait dans LLBL mais si vous pouvez supprimer l'un des DISTINCT du code généré – en particulier le premier – ou l'imbrication supplémentaire (ou la jointure supplémentaire), cela aidera probablement l'optimiseur confus.