GianniP
asked on
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.OL EDB.4.0;Da ta Source=" + ConfigurationSettings.AppS ettings["A ccess"] + ";Persist Security Info=False";
and the appSettings Key is <add key="Access" value="D:\Sites\SiteName\d b.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.
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.OL
and the appSettings Key is <add key="Access" value="D:\Sites\SiteName\d
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.
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?
Can you post the URL ?
mx
mx
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
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
ASKER
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.
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.
ASKER
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
Unfortunately the site is still local and I can't post a URL
ASKER
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 ?
I can't see anywhere if he is buffering or not..would that be a good starting point ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> 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.
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.
ASKER
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%
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%
ASKER
.......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 .........
do I create a page and then paste the code in from Reflector ? sorry a bout this .........
ASKER
seems like the problmeiwith all the code and am getting another developer to look into it.........
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??
ASKER
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.
I don't care about the points. If you believe that the above is the case, fine by me.
Well, the solution is the same, regardless if you do it yourself or let someone else do it... :)
ASKER
Just for the record. It was not the use of Refelctor that solved this issue.
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.