slow performance

[ronan76]

Hi, I'm running about 15 queries on a linked access database and publishing to our intranet. The page takes about 10-15 seconds to load, probably due to my crap code and the fact that i open a database connection for each query. I tried opening one database connection and passing the results of the query into variables but it didn't make it much quicker.

If anyone could offer some advice on how to improve the performace i'd be greatful.

Thanks
ronan

 

[Mile-O]

probably due to my crap code and the fact that i open a database connection for each query.

Do we get to see the code or is this a guessing game?

 

[ronan76]

oh yeah sorry bout that....just didn't want to be presumptious and post code up straight away...thanks
here are the queries, i've included three of them there is about 15 in total, all similar to the ones below.

'2020 Strategies
DIM mySQL, objRS
mySQL = "SELECT Count(*) AS intTotal FROM VivasLives WHERE VivasLives.group_name='2020 STRATEGIES';"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn

'Carat
DIM mySQL1, objRS1
mySQL1 = "SELECT Count(*) AS intTotal1 FROM VivasLives WHERE VivasLives.group_name='Carat Ireland' AND leaving_date is NULL;"
Set objRS1 = Server.CreateObject("ADODB.Recordset")
objRS1.Open mySQL1, objConn

'Catalyst
DIM mySQL2, objRS2
mySQL2 = "SELECT Count(*) AS intTotal2 FROM VivasLives WHERE VivasLives.group_name='CATALYST DNA' AND leaving_date is NULL;"
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open mySQL2, objConn


here is where i print out the values..in my HTML page


<tr>
<td class="main"><strong class="private">2020 Strategies</strong></td>
<td align="center" class="main"><% ' Display result
Response.Write objRS("intTotal")%></td>
</tr>
<tr>
<td class="main"><strong class="private">Carat</strong></td>
<td align="center" class="main"><%' Display result
Response.Write objRS1("intTotal1")%></td>
</tr>
<tr>
<td class="main"><strong class="private">Catalyst</strong></td>
<td align="center" class="main"><%' Display result
Response.Write objRS2("intTotal2")%></td>
</tr>

 

[FLabrecque]

Have you tried using a DCOUNT? I'm guessing that it's opening the recordset that takes up those nasty seconds.

 

[Mile-O]

Have you tried using a DCOUNT? I'm guessing that it's opening the recordset that takes up those nasty seconds.

Have you ever used a DCount? They are a lot slower than opening a typical connection to a recordset.

 

[FLabrecque]

I've never experienced that much time difference, but then again, never used it on huge tables. Otherwise, the queries seems simple enough. Hard to optimize that!

 

[ronan76]

Thanks for the advice guys.
Helpful as always.

Ronan