ASP and database lookup performance

[cybercop23]

Hi all.
Here's my issue. I have an ASP page that searches an Access database and fetches some data. The databases are betweeen 1 million and 1.5 million records. I'm using an ADO connection to connect to the DBs, however I'm getting a slow response retrieving data. Is there another type of connection that I should be using? Just a note, the tables are not indexed.

 

[Ladyhawk]

An ADO connection is probably the easiest, although I have done some time trials for DAO and ADO against an Access database and have found that DAO is actually quicker than ADO. It didn't seem to matter whether it was Access 97 or Access 2000. However, I am not sure whether you can use DAO in ASP. Since the tables that you are accessing are relatively large, it is essential that they be indexed. That, I think, is the main area where you are loosing performance.

Ladyhawk.

 

[Ladyhawk]

P.S. to my previous post. I was using VB6 for the time trials.

Ladyhawk.

 

[cybercop23]

Thanks for the feedback. I'll try indexing the tables and see if I get any noticiable preformance improvements.

AB.

 

[cybercop23]

I have just indexed my largest table and it is the same as before. However what I've noted is that if I open the table in Access and filter it, I get my results alsmost instantly. This is the 1.5 million records table.
Just timed it. It took 63 seconds to return 2 records.
Any one has any ideas why is sooooo sloow

http://www with

ASP?

 

[Ladyhawk]

How did you index the table and how are you accessing the table? Ladyhawk.
** ASP/VB/Java Programmer **

 

[teenmonk]

Cybercop23

During the long database searches via ASP, how does your mouse cursor look like? Is it the standard arrow??

 

[Ladyhawk]

Yeah... it's just the standard mousepointer... don't know if there is a way to change it to the hourglass like in VB. Ladyhawk.
** ASP/VB/Java Programmer **

 

[cybercop23]

Ladyhawk,
I've used Access to index the fields that I allow the user to search on. It created a larger DB file, as expected. If I create a query in Access and search on anything, I get the data as fast as using a filter in table view.
I think this has something to do with the ASP connection string.

The way I access it, is by creating an sqlstr and put in my sql query. I'll post it here is needed.

Thanks

 

[cybercop23]

Here's the ASP...
strDBPath = Server.MapPath("testdatabase.mdb&quot
Set cnnSearch = Server.CreateObject("ADODB.Connection&quot
strSQL = "SELECT LN, FN, Add " _
& "FROM Tbls " _
& "WHERE LN LIKE '%" & Replace(strSearch, "'", "''&quot & "%' " _
& "OR FN LIKE '%" & Replace(strSearch, "'", "''&quot & "%' " _
& "ORDER BY LN;"
Set rstSearch = cnnSearch.Execute(strSQL)

 

[Ladyhawk]

Using "LIKE" in a SELECT statement will always be relatively slow. Do you have to use "LIKE" or can you get away with "="? Did you create an index that indexed both LN and FN together? Ladyhawk.
** ASP/VB/Java Programmer **

 

[cybercop23]

I have to use LIKE, since the user can search on a substring in the LN and FN fields. LN and FN are two different fields. Access doesn't allow for indexing fields in paid. At least not that I'm aware.
Also note that if I use the LIKE operand in a query right in Access it works perfect. I'm stomped.

AB

 

[Ladyhawk]

Yeah... you can create an index that is make up of multiple fields. If you go to View - Indexes menu. Give the index a name and assign the two fields.

Like this...

myIndex - LN
- FN

Might speed it up a bit. Ladyhawk.
** ASP/VB/Java Programmer **

 

[cybercop23]

Thanks. I'll try it.

 

[cybercop23]

Now, that's what I call response time.
I did ended up removing the LIKE operand and used the double field index. Things are much smoother.

Thanks again.

 

[Ladyhawk]

No problem... glad to help. Ladyhawk.
** ASP/VB/Java Programmer **