Performance Update query too slow over network

[bhoran]

I have a database with very few users, it is stored on a network and has user level security, Access 2k with the auto name correction switched off.

I run a very simple update query during an import process where I import from a 3rd party logistics system. After import I update nulls in the value field to zero.

Locally this works fine and takes less than 1 minute however over the network it takes forever more than 50 mins.

I have many queries that update and append from this table so I was hoping to adjust the nulls in the table and not through the other queries.

The table has around 265000 records and around 160000 records need to be updated, this is a regular upload that will get bigger in time.

My sql is as follows:

UPDATE IbmfcstUpload SET IbmfcstUpload.Cases = 0
WHERE (((IbmfcstUpload.Cases) Is Null));

very simple.

I have tried running the SQL as a module but that had no effect.

One thing I have noticed is that if I run this query in datasheet view mode, it returnd the data fairly quickly, it is just the update that takes forever.

Is there some way I can speed up this update process?

 

[BNPMike]

If there is an index on IbmfcstUpload.Cases you might try dropping that temporarily. Relational dbmss tend to use an index for a simple where. If you have a large number of similar values this will slow things down as you'll need to open every page so you might as well do an area sweep and ignore the index.

 

[bhoran]

I don't have an index on cases.

I have decided to bite the bullet and test changing all my queries using the val(nz()) function, I did a quick test and it seemed to be fairly fast, it will also drop one whole function so it should speed the process.

Cheers