Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-31-2019, 07:51 AM   #1
PSJupiter2
Newly Registered User
 
Join Date: May 2019
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
PSJupiter2 is on a distinguished road
Unruly query: multiple iterations of same table, poor performance

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;

PSJupiter2 is offline   Reply With Quote
Old 05-31-2019, 08:03 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Unruly query: multiple iterations of same table, poor performance

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
PSJupiter2 (05-31-2019)
Old 05-31-2019, 09:11 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,773
Thanks: 93
Thanked 1,729 Times in 1,600 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Unruly query: multiple iterations of same table, poor performance

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/micros...urse-resource/

Quote:
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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
PSJupiter2 (05-31-2019)
Old 05-31-2019, 10:07 AM   #4
rpeare
Newly Registered User
 
Join Date: Sep 2016
Posts: 18
Thanks: 0
Thanked 3 Times in 2 Posts
rpeare is on a distinguished road
Re: Unruly query: multiple iterations of same table, poor performance

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.
rpeare is offline   Reply With Quote
Old 05-31-2019, 10:20 AM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,379
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Unruly query: multiple iterations of same table, poor performance

I have just deleted the duplicate thread and moved a post to this thread
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 05-31-2019, 11:02 AM   #6
PSJupiter2
Newly Registered User
 
Join Date: May 2019
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
PSJupiter2 is on a distinguished road
Re: Unruly query: multiple iterations of same table, poor performance

Quote:
Originally Posted by theDBguy View Post
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 by PSJupiter2; 05-31-2019 at 11:11 AM. Reason: Clarification
PSJupiter2 is offline   Reply With Quote
Old 05-31-2019, 11:09 AM   #7
PSJupiter2
Newly Registered User
 
Join Date: May 2019
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
PSJupiter2 is on a distinguished road
Re: Unruly query: multiple iterations of same table, poor performance

Quote:
Originally Posted by The_Doc_Man View Post
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.
Attached Images
File Type: jpg dbo_asCodes2.jpg (53.9 KB, 9 views)
File Type: jpg asSlsImprRes.jpg (15.7 KB, 7 views)

PSJupiter2 is offline   Reply With Quote
Old 05-31-2019, 11:19 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Unruly query: multiple iterations of same table, poor performance

Quote:
Originally Posted by PSJupiter2 View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-31-2019, 12:52 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,773
Thanks: 93
Thanked 1,729 Times in 1,600 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Unruly query: multiple iterations of same table, poor performance

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-02-2019, 12:17 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Unruly query: multiple iterations of same table, poor performance

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?

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
performance , query , repeated table , simplifying code

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Poor query performance KirRoyale Queries 13 08-11-2013 02:03 AM
Very poor performance on multi-join query GanzPopp Queries 7 05-19-2013 06:04 AM
Poor Search Performance on ODBC linked to MySQL table ashwah Other Software 1 05-15-2013 03:49 AM
Poor performance mark curtis Reports 0 06-19-2001 02:02 AM




All times are GMT -8. The time now is 11:27 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World