MS Access driven website VERY slow

Hi

Could anyone point me in the direction of a check list re: MS Access drive websites. I have a site (created by a develope who dissapeared) which is running VERY slow (even localy). It takes the site about 20-25s to load a page.........EVEN localy.

While I know that Access is not meant to be used for a website, this will be the case for a while for this particular site. It's not live yet so there's not much traffic except me testing etc.

The other issue is that the connectionstring is in the dll and as I'm quite new to all of this I'.m not sure as to how I can make changes.

Thanks for your time.

GP

p.s. the connection stirng in the dll looks like this:  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConfigurationSettings.AppSettings["Access"] + ";Persist Security Info=False";

 and the appSettings Key is  <add key="Access" value="D:\Sites\SiteName\db.mdb"/>

 Which I can't see how this would create speed issues as it seems fine.

I'm hoping ot change this at some stage to SQL but the dll issues is another stumbling block.
GianniPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Göran AnderssonCommented:
The connection string looks fine, but why not put the entire connection string in web.config? That way you can change other settings than just the file name.

Access doesn't perform very well compared to a real database server, but for a low traffic web site it's often enough. I know of a lot of web sites that run on an Access database. Response times of 20-25 s. is surely not typical.

First, compact the database. An access database grows over time as it doesn't reuse all space released when data is deleted.

Examine the queries that are run against the database. Make sure that you don't have queries in loops, for example looping a list of comments and querying the database once for each comment to get the name of the author. A list of comments should be fetched using one query, not a hundred.

Examine the conditions and sorting used in the queries, and make sure that the database has indexes for the fields that are used in the conditions to select records and the records used in sorting. For example:

select u.UserId, u.Firstname, u.LastName, c.Comment, c.WriteTime
from Comments c
inner join Users u on u.UserId = c.AuthorUserId
order by c.WriteTime desc

Here, Users.UserId and Comments.WriteTime should have indexes. Primary keys have an index automatically, but you would add one to Comments.WriteTime if there isn't one.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What is happening on the website? Are you just displaying text, or are you also storing images and such in your database and trying to retrieve them?

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Can you post the URL ?

mx
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Just an fyi ... this site is driven by an Access MDB backend ... each page with a .ASPX is reading data from the MDB ... no speed issues here:

http://www.blusd.org/  (you can skip the flash intro).  One thing to note is ... I have pre constructed queries  in the MDB that do any sorting, filtering etc .. and then I just reference those query names in the VB.Net / ASPX code.  And all field used for sorting, filtering etc., have indexes in the relevant table. And this is definitely a low traffic site.   The only R/W portion is the Bulletin Board.

mx
0
GianniPAuthor Commented:
GreehGhost:   As I mentioned, the connection string is in the dll which was created by the developer who is not around anymore otherwise I would have change asap.
The DB is 3MB and does not have much info at the moment at all.
Tables are indexed.

LMS Consulting. No I am not storing image. This is an online shop site and does have images on the fornt page but only 12 images of 150x150pixels.
0
GianniPAuthor Commented:
I've just compacted it and it akes no difference. My biggest problem is that all the queries are in the dll..........

Unfortunately the site is still local and I can't post a URL
0
GianniPAuthor Commented:
The more I look at it the more I think it's something in the code. There is an admin section and that works pretty quickly.

I can't see anywhere if he is buffering or not..would that be a good starting point ?
0
Göran AnderssonCommented:
Download .NET Reflector, open the dll, get the disassembled code in the language that you prefer, create a new project in Visual Studio and paste the code in it, compile into a new dll.

http://www.aisto.com/roeder/dotnet/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Göran AnderssonCommented:
> I can't see anywhere if he is buffering or not..would that be a good starting point ?

Buffering is on by default and there is rarley any reason to turn it off.

Turning off buffering would reduce the speed somewhat, but not as much as you experience. Also it would have minimal impact when you run the site locally.

In classic ASP, turning buffering off would make the server send a package to the browser for every <%%> block that you enter, which would drastically increase the number of packages sent. ASP.NET renders the entire page at once, so turning buffering off has much less impact in an ASP.NET site.
0
GianniPAuthor Commented:
Hi GreenGhost

I have reflector :-) and was trying to figure it out. There's a LOT of references in that dll. I suppose it's just a case of doing the hard work an copying everything over ?

I'm almost sure it's something in that front end code as the admin works 100%
0
GianniPAuthor Commented:
.......this is so frustarting. WIll teach me to find a better dev eloper next time :-)

do I create a page and then paste the code in from Reflector ? sorry a bout this .........
0
GianniPAuthor Commented:
seems like the problmeiwith all the code and am getting another developer to look into it.........
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Seems to me like GreenGhost provided the solution ... use REflector and find out what the problem is ... and that's apparently what the poster did??
0
GianniPAuthor Commented:
No, the poster contacted a ISO certified developer to sort out the mess.

I don't care about the points. If you believe that the above is the case, fine by me.
0
Göran AnderssonCommented:
Well, the solution is the same, regardless if you do it yourself or let someone else do it... :)
0
GianniPAuthor Commented:
Just for the record. It was not the use of Refelctor that solved this issue.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.