PSJupiter2
New member
- Local time
- Today, 01:38
- Joined
- May 31, 2019
- Messages
- 3
Hello,
I am new here, so I will do my best with etiquette.
TLDR: I have a query with 14 iterations of a table to pull descriptive data. Performance begins to suffer exponentially after the 10th iteration, why does this happen? Also, does anyone have suggestions to tame this unruly beast?
eg:
dbo_asCodes2
dbo_asCodes2_1
dbo_asCodes2_2
dbo_asCodes2_3
etc...
The full story:
I am running with the following set-up
Access 2016 (32-bit)
Windows 10 - 1809 (64-bit)
Intel I7-4790 3.60GHz, 16 GB RAM
All Access tables are Linked ODBC connections to a LAN
Of note, I have very little control over my PC, my network, or my data structure. This includes installing new programs (like MS SQL management studio etc...), adding custom views to my networked location, and modifying tables on LAN (eg adding primary keys).
Now, I am creating a report that is controlled by one query. That query is a MONSTER. There are 22 tables, something like 21 joins, and 4 formulas. I know that it needs to be modified and broken up. The problem is that I don't know how, given the tools available.
I have 14 iterations of the same table because I need descriptive data that is held there.
This is what the data looks like:
dbo_asSlsImprRes: See Attached ~ 58,000 records
dbo_asCodes2 See Attached ~ 1,764 records
dbo_asLandRate ~ 57 records
dbo_asParcelAddresses ~ 87,700 records
dbo_asAssessRoll ~ 717,968 records
dbo_asLandSumm ~ 786,987 records
dbo_asAddition ~ 1,140 records
The query takes about 8-9 minutes to run in its current state. The query takes about 6 seconds to run with 10 iterations of the above dbo_asCodes2 table. The performance fell off a cliff when I added the last few dbo_asCodes2 iterations. Why did that happen?
I have tried splitting the query into two subqueries; one with all of the descriptors, and one with all other information. Performance was equal or worse.
I also tried splitting each instance of dbo_asCodes2 into it's own subquery. Again, performance was equal or worse.
Is there some way to set up a subquery of dbo_asCodes2 that will allow me to only have one iteration in my main query?
In Summary:
Why does performance suffer exponentially after the nth iteration of a table?
Can I create a subquery from my dbo_asCodes2 table to allow for faster performance and a more elegant main query?
Does anyone have any suggestions on how to break up this query for better performance?
Here is my query: See attached for screenshot
I am new here, so I will do my best with etiquette.
TLDR: I have a query with 14 iterations of a table to pull descriptive data. Performance begins to suffer exponentially after the 10th iteration, why does this happen? Also, does anyone have suggestions to tame this unruly beast?
eg:
dbo_asCodes2
dbo_asCodes2_1
dbo_asCodes2_2
dbo_asCodes2_3
etc...
The full story:
I am running with the following set-up
Access 2016 (32-bit)
Windows 10 - 1809 (64-bit)
Intel I7-4790 3.60GHz, 16 GB RAM
All Access tables are Linked ODBC connections to a LAN
Of note, I have very little control over my PC, my network, or my data structure. This includes installing new programs (like MS SQL management studio etc...), adding custom views to my networked location, and modifying tables on LAN (eg adding primary keys).
Now, I am creating a report that is controlled by one query. That query is a MONSTER. There are 22 tables, something like 21 joins, and 4 formulas. I know that it needs to be modified and broken up. The problem is that I don't know how, given the tools available.
I have 14 iterations of the same table because I need descriptive data that is held there.
This is what the data looks like:
dbo_asSlsImprRes: See Attached ~ 58,000 records
dbo_asCodes2 See Attached ~ 1,764 records
dbo_asLandRate ~ 57 records
dbo_asParcelAddresses ~ 87,700 records
dbo_asAssessRoll ~ 717,968 records
dbo_asLandSumm ~ 786,987 records
dbo_asAddition ~ 1,140 records
The query takes about 8-9 minutes to run in its current state. The query takes about 6 seconds to run with 10 iterations of the above dbo_asCodes2 table. The performance fell off a cliff when I added the last few dbo_asCodes2 iterations. Why did that happen?
I have tried splitting the query into two subqueries; one with all of the descriptors, and one with all other information. Performance was equal or worse.
I also tried splitting each instance of dbo_asCodes2 into it's own subquery. Again, performance was equal or worse.
Is there some way to set up a subquery of dbo_asCodes2 that will allow me to only have one iteration in my main query?
In Summary:
Why does performance suffer exponentially after the nth iteration of a table?
Can I create a subquery from my dbo_asCodes2 table to allow for faster performance and a more elegant main query?
Does anyone have any suggestions on how to break up this query for better performance?
Here is my query: See attached for screenshot
Code:
SELECT dbo_asSlsImprRes.ParcelNo,
dbo_asParcelAddresses.Address,
dbo_asSlsImprRes.SaleDate,
dbo_asSlsImprRes.GrantorNm,
dbo_asSlsImprRes.GranteeNm,
dbo_asSlsImprRes.SalePriceContract,
dbo_asSlsImprRes.Specials,
dbo_asSlsImprRes.SalePriceAdjusted,
dbo_asSlsImprRes.T_ActiveFile,
dbo_asSlsImprRes.Ratio,
dbo_asSlsImprRes.Usable,
dbo_asAssesRoll.FullImprValue,
dbo_asAssesRoll.FullLandValue,
[dbo_asAssesRoll]![FullImprValue]+[dbo_asAssesRoll]![FullLandValue] AS [T&FValue],
[dbo_asSlsImprRes]![SalePriceAdjusted]/[dbo_asSlsImprRes]![BldgTotSF] AS [$/SF],
([dbo_asAssesRoll].[FullImprValue]+[dbo_asAssesRoll].[FullLandValue])/[dbo_asSlsImprRes].[SalePriceAdjusted] AS SaleRatio,
dbo_asSlsImprRes.T_OptionCode,
dbo_asSlsImprRes.InspDate,
dbo_asSlsImprRes.T_PropType,
dbo_asCodes2.CodeDesc AS PropType,
dbo_asLandRate.T_LandType,
dbo_asLandRate.LandTypeDesc,
dbo_asSlsImprRes.T_BldgStoryHeight,
dbo_asCodes2_1.CodeDesc AS StoryHeight,
dbo_asSlsImprRes.T_Grade,
dbo_asCodes2_2.CodeDesc AS Grade,
dbo_asSlsImprRes.T_BldgCondition,
dbo_asCodes2_3.CodeDesc AS CurrCond,
dbo_asSlsImprRes.BldgYrBuilt,
dbo_asSlsImprRes.BldgTotSF,
dbo_asSlsImprRes.BldgMainFloorSF,
dbo_asCodes2_4.CodeDesc AS BsmtArea,
dbo_asCodes2_5.CodeDesc AS BsmtFin,
dbo_asSlsImprRes.NoOfBedrooms,
dbo_asCodes2_6.CodeDesc AS Baths,
dbo_asCodes2_7.CodeDesc AS BltInAdj,
dbo_asCodes2_8.CodeDesc AS NoFrpls,
dbo_asCodes2_9.CodeDesc AS GarageType,
dbo_asCodes2_10.CodeDesc AS NoStalls,
dbo_asCodes2_12.CodeDesc AS AirCond,
dbo_asCodes2_13.CodeDesc AS PorchDeck,
dbo_asSlsImprRes.PorchValue,
dbo_asCodes2_14.CodeDesc AS Extras,
dbo_asSlsImprRes.ExtraValue,
dbo_asAddition.AdditionName,
dbo_asCodes2_11.CodeDesc AS MapZone,
dbo_asSlsImprRes.Comments,
/*I Commented out the following formula as SQL syntax doesn't seem to like it. BTW this formula is not the problem, I have been running it without issue for years*/
/*"T:\Assessors\ParcelDocumentsNew\Parcel\AS\Parcel\01" & "\" & Mid([dbo_asSlsImprRes]![ParcelNo],4,4) & "\" & Mid([dbo_asSlsImprRes]![ParcelNo],9,5) & "\" & Mid([dbo_asSlsImprRes]![ParcelNo],15,3) & "\" & [dbo_asSlsImprRes]![ParcelNo] & ".jpg" AS Fullpath*/
FROM [80319 through 80415]
INNER JOIN (((((((((((((((((((dbo_asSlsImprRes
INNER JOIN dbo_asAssesRoll
ON (dbo_asSlsImprRes.SegId = dbo_asAssesRoll.SegId)
AND (dbo_asSlsImprRes.CoNo = dbo_asAssesRoll.CoNo)
AND (dbo_asSlsImprRes.ParcelNo = dbo_asAssesRoll.ParcelNo))
INNER JOIN dbo_asCodes2
ON (dbo_asSlsImprRes.T_PropType = dbo_asCodes2.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_1
ON (dbo_asSlsImprRes.T_BldgStoryHeight = dbo_asCodes2_1.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_1.CoNo))
INNER JOIN dbo_asLandRate
ON (dbo_asSlsImprRes.T_LandType = dbo_asLandRate.T_LandType)
AND (dbo_asSlsImprRes.CoNo = dbo_asLandRate.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_2
ON (dbo_asSlsImprRes.T_Grade = dbo_asCodes2_2.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_2.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_3
ON (dbo_asSlsImprRes.T_BldgCondition = dbo_asCodes2_3.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_3.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_4
ON (dbo_asSlsImprRes.T_BasemtArea = dbo_asCodes2_4.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_4.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_5
ON (dbo_asSlsImprRes.T_BasemtFinish = dbo_asCodes2_5.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_5.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_6
ON (dbo_asSlsImprRes.T_NoOfBaths = dbo_asCodes2_6.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_6.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_7
ON (dbo_asSlsImprRes.T_BltInAdj = dbo_asCodes2_7.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_7.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_8
ON (dbo_asSlsImprRes.T_NoOfFireplces = dbo_asCodes2_8.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_8.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_9
ON (dbo_asSlsImprRes.T_GarageType = dbo_asCodes2_9.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_9.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_10
ON (dbo_asSlsImprRes.T_NoOfGarageStalls = dbo_asCodes2_10.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_10.CoNo))
INNER JOIN dbo_asParcelAddresses
ON (dbo_asAssesRoll.SegId = dbo_asParcelAddresses.SegId)
AND (dbo_asAssesRoll.ParcelNo = dbo_asParcelAddresses.ParcelNo)
AND (dbo_asAssesRoll.CoNo = dbo_asParcelAddresses.CoNo))
INNER JOIN (dbo_asLandSumm
INNER JOIN dbo_asAddition
ON dbo_asLandSumm.AdditionNo = dbo_asAddition.AdditionNo)
ON (dbo_asAssesRoll.Year = dbo_asLandSumm.Year)
AND (dbo_asAssesRoll.ParcelNo = dbo_asLandSumm.ParcelNo)
AND (dbo_asAssesRoll.CoNo = dbo_asLandSumm.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_11
ON (dbo_asLandSumm.T_AsessorsMapZoneRes = dbo_asCodes2_11.CodeId)
AND (dbo_asLandSumm.CoNo = dbo_asCodes2_11.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_12
ON (dbo_asSlsImprRes.T_AirCond = dbo_asCodes2_12.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_12.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_13
ON (dbo_asSlsImprRes.T_PorchType = dbo_asCodes2_13.CodeId)
AND (dbo_asSlsImprRes.CoNo = dbo_asCodes2_13.CoNo))
INNER JOIN dbo_asCodes2 AS dbo_asCodes2_14
ON (dbo_asSlsImprRes.CoNo = dbo_asCodes2_14.CoNo)
AND (dbo_asSlsImprRes.T_ExtraCode = dbo_asCodes2_14.CodeId))
ON [80319 through 80415].PK = dbo_asSlsImprRes.PK
WHERE (((dbo_asParcelAddresses.AddrType)="Parcel")
AND ((dbo_asSlsImprRes.CoNo)="Fargo")
AND ((dbo_asAssesRoll.Year)=2019)
AND ((dbo_asLandRate.T_UseCode)="R")
AND ((dbo_asCodes2.CodeType)="PropType")
AND ((dbo_asCodes2_1.CodeType)="StoryHeight")
AND ((dbo_asCodes2_2.CodeType)="Grade")
AND ((dbo_asCodes2_3.CodeType)="CurrCond")
AND ((dbo_asCodes2_4.CodeType)="BsmtArea")
AND ((dbo_asCodes2_5.CodeType)="BsmtFin")
AND ((dbo_asCodes2_6.CodeType)="Baths")
AND ((dbo_asCodes2_7.CodeType)="BltInAdj")
AND ((dbo_asCodes2_8.CodeType)="NoFrpls")
AND ((dbo_asCodes2_9.CodeType)="GarageType")
AND ((dbo_asCodes2_10.CodeType)="NoStalls")
AND ((dbo_asCodes2_11.CodeType)="asessorsmapzoneres")
AND ((dbo_asCodes2_12.CodeType)="AirCond")
AND ((dbo_asCodes2_13.CodeType)="PorchDeck")
AND ((dbo_asCodes2_14.CodeType)="Extras"))
ORDER BY dbo_asSlsImprRes.SalePriceAdjusted DESC;