Displaying database results really slow ... can't figure out why

[dragonwoman]

Hi there,

I'm using FP 2003 and Access 2003 (I know, I know, but I'm required to
use Access). Recently I noticed some of my pages were timing out when
I was contacting the database. I tried a test and here's what I
found.

Database connection: System data source on webserver
Data: Table with two columns and about 15 rows

A. I created a new form with a text box where I enter a number. That
form calls query.asp which is nothing but a database results wizard
insert table with the query "SELECT * FROM "_Cost Center" WHERE Number
LIKE ::Number::" (Remember, * is just two columns.)

This page constantly times out. I.e., the script times out. I don't
want to increase the timeout because, in my opinion, this shouldn't
take 2 minutes!

B. I created a second ASP page that uses Perlscript to do the same
query and display the results instantaneously.


I've never had this problem with the Database Results Wizard before
and can't find anything wrong. Other than Perl, I don't do much
scripting. It's supposed to be a simple search, so the DRW is perfect
and easier for me than perlscripting all the text box entries, etc.

Does anyone have any idea what could be causing this?

Thanks for anything you can offer.
-Diana

 

[Thomas A. Rowe]

Try saving the database as a Access 2000 database, and see if you see an improvement.

I use Access 2000 (System DSN) with hand coded ASP/VBScript and have no timeout issues on all of my
projects.

--
==============================================
Thomas A. Rowe
Microsoft MVP - FrontPage

http://www.Ecom-Data.com
==============================================

 

[Jon Spivey]

A couple of things to try

1/ using select * is always bad - much better to list out your columns
select column1, column2 etc
2/ Against a number field you shouldn't use like - either a number is equal
or it isnt. Also look at putting an index on the field you're searching
against
3/ a system dsn isn't the best way to connect to a database.
4/ The FP scripts can never be as good as hand coding - maybe it's time to
try some hand code? With asp you could do a database lookup in half a dozen
lines of code

Cheers,
Jon

 

[Tom Miller]

I've never had this problem with the Database Results Wizard before
and can't find anything wrong.
-Diana

I like everyone elses answers but you didn't mention how big/old the DB is
and how many other people are hitting it at the same time. If this is a
well-used DB then you certainly may need to perform a db compression to see
if the problem goes away.

Classic asp scripting is actually easier (I think) than writting something
in Perl.

Hope this helps.

 

[Diana]

Thanks, everyone, for the suggestions.

This is a new database with a few empty tables and the table I'm
working with has two columns and ten rows. I've indexed the table, as
well. I rarely use a select *, but this table was so small and it was
just an example. You're right about the "LIKE". If it's really a
number I use "=", but in this case, 'number' may have a letter
attached to it.

I hadn't seen this on an older, bigger database I was testing on
before. But going back to it now, I'm having the same issues. Could
it be a permissions thing? The IS guys tried to limit access to the
pages by setting permissions on the web folders involved. Not the
best way to do this, I can imagine, but it was quick. Could this
cause problems?

It just didn't make sense that a simple FP query on a tiny database
would take so long.

What's another option to the system DSN?

Thanks, again.
-Diana

 

[Diana]

Thanks, everyone. I've used the brackets and removed the (*) and
changed the data so it's = for a number. Seems much improved.

I'll have to keep "LIKE" for some name searches, but I'll start with
this. Gee, and these are all things I'd check for when working with
my Oracle databases, but thought FP would be efficient with Access
when creating queries. Guess it's all manually written queries for me
from now on.

Appreciate the help!

-Diana