MS ACCESS Vs SQL

Which Database

[bluesquirel]

I need to know will my Access Databse cope and how can I decrease query times.

I have an Access DB with 16900 records in and I have written a search query to search these records but I am worried the search takes to long. Do I need to use SQL.

The Code is below:

<!--#INCLUDE FILE = "adovbs.inc"-->
<%

Response.Buffer=TRUE
Dim County, Elderly, AcuteMed, Alcohol, AIDS, Alzheim, Anorexia, Cancer, Drug, LearnDis, MentDis, Neuro, Ortho, PhysDis, Surgical, Terminal
County = Request("County")
Elderly = Request("Elderly")
AcuteMed = Request("AcuteMed")
AIDS = Request("AIDS")
Alcohol = Request("Alcohol")
Alzheim = Request("Alzheim")
Anorexia = Request("Anorexia")
Cancer = Request("Cancera")
Drug = Request("Drug")
LearnDis = Request("LearnDis")
MentDis = Request("MentDis")
Neuro = Request("Neuro")
Ortho = Request("Ortho")
PhysDis = Request("PhysDis")
Surgical = Request("Surgical")
Terminal = Request("Terminal")

If Elderly = "" Then
Elderly = "%"
End If
If AcuteMed = "" Then
AcuteMed = "%"
End If
If AIDS = "" Then
AIDS = "%"
End If
If Alcohol = "" Then
Alcohol = "%"
End If
If Alzheim = "" Then
Alzheim = "%"
End If
If Anorexia = "" Then
Anorexia = "%"
End If
If Cancer = "" Then
Cancer = "%"
End If
If Drug = "" Then
Drug = "%"
End If
If LearnDis = "" Then
LearnDis = "%"
End If
If MentDis = "" Then
MentDis = "%"
End If
If Neuro = "" Then
Neuro = "%"
End If
If Ortho = "" Then
Ortho = "%"
End If
If PhysDis = "" Then
PhysDis = "%"
End If
If Surgical = "" Then
Surgical = "%"
End If
If Terminal = "" Then
Terminal = "%"
End If
Dim Conn, rsListHomes, sqlGetHomes, i

Set rsListHomes = Server.CreateObject("ADODB.Recordset")
rsListHomes.CursorLocation=3 ' Client Side
rsListHomes.CursorType=3 ' Static Recordset
rsListHomes.PageSize=20

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("..\private\Care.mdb") & ";"

sqlGetHomes = "Select * From Homes Where County ='" & County & "'"
sqlGetHomes = sqlGetHomes &" AND Elderly LIKE '" & Elderly & "'"
sqlGetHomes = sqlGetHomes &" AND AcuteMed LIKE '" & AcuteMed & "'"
sqlGetHomes = sqlGetHomes &" AND AIDS LIKE '" & AIDS & "'"
sqlGetHomes = sqlGetHomes &" AND Anorexia LIKE '" & Anorexia & "'"
sqlGetHomes = sqlGetHomes &" AND Alcohol LIKE '" & Alcohol & "'"
sqlGetHomes = sqlGetHomes &" AND Drug LIKE '" & Drug & "'"
sqlGetHomes = sqlGetHomes &" AND Alzheim LIKE '" & Alzheim & "'"
sqlGetHomes = sqlGetHomes &" AND LearnDis LIKE '" & LearnDis & "'"
sqlGetHomes = sqlGetHomes &" AND MentDis LIKE '" & MentDis & "'"
sqlGetHomes = sqlGetHomes &" AND Neuro LIKE '" & Neuro & "'"
sqlGetHomes = sqlGetHomes &" AND Ortho LIKE '" & Ortho & "'"
sqlGetHomes = sqlGetHomes &" AND PhysDis LIKE '" & PhysDis & "'"
sqlGetHomes = sqlGetHomes &" AND Surgical LIKE '" & Surgical & "'"
sqlGetHomes = sqlGetHomes &" AND Terminal LIKE '" & Terminal & "'"
sqlGetHomes = "Select * From Homes"

rsListHomes.Open sqlGetHomes, Conn
%>

Regards

Dave

[txbakers]

if the DB is to be accessed over the web, use SQL. Access will not support lots of ins and outs. It's not designed for that.

Lots of discussion on this topic already in this forum, just do a site search and you'll be reading the specs for days.

[webdevsf]

your performance is bad because of all the like clauses.

Since you are constructing the sql dynamically, just remove the parts of the where statement that have '%' as the search term.

ie,

it is better to do this (if they've only entered country + Elderly:

sqlGetHomes = "Select * From Homes Where County ='" & County & "'"
sqlGetHomes = sqlGetHomes &" AND Elderly LIKE '" & Elderly & "'"

and then if all the rest are blank, don't include them in the where clause.

This will cause your query to execute more efficiently.

Also change "select * from homes" to
select col1,col2,col3, etc from homes

this will cause less data to be returned from your query which will be faster.

[bluesquirel]

I am not sure how to only generate a dynamic query to search on only the specifics e.g County + Elderly.

Can anyone point me in the right direction.

Regards

Dave

[bluesquirel]

Would it be

If Elderly = "" Then
Else
sqlGetHomes = sqlGetHomes &" AND Elderly LIKE '" & Elderly & "'"
End If
If Cancer = "" Then
ELSE
sqlGetHomes = sqlGetHomes &" AND Elderly LIKE '" & Elderly & "'"
End IF

....And So On

Cheers in Adv

[raptorix]

As far as i can see this is a typical BAD database design, better do something like this:

make a table home ie (home_id,home_name,home_adress,etc)
make a table care(?) (care_id,care_name)
make a table home_care(home,care)

Now you can directly query the properties of the homes.
so something like:

select * from home where home_id in (select home_id from home_care where care in (6,7,9))

This works much better! I am not completely sure or this fits, because you did not describe your database model, but you have to keep in mind that querying on crosstable is much faster!

Searching with '%foo%' queries makes that your index is not used! And searching without a index is normally slow!

If you consider to use Sqlserver u can make sure of the full text index function, which is more suitable on like queries.