Access query going haywire...

Y

yorri

Perch
I have an access query which pulls data from one table from an asp page. There are about 2200 records in this table. When that page executes on my local server, the cpu usage shoots up to 100% for the few seconds it needs to run.

When I run the EXACT same sql statement through the access database application on my computer, it makes my cpu usage only just a few percent and returns 10 times faster.

I am using oledb connection (not odbc).

I find this an odd thing. Any suggestions or is this what happens when you pull that many records from access through an asp page? The database isn't that large and it is only text. I just don't get it.
 
yorri said:
I have an access query which pulls data from one table from an asp page. There are about 2200 records in this table. When that page executes on my local server, the cpu usage shoots up to 100% for the few seconds it needs to run.

When I run the EXACT same sql statement through the access database application on my computer, it makes my cpu usage only just a few percent and returns 10 times faster.

I am using oledb connection (not odbc).

I find this an odd thing. Any suggestions or is this what happens when you pull that many records from access through an asp page? The database isn't that large and it is only text. I just don't get it.
Click to expand...

The only thing that comes to mind is that when you are using the Access program, the connection is established and the database is open. When you run the query, that's all that happens. Obviously there is more overhead involved when doing this from an ASP page (establishing the connection, etc.), but it's hard to believe that this could account for the huge discrepancy you describe. Perhaps the Access program does some sort of "compilation" or "tokenizing" of the query once you enter it, thereby saving the time required to do the interpretation of you sql statement. Your request from the ASP page would not have such an advantage. Just some thoughts...

riley
 
Thanks riley,

I've tried a variety of techniques. I changed the buffering, change the items in the query to make it return less records etc but nothing seems to make a difference. I do expecta difference in speed between the access application and asp page but I don't expect it to cause the cpu to shoot to 100%.

I wonder if this is the sort of thing that was causing the cpu spikes on win1 with some people's access dbs?? Other than those scripts that had endless loops etc.

I am lucky that I don't need to run this specific query (which is just a select) from the webserver but it definitely would save having to create an offline solution and it certainly worries me that there may be other queries doing this to us people who use access for some projects that don't require MSSQL.
 
Oh and just to add...when I am using the same script but only returning about 100 records it seems to be fine.

Someone suggested if I am sorting that might be a cause for extra processing power but there isn't any sorting...just a simple select.
 
are you writing all the data to the output too? or only selecting?


this is a really big problem on shared servers. Access databases with more than a few records and bigger than 5-6 MB should be avoided..
 
The Access user interface might be doing some query optimization, though it sounds somewhat unlikely. Depending on the complexity of your query, you might be able to swap around conditions to make it execute faster. In MySQL I've gotten a significant performance increase like this on occasion. Of course that particular database had 600,000 records in a single table :p
 
DirtBag said:
are you writing all the data to the output too? or only selecting?


this is a really big problem on shared servers. Access databases with more than a few records and bigger than 5-6 MB should be avoided..
Click to expand...

I am testing this on my home server so this is not shared hosting, this is less than a 2 MB database. This database is working perfectly fine in a shared environment although I am not using this particular query from any of those web pages. I am actually using many more complex queries (with Joins and sorting etc) from web pages from this database without problem.
 
yorri said:
I am testing this on my home server so this is not shared hosting, this is less than a 2 MB database. This database is working perfectly fine in a shared environment although I am not using this particular query from any of those web pages. I am actually using many more complex queries (with Joins and sorting etc) from web pages from this database without problem.
Click to expand...

Yorri,
Post the query. Perhaps someone here can see something.

riley
 
riley said:
Yorri,
Post the query. Perhaps someone here can see something.

riley
Click to expand...

Select name from tblProducts

The original query returns a couple more fields but I tried this one too with the same results.

it is a very simple query
 
yorri said:
Select name from tblProducts

The original query returns a couple more fields but I tried this one too with the same results.

it is a very simple query
Click to expand...

Well, it doesn't get much simpler than that. It isn't as if there is a non-indexed field in a WHERE clause or any other such thing.

Have you tried doing a "compact and repair database" from the tools menu? I'm not sure that would help much (if at all), but it's worth a try.

riley
 
riley said:
Well, it doesn't get much simpler than that. It isn't as if there is a non-indexed field in a WHERE clause or any other such thing.

Have you tried doing a "compact and repair database" from the tools menu? I'm not sure that would help much (if at all), but it's worth a try.

riley
Click to expand...

I think I have done that because I was playing with the database and deleting data but maybe not since I have been running these tests.

I will try again and run my test again.

Thanks for the suggestion.
 
WebDeveloper said:
Post the entire .asp page...
Click to expand...

That suggestion raises an interesting thought: perhaps the slowness is in the handling the resultant recordset and not the retrieval of it.

Display a time stamp before you request the data and after you get the data, and see how long the actual data access is taking. Or, comment out the code the processes the recordset and simply display something like "Got it." Then see if the process seems much faster (not as systematic, but still effective).

riley
 
riley said:
That suggestion raises an interesting thought: perhaps the slowness is in the handling the resultant recordset and not the retrieval of it.

Display a time stamp before you request the data and after you get the data, and see how long the actual data access is taking. Or, comment out the code the processes the recordset and simply display something like "Got it." Then see if the process seems much faster (not as systematic, but still effective).

riley
Click to expand...

I just found something interesting.

I imported the exact same table (2000 records) into a sql server database and my cpu only jumped to 16% so as you are suggesting riley, it IS the handling of query rather than the retrieval. I also changed the access method to getrows which sped up the retrieval but still was 100% usage. However the data was shown in the browser much more quickly.

Anyway, it seems that when returning that many rows in access causes a major problem and that is one place where you question the upgrade to a more robust database engine.

What I find interesting is that I have a search on this website that accesses this database and it can "return" all the records but since I am using paging, in reality I am only showing 40 records at a time so I think I am sheltered from the slowdown of returning the data from all the records at once but I have yet to test to see if this is true. I really want to test this out and learn where the limitations of Access are especially since my client is talking about adding 5000 more records this weekend. Overall I am happy with the access database (no corruption, fast speed) with the exception of this one particular query which seems to make access (or rather the oledb driver because there is no spike when executing the query within the program itself) work like crazy.

This has been a good learning experience that is for sure.

It would be interesting to find out how many records it takes to cause the cpu to jump to unreasonable levels. 200,500,1000??? Maybe I should test that out and let you guys know. Definitely worth knowing when designing access solutions for shared hosting.

Ok back to work for me.
 
WebDeveloper said:
You should really consider reading the information on this page regarding Access limitations (so should anyone else currently using Access for handling their website data): http://www.aspfaq.com/show.asp?id=2195
Click to expand...

It's an interessting article.

First off, don't believe everything you read. Just because it says so on the Internet doesn't make it so (my Mommy taught me that, except she referred to the newspaper and TV not the internet, but I digress...)

I do agree that if I was operating a large mission-critical application, I would never, read NEVER use MS Access. The important point to come away from this article, and the point in general, is that MS Access is not a flawed solution, it is a limited one, and it is impossible to tell where the limit is since it greatly varies depending on each installation.

Moving on...
 
WebDeveloper said:
Riley,

You should really consider reading the information on this page regarding Access limitations (so should anyone else currently using Access for handling their website data): http://www.aspfaq.com/show.asp?id=2195
Click to expand...


Yes, I've read it and have also read others like it. Furthermore, I whole heartedly agree with them and have for years. I would (and do) only use Access/Jet in applications with VERY low concurrency, using queries with VERY limited JOINS and/or WHERE clauses (i.e., referencing few tables), returning relatively small result sets from relatively small tables, where update/insert/delete activities are infrequent. This would be personal web sites or, perhaps, a slight level above, where the site is visited infrequently. I would (and do) avoid Access/Jet for e-business sites and forums.

But the actual issue here is that Yorri is reading an Access database on his local computer using 2 different methods to run a very simple query and is getting drastically different performance results. It would be interesting to know why.

riley
 
Logan said:
...MS Access is not a flawed solution, it is a limited one, and it is impossible to tell where the limit is since it greatly varies depending on each installation.
Click to expand...

Well put, Logan.

However, people should pay attention to the issue of scalability (as pointed out in the article). If you think your web site has the potential to become a little busy, build it with something other than Access. You'll save yourself the time and effort of converting it later...

riley
 
Well the issue here has nothing to do with whether one should use Access or another database engine. I don't want to get into that issue because most people who are against Access are doing it because they read somewhere that they shouldn't use it rather than from personal experience. Not every web application has thousands of concurrent users therefore requiring a high end database engine. End of debate.

The ecommerce site that I have built is running very well with over 2000 products because he is getting on average 40-50 users per day so MS Access is fine. He is marketing to a very small select group of people and using SQL Server would just add unnecessary complexities for my client.

It wasn't until he asked me to build him a query to extract ALL the 2200 (or so) product's information that I noticed a problem. At worst, the site only shows 40 products at most (default is 12 but users can change the view). This is nowhere near the limits of an Access database (if such a thing can be stated as mentioned in that article).

I am not going to upgrade it to SQL Server because it is not necessary. I am going to create a local solution for him based on this query but I still like to learn and understand when things act "weird" like with this query. I just want to make sure that I don't cause cpu spikes on shared hosting and if I know where these things occur I can properly design and develop MS Access solutions and will continue to do so.
 
yorri said:
Well the issue here has nothing to do with whether one should use Access or another database engine. I don't want to get into that issue because most people who are against Access are doing it because they read somewhere that they shouldn't use it rather than from personal experience.
Click to expand...
I use Access in a multiple number of websites, have extensive experience with it and have nothing against it personally.


yorri said:
Not every web application has thousands of concurrent users therefore requiring a high end database engine.
Click to expand...
Even Microsoft recommends against using Access for web applications.


yorri said:
End of debate.
Click to expand...
There really never was a debate, the facts are there, whether you chose to accept them is your choice.
 
You must log in or register to reply here.
Back
Top