Ask your JavaScript questions! Pay money and get answers fast! (more info)

Fast way to count spec. values from a column in Access ASP Page JavaScript

  • REFUNDED

Hello there;
I want to have the best/quickest method to get & display the sum of values from specific tables. It is a bit slow now. I also have different counters (about 20) on my page. (see for the code below)

I think Javascript can do this faster and I read the function "GetRows" is much faster than my ADODB method I used..

Here is my code now to display the hours I trained today & total hours this week (fitness ).
Can someone please help me to get it as fast as possible! in javascipt or getrows? (Or another method)


<%
dim accessdb, cn
' Name of the Accessdb being read
accessdb="../../database/" & db

' Connect to the db with a DSN-less connectionf
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
%>

<%
dim rs1, sql1
Set rs1 = Server.CreateObject("ADODB.Recordset")
sql1 = "select SUM(hour) as counter from LOG WHERE subcat LIKE '%training%' AND datum = date();"
rs1.Open sql1, cn
response.write rs1("counter")
rs1.close
set rs1=nothing
%>

<%
dim rs2, sql2
Set rs2 = Server.CreateObject("ADODB.Recordset")
sql2 = "select SUM(uur) as counter from LOG WHERE subcat LIKE '%training%' AND DatePart('ww',[datum],2)= DatePart('ww',date(),2) AND DatePart('yyyy',[datum])= DatePart('yyyy',date());"
rs2.Open sql2, cn
response.write rs2("counter")
rs2.close
set rs2=nothing
%>


etc. etc..

Cheers! Jeroen

Answers (1)

2014-01-27

Ross Wilson answers:

Hi Jeron,

Most likely your slowness is actually in the time that it takes to execute the SQL query against your access database. There are some improvements you could make to your queries, as well as to your database as a whole to make sure you are getting the optimal performance. Do you know if you have indexes created for each of the fields you are using in your WHERE clauses? Also, is it necessary to use "LIKE" in your query, or can you change that to "= 'training'?

Since you are only querying for one row the getRows, or any client-side javascript wouldn't really help your performance.

If you can send a screenshot of your indexes for your database we can get those optimized (here is how: http://office.microsoft.com/en-us/access-help/view-or-edit-indexes-mdb-HP005187563.aspx)

Ross


Jeroen Hollander comments:

Hi Ross;

I made my code like underneath; is this the best way? Is indexing in Access making it quicker?
(My "subcat" column is a column with lots of subcategories comma seperated: like "health, fitness" etc. )


<%
dim accessdb, cn
' Name of the Accessdb being read
accessdb="../../database/" & db

' Connect to the db with a DSN-less connectionf
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
%>

Today training:
<%
Set con = Server.CreateObject("ADODB.Connection")
con.open cn

dim rs1, sql1
Set rs1 = Server.CreateObject("ADODB.Recordset")
sql1 = "select SUM(hour) as counter from LOG WHERE subcat LIKE '%fght%' AND datum = date();"

rs1.Open sql1, con
response.write rs1("counter")
rs1.close
%>
Week training:
<%
dim sql2
sql2 = "select SUM(hour) as counter from LOG WHERE subcat LIKE '%training%' AND DatePart('ww',[datum],2)= DatePart('ww',date(),2) AND DatePart('yyyy',[datum])= DatePart('yyyy',date());"

rs1.Open sql2, con
response.write rs1("counter")
rs1.close
%>