August 9, 2013 at 1:25 am
I have a query that is returning two copies of the last record. The record exists only once - there is an index on the table to prevent two such records, and examining the actual table confirms that it is present only once. The field [EvidenceLetter] selects groups of related records. No other value besides 'E' exhibits this behavior.
It is definitely the same record - my application displays the unique autonumber primary key, a when I select one (either one) of the duplicates in the application and make a change, the change is immediately reflected in both displayed copies of the record.
Both the application AND SSMS give the same results. I can't think of anything that might be causing such a thing. Can anyone give me some ideas on where to start looking for the problem?
I call it from a MSAccess application as a pass-through query, and from SSMS via an EXEC statement, like this:
Exec NoFiltr.spPodrobnosti @What = 'Zaznamy', @LtrFiltr = 'E'
The execution plan is attached, and here is the query generated and executed by the stored procedure:
SELECT * FROM ( SELECT
P.PodrobnostiAutoID, P.EvidenceLetter, P.EvidenceNumber, P.EvidenceExtra,
P.AkcesAutoID, A.AkcesitPred, A.Akcesit, A.Rok,
P.PocetKusu,
P.NepublikovatYN,
P.Lokalita,
tT.Zkratka Typ,
P.Original,
P.Popis,
P.Poznamka,
P.SystemAutoID,
P.SeriesAutoID,
P.StageAutoID,
P.SubStageAutoID,
P.LithographicUnitAutoID,
P.LithographicSubUnitAutoID,
P.ZoneAutoID,
P.GroupAutoID,
P.OrderAutoID,
P.FamilyAutoID,
P.GenusAutoID,
P.SubGenusAutoID,
P.SpeciesAutoID,
P.SubSpeciesAutoID,
P.ODAutoID,
P.PDAutoID,
P.OriginAutoID,
P.TypAutoID,
case when P.EvidenceLetter Is Not Null then P.EvidenceLetter + ' ' + convert(varchar(5),P.EvidenceNumber) + case when P.EvidenceExtra <> '' then ' (' + P.EvidenceExtra + ')' else '' end else '' end AS DrEvid,
case AkcesitPred when 'A' then A.AkcesitPred + '/' when 'Br' then A.AkcesitPred + '-' else '' end + convert(varchar(5),A.Akcesit) + '/' + convert(varchar(4),A.Rok) AKC,
CASE WHEN tDF.DepozitarAutoID IS NULL THEN '' ELSE tDF.DepozitarFormatted END + CASE WHEN UlozisteDocasne IS NULL THEN '' ELSE Replace(Replace(' {' + UlozisteDocasne + '}','{{','{'),'}}','}') END AS Ulozeni,
tNI.Clovek + ', ' + left(CONVERT(varchar(20), tNI.Datum, 21),10) As Inventarizace,
dbo.fnsTaxonomieFormatted(tGe.Genus, tSGe.SubGenus, tSp.Species, tSSp.SubSpecies, Authors, tFa.Family, tOrd.[Order], tGr.[Group]) AS Taxonomie,
dbo.fnsTaxonomie(tGe.Genus, tSGe.SubGenus, tSp.Species, tSSp.SubSpecies, Authors, tFa.Family, tOrd.[Order], tGr.[Group]) AS TaxonomiePlain,
dbo.fnsStratigrafie(tSy.[System], tSe.Series, tSt.Stage, tSSt.SubStage, tLU.LithographicUnit, tLSU.LithographicSubUnit, tZo.Zone) AS Stratigrafie
FROMPodrobnosti P
INNER JOINdbo.fntStringsToTable ('E') L on L.EvidenceLetter = P.EvidenceLetter
INNER JOINdbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID
LEFT JOINvwTableOfDepozitars tDF ON P.DepozitarAutoID = tDF.DepozitarAutoID
LEFT JOINvwNejnovejsiInventarizace tNI ON P.PodrobnostiAutoID = tNI.PodrobnostiAutoID
LEFT JOINTableOfGenuss tGe ON P.GenusAutoID = tGe.GenusAutoID
LEFT JOINTableOfSubGenuss tSGe ON P.SubGenusAutoID = tSGe.SubGenusAutoID
LEFT JOINTableOfSpeciess tSp ON P.SpeciesAutoID = tSp.SpeciesAutoID
LEFT JOINTableOfSubSpeciess tSSp ON P.SubSpeciesAutoID = tSSp.SubSpeciesAutoID
LEFT JOINTableOfFamilys tFa ON P.FamilyAutoID = tFa.FamilyAutoID
LEFT JOINTableOfOrders tOrd ON P.OrderAutoID = tOrd.OrderAutoID
LEFT JOINTableOfGroups tGr ON P.GroupAutoID = tGr.GroupAutoID
LEFT JOINTableOfSystems tSy ON P.SystemAutoID = tSy.SystemAutoID
LEFT JOINTableOfSeriess tSe ON P.SeriesAutoID = tSe.SeriesAutoID
LEFT JOINTableOfStages tSt ON P.StageAutoID = tSt.StageAutoID
LEFT JOINTableOfSubStages tSSt ON P.SubStageAutoID = tSSt.SubStageAutoID
LEFT JOINTableOfLithographicUnits tLU ON P.LithographicUnitAutoID = tLU.LithographicUnitAutoID
LEFT JOINTableOfLithographicSubUnits tLSU ON P.LithographicSubUnitAutoID = tLSU.LithographicSubUnitAutoID
LEFT JOINTableOfZones tZo ON P.ZoneAutoID = tZo.ZoneAutoID
LEFT JOINTableOfTyps tT ON P.TypAutoID = tT.TypAutoID
) PP
WHERE 1=1
Order By PP.EvidenceLetter, PP.EvidenceNumber, PP.EvidenceExtra
August 9, 2013 at 1:44 am
One of the items (table sources) in your FROM list has two rows per single row in table Podrobnosti. Since there's no aggregation and all of the other tables are joined to Podrobnosti, finding which one is quite trivial. First you remove whatever you can from the query to make it simpler, then you comment out all of the joins and run the query to ensure you return a rowset which doesn't contain dupes. Then you uncomment the first join dbo.fntStringsToTable and run again, see if the dupes appear. Re-comment and repeat with the next join. Here's a starting point - the simplified query with all of the joins commented out:
SELECT
P.PodrobnostiAutoID, P.EvidenceLetter, --
P.EvidenceNumber, P.EvidenceExtra
FROMPodrobnosti P
--INNER JOINdbo.fntStringsToTable ('E') L on L.EvidenceLetter = P.EvidenceLetter
--INNER JOINdbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID
--LEFT JOINvwTableOfDepozitars tDF ON P.DepozitarAutoID = tDF.DepozitarAutoID
--LEFT JOINvwNejnovejsiInventarizace tNI ON P.PodrobnostiAutoID = tNI.PodrobnostiAutoID
--LEFT JOINTableOfGenuss tGe ON P.GenusAutoID = tGe.GenusAutoID
--LEFT JOINTableOfSubGenuss tSGe ON P.SubGenusAutoID = tSGe.SubGenusAutoID
--LEFT JOINTableOfSpeciess tSp ON P.SpeciesAutoID = tSp.SpeciesAutoID
--LEFT JOINTableOfSubSpeciess tSSp ON P.SubSpeciesAutoID = tSSp.SubSpeciesAutoID
--LEFT JOINTableOfFamilys tFa ON P.FamilyAutoID = tFa.FamilyAutoID
--LEFT JOINTableOfOrders tOrd ON P.OrderAutoID = tOrd.OrderAutoID
--LEFT JOINTableOfGroups tGr ON P.GroupAutoID = tGr.GroupAutoID
--LEFT JOINTableOfSystems tSy ON P.SystemAutoID = tSy.SystemAutoID
--LEFT JOINTableOfSeriess tSe ON P.SeriesAutoID = tSe.SeriesAutoID
--LEFT JOINTableOfStages tSt ON P.StageAutoID = tSt.StageAutoID
--LEFT JOINTableOfSubStages tSSt ON P.SubStageAutoID = tSSt.SubStageAutoID
--LEFT JOINTableOfLithographicUnits tLU ON P.LithographicUnitAutoID = tLU.LithographicUnitAutoID
--LEFT JOINTableOfLithographicSubUnits tLSU ON P.LithographicSubUnitAutoID = tLSU.LithographicSubUnitAutoID
--LEFT JOINTableOfZones tZo ON P.ZoneAutoID = tZo.ZoneAutoID
--LEFT JOINTableOfTyps tT ON P.TypAutoID = tT.TypAutoID
Order By P.EvidenceLetter, P.EvidenceNumber, P.EvidenceExtra
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 9, 2013 at 2:01 am
ChrisM@Work (8/9/2013)
One of the items (table sources) in your FROM list has two rows per single row in table Podrobnosti. Since there's no aggregation and all of the other tables are joined to Podrobnosti, finding which one is quite trivial. First you remove whatever you can from the query to make it simpler, then you comment out all of the joins and run the query to ensure you return a rowset which doesn't contain dupes. Then you uncomment the first join dbo.fntStringsToTable and run again, see if the dupes appear. Re-comment and repeat with the next join. Here's a starting point - the simplified query with all of the joins commented out:
Bulls-eye! There was a duplicate in the inventory trail attached table. I previously had code to allow inventory records no more than once per minute, to guard against accidental multiple clicks of my "Inventory this item" button. That got hosed during some recent rework of the app, and the SQL Server table never had a protection against this, because I was still fooling with the design and forgot to include that.
I pasted the query you modified for me and did exactly as you wrote - uncommented one join at a time until the duplicate appeared (there were two, BTW, another one somewhere higher up that I didn't notice earlier). As soon as I saw which join made it appear, the inventory trail, it was obvious what had happened.
Many thanks, Chris, I was drawing a complete blank on where to even start.
August 9, 2013 at 2:11 am
You're welcome Mr Danes. Assuming you're a taxonomist first and a programmer second, this is a fairly standard method to identify cardinality issues in a query.
Care to share the code in dbo.fntStringsToTable? SSC has the fastest pure TSQL string-to-table converter on the planet. If your function splits strings somewhere, a little code splicing could go a long way.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 9, 2013 at 2:33 am
ChrisM@Work (8/9/2013)
You're welcome Mr Danes. Assuming you're a taxonomist first and a programmer second, this is a fairly standard method to identify cardinality issues in a query.
No, actually I'm primarily a computer tech. The app is for the paleontology department of a museum for which I have been working on and off for almost twenty years. This is something I should have caught, but I was hung up on it being the last record in the set (of about 6500, so a manual examination wasn't practical). If it had been something other than the last one, I might (maybe) have figured it out myself. Most of my DB experience is with Access, though, so I'm still floundering a bit with SQL Server.
Care to share the code in dbo.fntStringsToTable? SSC has the fastest pure TSQL string-to-table converter on the planet. If your function splits strings somewhere, a little code splicing could go a long way.
Certainly, here you go:
CREATE FUNCTION [dbo].[fntStringsToTable] (@LtrFiltr varchar(1000))
RETURNS
@Ltrs TABLE(EvidenceLetter varchar(2))
AS
BEGIN
declare @CommaPos int
while LEN(@LtrFiltr) > 0
begin
set @CommaPos = CHARINDEX(',',@LtrFiltr,1)
if @CommaPos > 0
begin
insert @Ltrs (EvidenceLetter) Select left(@LtrFiltr,@CommaPos-1)
set @LtrFiltr = SUBSTRING(@LtrFiltr,@CommaPos+1,len(@LtrFiltr)-@CommaPos+1)
end
else
begin
insert @Ltrs (EvidenceLetter) Select @LtrFiltr
set @LtrFiltr = ''
end
end
return
END
The string passed is a comma-delimited set of one- or two-character strings, usually just one item, very occasionally up to around eight, and this server is very lightly loaded, so performance of building the temporary join table is not a huge issue here.
And the issue you helped me uncover is already fixed, both in the app code and with a unique index on the SQL Server inventory trail table.
August 9, 2013 at 3:08 am
pdanes (8/9/2013)
ChrisM@Work (8/9/2013)
You're welcome Mr Danes. Assuming you're a taxonomist first and a programmer second, this is a fairly standard method to identify cardinality issues in a query.No, actually I'm primarily a computer tech. The app is for the paleontology department of a museum for which I have been working on and off for almost twenty years. This is something I should have caught, but I was hung up on it being the last record in the set (of about 6500, so a manual examination wasn't practical). If it had been something other than the last one, I might (maybe) have figured it out myself. Most of my DB experience is with Access, though, so I'm still floundering a bit with SQL Server.
...
Lucky man! I'd love to have a sideline like this.
Here's a link to the 'house string-splitter', written by Jeff Moden et al: http://www.sqlservercentral.com/articles/Tally+Table/72993/%5B/url%5D It's a worthy read in it's own right and a rich source of TSQL programming hints and tips.
Have you come across ISNULL yet? It could tidy up some of the CASE constructs in your output list. Something like this:
P.TypAutoID,
DrEvid = ISNULL(P.EvidenceLetter + ' ' + CAST(P.EvidenceNumber AS VARCHAR(5)) + ISNULL(' (' + NULLIF(P.EvidenceExtra,'') + ')', ''),''),
AKC = case AkcesitPred
when 'A' then A.AkcesitPred + '/'
when 'Br' then A.AkcesitPred + '-'
else '' end
+ convert(varchar(5),A.Akcesit) + '/' + convert(varchar(4),A.Rok),
Ulozeni = ISNULL(tDF.DepozitarFormatted,'') + ISNULL(Replace(Replace(' {' + UlozisteDocasne + '}','{{','{'),'}}','}'),''),
Inventarizace = tNI.Clovek + ', ' + left(CONVERT(varchar(20), tNI.Datum, 21),10),
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 9, 2013 at 3:51 am
Lucky man! I'd love to have a sideline like this.
Yeah, it's a pretty interesting place. Some of the top management is straight out of a Dilbert cartoon, but the people I work with are great. Lots of cool international projects and a pretty low-pressure environment.
Here's a link to the 'house string-splitter', written by Jeff Moden et al: http://www.sqlservercentral.com/articles/Tally+Table/72993/%5B/url%5D It's a worthy read in it's own right and a rich source of TSQL programming hints and tips.
Have you come across ISNULL yet?
Thanks, I've seen both the splitter and IsNull. Some of my early coding attempts are not the best, but it's too much work to go through every bit of existing code every time I learn something new. I try to use what I know when creating new things, and update whatever I need to work on with any new knowledge gained since I wrote it. It takes a lot of time to review and possibly rewrite something completely every time I work on it, but I try my best.
August 9, 2013 at 6:49 am
That does sound like an uber-cool place to do some work. Talk about interesting stuff...
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply