Working Indexes Example?

[gkl]

Dear All,

I am looking for an example that will show the difference in time that the use of indexes will make. I created a user table with random data from Mockaroo (10,000 rows), indexed one non-key field and developed two queries one on the indexed non-key field and another one on a non-indexed non-key field but there was no time difference.

Is there a way or a combination of data that will highlight the time differences?

Regards,
George

 

[spikepl]

Imagine a phone book. With all entries not ordered alphabetically but randomly. That is like a table without an index. It takes few seconds to find a name in an ordered phone book. It takes ages if it is random, and it just gets worse with size. The exactly same thing applies to a table.

 

[spikepl]

For your case, show the SQL for the two queries

 

[jdraw]

There is a demo in this free video from Datapig.

 

[gkl]

Imagine a phone book. With all entries not ordered alphabetically but randomly. That is like a table without an index. It takes few seconds to find a name in an ordered phone book. It takes ages if it is random, and it just gets worse with size. The exactly same thing applies to a table.

I am familiar with the concept of the index; I am just looking for an example and Access does not give me access to the scheduler (something equivalent to explain plan in Oracle)

Regards,
George

 

[gemma-the-husky]

there may not be any difference. 10000 rows is relatively small. access may be able to optimise the search on the non-indexed field even without an index.

try a query that requires each row to use a function, and then filter on the function result. that would run more slowly, I expect.

 

[vbaInet]

The closest thing to an explain plan is the JETSHOWPLAN but unfortunately it doesn't show timed executions. Still worth looking at:

http://www.techrepublic.com/article...lan-to-write-more-efficient-queries/5064388/#

If you're having problems with the Debug key path, just tell me which Windows version and whether it's 32 or 64 bit and I'll tell you the path.

The other is using ISAMStats which is explained here:

http://bytes.com/topic/access/insights/746787-isamstats-method

I think the latter is more what you're after, but a combination of both methods is somewhat useful. Unfortunately, neither are as comprehensive as the explain plan but good enough for Access.

 

[vbaInet]

Forgot to mention that the second option 'may' not work in the newer versions of Access as the engine is different. Still worth trying.

 

[vbaInet]

...just remember to "Show Hidden Members" in the VBA editor.

 

[gkl]

For your case, show the SQL for the two queries

Sure, the first one is:

select first_name from user where first_name='John' (first_name is an indexed field with a single-column index) and

select last_name from user where last_name='Jones' (last_name does not have an index defined on it).

Obviously, neither first_name nor last_name are primary key fields.

Regards,
George