Access DB

causing timeouts

[SuzyUK]

I know I know a frequent question but I have to live with it just now :(

Situ.!

a site that's using 2 different databases one for content one for tracking...
(I figure if one goes down the others may work? Am I misguided ;))

1 database is for tracking, DSN-less connection..

It's working fine most of the time, the site traffic is not that heavy so the multi user connection is not coming into play yet (I think?)

I have the session set to a short time so it's not holding sessions unneccessarily in memory...

Now it just won't load the traffic reports, it times out even though I set it to a high period timeout.. also it's crippling the site itself even with no connections.. (well apart from the one I tried to make;))

I replaced it with a 2 day old database(small) and the problem is still occuring so it can't be "too much data" can it?

I've compacted and repaired
I've removed old data

Is there anything else can I do... apart from move to MSSQL.. which is my only other option at this time :(

Suzy

[sun818]

> apart from move to MSSQL

That's a lot of calls to the database. Some web host won't like that you use the database for tracking purposes. You could try a flat file log that appends to the end of the file.

[IanTurner]

Are you using connection.close and set conn = nothing to clean up whenever you finish db processing on a page?

Are you getting the problem with a simple select or is it only when you update or insert that the problem occurs?

[Zaphod Beeblebrox]

Also, check if the important fields you search and link on are properly indexed. I use Access too, and it really shouldn't be a problem.

[SuzyUK]

Hi the script is not mine, so reading it takes me while ;)

But thanks Ian I ran search and replace and found 2 x instances where SET was not set = nothing or conn.close at the end so I've amended that .. it seems to be working again now..

Zaphod..

properly indexed

what does this mean?

And Sun818, hmm I never though about that but really the calls are not excessive (yet) and I download that data after a month..

Also I've discovered my only other option on this host is MSSQL Database which is not cheap :(

Access has worked fine for me in other applications..

Suzy

[sun818]

Download your database to your workstation. Go to Tools > Analyze > Performance. The analysis will tell you if certain tables would perform better with an index. An index on specific fields can help queries and joins process faster if done correctly.

[SuzyUK]

>>indexing

Thanks Sun818.. I just checked the copy I was working on yesterday... no primary key or index..

There is only one table involved here so no joins..

I have now created an index on the column that was suggested by the Access tool..

I notice too there isn't a primary key is that essential, should I create one?

Suzy

<added> site is down again so after a phone call to host it appears it's their server and not all my fault which is good news... but I will sort the indexing too when it comes back online! </added>

[sun818]

If it is just one table and you're only adding to the table, don't index the field. If you index it, then whenever a new row is created - two calls have to made to database - 1) to create the record and 2) another to update the index. Indexes are good for select statements and joins.

[SuzyUK]

Thanks sun818..

>>don't index..
I discovered that when I tried it ;)

All seems OK again for now... it seems it was a horrible coincidence that the same two times I was working on the DB the Hosts server was having issues at the same time..

but PHP and MySQL books are still high on the Amazon wishlist!

Thanks All

Suzy