November 10, 2020 at 4:52 pm
as a zip file it works as seen below - you may be able just to rename the .sqlplan to be .sqlplan.zip and upload it
November 10, 2020 at 5:07 pm
one initial question - your query has lots of functions - as it is anonymizes we can't see what they are, but can you at least clarify what they are - specially those on the joins/where clauses as those are the ones more likely to cause issues.
and this is a huge sql - your best solution will most likely be to separate them into 3 temp tables before joining as one of the likely issues is the memory requirement for all 3 unions running into a single request.
November 10, 2020 at 9:44 pm
Not that it matters for this but just to be clear this is not something I made and if we figure out whats wrong with it I can't make any changes to it I can only pass that along to the vendor and they will have to make any necessary updates/fixes. I'd shoot myself before making something that looked like this beast.
Because this code its not mine I don't know what the functions do but I can investigate however it will take some time so I probably will not have the answer to that by today.
Thanks
Kindest Regards,
Just say No to Facebook!November 11, 2020 at 8:03 pm
FYI...
Per recommendation I created 3 temp tables by breaking up the 3 queries and adding an INSERT command to create temp tables. I then compared the 3 temp tables using sp_columns. There are 61 columns in each table and within these 61 there are 4 columns that are not the same data type in all 3 tables. Columns #13, 17, 19 & 21 (positions 13,17,19 and 21 in the queries) are of type DATETIME in the first temp table but are set to INT in the 2nd and 3rd temp tables.
This means that when the 3 are running together via the UNION ALL SQL Server is trying to find a common value between DATETIME and INT for the values in positions 13,17,19 and 21 of the results.
I haven't finished checking the rest but I imagine this would be a problem.
Kindest Regards,
Just say No to Facebook!November 11, 2020 at 9:36 pm
FYI...
Per recommendation I created 3 temp tables by breaking up the 3 queries and adding an INSERT command to create temp tables. I then compared the 3 temp tables using sp_columns. There are 61 columns in each table and within these 61 there are 4 columns that are not the same data type in all 3 tables. Columns #13, 17, 19 & 21 (positions 13,17,19 and 21 in the queries) are of type DATETIME in the first temp table but are set to INT in the 2nd and 3rd temp tables.
This means that when the 3 are running together via the UNION ALL SQL Server is trying to find a common value between DATETIME and INT for the values in positions 13,17,19 and 21 of the results.
I haven't finished checking the rest but I imagine this would be a problem.
Not necessarily - an integer value will (in most cases) natively convert to a datetime value. For example: select cast(0 as datetime) returns 1900-01-01 00:00:00.000. This also works for negative numbers from -53690 and up (returns '1753-01-01 00:00:00.000').
Now - the real question is whether those integer values are valid, or if they need to be corrected to return actual datetime values. My suspicion is that they are not correct.
I don't think this would be part of the performance issue though, it is more likely causing invalid data to be returned.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 11, 2020 at 9:37 pm
Not that it matters for this but just to be clear this is not something I made and if we figure out whats wrong with it I can't make any changes to it I can only pass that along to the vendor and they will have to make any necessary updates/fixes. I'd shoot myself before making something that looked like this beast.
Because this code its not mine I don't know what the functions do but I can investigate however it will take some time so I probably will not have the answer to that by today.
Thanks
If you cannot make the changes - then why not put this back on the vendor to fix? Or is the vendor stating there isn't an issue here and it is working as designed?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply