User Profile
JohnTM
Copper Contributor
Joined 8 years ago
User Widgets
Recent Discussions
Re: Unused Join affects performance on View select
...and have now found that this is not a general solution... I have now found another example, without uniqueidentifier data types, where specifying a join has had an enormous impact on a query's performance for no apparent reason - and there's no "PlanAffectingConvert" issues that I can see. I presume this qualifies as a bug in the Query engine. The issue is found in an Azure SQL database - I'll do some testing to see if this is a general SQL thing or just Azure SQL.110Views0likes1CommentRe: Unused Join affects performance on View select
Hirodgerkong, , Thanks for your reply. Using an Indexed view isn't an option - there are a lot of restrictions on creating indexed views and the CustomerTeam view falls foul of one of those - the underlying table is in a different schema. But it did lead me to a solution - if not an explanation... The underlying table has a number of uniqueidentifier data type columns and the view uses CAST to convert these to varchar. The execution plan referenced a"PlanAffectingConvert" on thedbo.CustomerTeam.[Customer Team DynID] column.I changed the data type on the column on the underlying table, recreated the view without the CAST, and the problem is gone... So, changing the underlying data type (luckily an option in my scenario), works around the problem. But it still doesn't explain why the PlanAffectingConverton one join 'branch' of the query had such a considerable impact on an entirely unrelated 'branch'.227Views0likes2CommentsUnused Join affects performance on View select
The query below runs in less than 1 second when dbo.CustomerTeam, which contains 6 rows, is a table but almost 4 minutes when it is created as a view. ProjectManagement contains 737615 rows. The query plan shows221663 rows read on ProjectManagement to Project/ProjectTask join when CustomerTeam is a table but4195075 when it is a view - despite CustomerTeam not being on the join path! Rows read on the Project Management to Customer Team join is149790 in both cases - which is the number of rows in team 'Sales Team'. Why is the performance on theProjectManagement to Project/ProjectTask join being negatively influenced by CustomerTeam, which is on a different join, being a view? SELECT Customer FROM ProjectManagement pm LEFT OUTER JOIN dbo.CustomerTeam ct ON ct.[Customer Team DynID] = pm.[Customer Team DynID] LEFT OUTER JOIN dbo.Project p INNER JOIN dbo.ProjectTask pt ON p.[Project DynID] = pt.[Project DynID] ON pt.[Project DynID] = pm.[Project DynID] AND pt.[Task DynID] = pm.[Task DynID] LEFT OUTER JOIN dbo.Customer c ON p.[Customer DynID] = c.[Customer DynID] WHERE ct.[Customer Team] IN ('Sales Team') GROUP BY Customer OPTION(RECOMPILE)291Views0likes4CommentsMS Word: Opening a template from Organisational Asset Library doesn't pick up newest version
In MS Word, A user created a new document from the Organisational Asset Library. Subsequently, the Template was updated in theOrganisational Asset Library. When the user creates a new document, the original version is not returned.562Views0likes0CommentsRe: Horrible sharing URLs in Sharepoint
Hi Andrew Hodges , thanks for the response. The rejected idea was to provide a new format short URL. I'm looking to revert back to the way it was before - a user-friendly URL displaying the Item Title. As to the permissions, Existing Access is only available through the individual Active Sites panel - that's fine for the SharePoint Root site (and I've changed that setting now, thanks) but I'd have to go each of the myriad sites created by Office365 groups, Teams, PWA, etc. and then there's OneDrive! Head - wrecking! ... and I'd still have the ugly URL format...! I'm just amazed that Microsoft haven't seen this as a real barrier to user adoption. Every (as in EVERY!) user I've dealt with has mentioned it to me. We have a large variety of solutions (workarounds!) - from getting the ugly URLs (meaning the emails/IMs are, generally, unreadable), to people using the Share via email and sending a sharing request every time, to people not providing a link and just referring, in text, to the document location, to people sending attachments. Ultimately, people are not happy with this! Part of the frustration is that I had created videos and instruction Wikis showing people how easy it was to get a neat user-friendly link; one of the catalysts for adoption was me demonstrating how easy it was to get a neat link! - and then Microsoft changed it without asking...! Aaaarrgh! Regards, John M6.1KViews0likes9CommentsHorrible sharing URLs in Sharepoint
What is the story with Sharing URLs - they are horrible! In classic SharePoint you could click/drag over a document and you had a user-friendly URL that linked to the document and could be pasted directly into documents, emails etc. Permissions were set once based on the organisation's policy. The link was completely separate to access. Now it creates a horrible unprofessional looking GUID filled thing! In addition, you now have to specify the type of sharing/access you are providing - in most cases existing access is fine! - which can then result in completely unnecessary permissions entries that now have to be managed/audited. Not only was it fine the way it was, it was better! Any chance of sorting this out?6.2KViews0likes11Comments