query very slow

[SusanV]

I have a form based on a user input query (single field, which is indexed)
that pulls data from a linked table with over 376,000 records (and growing).
It takes several minutes for the query to run before loading the report.

Users are impatient, and I'd like to improve performance if possible. Is
there any way to speed this query up at all or is this simply a matter of
there being too many records? I've already compacted / repaired the
database, no change.

Thanks in Advance,

SusanV

 

[Brendan Reynolds]

If you post the SQL for the query, someone may be able to advise.

 

[SusanV]

Oh sure, wasn't thinking, sorry!

SELECT FleetPM.VesselName, FleetPM.EquipmentName, FleetPM.HSC,
FleetPM.MCode, MCode.MCodeTitle, FleetPM.TaskCode, FleetPM.Frequency,
FleetPM.OpStat
FROM MCode INNER JOIN FleetPM ON MCode.MCode = FleetPM.MCode
WHERE (((FleetPM.MCode) Like '*' & [Enter the MCode number to check] & '*'))
ORDER BY FleetPM.VesselName;

 

[Brendan Reynolds]

The Jet database engine can't use an index when you use a leading wildcard
like that. I believe it can use an index with a trailing wildcard, so if you
can use "LIKE [Enter the MCode number] & '*'" instead of "LIKE '*' & [Enter
the MCode number] & '*'" you may see a significant improvement.

--
Brendan Reynolds (MVP)

Oh sure, wasn't thinking, sorry!

SELECT FleetPM.VesselName, FleetPM.EquipmentName, FleetPM.HSC,
FleetPM.MCode, MCode.MCodeTitle, FleetPM.TaskCode, FleetPM.Frequency,
FleetPM.OpStat
FROM MCode INNER JOIN FleetPM ON MCode.MCode = FleetPM.MCode
WHERE (((FleetPM.MCode) Like '*' & [Enter the MCode number to check] &
'*'))
ORDER BY FleetPM.VesselName;

 

[SusanV]

Thanks - much faster! I didn't know that about the leading wildcard,
interesting... Out of curiosity, I changed it to =[Enter the MCode number to
check] and it runs even faster - in about 3-4 seconds. Wow!

Thanks tons,

SusanV

The Jet database engine can't use an index when you use a leading wildcard
like that. I believe it can use an index with a trailing wildcard, so if
you can use "LIKE [Enter the MCode number] & '*'" instead of "LIKE '*' &
[Enter the MCode number] & '*'" you may see a significant improvement.

--
Brendan Reynolds (MVP)

Oh sure, wasn't thinking, sorry!

SELECT FleetPM.VesselName, FleetPM.EquipmentName, FleetPM.HSC,
FleetPM.MCode, MCode.MCodeTitle, FleetPM.TaskCode, FleetPM.Frequency,
FleetPM.OpStat
FROM MCode INNER JOIN FleetPM ON MCode.MCode = FleetPM.MCode
WHERE (((FleetPM.MCode) Like '*' & [Enter the MCode number to check] &
'*'))
ORDER BY FleetPM.VesselName;

If you post the SQL for the query, someone may be able to advise.

--
Brendan Reynolds (MVP)

I have a form based on a user input query (single field, which is
indexed) that pulls data from a linked table with over 376,000 records
(and growing). It takes several minutes for the query to run before
loading the report.

Users are impatient, and I'd like to improve performance if possible.
Is there any way to speed this query up at all or is this simply a
matter of there being too many records? I've already compacted /
repaired the database, no change.

Thanks in Advance,

SusanV

 

[Brendan Reynolds]

You're welcome.

And what a good example of how important appropriate indexing is!

--
Brendan Reynolds (MVP)

Thanks - much faster! I didn't know that about the leading wildcard,
interesting... Out of curiosity, I changed it to =[Enter the MCode number
to check] and it runs even faster - in about 3-4 seconds. Wow!

Thanks tons,

SusanV

The Jet database engine can't use an index when you use a leading
wildcard like that. I believe it can use an index with a trailing
wildcard, so if you can use "LIKE [Enter the MCode number] & '*'" instead
of "LIKE '*' & [Enter the MCode number] & '*'" you may see a significant
improvement.

--
Brendan Reynolds (MVP)

Oh sure, wasn't thinking, sorry!

SELECT FleetPM.VesselName, FleetPM.EquipmentName, FleetPM.HSC,
FleetPM.MCode, MCode.MCodeTitle, FleetPM.TaskCode, FleetPM.Frequency,
FleetPM.OpStat
FROM MCode INNER JOIN FleetPM ON MCode.MCode = FleetPM.MCode
WHERE (((FleetPM.MCode) Like '*' & [Enter the MCode number to check] &
'*'))
ORDER BY FleetPM.VesselName;




If you post the SQL for the query, someone may be able to advise.

--
Brendan Reynolds (MVP)

I have a form based on a user input query (single field, which is
indexed) that pulls data from a linked table with over 376,000 records
(and growing). It takes several minutes for the query to run before
loading the report.

Users are impatient, and I'd like to improve performance if possible.
Is there any way to speed this query up at all or is this simply a
matter of there being too many records? I've already compacted /
repaired the database, no change.

Thanks in Advance,

SusanV

 

[SusanV]

Just curious - why is it that it doesn't use the index for the leading
wildcard?

You're welcome.

And what a good example of how important appropriate indexing is!

--
Brendan Reynolds (MVP)

Thanks - much faster! I didn't know that about the leading wildcard,
interesting... Out of curiosity, I changed it to =[Enter the MCode number
to check] and it runs even faster - in about 3-4 seconds. Wow!

Thanks tons,

SusanV

The Jet database engine can't use an index when you use a leading
wildcard like that. I believe it can use an index with a trailing
wildcard, so if you can use "LIKE [Enter the MCode number] & '*'"
instead of "LIKE '*' & [Enter the MCode number] & '*'" you may see a
significant improvement.

--
Brendan Reynolds (MVP)

Oh sure, wasn't thinking, sorry!

SELECT FleetPM.VesselName, FleetPM.EquipmentName, FleetPM.HSC,
FleetPM.MCode, MCode.MCodeTitle, FleetPM.TaskCode, FleetPM.Frequency,
FleetPM.OpStat
FROM MCode INNER JOIN FleetPM ON MCode.MCode = FleetPM.MCode
WHERE (((FleetPM.MCode) Like '*' & [Enter the MCode number to check] &
'*'))
ORDER BY FleetPM.VesselName;




message If you post the SQL for the query, someone may be able to advise.

--
Brendan Reynolds (MVP)

I have a form based on a user input query (single field, which is
indexed) that pulls data from a linked table with over 376,000 records
(and growing). It takes several minutes for the query to run before
loading the report.

Users are impatient, and I'd like to improve performance if possible.
Is there any way to speed this query up at all or is this simply a
matter of there being too many records? I've already compacted /
repaired the database, no change.

Thanks in Advance,

SusanV

 

[John Vinson]

Just curious - why is it that it doesn't use the index for the leading
wildcard?

Because the Index is on the *actual contents* of the field. If the
field contains

FVQQ831234RW99512

then that's what the index contains. Trailing wildcards work because
if you're searching for FV*, JET can quickly locate that part of the
index; but if you're looking for *RW*, JET must look in every record
anyway to see if any of them contain that substring somewhere within
the field. You might have AAQQ3421235RW12312 or ZZWXRW12312AC12345 -
since there's no way to predict where the RW might occur, it must look
at each record anyway.

John W. Vinson[MVP]

 

[SusanV]

That makes perfect sense - and clearly explains why the leading wildcard
makes the query take so long.

Again, thanks for your help and a great explanation!

SusanV