COU-628 - Getting media paths query results in SQL timeout when there is lots of data

Created by Shannon Deminick 05 Oct 2017, 13:39:20 Updated by Gareth Evans 08 Jan 2018, 00:17:46

Tags: Unscheduled

Subtask of: COU-521

Due to how much data there can be and that this query will result in executing an Index Scan on the cmsPropertyData PK which will literally iterate over every row in there (assume there's 1,000,000 and you can see why this will perform horribly) ultimately we need to fix this: http://issues.umbraco.org/issue/U4-10286 so in the meantime I've found an ugly query to work around this Index Scan issue which is to use a sub-query of Ids. Doing this results in the same Index Scan taking place but it only visits the explicit rows that are provided, not every row in the cmsPropertyData table. This results in a tremendously faster query when there is tons of data.

1 Attachments

Comments

Claus Jensen 06 Oct 2017, 11:16:02

Merged


Gareth Evans 11 Oct 2017, 22:27:53

@Shandem I've just found this issue and I think it might be the same as COU-627. Would it be possible for you to please review the stack trace & SQL query on COU-627 to determine if that issue is the same problem or just similar? I'm getting timeout errors when resolving media paths (from media referenced in an RTE in a grid), but I've tested with the 3.1.5-493 nightly and could still reproduce my issue. I cannot tell if this fix is included in that nightly. If there is a beta or nightly I could test with, i'd be happy to check and confirm back. I am deploying to a local server, not cloud, and am using the old UI version of courier, not concorde-ui.


Gareth Evans 22 Nov 2017, 04:06:04

Further to my comment above, I have tested COU-627 in 3.1.6 and can confirm the fault is NOT fixed.


Gareth Evans 08 Jan 2018, 00:17:46

Further to comment above, COU-627 is fixed independently and unrelated to this


Priority: Normal

Type: Bug

State: Fixed

Assignee:

Difficulty:

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 3.1.6

Sprint: Sprint 69

Story Points: 1

Cycle: 5