Performance Issue

[crich21]

I have a form with that has 3 listboxes on it. Each listbox displays a list of items that are missing on an order.
lstBox1 displays missing items that are standard on a particular model
lstBox2 displays missing items that are standard on a particular conversion
lstBox3 displays missing items that are standard in an options package

The form takes anywhere from 10-30 seconds to load.
I have already tried creating temp tables and basing the following query off of that.
Seems to have helped a small bit. Is there a faster/better way to do this?
Here is 1 of 3 queries that I would like to speed up.

SELECT tblOrderDetailsPackageItemsMissingTemp.OrderID, tblOrderDetailsPackageItemsMissingTemp.OptionID, tblOrderDetailsPackageItemsMissingTemp.Qty, tblOptions.Options, tblOrderDetailsPackageItemsMissingTemp.ModDescription, tblOrderDetailsPackageItemsMissingTemp.Comments, tblOrderDetailsPackageItemsMissingTemp.PackageID, tblOrderDetails.PackageID, tblOrderDetails.OptionID, tblOrderDetailsPackageItemsMissingTemp.UpgradeDescription
FROM (tblOrderDetailsPackageItemsMissingTemp LEFT JOIN tblOrderDetails ON (tblOrderDetailsPackageItemsMissingTemp.PackageID = tblOrderDetails.PackageID) AND (tblOrderDetailsPackageItemsMissingTemp.OrderID = tblOrderDetails.OrderID) AND (tblOrderDetailsPackageItemsMissingTemp.OptionID = tblOrderDetails.OptionID)) INNER JOIN tblOptions ON tblOrderDetailsPackageItemsMissingTemp.OptionID = tblOptions.OptionID
GROUP BY tblOrderDetailsPackageItemsMissingTemp.OrderID, tblOrderDetailsPackageItemsMissingTemp.OptionID, tblOrderDetailsPackageItemsMissingTemp.Qty, tblOptions.Options, tblOrderDetailsPackageItemsMissingTemp.ModDescription, tblOrderDetailsPackageItemsMissingTemp.Comments, tblOrderDetailsPackageItemsMissingTemp.PackageID, tblOrderDetails.PackageID, tblOrderDetails.OptionID, tblOrderDetailsPackageItemsMissingTemp.UpgradeDescription
HAVING (((tblOrderDetails.PackageID) Is Null Or (tblOrderDetails.PackageID)=0));

 

[theDBguy]

Are you using linked tables to SQL Server? Just curious...

 

[plog]

Why is this an aggregate query without any aggregate functions? GROUP BY is most often used for totals (SUM, COUNT, MAX, etc.) but you use none of those functions in the SELECT. The other reason is to use GROUP BY is to eliminate duplicates. Are there duplicates when you don't GROUP BY? If so, why are there duplicates?

Are all those tables necessary? Does that temp table have indexes on it? Can you post a sample database so we can see exactly the structure and all the parts going into this query?

 

[crich21]

Are you using linked tables to SQL Server? Just curious...

I am using a split access database where the backend resides on our server. I have purchased sql server. However, I have tried to convert from access to sql and i run into several errors. It's beyond my knowledge.

 

[The_Doc_Man]

I am using a split access database where the backend resides on our server.

If by that, you mean that the backend is a .MDB or .ACCDB file, then the most likely culprit is that you need indexes on the fields participating in the JOIN and/or GROUP BY. If the backend is on an SQL engine like SQL Server, MySQL, or something else like that, my colleagues will have to jump in since I only know general issues with SQL Server.

 

[Gasman]

I was thinking that, but all the joins appear to be ID fields, which are indexed automatically, are they not?

 

[plog]

No, not if you are using a MAKE TABLE query to create a temporary table and just passing a field named 'ID' through to it.

 

[Mark_]

Please note, HAVING is normally used with aggregate functions. I don't see an aggregate function so this should be part of a where clause.

 

[Gasman]

I do not suppose that almost every field is in the Group By is going to help?
Could you perhaps use Distinct?

 

[crich21]

Why is this an aggregate query without any aggregate functions? GROUP BY is most often used for totals (SUM, COUNT, MAX, etc.) but you use none of those functions in the SELECT. The other reason is to use GROUP BY is to eliminate duplicates. Are there duplicates when you don't GROUP BY? If so, why are there duplicates?

Are all those tables necessary? Does that temp table have indexes on it? Can you post a sample database so we can see exactly the structure and all the parts going into this query?

I have stripped everything I can and this site says its too large to post. Am I allowed to post a link to my dropbox for the file? It's complex and I will say I have achieved alot of what this db has become with the help of this forum. I don't fully understand sql so I use the access query designer. You will need to open the 'frmOrders' select the only quote listed in the drop down. Then you will be able to click 'standards'. This opens the form in question.

once I relinked the tables to my local machine, I noticed it opens in just a few seconds.

 

[crich21]

I have not learned enough to fully comprehend every ones replies. I will look up the different things mentioned here and try and understand.
I am not sure why the query turned out the way it did. I kept making changes until I got the desired results.
Does the temp table have indexes on it? unsure
Group by? No idea why the TOTALS was selected when I made the query - removing this still produces the desired result
It appears the removing the TOTALS also removed the HAVING from the sql.
Here is what access query designer changed the sql to now.

SELECT tblOrderDetailsPackageItemsMissingTemp.OrderID, tblOrderDetailsPackageItemsMissingTemp.OptionID, tblOrderDetailsPackageItemsMissingTemp.Qty, tblOptions.Options, tblOrderDetailsPackageItemsMissingTemp.ModDescription, tblOrderDetailsPackageItemsMissingTemp.Comments, tblOrderDetailsPackageItemsMissingTemp.PackageID, tblOrderDetails.PackageID, tblOrderDetails.OptionID, tblOrderDetailsPackageItemsMissingTemp.UpgradeDescription
FROM (tblOrderDetailsPackageItemsMissingTemp LEFT JOIN tblOrderDetails ON (tblOrderDetailsPackageItemsMissingTemp.PackageID = tblOrderDetails.PackageID) AND (tblOrderDetailsPackageItemsMissingTemp.OrderID = tblOrderDetails.OrderID) AND (tblOrderDetailsPackageItemsMissingTemp.OptionID = tblOrderDetails.OptionID)) INNER JOIN tblOptions ON tblOrderDetailsPackageItemsMissingTemp.OptionID = tblOptions.OptionID
WHERE (((tblOrderDetails.PackageID) Is Null Or (tblOrderDetails.PackageID)=0));

 

[Gasman]

I have stripped everything I can and this site says its too large to post. Am I allowed to post a link to my dropbox for the file? It's complex and I will say I have achieved alot of what this db has become with the help of this forum. I don't fully understand sql so I use the access query designer. You will need to open the 'frmOrders' select the only quote listed in the drop down. Then you will be able to click 'standards'. This opens the form in question.

once I relinked the tables to my local machine, I noticed it opens in just a few seconds.

Try compacting. Then zipping and see if that is small enough to upload.

 

[MadPiet]

I'm not sure if Access sneakily adds indexes to columns ending in "ID" etc, but I know for sure that SQL Server does absolutely no indexing on its own. If you want an index somewhere, you have to create one. The one thing your post is missing that would be really helpful is the CREATE TABLE scripts (and any indexes) on the tables in SQL Server. If you open up SSMS and you open Object Explorer (usually in the pane on the left...), and then click the + next to the tables folder. then down to the tables that are in your query. Open the Keys folder. (You can name them in SQL Server (good idea) and see where your indexes are.) If there are no indexes, performance is going to be pretty ugly, especially when your tables are of non-trivial size.

 

[The_Doc_Man]

I have stripped everything I can and this site says its too large to post. Am I allowed to post a link to my dropbox for the file? It's complex and I will say I have achieved alot of what this db has become with the help of this forum. I don't fully understand sql so I use the access query designer. You will need to open the 'frmOrders' select the only quote listed in the drop down. Then you will be able to click 'standards'. This opens the form in question.

once I relinked the tables to my local machine, I noticed it opens in just a few seconds.

The "Relinking to the local machine" improvement suggests that there might be a permissions issue or a network issue - OR that you need to open a persistent connection before you open that query or anything using it.

Just to confirm, because your previous answers imply this, but... when you say the BE is on the file server... is that a single back-end file or do you have multiple files?

Just out of curiosity, why did you choose to use GROUP BY instead of ORDER BY, and why did you choose HAVING rather than WHERE for your SQL? Since you don't have any SQL Aggregate functions in the SELECT clause, you should be able to successfully use ORDER BY and WHERE clauses, but to be honest, I don't know if that would result in a speed difference vs GROUP BY and HAVING usage. Using indexes on every field in a JOIN clause, GROUP BY (or ORDER BY), and HAVING (or WHERE) will make things go a lot faster. A persistent connection might also help since the local/remote speeds ARE radically different.

 

[crich21]

After compacting and zipping the file is 11mb and it says it’s too big. Yes I am using a make temp table query. So if it doesn’t automatically index then I need to figure out how to add indexes. So I can retest it.

 

[crich21]

The "Relinking to the local machine" improvement suggests that there might be a permissions issue or a network issue - OR that you need to open a persistent connection before you open that query or anything using it.

Just to confirm, because your previous answers imply this, but... when you say the BE is on the file server... is that a single back-end file or do you have multiple files?

Just out of curiosity, why did you choose to use GROUP BY instead of ORDER BY, and why did you choose HAVING rather than WHERE for your SQL? Since you don't have any SQL Aggregate functions in the SELECT clause, you should be able to successfully use ORDER BY and WHERE clauses, but to be honest, I don't know if that would result in a speed difference vs GROUP BY and HAVING usage. Using indexes on every field in a JOIN clause, GROUP BY (or ORDER BY), and HAVING (or WHERE) will make things go a lot faster. A persistent connection might also help since the local/remote speeds ARE radically different.

I did not write the sql. Access query design did that for me. I was able to remove all the group by and having from the sql and it still works as expected. I only have 1 backend file and it is just in a directory on our file server wihich is running windows server 2019. Not sure if there’s anything I can do on the server to make my backend a priority? Sounds like indexing may be some of my issue. I’m reading on how to do that with a ‘make table’ query now. If it’s not possible then I can create local tables with indexes and then just clear the tables and insert new data when needed?

 

[MadPiet]

What if you create a new blank database and import just the 3 tables and the minimum number of queries and forms for us to see the problem? Surely we don't need the entire database.

 

[Gasman]

I'm not sure if Access sneakily adds indexes to columns ending in "ID" etc, but I know for sure that SQL Server does absolutely no indexing on its own. If you want an index somewhere, you have to create one. The one thing your post is missing that would be really helpful is the CREATE TABLE scripts (and any indexes) on the tables in SQL Server. If you open up SSMS and you open Object Explorer (usually in the pane on the left...), and then click the + next to the tables folder. then down to the tables that are in your query. Open the Keys folder. (You can name them in SQL Server (good idea) and see where your indexes are.) If there are no indexes, performance is going to be pretty ugly, especially when your tables are of non-trivial size.

Well I meant the autonumber field, regardless of what it is called. I have always left mine as xxxID.

 

[The_Doc_Man]

You can execute DDL (Data Definition Language) via the SQL interface. If you make the table (populated or not), the next step is described in this article:

DDL - CREATE INDEX

 

[Isaac]

I am using a split access database where the backend resides on our server. I have purchased sql server. However, I have tried to convert from access to sql and i run into several errors. It's beyond my knowledge.

I've read through this entire thread , and I still can't quite tell for sure if your back end tables are SQL server or access. You kind of need to know that before you proceed.

If they are access I would recommend creating a database to upload with just enough data and objects as has been suggested to you already. If they are SQL server then my instructions to get more help would be quite different.