Performance Problem

[George1111]

I have a routine for calculating values based on variables sent to it (in a BAS)

The problem is that it requires to lookup about 8 files using syntax like

Select * from mytable where x = y

To achieve this I say STRSQL = "Select * from mytable where x = y"

Then I say

Set RSMyTable = DBCalc.OpenRecordset(StrSql, dbOpenDynaset)

This repeats for each table then I close the tables

How can I leave the tables open and still use the

syntax Select * from mytable where x = y

I tried using the DB.Execute STRSQL but it doesn't like that

Other than closing the Table, is there a way to do this (so that I can reduce the number of Opens and Closes and hopefully have a faster calculation routine)

[SweetCoder]

' for first table
STRSQL1 = "Select * from mytable1 where x = y"
Set RSMyTable1 = DBCalc.OpenRecordset(StrSql1, dbOpenDynaset)

' for second table
STRSQL2 = "Select * from mytable2 where x = y"
Set RSMyTable2 = DBCalc.OpenRecordset(StrSql2, dbOpenDynaset)


and so on ,
i hope it will help u

[George1111]

That's what I am doing - the problem is that to access the same table again, I need to firstly close it, then reopen as per your example.

What I will need to do is dump SQL Select Statements and go back to using SEEK which is several hundred times quicker than using Select Statements (it actually uses the indexes whereas I don't believe Select does this very well)

I hate to do this as it is much harder to code and it does not give me compatibility with SQL Server 2000

What else can I do ?

Select on an Access database is just slow.

[jvbd02]

Hi

How about putting the results of each query into its own table (use a make table query), after all 8 are done the results will be in these tables and accessable....

Might want to drop the tables after info in all of them has been accessed and also compact the database, particularly if there are many records involved as this method will soon bloat the mdb

Cheers

[George1111]

Basically we are calculating pricing based on a number of different customer, product, and special deal criteria - my problem stems from customers having contracts, belonging to certain groups or having global rules

Consequently you get

Select x from y where a=b and c=d and e=f style selections which I suspect are slowing down the whole process

[jvbd02]

Oh right.

Then why not just do one make table query on the source table where a=b and c=d and e=f etc. Then all results are in one table and subsequent querying of those results should be pretty quick, even if the initial make table query is a little tardy.

Given the conditions on the query of source table (where a=b and c=d and e=f etc.) Can you index any of these a, c or e fields? Might speed things up a little, but knowing access I doubt it'll help much!

Probably missing the point again, but hey, only trying to help!

Cheers

[George1111]

Thanks for your interest - I will try to make composite tables as the fewer the tables, the faster the access to the data

Cheers

[cjard]

i've never seemed to have a problem with this.. for what you say, i would use a prepared statement and leave it conencted.. i'd actually use 8 of them. i wrote a utility class for managing prepared statements a while ago, and i know it seems like im pushing it in every db topic, but it does make some things easier..

if you can find my PreparedStatement class module, you'd use it something like this:

Dim ps1 as PreparedStatement
Set ps1 = New PreparedStatement
ps1.init <database provider> <database path> 
'used with access DB, provider is e.g Microsoft.Jet.OLEDB.4.0 and the path is to the mdb file

ps1.prepareStatement "SELECT * FROM table1 WHERE column1 = ?"

repeat this process for all eight, and put a ? mark where you will have a variable. note that column and table names cannot be variables, only field values

then to use the ps is like:

ps1.setBigInteger 0, myInteger
myADORS = ps1.executeQuery()

so you just repeatedly call setXXX and it sets the value for the variable.. in this case if our myInteger var contains 1234, the query would be:
SELECT * FROM table1 WHERE column1 = 1234

if myInteger was 5678, the query would be:
SELECT * FROM table1 WHERE column1 = 5678


it is important to note that this is faster because Access doesnt have to recompile the SQL statement each time.. its like a vb program; using a variable is faster than using a hardcoded value and recompiling the program all the time.

the resultset you get back is just a normal boring ado resultset. if you only want one value out of it, you could even execute it like this:

valueWanted = (ps1.executeQuery)!someColumn


try it.. it might be acceptable. incidentally, using a dynaset is probably a bad idea, unless you need the update facility; it costs time to poll a recordset repeatedly for values that might have changed. if your values are static, use a snapshot

[George1111]

Thanks Cjard

Your logic defies my logic! (How on earth did you come up with this one)