March 4, 2020 at 4:17 pm
Is this a good example of someone who may have picked the wrong thing to do with their lives?
https://www.sqlservercentral.com/forums/topic/ssis-package-error-22
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 4, 2020 at 4:31 pm
Is this a good example of someone who may have picked the wrong thing to do with their lives?
https://www.sqlservercentral.com/forums/topic/ssis-package-error-22
oh man i wish i was in that stream ... jeff can be quite direct.
I was in a session with a guy that didn't understand the difference between a view and a table... he thought the view had switched from varchar to nvarcvhar) it took a day to get to the root of the issue.... he mentioned the word SSIS and oracle and i figured out what he was doing.. I wanted someone else (jeff, joe,grant, anyone) to just tell him he should not be around the complicated toys 🙂
MVDBA
March 4, 2020 at 4:44 pm
Michael L John wrote:Is this a good example of someone who may have picked the wrong thing to do with their lives?
https://www.sqlservercentral.com/forums/topic/ssis-package-error-22
oh man i wish i was in that stream ... jeff can be quite direct.
Is that good or bad? 😀 I thought I was pretty nice about saying that he needs to slow down and read his code... especially in the areas that SQL Server was telling him to look.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2020 at 4:55 pm
no, it's a good thing.. but I read the stream and was waiting for the jeffplosion (trademarked) where you decided he was "response by agonising reponse" (see what I did there) - I think we do need to be kind, but some times we do get "time vampires" - they suck the time out of your day because they don't try
MVDBA
March 4, 2020 at 4:59 pm
MVDBA (Mike Vessey) wrote:Michael L John wrote:Is this a good example of someone who may have picked the wrong thing to do with their lives?
https://www.sqlservercentral.com/forums/topic/ssis-package-error-22
oh man i wish i was in that stream ... jeff can be quite direct.
Is that good or bad? 😀 I thought I was pretty nice about saying that he needs to slow down and read his code... especially in the areas that SQL Server was telling him to look.
His last post was the kicker. "The files empty". Uh, duh, did you fill it with anything?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 4, 2020 at 5:03 pm
Michael L John wrote:Is this a good example of someone who may have picked the wrong thing to do with their lives?
https://www.sqlservercentral.com/forums/topic/ssis-package-error-22
oh man i wish i was in that stream ... jeff can be quite direct.
I was in a session with a guy that didn't understand the difference between a view and a table... he thought the view had switched from varchar to nvarcvhar) it took a day to get to the root of the issue.... he mentioned the word SSIS and oracle and i figured out what he was doing.. I wanted someone else (jeff, joe,grant, anyone) to just tell him he should not be around the complicated toys 🙂
I remember that thread. I guess I stopped following it before he mentioned SSIS, but then I could have just missed that.
March 4, 2020 at 5:04 pm
Jeff Moden wrote:MVDBA (Mike Vessey) wrote:Michael L John wrote:Is this a good example of someone who may have picked the wrong thing to do with their lives?
https://www.sqlservercentral.com/forums/topic/ssis-package-error-22
oh man i wish i was in that stream ... jeff can be quite direct.
Is that good or bad? 😀 I thought I was pretty nice about saying that he needs to slow down and read his code... especially in the areas that SQL Server was telling him to look.
His last post was the kicker. "The files empty". Uh, duh, did you fill it with anything?
good lad jeff.. you aren't dropping your standards
MVDBA
March 4, 2020 at 5:10 pm
could be worse jeff - I called a colleague over for some guidance on who wrote some code... he talked me through the 5000 line long proc with cursors, functions, triggers and all of the stuff that happens on Halloween... I put my head in my hands and said " which idiot wrote this pile of ****"
the reply was "me"
There are days when you feel like jeff and that was mine 🙂
now i'm waiting for a joe celko day 🙂
MVDBA
March 4, 2020 at 5:29 pm
Sean Lange wrote:x wrote:I (occasionally) work with an app like that, thousands of tables, no foreign key constraints, even better, if you add an index to a table, the next build will helpfully remove it again unless you add the index to the table from within the app.
For extra credit, name the vendor and app@!
How about this one? This is from our ERP. Everything that is used by the app uses a specific schema with a schema_id of 5.
select count(*)
from sys.tableswhere
schema_id = 5The above returns 3,773. Now let's look at foreign keys.
select *
from sys.foreign_keys
where schema_id = 50 rows returned!!!
From the description, by guess would have to be baan...or whatever the current incarnation of it is called nowadays. If you have table names like ttdsls040XXX where XXX is a company number, then that's the beast. I'm so very thankful I don't have to work with it any more.
Sean, you know what I'm talking about and the pain it brings.
Nope not Baan, but that company was assimilated by the company of the "ERP" I get to work with. Thankfully they abandoned that table naming policy.
Also not Dynamics CRM or SAP. Pretty sure that either of those would be a big step up. 🙁
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2020 at 5:31 pm
could be worse jeff - I called a colleague over for some guidance on who wrote some code... he talked me through the 5000 line long proc with cursors, functions, triggers and all of the stuff that happens on Halloween... I put my head in my hands and said " which idiot wrote this pile of ****"
the reply was "me"
There are days when you feel like jeff and that was mine 🙂
now i'm waiting for a joe celko day 🙂
I have that sort of day frequently when looking at some of the developers code that makes it through testing, IPAT, and finally into production where I finally get to see when the customer complains about the slow code. I look at the code, throw it to the side with the comment that the code sucks.
I end up rewriting it to improve the processing. Just think, this could be avoided if the developers would let me review the code early in development.
Oh well, not going to be our problem shortly as we didn't win the contract that would keep us developing/maintaining the system.
March 4, 2020 at 5:37 pm
Hell it's not just posted questions that are getting worse it's within our dev team
anyone else here wants to kill who ever invented Linq
just look at the end of the code sample - this is why we have DBAs and not entity frameworks
someone asked me if this was good code - I won't sleep tonight seeing that level of nesting and poor code
exec sp_executesql N'SELECT
[Project29].[ID] AS [ID],
[Project29].[AccountCode] AS [AccountCode],
[Project29].[ID1] AS [ID1],
[Project29].[AccountCode1] AS [AccountCode1],
[Project29].[C1] AS [C1],
[Project29].[Name] AS [Name],
[Project29].[TerritoryID] AS [TerritoryID],
[Project29].[ShortName] AS [ShortName],
[Project29].[C3] AS [C2],
[Project29].[C4] AS [C3],
[Project29].[C5] AS [C4],
[Project29].[C6] AS [C5],
[Project29].[C7] AS [C6],
[Project29].[C8] AS [C7],
[Project29].[C9] AS [C8],
[Project29].[C10] AS [C9],
[Project29].[C18] AS [C10],
[Project29].[C11] AS [C11],
[Project29].[C12] AS [C12],
[Project29].[C13] AS [C13],
[Project29].[C19] AS [C14],
[Project29].[C20] AS [C15],
[Project29].[C14] AS [C16],
[Project29].[C15] AS [C17],
[Project29].[C16] AS [C18],
[Project29].[C17] AS [C19],
[Project29].[C2] AS [C20]
FROM ( SELECT
[Project27].[AccountCode] AS [AccountCode],
[Project27].[ID] AS [ID],
[Project27].[Name] AS [Name],
[Project27].[TerritoryID] AS [TerritoryID],
[Project27].[ID1] AS [ID1],
[Project27].[AccountCode1] AS [AccountCode1],
[Project27].[ShortName] AS [ShortName],
[Project27].[C1] AS [C1],
CASE WHEN ([Extent26].[AccountCode] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE LOWER([Extent26].[UserName]) END AS [C2],
[Project27].[C2] AS [C3],
[Project27].[C3] AS [C4],
[Project27].[C4] AS [C5],
[Project27].[C5] AS [C6],
[Project27].[C6] AS [C7],
[Project27].[C7] AS [C8],
[Project27].[C8] AS [C9],
[Project27].[C9] AS [C10],
[Project27].[C10] AS [C11],
[Project27].[C11] AS [C12],
[Project27].[C12] AS [C13],
[Project27].[C13] AS [C14],
[Project27].[C14] AS [C15],
[Project27].[C15] AS [C16],
CASE WHEN ([Extent26].[AccountCode] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C17],
[Project27].[C16] AS [C18],
[Project27].[C17] AS [C19],
[Project27].[C18] AS [C20]
FROM (SELECT
[Project15].[AccountCode] AS [AccountCode],
[Project15].[ID] AS [ID],
[Project15].[Name] AS [Name],
[Project15].[TerritoryID] AS [TerritoryID],
[Project15].[ID1] AS [ID1],
[Project15].[AccountCode1] AS [AccountCode1],
[Project15].[ShortName] AS [ShortName],
1 AS [C1],
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent15].[AccountCode] AS [AccountCode],
[Extent15].[TemplateHeaderID] AS [TemplateHeaderID],
[Extent16].[AccountCode] AS [AccountCode1],
[Extent16].[ID] AS [ID],
[Extent16].[TemplateTypeID] AS [TemplateTypeID]
FROM [dbo].[TemplateHeaderCampaignAllocation] AS [Extent15]
INNER JOIN [dbo].[TemplateHeader] AS [Extent16] ON ([Extent15].[TemplateHeaderID] = [Extent16].[ID]) AND ([Extent15].[AccountCode] = [Extent16].[AccountCode])
WHERE ([Project15].[AccountCode] = [Extent15].[AccountCode]) AND ([Project15].[ID] = [Extent15].[StoreID])
) AS [Project16]
WHERE ([Project16].[TemplateTypeID] IN (225,226)) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[CampaignDespatchTerritoryTemplate] AS [Extent17]
WHERE ([Project16].[AccountCode] = [Extent17].[AccountCode]) AND ([Project16].[TemplateHeaderID] = [Extent17].[TemplateHeaderID]) AND ([Extent17].[TerritoryID] = [Project15].[TerritoryID]) AND ([Extent17].[despatch_group] = @p__linq__1)
))
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C2],
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent18].[AccountCode] AS [AccountCode],
[Extent18].[TemplateHeaderID] AS [TemplateHeaderID],
[Extent19].[AccountCode] AS [AccountCode1],
[Extent19].[ID] AS [ID],
[Extent19].[TemplateTypeID] AS [TemplateTypeID]
FROM [dbo].[TemplateHeaderCampaignAllocation] AS [Extent18]
INNER JOIN [dbo].[TemplateHeader] AS [Extent19] ON ([Extent18].[TemplateHeaderID] = [Extent19].[ID]) AND ([Extent18].[AccountCode] = [Extent19].[AccountCode])
WHERE ([Project15].[AccountCode] = [Extent18].[AccountCode]) AND ([Project15].[ID] = [Extent18].[StoreID])
) AS [Project19]
WHERE (191 = [Project19].[TemplateTypeID]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[CampaignDespatchTerritoryTemplate] AS [Extent20]
WHERE ([Project19].[AccountCode] = [Extent20].[AccountCode]) AND ([Project19].[TemplateHeaderID] = [Extent20].[TemplateHeaderID]) AND ([Extent20].[TerritoryID] = [Project15].[TerritoryID]) AND ([Extent20].[despatch_group] = @p__linq__2)
))
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C3],
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent21]
WHERE ([Project15].[AccountCode] = [Extent21].[AccountCode]) AND ([Project15].[ID] = [Extent21].[StoreID]) AND ([Extent21].[despatch_group] = @p__linq__3) AND (1 = [Extent21].[StepID])
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C4],
CAST( [Project15].[C1] AS datetime2) AS [C5],
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent22]
WHERE ([Project15].[AccountCode] = [Extent22].[AccountCode]) AND ([Project15].[ID] = [Extent22].[StoreID]) AND ([Extent22].[despatch_group] = @p__linq__5) AND (2 = [Extent22].[StepID])
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C6],
CAST( [Project15].[C2] AS datetime2) AS [C7],
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent23]
WHERE ([Project15].[AccountCode] = [Extent23].[AccountCode]) AND ([Project15].[ID] = [Extent23].[StoreID]) AND ([Extent23].[despatch_group] = @p__linq__7) AND (3 = [Extent23].[StepID])
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C8],
CAST( [Project15].[C3] AS datetime2) AS [C9],
CAST( [Project15].[C5] AS datetime2) AS [C10],
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent24]
WHERE ([Project15].[AccountCode] = [Extent24].[AccountCode]) AND ([Project15].[ID] = [Extent24].[StoreID]) AND ([Extent24].[despatch_group] = @p__linq__11) AND (5 = [Extent24].[StepID])
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C11],
CAST( [Project15].[C6] AS datetime2) AS [C12],
CAST( [Project15].[C9] AS datetime2) AS [C13],
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent25]
WHERE ([Project15].[AccountCode] = [Extent25].[AccountCode]) AND ([Project15].[ID] = [Extent25].[StoreID]) AND ([Extent25].[despatch_group] = @p__linq__16) AND (7 = [Extent25].[StepID])
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C14],
CAST( [Project15].[C10] AS datetime2) AS [C15],
[Project15].[C4] AS [C16],
[Project15].[C7] AS [C17],
[Project15].[C8] AS [C18]
FROM ( SELECT
[Project14].[AccountCode] AS [AccountCode],
[Project14].[ID] AS [ID],
[Project14].[Name] AS [Name],
[Project14].[TerritoryID] AS [TerritoryID],
[Project14].[ID1] AS [ID1],
[Project14].[AccountCode1] AS [AccountCode1],
[Project14].[ShortName] AS [ShortName],
[Project14].[C1] AS [C1],
[Project14].[C2] AS [C2],
[Project14].[C3] AS [C3],
[Project14].[C4] AS [C4],
[Project14].[C5] AS [C5],
[Project14].[C6] AS [C6],
[Project14].[C7] AS [C7],
[Project14].[C8] AS [C8],
[Project14].[C9] AS [C9],
(SELECT
MAX([Extent14].[CreatedDateTime]) AS [A1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent14]
WHERE ([Project14].[AccountCode] = [Extent14].[AccountCode]) AND ([Project14].[ID] = [Extent14].[StoreID]) AND ([Extent14].[despatch_group] = @p__linq__17) AND (7 = [Extent14].[StepID])) AS [C10]
FROM ( SELECT
[Project13].[AccountCode] AS [AccountCode],
[Project13].[ID] AS [ID],
[Project13].[Name] AS [Name],
[Project13].[TerritoryID] AS [TerritoryID],
[Project13].[ID1] AS [ID1],
[Project13].[AccountCode1] AS [AccountCode1],
[Project13].[ShortName] AS [ShortName],
[Project13].[C1] AS [C1],
[Project13].[C2] AS [C2],
[Project13].[C3] AS [C3],
[Project13].[C4] AS [C4],
[Project13].[C5] AS [C5],
[Project13].[C6] AS [C6],
[Project13].[C7] AS [C7],
[Project13].[C8] AS [C8],
(SELECT
MAX([Extent13].[CreatedDateTime]) AS [A1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent13]
WHERE ([Project13].[AccountCode] = [Extent13].[AccountCode]) AND ([Project13].[ID] = [Extent13].[StoreID]) AND ([Extent13].[despatch_group] = @p__linq__15) AND (6 = [Extent13].[StepID])) AS [C9]
FROM ( SELECT
[Project11].[AccountCode] AS [AccountCode],
[Project11].[ID] AS [ID],
[Project11].[Name] AS [Name],
[Project11].[TerritoryID] AS [TerritoryID],
[Project11].[ID1] AS [ID1],
[Project11].[AccountCode1] AS [AccountCode1],
[Project11].[ShortName] AS [ShortName],
[Project11].[C1] AS [C1],
[Project11].[C2] AS [C2],
[Project11].[C3] AS [C3],
[Project11].[C4] AS [C4],
[Project11].[C5] AS [C5],
[Project11].[C6] AS [C6],
[Project11].[C7] AS [C7],
(SELECT
COUNT(1) AS [A1]
FROM ( SELECT DISTINCT
[Extent12].[TemplateTypeID] AS [TemplateTypeID]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent12]
WHERE ([Project11].[AccountCode] = [Extent12].[AccountCode]) AND ([Project11].[ID] = [Extent12].[StoreID]) AND ([Extent12].[despatch_group] = @p__linq__14) AND (6 = [Extent12].[StepID])
) AS [Distinct3]) AS [C8]
FROM ( SELECT
[Project7].[AccountCode] AS [AccountCode],
[Project7].[ID] AS [ID],
[Project7].[Name] AS [Name],
[Project7].[TerritoryID] AS [TerritoryID],
[Project7].[ID1] AS [ID1],
[Project7].[AccountCode1] AS [AccountCode1],
[Project7].[ShortName] AS [ShortName],
[Project7].[C1] AS [C1],
[Project7].[C2] AS [C2],
[Project7].[C3] AS [C3],
[Project7].[C4] AS [C4],
[Project7].[C5] AS [C5],
[Project7].[C6] AS [C6],
(SELECT
COUNT(1) AS [A1]
FROM ( SELECT DISTINCT
[Project8].[TemplateTypeID] AS [TemplateTypeID]
FROM ( SELECT
[Extent9].[AccountCode] AS [AccountCode],
[Extent9].[TemplateHeaderID] AS [TemplateHeaderID],
[Extent9].[Qty] AS [Qty],
[Extent10].[AccountCode] AS [AccountCode1],
[Extent10].[ID] AS [ID],
[Extent10].[TemplateTypeID] AS [TemplateTypeID]
FROM [dbo].[TemplateHeaderCampaignAllocation] AS [Extent9]
INNER JOIN [dbo].[TemplateHeader] AS [Extent10] ON ([Extent9].[TemplateHeaderID] = [Extent10].[ID]) AND ([Extent9].[AccountCode] = [Extent10].[AccountCode])
WHERE ([Project7].[AccountCode] = [Extent9].[AccountCode]) AND ([Project7].[ID] = [Extent9].[StoreID])
) AS [Project8]
WHERE ([Project8].[Qty] > 0) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[CampaignDespatchTerritoryTemplate] AS [Extent11]
WHERE ([Project8].[AccountCode] = [Extent11].[AccountCode]) AND ([Project8].[TemplateHeaderID] = [Extent11].[TemplateHeaderID]) AND ([Extent11].[despatch_group] = @p__linq__13) AND ([Extent11].[TerritoryID] = [Project7].[TerritoryID])
))
) AS [Distinct2]) AS [C7]
FROM ( SELECT
[Project6].[AccountCode] AS [AccountCode],
[Project6].[ID] AS [ID],
[Project6].[Name] AS [Name],
[Project6].[TerritoryID] AS [TerritoryID],
[Project6].[ID1] AS [ID1],
[Project6].[AccountCode1] AS [AccountCode1],
[Project6].[ShortName] AS [ShortName],
[Project6].[C1] AS [C1],
[Project6].[C2] AS [C2],
[Project6].[C3] AS [C3],
[Project6].[C4] AS [C4],
[Project6].[C5] AS [C5],
(SELECT
MAX([Extent8].[CreatedDateTime]) AS [A1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent8]
WHERE ([Project6].[AccountCode] = [Extent8].[AccountCode]) AND ([Project6].[ID] = [Extent8].[StoreID]) AND ([Extent8].[despatch_group] = @p__linq__12) AND (5 = [Extent8].[StepID])) AS [C6]
FROM ( SELECT
[Project5].[AccountCode] AS [AccountCode],
[Project5].[ID] AS [ID],
[Project5].[Name] AS [Name],
[Project5].[TerritoryID] AS [TerritoryID],
[Project5].[ID1] AS [ID1],
[Project5].[AccountCode1] AS [AccountCode1],
[Project5].[ShortName] AS [ShortName],
[Project5].[C1] AS [C1],
[Project5].[C2] AS [C2],
[Project5].[C3] AS [C3],
[Project5].[C4] AS [C4],
(SELECT
MAX([Extent7].[CreatedDateTime]) AS [A1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent7]
WHERE ([Project5].[AccountCode] = [Extent7].[AccountCode]) AND ([Project5].[ID] = [Extent7].[StoreID]) AND ([Extent7].[despatch_group] = @p__linq__10) AND (4 = [Extent7].[StepID])) AS [C5]
FROM ( SELECT
[Project3].[AccountCode] AS [AccountCode],
[Project3].[ID] AS [ID],
[Project3].[Name] AS [Name],
[Project3].[TerritoryID] AS [TerritoryID],
[Project3].[ID1] AS [ID1],
[Project3].[AccountCode1] AS [AccountCode1],
[Project3].[ShortName] AS [ShortName],
[Project3].[C1] AS [C1],
[Project3].[C2] AS [C2],
[Project3].[C3] AS [C3],
(SELECT
COUNT(1) AS [A1]
FROM ( SELECT DISTINCT
[Extent6].[TemplateTypeID] AS [TemplateTypeID]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent6]
WHERE ([Project3].[AccountCode] = [Extent6].[AccountCode]) AND ([Project3].[ID] = [Extent6].[StoreID]) AND ([Extent6].[despatch_group] = @p__linq__9) AND (4 = [Extent6].[StepID])
) AS [Distinct1]) AS [C4]
FROM ( SELECT
[Project2].[AccountCode] AS [AccountCode],
[Project2].[ID] AS [ID],
[Project2].[Name] AS [Name],
[Project2].[TerritoryID] AS [TerritoryID],
[Project2].[ID1] AS [ID1],
[Project2].[AccountCode1] AS [AccountCode1],
[Project2].[ShortName] AS [ShortName],
[Project2].[C1] AS [C1],
[Project2].[C2] AS [C2],
(SELECT
MAX([Extent5].[CreatedDateTime]) AS [A1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent5]
WHERE ([Project2].[AccountCode] = [Extent5].[AccountCode]) AND ([Project2].[ID] = [Extent5].[StoreID]) AND ([Extent5].[despatch_group] = @p__linq__8) AND (3 = [Extent5].[StepID])) AS [C3]
FROM ( SELECT
[Project1].[AccountCode] AS [AccountCode],
[Project1].[ID] AS [ID],
[Project1].[Name] AS [Name],
[Project1].[TerritoryID] AS [TerritoryID],
[Project1].[ID1] AS [ID1],
[Project1].[AccountCode1] AS [AccountCode1],
[Project1].[ShortName] AS [ShortName],
[Project1].[C1] AS [C1],
(SELECT
MAX([Extent4].[CreatedDateTime]) AS [A1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent4]
WHERE ([Project1].[AccountCode] = [Extent4].[AccountCode]) AND ([Project1].[ID] = [Extent4].[StoreID]) AND ([Extent4].[despatch_group] = @p__linq__6) AND (2 = [Extent4].[StepID])) AS [C2]
FROM ( SELECT
[Extent1].[AccountCode] AS [AccountCode],
[Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[TerritoryID] AS [TerritoryID],
[Extent2].[ID] AS [ID1],
[Extent2].[AccountCode] AS [AccountCode1],
[Extent2].[ShortName] AS [ShortName],
(SELECT
MAX([Extent3].[CreatedDateTime]) AS [A1]
FROM [dbo].[CampaignDespatchStoreConfirmation] AS [Extent3]
WHERE ([Extent1].[AccountCode] = [Extent3].[AccountCode]) AND ([Extent1].[ID] = [Extent3].[StoreID]) AND ([Extent3].[despatch_group] = @p__linq__4) AND (1 = [Extent3].[StepID])) AS [C1]
FROM [dbo].[Store] AS [Extent1]
INNER JOIN [dbo].[Territory] AS [Extent2] ON ([Extent1].[AccountCode] = [Extent2].[AccountCode]) AND ([Extent1].[TerritoryID] = [Extent2].[ID])
WHERE ([Extent1].[AccountCode] = @p__linq__0) AND ([Extent1].[Active] = 1)
) AS [Project1]
) AS [Project2]
) AS [Project3]
) AS [Project5]
) AS [Project6]
) AS [Project7]
) AS [Project11]
) AS [Project13]
) AS [Project14]
) AS [Project15] ) AS [Project27]
LEFT OUTER JOIN [dbo].[UserStore] AS [Extent26] ON ([Project27].[AccountCode] = [Extent26].[AccountCode]) AND ([Project27].[ID] = [Extent26].[StoreID])
WHERE EXISTS (SELECT
1 AS [C1]
FROM [dbo].[UserStore] AS [Extent27]
WHERE ([Project27].[AccountCode] = [Extent27].[AccountCode]) AND ([Project27].[ID] = [Extent27].[StoreID]) AND (LOWER([Extent27].[UserName]) IN (N''obfuscated for security')) AND (LOWER([Extent27].[UserName]) IS NOT NULL)
)
) AS [Project29]
ORDER BY [Project29].[ID] ASC, [Project29].[AccountCode] ASC, [Project29].[ID1] ASC, [Project29].[AccountCode1] ASC, [Project29].[C17] ASC',N'@p__linq__0 varchar(8000),@p__linq__4 varchar(8000),@p__linq__6 varchar(8000),@p__linq__8 varchar(8000),@p__linq__9 varchar(8000),@p__linq__10 varchar(8000),@p__linq__12 varchar(8000),@p__linq__13 varchar(8000),@p__linq__14 varchar(8000),@p__linq__15 varchar(8000),@p__linq__17 varchar(8000),@p__linq__1 varchar(8000),@p__linq__2 varchar(8000),@p__linq__3 varchar(8000),@p__linq__5 varchar(8000),@p__linq__7 varchar(8000),@p__linq__11 varchar(8000),@p__linq__16 varchar(8000)',@p__linq__0='STARBUCKS',@p__linq__4='Spring 2020',@p__linq__6='Spring 2020',@p__linq__8='Spring 2020',@p__linq__9='Spring 2020',@p__linq__10='Spring 2020',@p__linq__12='Spring 2020',@p__linq__13='Spring 2020',@p__linq__14='Spring 2020',@p__linq__15='Spring 2020',@p__linq__17='Spring 2020',@p__linq__1='Spring 2020',@p__linq__2='Spring 2020',@p__linq__3='Spring 2020',@p__linq__5='Spring 2020',@p__linq__7='Spring 2020',@p__linq__11='Spring 2020',@p__linq__16='Spring 2020'
MVDBA
March 4, 2020 at 5:53 pm
That's some bad code there Mike. I've seen some similar, but add a couple UNION statements and all with in an SSRS report. Talk about slow code.
I've also had that moment when I look at some code and say "Who wrote this crap?" Only to realize later it was me, about 10+ years ago. 😉
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
March 4, 2020 at 5:57 pm
I'm dealing with a crappy proc, and a crappy design, right now.
The devs did the "Code first" design. In other words, the ORM designed the database for them.
I can't post the code for the biggest offending proc, I don't feel like taking the time to obfuscate it. But, it does 16 selects into a temp table, of the same tables, with slightly differing WHERE clauses. it then selects distinct into another temp table, and then finally does a count and group by to get a count of each item.
There are 10 tables in this database. There are FORTY unused indexes in it. The devs keep trial and erroring adding indexes.
I could go on and on, but my fingers would get tired.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 4, 2020 at 9:37 pm
That's some bad code there Mike. I've seen some similar, but add a couple UNION statements and all with in an SSRS report. Talk about slow code.
I've also had that moment when I look at some code and say "Who wrote this crap?" Only to realize later it was me, about 10+ years ago. 😉
Gee, I look at code I wrote 2 days ago and wonder the same thing!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 4, 2020 at 9:40 pm
below86 wrote:That's some bad code there Mike. I've seen some similar, but add a couple UNION statements and all with in an SSRS report. Talk about slow code.
I've also had that moment when I look at some code and say "Who wrote this crap?" Only to realize later it was me, about 10+ years ago. 😉
Gee, I look at code I wrote 2 days ago and wonder the same thing!
In a similar vein, if I look at any code I wrote more than about two weeks ago, I always wonder: "What is this trying to do and how the hell does it work?"
Viewing 15 posts - 64,546 through 64,560 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply