Poor DSUM performance on ODBC table

[Vidar13]

In Access 2000,

I'm trying to create an update query based on another selection query that is, in turn, based on a ODBC-linked SQL server table. The problem is, that I also need to sum a quantity field before updating my target table.

As I re-discovered (deja-vu), you can't base an update query on a calculated (sum) selection query... instead Access help suggests doing this in the update query itself using the DSUM function. This works, but incredibly slowly (how about 1 record per 5 seconds?)

I've tried changing to a snapshot on my source selection query instead of a dynaset but it didn't make a difference. I would have thought this would have worked, since it would appear this bottleneck is due to requering of the SQL database per every DSUM field calculation and the snapshot setting should prevent this requerying. Is there something that I'm missing? Is there another way to summarize a recordset to be used in an update query? (I'm updating a local db table, btw).

 

[MichaelRed]

All of the domain aggregate functions are 'notoriously' SLoooooooooooooooooooooooooW!. ODBC is NOTORIOUSLY SLoooooooooooooooooooooooooW.

NOTORIOUSLY SLoooooooooooooooooooooooooW ^
'notoriously' SLoooooooooooooooooooooooooW! aint ever going to impress with speed.

Your'e probably stuck w/ ODBC, but you should be able to do the other calc (pseudo DSum) via other nethod(s) and gen back to just plain "SLOW".

Create a select query to get the sums and use these in the update.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Vidar13]

Unfortunately, you can't use an aggregate select query as a source for update queries. You get the infamous "Operation must use an updateable query". MS notes this as a result of trying to use an aggregate function to summarize info into an update query. The only thing they say as an alternative, is to use the DSUM.. which brings me back to the really, really, sloooooooooooooooooooow performance.

 

[MichaelRed]

No! No! No!

Use the aggregate query to just collect the info on WHAT records to obtain for the update query, of to generate the aggregate values to SEPERATLY update the other records (via code if necessary). Don't be fixated on using the tools in the same way all the time.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Vidar13]

I'm confused now.

I did try using the aggregate select query to select just the records I wanted and then to summarize them.

I then built an update query that pulled from the select query, but the rules of Access don't allow you to do this, apparently.

Are you suggesting that I update the target table via code, rather then by an updating query?