March 15, 2022 at 7:43 pm
I have a fairly simple query that is supposed to return only a single number – the ID of the TOP record from the query. It is not especially elegant, but it works well, except for the Sort clause. When I select and execute all but the Sort clause, it executes instantly, but naturally does not return the proper first record. When I add the sort, it hangs. I have let it run around ten minutes while watching the task manager. It shows regular peaks of 100% CPU usage alternately on two of the server's four cores, but does not bog the machine in any noticeable fashion.
When I run just the inner part, it returns 6070 records. Sorting that and returning the first one should be a completely trivial task, but that is not the case. I can't even check the execution plan, because it never finishes, at least not in the amount of time I am willing to let it run.
What can be going wrong, that SQL Server cannot properly handle such a simple query?
declare @Taxonomie nvarchar(max) = '%rupicapra%'
SELECT Top 1 AkcesAutoID, PP.Rok, PP.AkcesitPred, PP.Akcesit FROM
( SELECT
P.AkcesAutoID, Isnull(A.AkcesitPred,'') AkcesitPred, A.Akcesit, A.Rok
FROMdbo.Podrobnosti P
LEFT JOIN dbo.vwFirstSynonymika S ON P.PodrobnostiAutoID = S.PodrobnostiAutoID
INNER JOINdbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID
LEFT JOIN dbo.Taxonomy T ON P.TaxonAutoID = T.TaxonAutoID
Where (T.CompleteTaxonText Like @Taxonomie)
) PP
Order By PP.Rok, PP.AkcesitPred, PP.Akcesit
March 15, 2022 at 8:53 pm
2. Eliminate the left join to vwFirstSynonymika -- you aren't even referencing it. And we don't know what lurks in that view.
LEFT JOIN dbo.vwFirstSynonymika S ON P.PodrobnostiAutoID = S.PodrobnostiAutoID
3. Change the left join to Taxonomy
LEFT JOIN dbo.Taxonomy T ON P.TaxonAutoID = T.TaxonAutoID
Where (T.CompleteTaxonText Like @Taxonomie)
to an inner join --- that's what your where clause does anyway.
4. Why are you using a subquery? Have you tried just selecting the top 1 -- e.g.,
SELECT TOP 1
P.AkcesAutoID, Isnull(A.AkcesitPred,'') AkcesitPred, A.Akcesit, A.Rok
FROM dbo.Podrobnosti P
INNER JOIN dbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID
INNER JOIN dbo.Taxonomy T ON P.TaxonAutoID = T.TaxonAutoID
Where (T.CompleteTaxonText Like @Taxonomie)
Order By Rok, AkcesitPred, Akcesit
5. How many rows are returned by the subquery?
March 15, 2022 at 10:16 pm
1. Yes:>
-----------------------------------------------------
CREATE TABLE [dbo].[Akces](
[AkcesAutoID] [int] IDENTITY(1,1) NOT NULL,
[Rok] [smallint] NOT NULL,
[AkcesitPred] [varchar](5) NOT NULL,
[Akcesit] [int] NOT NULL,
[KusuVKatalogu] [int] NULL,
[Petrol] [bit] NULL,
[BezPoctuKusu] [bit] NULL,
[Jine] [bit] NULL,
[UzOdepsane] [bit] NULL,
[VDatabaziMK] [bit] NULL,
[Poznamka] [nvarchar](4000) NULL,
[Inventarizace] [nvarchar](4000) NULL,
[CelyAkcesit] AS (((case when [AkcesitPred]='Br' then [AkcesitPred]+'-' when [AkcesitPred]='a' then [AkcesitPred]+'/' else '' end+CONVERT([varchar](9),[Akcesit]))+'/')+CONVERT([char](4),[Rok])) PERSISTED,
CONSTRAINT [PK_Akces] PRIMARY KEY NONCLUSTERED
(
[AkcesAutoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Akces] WITH CHECK ADD CONSTRAINT [CK_Akcesit] CHECK (([Akcesit]>(0) AND [Akcesit]<(40000) OR ([Akcesit]=(666666666) OR [Akcesit]=(777777777) OR [Akcesit]=(888888888) OR [akcesit]=(999999999) AND [rok]=(9999))))
GO
ALTER TABLE [dbo].[Akces] CHECK CONSTRAINT [CK_Akcesit]
GO
ALTER TABLE [dbo].[Akces] WITH CHECK ADD CONSTRAINT [CK_AkcesitPred] CHECK (([AkcesitPred]='' OR [AkcesitPred]='a' OR [AkcesitPred]='Br'))
GO
ALTER TABLE [dbo].[Akces] CHECK CONSTRAINT [CK_AkcesitPred]
GO
ALTER TABLE [dbo].[Akces] WITH CHECK ADD CONSTRAINT [CK_Rok] CHECK (([Rok]>=(1864) AND [Rok]<=datepart(year,getdate()) OR [Rok]=(9999)))
GO
ALTER TABLE [dbo].[Akces] CHECK CONSTRAINT [CK_Rok]
GO
-----------------------------------------------------
CREATE VIEW [dbo].[vwFirstSynonymika]
with schemabinding
AS
select PodrobnostiAutoID, Poradi, TaxonText, Typ, TypPismeno, PublikcaceTypZdroje, PublikaceAutor, PublikaceRok, PublikaceNazev, Figura, Stranka, BHLOdkaz, Poznamka, LSID from (
SELECT PodrobnostiAutoID, Poradi, TaxonText, Typ, TypPismeno, PublikcaceTypZdroje, PublikaceAutor, PublikaceRok, PublikaceNazev, Figura, Stranka, BHLOdkaz, Poznamka, LSID, row_number() over (partition by PodrobnostiAutoID order by PublikaceRok desc, poradi) as seq
FROM dbo.Synonymika) S
where seq = 1
-----------------------------------------------------
CREATE TABLE [dbo].[Podrobnosti](
[PodrobnostiAutoID] [int] IDENTITY(1,1) NOT NULL,
[AkcesAutoID] [int] NOT NULL,
[EvidenceLetter] [nvarchar](2) NOT NULL,
[EvidenceNumber] [int] NULL,
[EvidenceExtra] [varchar](2) NULL,
[CountryAutoID] [int] NULL,
[PDAutoID] [int] NULL,
[ODAutoID] [int] NULL,
[TypAutoID] [int] NULL,
[PocetKusu] [int] NULL,
[OTHER_NO] [varchar](255) NULL,
[Poznamka] [varchar](4000) NULL,
[Description] [varchar](4000) NULL,
[RockType] [varchar](255) NULL,
[UlozisteDocasne] [varchar](255) NULL,
[Original] [varchar](1000) NULL,
[PublikovatYN] [bit] NOT NULL,
[Barva] [varchar](50) NULL,
[KatastrAutoID] [int] NULL,
[LokalitaAutoID] [int] NULL,
[HorninaAutoID] [int] NULL,
[Velikost] [varchar](50) NULL,
[PreservAutoID] [int] NULL,
[PanevReliktAutoID] [int] NULL,
[StratigrafieChronoID] [int] NULL,
[StratigrafieChronoPoznamka] [varchar](255) NULL,
[StratigrafieChronoNejistaYN] [bit] NOT NULL,
[StratigrafieLitoID] [int] NULL,
[StratigrafieLitoPoznamka] [varchar](255) NULL,
[StratigrafieLitoNejistaYN] [bit] NOT NULL,
[GPSID] [tinyint] NULL,
[Lat1] [float] NULL,
[Lon1] [float] NULL,
[Lat2] [float] NULL,
[Lon2] [float] NULL,
[TaxonAutoID] [int] NULL,
[aff] [bit] NOT NULL,
[cf] [bit] NOT NULL,
[bit] NOT NULL,
[quoted] [bit] NOT NULL,
[DrEvidenceCela] AS (case when [EvidenceLetter]='1e' then NULL else (([EvidenceLetter]+' ')+CONVERT([varchar](5),[EvidenceNumber]))+case when [EvidenceExtra]='' then '' else ('('+[EvidenceExtra])+')' end end) PERSISTED,
[OriginPoznamka] [nvarchar](255) NULL,
CONSTRAINT [PK_Podrobnosti] PRIMARY KEY CLUSTERED
(
[PodrobnostiAutoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Podrobnosti] ADD CONSTRAINT [DF_Podrobnosti_PublikovatYN] DEFAULT ((0)) FOR [PublikovatYN]
GO
ALTER TABLE [dbo].[Podrobnosti] ADD CONSTRAINT [DF_Podrobnosti_chkChronoStratigrafieNejista] DEFAULT ((0)) FOR [StratigrafieChronoNejistaYN]
GO
ALTER TABLE [dbo].[Podrobnosti] ADD CONSTRAINT [DF_Podrobnosti_StratigrafieChronoNejistaYN1] DEFAULT ((0)) FOR [StratigrafieLitoNejistaYN]
GO
ALTER TABLE [dbo].[Podrobnosti] ADD CONSTRAINT [DF_Podrobnosti_aff] DEFAULT ((0)) FOR [aff]
GO
ALTER TABLE [dbo].[Podrobnosti] ADD CONSTRAINT [DF_Podrobnosti_cf] DEFAULT ((0)) FOR [cf]
GO
ALTER TABLE [dbo].[Podrobnosti] ADD CONSTRAINT [DF_Podrobnosti_Q] DEFAULT ((0)) FOR
GO
ALTER TABLE [dbo].[Podrobnosti] ADD CONSTRAINT [DF_Podrobnosti_quoted] DEFAULT ((0)) FOR [quoted]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_Akces] FOREIGN KEY([AkcesAutoID])
REFERENCES [dbo].[Akces] ([AkcesAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_Akces]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_EvidenceLetters] FOREIGN KEY([EvidenceLetter])
REFERENCES [dbo].[EvidenceLetters] ([EvidenceLetter])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_EvidenceLetters]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_StratigrafieChrono] FOREIGN KEY([StratigrafieChronoID])
REFERENCES [dbo].[StratigrafieChrono] ([StratigrafieChronoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_StratigrafieChrono]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_StratigrafieLito] FOREIGN KEY([StratigrafieLitoID])
REFERENCES [dbo].[StratigrafieLito] ([StratigrafieLitoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_StratigrafieLito]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_TableOfCountrys] FOREIGN KEY([CountryAutoID])
REFERENCES [dbo].[TableOfCountrys] ([CountryAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_TableOfCountrys]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_TableOfHorninas] FOREIGN KEY([HorninaAutoID])
REFERENCES [dbo].[TableOfHorninas] ([HorninaAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_TableOfHorninas]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_TableOfKatastrs] FOREIGN KEY([KatastrAutoID])
REFERENCES [dbo].[TableOfKatastrs] ([KatastrAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_TableOfKatastrs]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_TableOfLokalitas] FOREIGN KEY([LokalitaAutoID])
REFERENCES [dbo].[TableOfLokalitas] ([LokalitaAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_TableOfLokalitas]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_TableOfODs] FOREIGN KEY([ODAutoID])
REFERENCES [dbo].[TableOfODs] ([ODAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_TableOfODs]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_TableOfPanevRelikts] FOREIGN KEY([PanevReliktAutoID])
REFERENCES [dbo].[TableOfPanevRelikts] ([PanevReliktAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_TableOfPanevRelikts]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_TableOfPDs] FOREIGN KEY([PDAutoID])
REFERENCES [dbo].[TableOfPDs] ([PDAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_TableOfPDs]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_TableOfPreservs] FOREIGN KEY([PreservAutoID])
REFERENCES [dbo].[TableOfPreservs] ([PreservAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_TableOfPreservs]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_TableOfTyps] FOREIGN KEY([TypAutoID])
REFERENCES [dbo].[TableOfTyps] ([TypAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_TableOfTyps]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [FK_Podrobnosti_Taxonomy] FOREIGN KEY([TaxonAutoID])
REFERENCES [dbo].[Taxonomy] ([TaxonAutoID])
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [FK_Podrobnosti_Taxonomy]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [CK_Lat1] CHECK (([Lat1]>=(-90) AND [lat1]<=(90)))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [CK_Lat1]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [CK_Lat2] CHECK (([Lat2]>=(-90) AND [Lat2]<=(90)))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [CK_Lat2]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [CK_Lon1] CHECK (([Lon1]>=(-180) AND [Lon1]<=(180)))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [CK_Lon1]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [CK_Lon2] CHECK (([Lon2]>=(-180) AND [Lon2]<=(180)))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [CK_Lon2]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [CK_Podrobnosti_DrEvidence] CHECK (([EvidenceLetter]='1e' AND [EvidenceNumber] IS NULL AND [EvidenceExtra] IS NULL OR (len([EvidenceLetter])=(1) OR len([EvidenceLetter])=(2) AND [EvidenceLetter]<>'1e' AND ascii(right([EvidenceLetter],(1)))<>(115) OR len([EvidenceLetter])=(2) AND ascii(right([EvidenceLetter],(1)))=(115)) AND [EvidenceNumber] IS NOT NULL AND [EvidenceNumber]>(0) AND [EvidenceExtra] IS NOT NULL))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [CK_Podrobnosti_DrEvidence]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH NOCHECK ADD CONSTRAINT [CK_Podrobnosti_EvidenceExtra] CHECK ((len([EvidenceExtra])=(0) OR len([EvidenceExtra])=(1) AND ([EvidenceExtra]>='a' AND [EvidenceExtra]<='z' OR [EvidenceExtra]='+' OR [EvidenceExtra]='-') OR len([EvidenceExtra])=(2) AND (left([EvidenceExtra],(1))>='a' AND left([EvidenceExtra],(1))<='z' AND right([EvidenceExtra],(1))>='a' AND right([EvidenceExtra],(1))<='z') OR [EvidenceExtra]='+-' OR [EvidenceExtra]='-+'))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [CK_Podrobnosti_EvidenceExtra]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [CK_PodrobnostiUlozisteDocasne] CHECK ((len([UlozisteDocasne])>(0)))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [CK_PodrobnostiUlozisteDocasne]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [GPS_BothLatLon1] CHECK ((case when [Lat1] IS NULL then (1) else (0) end=case when [Lon1] IS NULL then (1) else (0) end))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [GPS_BothLatLon1]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [GPS_BothLatLon2] CHECK ((case when [Lat2] IS NULL then (1) else (0) end=case when [Lon2] IS NULL then (1) else (0) end))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [GPS_BothLatLon2]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [GPS_ID_And_LatLon1] CHECK ((((case when [gpsid]=(4) OR [gpsid]=(3) OR [gpsid]=(2) OR [gpsid]=(1) then (1) else (0) end+case when [Lat1] IS NOT NULL then (1) else (0) end)+case when [Lon1] IS NOT NULL then (1) else (0) end)=(3) OR ((case when [gpsid]=(4) OR [gpsid]=(3) OR [gpsid]=(2) OR [gpsid]=(1) then (1) else (0) end+case when [Lat1] IS NOT NULL then (1) else (0) end)+case when [Lon1] IS NOT NULL then (1) else (0) end)=(0)))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [GPS_ID_And_LatLon1]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [GPS_ID_And_LatLon2] CHECK ((((case when [gpsid]=(4) OR [gpsid]=(3) then (1) else (0) end+case when [Lat2] IS NOT NULL then (1) else (0) end)+case when [Lon2] IS NOT NULL then (1) else (0) end)=(3) OR ((case when [gpsid]=(4) OR [gpsid]=(3) then (1) else (0) end+case when [Lat2] IS NOT NULL then (1) else (0) end)+case when [Lon2] IS NOT NULL then (1) else (0) end)=(0)))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [GPS_ID_And_LatLon2]
GO
ALTER TABLE [dbo].[Podrobnosti] WITH CHECK ADD CONSTRAINT [GPS_ID1234] CHECK (([GPSID]=(4) OR [GPSID]=(3) OR [GPSID]=(2) OR [GPSID]=(1)))
GO
ALTER TABLE [dbo].[Podrobnosti] CHECK CONSTRAINT [GPS_ID1234]
GO
-----------------------------------------------------
CREATE TABLE [dbo].[Taxonomy](
[TaxonAutoID] [int] IDENTITY(1,1) NOT NULL,
[TaxonName] [varchar](100) NOT NULL,
[TaxonLevelID] [tinyint] NOT NULL,
[NextHigherTaxonAutoID] [int] NULL,
[AutorAutoID] [int] NULL,
[KingdomAutoID] [int] NULL,
[CompleteTaxonText] [varchar](255) NULL,
CONSTRAINT [PK_Taxonomy] PRIMARY KEY CLUSTERED
(
[TaxonAutoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Taxonomy] WITH CHECK ADD CONSTRAINT [FK_Taxonomy_TableOfAuthors] FOREIGN KEY([AutorAutoID])
REFERENCES [dbo].[TableOfAutors] ([AutorAutoID])
GO
ALTER TABLE [dbo].[Taxonomy] CHECK CONSTRAINT [FK_Taxonomy_TableOfAuthors]
GO
ALTER TABLE [dbo].[Taxonomy] WITH CHECK ADD CONSTRAINT [FK_Taxonomy_TaxonLevels] FOREIGN KEY([TaxonLevelID])
REFERENCES [dbo].[TaxonLevels] ([TaxonLevelID])
GO
ALTER TABLE [dbo].[Taxonomy] CHECK CONSTRAINT [FK_Taxonomy_TaxonLevels]
GO
ALTER TABLE [dbo].[Taxonomy] WITH CHECK ADD CONSTRAINT [FK_Taxonomy_Taxonomy] FOREIGN KEY([NextHigherTaxonAutoID])
REFERENCES [dbo].[Taxonomy] ([TaxonAutoID])
GO
ALTER TABLE [dbo].[Taxonomy] CHECK CONSTRAINT [FK_Taxonomy_Taxonomy]
GO
ALTER TABLE [dbo].[Taxonomy] WITH CHECK ADD CONSTRAINT [CK_Taxonomy] CHECK (([NextHigherTaxonAutoID] IS NOT NULL AND [KingdomAutoID] IS NOT NULL OR [NextHigherTaxonAutoID] IS NULL AND [KingdomAutoID] IS NULL))
GO
ALTER TABLE [dbo].[Taxonomy] CHECK CONSTRAINT [CK_Taxonomy]
GO
ALTER TABLE [dbo].[Taxonomy] WITH CHECK ADD CONSTRAINT [CK_Taxonomy_NoSelfRef] CHECK (([NextHigherTaxonAutoID]<>[TaxonAutoID]))
GO
ALTER TABLE [dbo].[Taxonomy] CHECK CONSTRAINT [CK_Taxonomy_NoSelfRef]
GO
ALTER TABLE [dbo].[Taxonomy] WITH CHECK ADD CONSTRAINT [CK_Taxonomy_NoSpaces] CHECK ((NOT [taxonname] like ' %' AND NOT [taxonname] like '% '))
GO
ALTER TABLE [dbo].[Taxonomy] CHECK CONSTRAINT [CK_Taxonomy_NoSpaces]
GO
-----------------------------------------------------
2. This is generated by text functions, based on a number of parameters. But when I captured the generated text for these tests, I tried eliminating that, since this instance does not need it. Didn't help – even without that view in the query, it still hangs with the sort clause and runs fine without it.
3. Again, that is created by the generating function, but I did try changing it to inner. No change – still hangs.
4. Because of the generating functions. They 'wrap' the inner query when the parameters indicate something like this – wanting a total or a singleton from a selection.
5. 6070. As I wrote in the initial post, I would expect the inner query to return these rows, a fairly small number, then give me the top one based on Order By of this result set. That is not happening, but I don't know what is happening, since I can't even look at the execution plan.
March 16, 2022 at 4:19 am
...
5. 6070. As I wrote in the initial post, I would expect the inner query to return these rows, a fairly small number, then give me the top one based on Order By of this result set. That is not happening, but I don't know what is happening, since I can't even look at the execution plan.
Under the hood, SQL is rewriting your query to be similar to ratbak's query.
Try the Divide & Conquer method
SELECT P.AkcesAutoID
, Isnull(A.AkcesitPred, '') AkcesitPred
, A.Akcesit
, A.Rok
INTO #Stage
FROM dbo.Podrobnosti P
LEFT JOIN dbo.vwFirstSynonymika S
ON P.PodrobnostiAutoID = S.PodrobnostiAutoID
INNER JOIN dbo.Akces A
ON P.AkcesAutoID = A.AkcesAutoID
LEFT JOIN dbo.Taxonomy T
ON P.TaxonAutoID = T.TaxonAutoID
WHERE T.CompleteTaxonText LIKE @Taxonomie;
SELECT TOP (1)
PP.AkcesAutoID
, PP.Rok
, PP.AkcesitPred
, PP.Akcesit
FROM #Stage PP
ORDER BY PP.Rok, PP.AkcesitPred, PP.Akcesit;
March 16, 2022 at 6:50 am
Try this query:
declare @Taxonomie nvarchar(max) = '%rupicapra%'
SELECT TOP 1
--replacing P.AkcesAutoID with identical A.AkcesAutoID, so only values from dbo.Akces are left in SELECT
A.AkcesAutoID,
Isnull(A.AkcesitPred,'') AkcesitPred, A.Akcesit, A.Rok
FROM dbo.Akces A
--no use of the view in the query, leaving it out
--LEFT JOIN dbo.vwFirstSynonymika S ON P.PodrobnostiAutoID = S.PodrobnostiAutoID
WHERE --dbo.Taxonomy is not represented in SELECT - move it to WHERE EXISTS check
EXISTS (select * from dbo.Taxonomy T
where P.TaxonAutoID = T.TaxonAutoID
and T.CompleteTaxonText Like @Taxonomie)
--dbo.Podrobnosti is not represented in SELECT - move it to WHERE EXISTS check
AND EXISTS (select * from dbo.Podrobnosti P
where P.AkcesAutoID = A.AkcesAutoID
)
--Check the data structure logic - if this check agaist dbo.Podrobnosti
--is not necessary then exclude it from the query completely
Order By A.Rok, A.AkcesitPred, A.Akcesit
_____________
Code for TallyGenerator
March 16, 2022 at 9:02 am
SQL does not necessarily evaluate a CTE or sub-query when you think it does. It will un-nest them if it believes that it is more efficient to do so.
Watch the following video by Itzik Gen-Gan for an explanation. Nothing wrong with watching the entire video, but the portion relevant to the above statement start at ±35:30
March 16, 2022 at 1:08 pm
Extremely important thing to remember:
A common table expression (CTE) is not a table. It is an expression. A query. It does not, in any way, represent data storage, temporary or otherwise. It is a query. An expression.
Remembering that will absolutely help in using CTEs and troubleshooting performance with CTEs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2022 at 7:45 pm
Extremely important thing to remember:
A common table expression (CTE) is not a table. It is an expression. A query. It does not, in any way, represent data storage, temporary or otherwise. It is a query. An expression.
Remembering that will absolutely help in using CTEs and troubleshooting performance with CTEs.
I understand that, but I don't understand what the implications are for this query. Do you mean that it executes the inner subquery repeatedly? It only returns 6070 records, so even if that were the case, it should still finish in some semi-reasonable time, maybe a few minutes. This doesn't.
I simplified it a bit and finally just let it run - around 20 minutes, and it eventually returned that one record. But at least I got an execution plan out of the ordeal - two clustered index scans fed into a nested loop join. The one scanning the main table generated over 6 BILLION (B!) records, out of a table of about 150 K records. I guess that explains the time it took, although not WHY it's doing such a cock-up job. It suggested an index, claiming an impact of 99.o64%. I created the index and it did not help, or at least not enough to matter - I didn't let it run to completition again, but several minutes, and no response. If the index helped, it didn't help enough to make the query useable.
But a bit more playing uncovered something truly weird:
SELECT Top 1 AkcesAutoID, PP.Rok, PP.AkcesitPred, PP.Akcesit
FROM (
SELECT
A.AkcesAutoID, A.AkcesitPred, A.Akcesit, A.Rok
FROM dbo.Podrobnosti P
INNER JOIN dbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID
INNER JOIN (Select TaxonAutoID From dbo.Taxonomy WHERE CompleteTaxonText Like '%rupicapra%') T ON P.TaxonAutoID = T.TaxonAutoID
) PP
ORDER BY PP.Rok, PP.AkcesitPred, PP.Akcesit
The second inner join, I replaced the variable @Taxonomie with the literal '%rupicapra%', and execution is instant. I thought it might be my Max declaration. Nope - declaring @Taxonomie as varchar(100) or nvarchar(100) both continued to be impossibly slow. But putting in the literal went to elapsed time essentially zero.
Now what? I can modify the build function to put in the passed value of the parameter, but all the publications on SQL injection attacks make a point of saying to NOT do that, as it is a security risk.
None of this is making the slightest bit of sense to me.
March 16, 2022 at 8:26 pm
Got it - I was missing an index for AkcesAutoID on the Podrobnosti table. Adding that makes it run properly.
March 16, 2022 at 8:39 pm
Glad you got it working.
This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'
That kind of wild card search can't make use of statistics and therefore can't use indexes. You'll always get a scan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2022 at 10:10 pm
Glad you got it working.
This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'
That kind of wild card search can't make use of statistics and therefore can't use indexes. You'll always get a scan.
I know, but that doesn't really matter here. The table is small, the server is very lightly loaded, and this class of queries is sort of a catch-all, for things that users are unable to find using more tightly directed searches. The app even has a small warning label on the screen that generates these queries, that combining too many fields in the search may result in a very slow response. If they are unable to find stuff using the precision tools, or just don't care that it may take a while, this is the tool that lets them find pretty much anything.
Thanks for the assist.
March 16, 2022 at 10:13 pm
Glad you got it working.
This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'
That kind of wild card search can't make use of statistics and therefore can't use indexes. You'll always get a scan.
A question, though - do you have any idea why using a variable bogs the query so badly, while using a literal flies, even without that index? I don't understand why that one thing should make such a difference, or really, any difference.
March 17, 2022 at 3:52 am
Grant Fritchey wrote:Glad you got it working.
This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'
That kind of wild card search can't make use of statistics and therefore can't use indexes. You'll always get a scan.
A question, though - do you have any idea why using a variable bogs the query so badly, while using a literal flies, even without that index? I don't understand why that one thing should make such a difference, or really, any difference.
The query, its plan and the resulting resultset (being quite small) are stored in the server's cache. When you re-run such a query, the server checks if there is a change in statistics, and if no data change recorded, it does not even bother going to database, result just being pulled from the cache.
But it only works when optimizer can see the actual value to be matched to - when the query contains it as a literal constant.
When you use a variable its actual value is not available for the optimizer during compilation time, so it cannot assume it's matching to any cached queries and has to actually run the query against the data in the tables.
_____________
Code for TallyGenerator
March 17, 2022 at 4:04 am
Grant Fritchey wrote:Glad you got it working.
This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'
That kind of wild card search can't make use of statistics and therefore can't use indexes. You'll always get a scan.
I know, but that doesn't really matter here. The table is small, the server is very lightly loaded, and this class of queries is sort of a catch-all, for things that users are unable to find using more tightly directed searches.
It does matter.
The way you wrote the query will cause the engine to join the small table to the big recordsets coming from tables Podrobnosti and Akces, and then apply the filter to every record in that huge resulting recordset.
That's why I recommended to move dbo.Taxonomy from JOIN to WHERE EXISTS.
This way the filter will apply to the small set within the table and only then the outcome will be used for filtering the big record sets.
And in this case the use of variable would not really matter, assuming the number of records in Taxonomy will stay small forever.
_____________
Code for TallyGenerator
March 17, 2022 at 8:53 am
pdanes wrote:Grant Fritchey wrote:Glad you got it working.
This part of the query is still going to cause problems: WHERE CompleteTaxonText Like '%rupicapra%'
That kind of wild card search can't make use of statistics and therefore can't use indexes. You'll always get a scan.
A question, though - do you have any idea why using a variable bogs the query so badly, while using a literal flies, even without that index? I don't understand why that one thing should make such a difference, or really, any difference.
The query, its plan and the resulting resultset (being quite small) are stored in the server's cache. When you re-run such a query, the server checks if there is a change in statistics, and if no data change recorded, it does not even bother going to database, result just being pulled from the cache.
But it only works when optimizer can see the actual value to be matched to - when the query contains it as a literal constant.
When you use a variable its actual value is not available for the optimizer during compilation time, so it cannot assume it's matching to any cached queries and has to actually run the query against the data in the tables.
Ah, I knew about plans getting cached, but didn't realize it also stored results. Thanks.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply