Unruly query: multiple iterations of same table, poor performance (1 Viewer)

PSJupiter2

New member
Local time
Today, 02:02
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

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;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:02
Joined
Oct 29, 2018
Messages
21,358
Hi.Welcome to the forum. One option to increase query performance is making sure the tables involved have the proper indexing set. Since this may not be possible for you, you will have to resort to other methods. For example, rather than use a query to identify the descriptive data, have you tried creating a separate table for them and just join your linked table to it?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:02
Joined
Feb 28, 2001
Messages
27,001
It is not clear to me why you need several "iterations" of a table for your descriptive data. I acknowledge that you might need a second reference to a table when you are dealing with self-referential tables. But 14 seems a bit excessive and this doesn't appear to be self-referential anyway.

Performance will get bad as you approach Access limits. See also

http://www.avantixlearning.ca/micro...ns-microsoft-access-training-course-resource/

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.

It isn't 14 instances of the table eating your lunch, I think (though that is a contributor), but rather having 21 total joins in a single query. Supposedly, Access tops out at 16 JOINs in a single query. I'm surprised it didn't just "blow you a raspberry" when you tried that. :p
 

rpeare

Registered User.
Local time
Today, 00:02
Joined
Sep 15, 2016
Messages
18
Are you allowed to create your own custom functions? you could create one to lookup the value you want rather than relinking the same table multiple times. You may also be able to solve this by creating temp tables, for instance creating a pivot table where the link criteria is the primary key/foreign key field, inserting that result into a temp table then linking the temp table to your 'production' query.
 

isladogs

MVP / VIP
Local time
Today, 07:02
Joined
Jan 14, 2017
Messages
18,186
I have just deleted the duplicate thread and moved a post to this thread
 

PSJupiter2

New member
Local time
Today, 02:02
Joined
May 31, 2019
Messages
3
rather than use a query to identify the descriptive data, have you tried creating a separate table for them and just join your linked table to it?

Thank you, this solved my immediate problem. I ended up doing just that and adding primary keys to my local tables. I was concerned about the performance implications of "mixing" network and local tables. But it doesn't seem to matter in this case.

On pitfall here, however, is that if the main table changes in any way, my query will be broken. But we will cross that bridge when we get to it. Ideally, I would be able to not worry about that.
 
Last edited:

PSJupiter2

New member
Local time
Today, 02:02
Joined
May 31, 2019
Messages
3
It is not clear to me why you need several "iterations" of a table for your descriptive data.

Sorry, as I said, I am new to this forum. My original post included attachments, which showed an example of my data. I am not sure where they went! I did add them again.

I do not want several iterations of my table. But I don't know how else to join my tables without doing this. I am open to another way, assuming I have the permissions to accomplish it.

Also, I think you are on to something with the join limits. I am not sure what happens when you "go over" 16 joins. But its possible that before adding more tables, I had under that number.
 

Attachments

  • dbo_asCodes2.jpg
    dbo_asCodes2.jpg
    53.9 KB · Views: 111
  • asSlsImprRes.jpg
    asSlsImprRes.jpg
    15.7 KB · Views: 112

theDBguy

I’m here to help
Staff member
Local time
Today, 00:02
Joined
Oct 29, 2018
Messages
21,358
Thank you, this solved my immediate problem. I ended up doing just that and adding primary keys to my local tables. I was concerned about the performance implications of "mixing" network and local tables. But it doesn't seem to matter in this case.

On pitfall here, however, is that if the main table changes in any way, my query will be broken. But we will cross that bridge when we get to it. Ideally, I would be able to not worry about that.
Hi. Glad to hear you managed to sort it out. One advantage of using a table approach is when the "rules" change, all you have to do is update the data in the table rather than modify the design of queries, which I think is much easier to do. Good luck with your project.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:02
Joined
Feb 28, 2001
Messages
27,001
I will tell you now that part of the problem is putting too many codes in a single table and then trying to use them all at once. You showed us data suggesting at least three potential tables smashed into one table in the dbo_asCodes image. Having a few small translation tables might be preferable to one humongous (and therefore unwieldy) table. But there is another possibility.

I can't see the details of your actual table with the ugly name, but it appears that the raw text values are in the table and the encoded values are what you seek in your query. I'm not trying to be rude but I will be a bit brief which could be taken as rudeness. Please take it into account that I want to give you a suggestion that might be better and, while more tedious to set up, might be more efficient in the long run.

You have this in the query you showed us: FROM [80319 through 80415] - which is a strange name for a table. It makes me think that you imported this data from another source and now have to try to do something with it. The impression I get is that this ungainly table is a text-only source and you are trying to get code numbers for some of the fields in it. BUT... there is another way to approach this.

Import your raw data as needed to that table. Or if it is a spreadsheet, map it as a temporary external table. BUT... don't plan on keeping the big-ugly table with all the literal names. Design a table with a shorter name and instead of having text fields for your code-translated descriptive stuff, have only numeric fields. Populate the final table with whatever is the PK of the "big ugly" table. From what you showed us, I'm guessing "ParcelNo" would be the key? (Not important to me, but important that such a key exists.)

Then, in a SERIES of short queries, update one code field at a time in a JOIN between the final table as the destination and the "big ugly" table JOINED to a code translation table. Heck, you could even use the code table you already have because this time the number of fields to join will be limited.

Code:
UPDATE ( BigUgly INNER JOIN CodeTable ON BigUgly.T_NoOfBaths = CodeTable.CodeDescriptor ) INNER JOIN FinalTable on FinalTable.PrimeKey = BigUgly.PrimeKey
SET FinalTable.NoOfBaths = CodeTable.CodeId ;

You might have to play with the parentheses but that should work OK because those are all INNER JOIN cases.

Sweep through the list of things to be translated. Populate the final table with anything to be taken literally, and I'm guessing that some of these items in the next block fall into that category of "things taken literally".

Code:
	        dbo_asParcelAddresses.Address, 
		dbo_asSlsImprRes.SaleDate, 
		dbo_asSlsImprRes.GrantorNm, 
		dbo_asSlsImprRes.GranteeNm, 
		dbo_asSlsImprRes.SalePriceContract, 
		dbo_asSlsImprRes.Specials, 
		dbo_asSlsImprRes.SalePriceAdjusted,

As to speed? That becomes a three-way JOIN, which as you have noted, should not be a problem since you had a 10-way that you said had decent performance.

Now, if this is something you do a lot, build those queries and gang them together using a macro to sequentially execute them. Then always use the same name for the import of the temporary table and the final table.

Last but not least, if you must keep every import sequence separate, then have the last part of the macro execute a little bit of VBA to rename the final table to its "truly final" name. This is an example of "divide and conquer" strategy.

Oh... if you need to build reports or show this kind of stuff in forms, it is possible to have code translator expressions as the source in the text boxes on said forms/reports. AND doing it this way saves space. The odds are that you will never have more than 65,535 codes that could apply to one of your CodeType cases, so you can use a WORD (16-bit, a.k.a. INTEGER) field for each code.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:02
Joined
Feb 19, 2002
Messages
42,981
I haven't read all the threads so let me apologize in advance if I step on anyone's toes. I use a code table in all my apps and that table contains dozens of tables. I don't think I have a case where I need 14 joins but I think I understand how my method is different from yours.
In the codes table, each individual value has a unique ID. The row has a foreign key that identifies the "table" the row belongs to and there is a unique index on the foreign key + the descriptive value to prevent duplicates. You seem to have a two-part PK where you include tableNum + sequence within that table.

Because each value has a unique ID, I only need to store that unique ID. I don't need to also store the tableid also.

Then when the user wants to use the type of selection you are doing, the combos produce a unique ID and so to find a requested type of record, I don't need to actually join to the table and look for "bath", the criteria looks for 1345 which is the unique ID for "bath"

An example from one of my apps has Clients which have a bunch of attributes such as Gender, PrimaryLanguage, Ethnicity, MarriageStatus, etc. Each distinct value ends up with a unique ID in the code table so 45 might be Female, 877 might be English, 412 might be Caucasian, and 92 might be Married. So the user picks the values from 1 or more combos and comes up with the actual values stored in the client record. That allows the criteria to always be applied to the client record rather than all the child tables which is what you are doing by selecting based on text values.

Does that make sense?
 

Users who are viewing this thread

Top Bottom