Switch database to improve speed?

[EvilCabal]

Hey guys, I have an access database that is getting quite big and quite slow, I've done a lot to improve it speed but it's still really slow. I thought maybe keeping my access front-end and changing the back-end database for SQL server or any other database might improve it speed. Am I right? If so what happens to all the query and stuff in the original database? Will they still work?

 

[markphsd]

What parts of it are slow? Is it your queries and pulling data to the FE that is slow, or do your forms on your FE just have a lot of design features in them? Is it the front end or the back end that is large, and how large is quite big?

 

[EvilCabal]

Well everything is slow. My guest is that it's because of the network traffic because if I place the FE and the BE on the same computer it's much faster.

The FE contain maybe 100 form and 150 queries. The BE contain 100 (The biggest contains 50 000 records but most of them are below 100 records) And is 30 mb.

 

[chrissie1]

you wont have much increase in speed if you use sql-server, i think you need to rethink the desing or learn to live with it. Wath i do is the following

1) i make unbound forms
2) i make my own browse buttons
3) i have a recordnumber in the table
4) this is how i browse select * from table where recordnumber = txt_currentrecord
5) the first button will have this code in it
txt_currentrecord = 1
the next button has this txt_currentrecord = txt_currentrecord+1
do this for only for the major forms
offcourse when you start in this way you need to think of a lot more but this is just to tel you how it could be done


"What a wonderfull world" - Louis armstrong

 

[EvilCabal]

I don't use browse button at all and most of my forms, if not all, are unbound...

What do you mean by I have a recordnumber in the table

Thanx

 

[chrissie1]

yeah well but as you can see (or not) i only get one record at a time wich reduces the network traffic to nearly zero
and what i mean by a recordnumber if you want a 1 of n number of records then each record needs a following number to identify it. and you need to renumber after a delete "What a wonderfull world" - Louis armstrong

 

[EvilCabal]

Ok I understand but what do you do about forms that show a lot a record at the same time? And most of the network traffic is caused by reports and other programs that are not part of access at all...

 

[chrissie1]

the problem with forms with a lot of records on it is that you have to count the records then devide them up in portions of 20 and only show an 20 at a time instead of
txt_currentrecord = txt_currentrecord + 1 you do this
txt_currentrecord = txt_currentrecord + 20

reports and other programs you cant do much about them
"What a wonderfull world" - Louis armstrong

 

[cmmrfrds]

Can you quantify what real slow is. Is it 5 seconds 10, 15????

 

[FirstAndGoal4]

Try this. Put a dummy table in the BE (no records). Create a global recordset variable in an FE module. First thing to do when a user opens the FE is open the recordset on the dummy table and keep it open the entire session. Access does weird things in the LDB that cause slowness and this should help. That is why you don't see the problem when you run both the FE and BE on the same machine.

 

[EvilCabal]

Thanks guys, I'll try this as soon as I get to work next monday. The slowness of the DB depends on what reports and what machine. Sometimes takes 5 sec. sometimes 35... I'll give you the result next week.

 

[EvilCabal]

Hi, I tried your suggestions I got some pretty good speed increase in some parts of the database. The dummy recordset seems to help a lot. Report printing is still slow and some extra-loaded forms too, but I believe this is normal. If anyone has another good idea tell me. Thank you.