November 12, 2014 at 5:19 am
I have a really weird situation - never seen anything quite like it.
I have a view defined as a Union. Each separate clause works correctly, and together they work correctly, either with a select from the query, or by selecting and executing the statement directly from the view definition, as long as I do it from SSMS.
But when I call the same view from Access, the driving application, I get two copies of the first set of records, and none from the second. It's definitely the right view, in the right database. When I change the definition to comment out one or the other of the selects, I get exactly what I should - one set, of the correct records. Only when I leave in the UNION of the two does it misbehave. When I change the order of the two select statements, I always get two copies of the first result set, and nothing from the second. Yesterday, when I first wrote the query, it worked fine. This morning it started doing this (no code change from yesterday, I went to bed with it working and when I got up, this was happening), and I'm completely stumped at the moment. I've even restarted both the server and the client machines, no change.
Create VIEW [BotRO].[vwLinkedNamesInTaxa]
AS
SELECT1 Dir, LN1.LinkedNameAutoID, TNO1.Taxon, AS1.AuthorString,
LN1.TaxaAutoID SourceTaxaID, LN1.LinkedNameTaxonAutoID LinkedTaxaID, LNS1.LinkedNameZkratka Zkratka
FROMdbo.TableOfLinkedNames LN1
INNER JOIN dbo.Taxa TX1 ON LN1.LinkedNameTaxonAutoID = TX1.TaxaAutoID
INNER JOIN Bot.vwAuthorStrings AS1 ON TX1.AuthorOfSpeciesAutoID = AS1.SkupinaAutoID
INNER JOIN Bot.vwTaxaNameOnly TNO1 ON LN1.LinkedNameTaxonAutoID = TNO1.TaxaAutoID
INNER JOIN Bot.vwTableOfLinkedNameStatuss LNS1 ON LNS1.LinkedNameStatusAutoID = LN1.LinkedNameStatusAutoID
union all
select2 Dir, LN2.LinkedNameAutoID, TNO2.Taxon, AS2.AuthorString,
LN2.LinkedNameTaxonAutoID SourceTaxaID, LN2.TaxaAutoID LinkedTaxaID, LNS2.ReverseLinkedNameZkratka Zkratka
FROMdbo.TableOfLinkedNames LN2
INNER JOIN dbo.Taxa TX2 ON LN2.TaxaAutoID = TX2.TaxaAutoID
INNER JOIN Bot.vwAuthorStrings AS2 ON TX2.AuthorOfSpeciesAutoID = AS2.SkupinaAutoID
INNER JOIN Bot.vwTaxaNameOnly TNO2 ON TX2.TaxaAutoID = TNO2.TaxaAutoID
INNER JOIN Bot.vwTableOfLinkedNameStatuss LNS2 ON LNS2.LinkedNameStatusAutoID = LN2.LinkedNameStatusAutoID
Edit - just tried breaking up the view into two separate stored views, then UNIONing those - same results.
Edit again - I created the two SELECTs as separate views, made ODBC links to those views and created an Access query to perform the actual join in the client app. Seems like a crappy way to have to do it, but it works.
November 12, 2014 at 11:29 am
That's weird, considering that it did work for a while. I have seen complex queries get themselves messed up from time to time, but that is generally from the get go. There may be a couple of query plans saved for it and one of them is messed up?
It appears that you are joining to a number of views, how complex are these views? Personally I try to avoid views on views. It shouldn't cause this issue though.
You could try rewriting your query to something like
SELECT CASE WHEN LN1.LinkedNameTaxonAutoID = TX1.TaxaAutoID THEN 1 ELSE 2 END, TNO1.Taxon, AS1.AuthorString,
CASE WHEN LN1.LinkedNameTaxonAutoID = TX1.TaxaAutoID THEN LN1.TaxaAutoID ELSE LN1.LinkedNameTaxonAutoID END SourceTaxaID,
X.TaxaAutoID LinkedTaxaID, LNS1.LinkedNameZkratka Zkratka
FROM dbo.TableOfLinkedNames LN1
CROSS APPLY (SELECT * FROM dbo.Taxa TX1 WHERE LN1.LinkedNameTaxonAutoID = TX1.TaxaAutoID OR LN1.TaxaAutoID = TX1.TaxaAutoID) X
INNER JOIN Bot.vwTaxaNameOnly TNO1 ON X.TaxaAutoID = TNO1.TaxaAutoID
INNER JOIN Bot.vwAuthorStrings AS1 ON TX1.AuthorOfSpeciesAutoID = AS1.SkupinaAutoID
INNER JOIN Bot.vwTableOfLinkedNameStatuss LNS1 ON LNS1.LinkedNameStatusAutoID = LN1.LinkedNameStatusAutoID
Or
SELECT U.Dir, U.LinkedNameAutoID, TNO1.Taxon, AS1.AuthorString,
U.SourceTaxaID, U.LinkedTaxaID, LNS1.LinkedNameZkratka Zkratka
FROM (
SELECT 1 Dir, LN1.LinkedNameAutoID,LNS1.LinkedNameStatusAutoID
LN1.TaxaAutoID SourceTaxaID, LN1.LinkedNameTaxonAutoID LinkedTaxaID
FROMdbo.TableOfLinkedNames LN1
INNER JOIN dbo.Taxa TX1 ON LN1.LinkedNameTaxonAutoID = TX1.TaxaAutoID
UNION ALL
SELECT 2 Dir, LN2.LinkedNameAutoID,LNS1.LinkedNameStatusAutoID
LN2.LinkedNameTaxonAutoID SourceTaxaID, TX2.TaxaAutoID LinkedTaxaID
FROMdbo.TableOfLinkedNames LN2
INNER JOIN dbo.Taxa TX2 ON LN2.TaxaAutoID = TX2.TaxaAutoID
) U
INNER JOIN Bot.vwTaxaNameOnly TNO1 ON U.LinkedTaxaID = TNO1.TaxaAutoID
INNER JOIN Bot.vwAuthorStrings AS1 ON TX1.AuthorOfSpeciesAutoID = AS1.SkupinaAutoID
INNER JOIN Bot.vwTableOfLinkedNameStatuss LNS1 ON LNS1.LinkedNameStatusAutoID = U.LinkedNameStatusAutoID
November 13, 2014 at 6:46 am
Thanks, but neither of those queries worked as written - they have scope errors with the aliases. I'll take a look at them later today and see if I can straighten them out, to check if the logic works correctly.
As for the referenced views, yes one of them is fairly involved, although not especially complex- it concatenates a set of records into a single string. Here it is:
CREATE VIEW [Bot].[vwAuthorStrings]
with schemabinding
AS
SELECT VLTO.SkupinaAutoID, cast(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(STUFF
((SELECT ',' + TC.BPAuthorAbbreviation
FROM dbo.SouhrnyLidi VLTI INNER JOIN
dbo.TableOfCloveks TC ON VLTI.ClovekAutoID = TC.ClovekAutoID
WHERE VLTI.SkupinaAutoID = VLTO.SkupinaAutoID FOR XML PATH('')), 1, 1, ''), '&', '&'), '(,', '('), ',&,', ' & '), ',),', ') '), ',ex,', ' ex '), ',et,', ' et '), ',in,', ' in '), ',,,',
', ')as varchar(200)) AuthorString
FROM dbo.SouhrnyLidi VLTO
GROUP BY VLTO.SkupinaAutoID
The second only does some joins and string replacements, here:
CREATE VIEW [Bot].[vwTaxaNameOnly]
with schemabinding
AS
SELECTdbo.Taxa.TaxaAutoID,
Replace(Replace(Replace(Replace(CAST(CASE WHEN dbo.TableOfGenusNames.GenusNameAutoID IS NULL
THEN dbo.TableOfFamilyNames.FamilyName
ELSE ISNULL(dbo.TableOfGenusNames.GenusName, '')
+ ' '
+ ISNULL(dbo.Taxa.SpeciesEpithet, '')
+ ' '
+ ISNULL(dbo.Taxa.[Rank], N'')
+ ' '
+ ISNULL(dbo.Taxa.RankOrig , N'')
+ ' '
+ ISNULL(dbo.Taxa.SubSpecies, '') END AS varchar(255)), ' ',' '),' ',' '), ' ',' '),' ',' ') AS Taxon,
dbo.Taxa.SpeciesEpithet,
dbo.Taxa.[Rank],
dbo.Taxa.RankOrig,
dbo.Taxa.SubSpecies
FROMdbo.Taxa LEFT OUTER JOIN
dbo.TableOfFamilyNames ON dbo.Taxa.FamilyNameAutoID = dbo.TableOfFamilyNames.FamilyNameAutoID LEFT OUTER JOIN
dbo.TableOfGenusNames ON dbo.Taxa.GenusNameAutoID = dbo.TableOfGenusNames.GenusNameAutoID
The third is simply a select of all fields from the underlying table, and really has no business being there. I've changed it to the proper table reference, but the result is the same.
Interestingly, the query is fairly slow, even though the result is only 524 records. When it ran properly, it was quick - essentially instantaneous. Only when it started misbehaving did it slow down. It's not horrible, around two seconds, but it's a noticeable difference. And since I changed the order of the select statements and got different results (although still wrong in the same way), it seems unlikely that a stuck query plan would be the issue. I tried the following:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
but no improvement. I've also been fiddling with OPTION(RECOMPILE) and OPTION(optimize for unknown), but so far only gotten compile errors.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply