Performance Issue

[crich21]

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.

It is a single access backend file located on our server. I am not using sql server.
I went thru all tables and it appears I was missing a few indexes. I will be able to make a blank database on Monday and post it. Lots of good advice here. I’m self taught so when I learn knew things I try and go thru the entire database and implement. Always trying to improve.
If i create local indexed tables and just empty them and repopulate them, is that a better practice than creating temp tables on the fly?

 

[Isaac]

If i create local indexed tables and just empty them and repopulate them, is that a better practice than creating temp tables on the fly?

I think no one can tell you for sure, testing is always the best measure of the performance of indexes. Test your process both ways.

The thing about indexes is they are a double-edged sword. They will of course often slow down the inserting, updating, and deleting of data but they will often speed up the selection of data. I know this sounds like a non-answer but you probably need to test it both ways in some particular process to find out which is faster. If you're only going to insert or delete once in awhile but you're going to do a lot of selecting then the index is probably likely to be a better idea. If you're going to constantly be doing inserts updates and deletes and only occasionally do selection, then the indexes might cause you more trouble than not

 

[MadPiet]

If they're SQL Server and you have not added any indexes, then performance can be utterly brutal. I had a table once that had maybe 160K records and performance was terrible. So which is it? SQL Server or Access? If the table comes in with a prefix (usually dbo.) then that's SQL Server (it's the schema that the table is part of)

 

[Isaac]

If they're SQL Server and you have not added any indexes, then performance can be utterly brutal. I had a table once that had maybe 160K records and performance was terrible. So which is it? SQL Server or Access? If the table comes in with a prefix (usually dbo.) then that's SQL Server (it's the schema that the table is part of)

Yeah - that Ctrl+L and paying attention to Suggested Indexes is pretty darn useful in SSMS, and can be used by any beginner to get a general idea and begin creating some

 

[Gasman]

But this is just an Access BE and not anything else?

It is a single access backend file located on our server. I am not using sql server.

 

[Pat Hartman]

If i create local indexed tables and just empty them and repopulate them, is that a better practice than creating temp tables on the fly?

Both are bad and lead to bloat. The best way to get around this issue if you must create temp tables is to create a totally separate template BE. Define the tables. Add the indexes and save as TempTables_Template.accdb. Put the db in a separate folder and zip it incase you have an accident. Then copy the template to your local FE folder and remove the _Template. Then link to it.

Now. write a procedure to copy the _Template version into your local folder and rename it on the way so you overlay the previous copy. Since your db name and tables haven't changed, the links haven't broken. No run the append queries to copy the summarized data.

This allows you to press a button and make the magic happen to replace the previous version of the temp tables with new ones that will never bloat.

 

[The_Doc_Man]

If i create local indexed tables and just empty them and repopulate them, is that a better practice than creating temp tables on the fly?

No. The better practice is to create the non-indexed table and then after it is populated, index it.

Creating the empty table indexed means that every time you add one record, you have to sort the index from scratch to add that one record. This will lead to repetitiously sorting the same index as many times as you have records to add.

If you create the table unindexed, you just add records to (we think) the back of the table linearly. If you then index it once it is full, even though that single act of indexing will take a while because it is a big lump, overall you will do less sorting. That is because when you were adding to a non-indexed table, you didn't have to sort anything. Fewer "visits" to the table are involved when doing wholesale rather than piecemeal index creation.

 

[Pat Hartman]

I've never had trouble with indexing the empty table in the side-BE but you are correct. If there is a lot of data, omitting the index but creating it later is more efficient. You can then use DAO from the main db to add the index to the table in the linked BE.