is null-criteria decreases performance

[harryklein]

Hi,
I am using a query that has only one thing to do: return the records where tha updateTime-field is not null.
This field has a date/time format, and is empty by default until the record is updated.
My experience is that this query is processes extremly slow.

SELECT * FROM reports WHERE updateTime IS NOT NULL

Is there any known issue with NULL and date-fields I am not aware of?


Thank you

[Minty]

Not normally. Is the field indexed ?

How many records are you searching through and how long is it taking?
Is it a linked table ? if so is it on a local network ?

[harryklein]

Number of records to be retrieved is 2 at the moment out of 70.000.
It is on the network, and not linked. It is not indexed and takes about 20seconds.
Would it be useful to index it?

[Minty]

Definitely - an index will help. Are there any other criteria you will apply to reduce the returned records ?
If so make sure those fields if appropriate are also indexed.

Does the table have a primary key field ?

I puzzled by the statement

It is on the network, and not linked.

Are you accessing the database remotely ?

[Peter M]

Also, unless you need every field, only select the primary key
q1 = Select [PrimaryKey] from Reports where [updateTime] is not null

q2 = select * from Reports inner join q1 on [Reports].[primarykey]=[q1].[primarykey]

In my experience this improves performance on large datasets, but 70,000 records should be almost instant regardless. (Definitely make sure you have a Primary Key and UpdateTime is an indexed field.)

[harryklein]

Yes, table has primary key. MsAccess is installed on my local drive but the accdb file is located on a shared (network)drive, it is that you are asking?
There are no other criteria, it is just the real situation currently, that only a few records hat different value than NULL.
I added an index to this field, but didn't change anything.
Thank you.

[harryklein]

Thanks, I will try this way.

[Minty]

If the database is being used over the network that will slow things down.
Do you have a hardwired connection or wireless? Physical connection much preferred.

Have a Google about splitting the database , Ideally you should have the data remotely shared and the front end database locally installed.

If your network is slow though, there maybe not a lot else you can do.

[CJ_London]

if the field is regularly used in criteria and/or sorting it definitely should be indexed. I'm surprised you say it makes no difference.

It is not clear to me what your setup is - it sounds like you have a backend on a server, but no mention of a front end and you are opening the backend directly. Since it is standard Access practice to have a split database, it may be worth creating a local front end and linking to the back end and applying your query to the local linked table.

[harryklein]

The frontend is excel, I use ADO from vba to get the data.
However, I don't experience differences between executing query from excel and directly from MsAccess. The first thing I do with problematic query is to put it into access to be sure that the root-cause is not excel-related. Without applying any where-criteria or search for a specific primary key it executes very quickly on both ways.
Anyway, I will do research on splitting as suggested.
Thank you very much.

[CJ_London]

Number of records to be retrieved is 2 at the moment out of 70.000.
I added an index to this field, but didn't change anything.

missed this earlier - just indexing this field may not be sufficient because 99.99999% of the records have null values. Modify the index to ignore nulls