I have a fairly simple stored procedure that checks to see if a value is present in a view. It's horribly slow when it's executed one way, and reasonably quick another way. i don't understand why there is such a difference.
Here they are:
if exists (select 1 From BotCBO.vwClovekStrings_PrZk Where ClovekString = '[gmelin]')
select 1 As Existuje
select 0 As Existuje
select 1 From BotCBO.vwClovekStrings_PrZk Where ClovekString = '[gmelin]'
In production, the literal text string is a variable, supplied to the stored procedure, but the speed difference is that same, variable or literal. The core is the select from the view. The view itself is a fairly inefficient grouping view, shown below, which I would love to make persisted for speed, but Sql Server doesn't allow such a view to be persisted. It isn't executed very often, so it doesn't matter all that much, and anyway, my problem is not the view, but the enormous difference in how it's called. When called alone, it's fairly quick (cca. one second), despite the inefficiency and dataset size, but when bundled inside the Exists clause, it can take up to a minute for the same results. What's going on?
ALTER VIEW [BotCBO].[vwClovekStrings_PrZk]
((SELECT ',' + rtrim(ltrim(isnull(BPAuthorAbbreviation,'')))
FROM dbo.SouhrnyCloveks VLTI
left JOIN (select ClovekAutoID, BPAuthorAbbreviation from dbo.TableOfCloveks) tCN
ON VLTI.ClovekAutoID = tCN.ClovekAutoID
WHERE VLTI.SkupinaID = VLTO.SkupinaID
order by VLTI.SkupinaID, VLTI.Poradi
FOR XML PATH('')), 1, 1, ''), '&', '&'), ',&,', ' & '), ',,,', ', ') as varchar(200)) ClovekString
FROM dbo.SouhrnyCloveks VLTO
GROUP BY VLTO.SkupinaID