June 29, 2023 at 7:45 am
Hi
I have a weird problem that I can't find the cause for.
I have the statement below, it consists of three individual statements combined with UNION.
In the production site, first SELECT outputs one row, middle SELECT nothing, and last SELECT 9 rows.
The full statement with UNION varies in returning 1 or 10 rows. So sometimes the UNION corrrectly merge 1+9 rows - other times it seems to just take the first select statement.
It is within a few seconds it varies, so if I click F5 in SSMS e.g. 10 times it's close to 50/50 whether it is 1 or 10 rows.
First impression from me and the DBA at the customer was that they were a few CU's behind. But he has now upgraded their test environment to 13.0.7024.30, production being 13.0.5850.14.
The issue occurs both on Test and Production so have it reproducable on two servers.
What am I doing wrong here?
Sql statement
SELECT JobHistory.*
FROM JobHistory
WHERE JobHistory.[Deleted] = 0
AND JobHistory.[LiveObjectGuid] IS NULL
AND CONTAINS(JobHistory.*, '"FN21HDA*"')
AND JobHistory.[UnitGuid] = 'f1a1a2ff-5a4b-4208-9081-27d473592ad8'
UNION
SELECT JobHistory.*
FROM JobHistory
JOIN JobInstance ON JobHistory.JobInstanceGuid = JobInstance.Guid
JOIN Job ON JobInstance.JobGuid = Job.Guid
WHERE JobHistory.[Deleted] = 0
AND JobHistory.[LiveObjectGuid] IS NULL
AND CONTAINS(Job.*, '"FN21HDA*"')
AND JobHistory.[UnitGuid] = 'f1a1a2ff-5a4b-4208-9081-27d473592ad8'
UNION
SELECT JobHistory.*
FROM JobHistory
JOIN Job ON JobHistory.LiveJobGuid = Job.Guid
JOIN Component ON Job.ComponentGuid = Component.Guid
WHERE JobHistory.[Deleted] = 0
AND JobHistory.[LiveObjectGuid] IS NULL
AND CONTAINS(Component.*, '"FN21HDA*"')
AND JobHistory.[UnitGuid] = 'f1a1a2ff-5a4b-4208-9081-27d473592ad8'
ORDER BY [JobHistory].[DONE] DESC,
[JobHistory].[IDSeq] DESC
hope someone has some input on this.
Best regards
/Anders
June 29, 2023 at 1:16 pm
Please provide sample data in the form of DDL to create (temp) tables and DML to insert data into those tables.
You do realize that UNION automatically removes duplicates. Since your subqueries are essentially the same and you are only returning data from JobHistory, I would expect that you are only getting duplicates. If you want all records you should use UNION ALL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 30, 2023 at 12:58 am
I do not know what fields your tables have but doing CONTAINS(JobHistory.*,'"FN21HDA*"') or CONTAINS(Job.*, '"FN21HDA*"') or CONTAINS(Component.*, '"FN21HDA*"') seems extremely wasteful. As I think you mean it to do (which I do not think it is doing) is if any column within these tables contains within them said value Return TRUE Else Return False.
Now surely you know exactly what columns might contain that value and what columns definitely will not. For instance, a numeric and/or a date column will definitely not contain a string. Further without testing that against your data I can only say that is theoretically what it is doing and might be part of the problem.
Lastly if it is working as theoretically as expected it is syntactically incorrect as they are missing their containing parenthesis around the list of column names. For instance here is an example: CONTAINS( ( JobHistory.* ), '"FN21HDA*"' ). This could another part of the problem.
That set aside, if I am understanding what [JobHistory].[UnitGuid] is this query makes little to no sense, as you are picking a single unique record represented by that UnitGUID (or Globally Unique Identifier) and if that is the case then the following is all you need:
SELECT [jh].*
FROM [JobHistory] AS [jh]
------
-- Note I am assuming these first two joins return something different
-- than the second two joins but without the real data I cannot say for
-- sure that [j].[Guid] = [j2].[Guid] if they do then the third JOIN is
-- not needed and the last ON would be [c].[Guid] = [j].[ComponentGuid]
LEFT JOIN JobInstance AS [ji]
ON [ji].[Guid] = [jh].[JobInstanceGuid]
------
LEFT JOIN Job AS [j]
ON [j].[Guid] = [ji].[JobGuid]
------
LEFT JOIN [Job] AS [j2]
ON [j2].[Guid] = [jh].[LiveJobGuid]
------
LEFT JOIN [Component] AS [c]
ON [c].[Guid] = [j2].[ComponentGuid]
------
-- Always go most restrictive to least restrictive
WHERE [jh].[UnitGuid] = 'f1a1a2ff-5a4b-4208-9081-27d473592ad8'
AND [jh].[Deleted] = 0
AND [jh].[LiveObjectGuid] IS NULL
AND ( CONTAINS( ( [jh].[ColumName1]
,[jh].[ColumnName2]
,[jh].[ColumnName2]
), '"FN21HDA*"'
)
OR
CONTAINS( ( [j].[ColumName-1]
,[j].[ColumnName-2]
,[j].[ColumnName-n]
), '"FN21HDA*"'
)
OR
CONTAINS( ( [c].[ColumName-1]
,[c].[ColumnName-2]
,[c].[ColumnName-n]
), '"FN21HDA*"'
)
)
-- Now this makes no sense to me as this is only going to return a
-- single record, that is if the UnitGUID is actually a GUID
ORDER BY [jh].[DONE] DESC
,[jh].[IDSeq] DESC;
You will need to of course replace the various generic [ColumnName-#] with an appropriate column name and also without data I was not able to test this so it may contain errors. Still you get back what you give, aka give incomplete information you get incomplete answers.
July 3, 2023 at 7:41 am
Hi Drew and Dennis
Thanks for the feedback.
@drew, I cannot produce sample data for it, as I can only reproduce it at this one customers site. They have above 20 mio rows just in the JobHistory table, and this is one rare case where it doesn't behave.
I am aware that the SQL itself can be constructed better, but it has always provided correct results. We have thousands of instances of this database scheme running, and this has never been an issue. The C# code that generates this SQL has been like this since 2015.
And yes, I am well aware of UNION vs UNION ALL - and in this case it doesn't make a difference. The 10 rows are unique, but sometimes the 9 from the last select is just missing in the output.
@dennis. I understand your reasoning, and the SQL can be written more precise. And that can remove the problem with the UNION, as the statement can express the same without the UNION.
That just requires a service release of our software and a production upgrade for our customer. And that might be the solution to this.
Your assumptions about unitGuid is incorrect though - and how should you understand our schema from the above. Every vessel for a customer has a unique Guid, in every table we have live and history versions for data rows - so to get all "live" records for a given vessel we need to filter for UnitGuid, LiveObject and non-delete records. That's part of our schema - and just how it is. Enterprise system, thousands of installations and terabytes of data. That's not gonna change because this query doesn't deliver. 😉
SQL FullText knows what columns are fulltext indexed, so telling it to do Component.* works fine - could be more explicit, but that require more metadata (or code changes) when we add fields to be full text indexed. Our new generation of the framework does that - this part just uses Component.* - but it works fine and has done for decades.
I think we can rewrite the SQL to become OR Contains clauses instead of UNIONs, and that obviously resolve the issue with UNION not getting all parts.
I still don't understand how that happens though, and whether or not it is a bug in SQL Server. I cannot see how our 10 distinct rows can be seens as one - and how SQL server running the same query does not produce same results when data hasn't changed.
July 3, 2023 at 2:35 pm
All I can say to you Anders is I do not envy your situation but I do empathize as I have and am sort-of working with something similar but we are dealing with over 300 mil records in some of our historic tables and streamlining stored procedures has become one of top priorities. For at one time, our Daily Reports were creaping up to taking over a half a day to complete and this of course effected daily operations. We were able to bring those reports back down to about 4 hours but we are still working on all the stored procedures to trim their runtimes down. That being said, simplicity is usually the best practice and I considered your UNION solution to be a bit too complex for what it was you were striving for. Keep in mind that the K.I.S.S. principle (and no my version is Keep It Simple and Smart as I prefer positive statements over negative ones) definitely applies to SQL coding.
Note I do know that even your mildly complex query can have unexpected results due to what is going on behind the curtain. That is why I strongly push the simpliest solution over one that should do the same thing but is more complex. Also, make sure you are aware of those things that slow a query down and/or can produce semi-random results. I have already ran into a few of these and had to fix them, so yeah I now your pain. Good luck and know you have folks here willing to lend a hand.
July 4, 2023 at 11:22 am
Thanks Dennis
Looking into the "OR" solution, at first glimpse it seems like the query optimizer is missing something though.
In the UNION solution I have 45, 8 and 3 reads in the three fulltext_index_docidmap_<id> tables.
When I make it an OR in the where clause something goes wrong and I get 878k, 878k and 672k reads. So instead of 64 reads I get 2.4 million reads. 🙁
Elapsed time is doubled from 4 to 8 seconds as well.
But will investigate if I can make it behave, as the query it self is more simple - but OR's can be hard to manage as well.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply