Table Performance

[thechazm]

It's ok. I know its complicated and I am sorry if I am not spelling it out well enough. I know it has something to do with the tables indexing/how many fields of data it has versus how many records there are.

There is no searches on the website its a direct query where I pass the badge number in the https string. This then loads the website with the users quals/courses.

Then the database (my database) has to look at each entry on the website then evaluate the entry. The slow down is when it tries to find the personnel id and the course number together if its outside of 80,000 records. I don't know why 80,000 is different but once the record numbers above that are tried to be found it slows to about 3-4 seconds per search. Before 80,000 records it finds them in .09 seconds.

I think mostly I just need to normalize the table better and do searches on just numbers and not text. Maybe that will help... I dunno.

 

[jzwp22]

Using numeric fields is generally more efficient within a relational database, and I would think that would translate to what you are doing but it is beyond with what I am familiar.

 

[thechazm]

Yea this project started out as just a little tool and has developed into a giant project based on demand. It has around 19,000 lines of code now and just keeps getting bigger. Thats why I want to switch over to c# as I could make some of this multithreaded and use a sql server as well. It would be way more efficient to use stored procedures and linq to sql code.

Thank you everyone for your thoughts/input on this. I think I know what I need to do.

 

[jzwp22]

Sorry that I could not be of more help. Good luck with the project.

 

[chrisguk]

Yea this project started out as just a little tool and has developed into a giant project based on demand. It has around 19,000 lines of code now and just keeps getting bigger. Thats why I want to switch over to c# as I could make some of this multithreaded and use a sql server as well. It would be way more efficient to use stored procedures and linq to sql code.

Thank you everyone for your thoughts/input on this. I think I know what I need to do.

No need to go as deep as C#, try out .Net with Visual basic Express. Its far easier and a easily deployable application platform. You will see great rewards from investing your time in it. Also if you are thinking of using SQL Server then .Net apps are perfect. You can deploy the .exe to all your users and they can directly tap into your database remotely with ease.

Access is great for standalone machines or even a tight closed network. But if it starts to expand then its time to move on...... although I still develop access stuff because its easy, I love .Net for the flexibility.

If you want advice on that or further help with your project then drop me a line and I will be happy to help

 

[The_Doc_Man]

You know, I read through that thread and didn't see any of the normal suggestions.

If there is a way to compact/repair your database regularly, that will help.

If there is a way to split that database to pull excessive text away from it, then any search not involving those fields could go faster.

Sometimes I make a temporary table, perhaps only one column, as a list of the PKs of records matched by a query that was very selective. Then when I wanted to process something, I could join the temporary table to the original table with a query that vastly limited my subsequent operations. Whenever you say "Access" + "network" you are asking for slowdowns and problems. You do yourself a favor by squeezing the query down to minimalist searches followed by separate processing based on the matches you've found. You will not be able to avoid database bloat, but if you copy the front-end down to your local machine with each use, that essentially resets the bloat. And trust me, bloat is NOT your friend.

If the FE bloats, it is easily fixed by making a new copy. If the BE bloats, you have to take the DB down so you can do the needed maintenance. And for a shared database, particularly if there are insertions and deletions in the BE file, you WILL need to do some serious maintenance. It WILL reach out and bite you.

 

[thechazm]

No need to go as deep as C#, try out .Net with Visual basic Express. Its far easier and a easily deployable application platform.

You know I used to program in vb and qb for a long time and I did love it then. It's funny though I have not tried to do vb in .net. I think I'll take a look at it but I am pretty confortable with c#. Thanks for the backup

Sometimes I make a temporary table, perhaps only one column, as a list of the PKs of records matched by a query that was very selective. Then when I wanted to process something, I could join the temporary table to the original table with a query that vastly limited my subsequent operations.

Not a bad Idea actually. I will definatly consider this.

One a crazy side note I took all the indexes off that table and my backend db size went from 52Megs to 28 .

So I started putting back in the indexes watching the file size and with indexes on numbers it didn't even make a dent but when I put the index back on Course Number which is a text field it went right back up to 48 Megs! Holy cow!

Its pretty crazy that it would use that much space for indexing for one text field with 187,000 records.

I think I may try to split that but its gonna take some magic with the way I have to pull the data. Gonna have to open up yet another recordset and use as a reference while I am pulling in the text from the website... Dang gonna suck but I bet thats whats going on.

Thanks Everyone,

TheChazm

 

[chrisguk]

One last thing TheChazm before I depart this thread.

If you are going to stick with Access FE and BE you may want to consider the following:

1. Make a persistent connection between the front end and back end. This can be achieved by doing the following:

Make a table called: tblPersistent

Fields: pkPersistent - Autonumber Primary Key

Make a form in your front end called frmPersistent and use your tblPersistent as the source data. Just drop that one field in it, save and close.

I assume you have an autoexec macro, if you do or dont add the following:

OpenForm = frmPersistent
Open as Hidden

This form will always stay open in the background keeping a connection between the FE and BE.

2. Tables in access by default make sub datasheets, this slows performance. Goto your table and put in design view. The in the properties box change the SubdatasheetName to [None].

Hopefully you will see some performance improvements after that.