Jet query execution

[Jim Bunton]

Is there any way of persuading Jet to execute queries in a specific manner.

My specific issue is that I have a function included in an sql statement

eg MySlowFunction([columnName]) as ABitSlow

The general form of the query is
SELECT aColumn
FROM LotsOfRecords
WHERE cutstherecordsdowndrastically
AND mySlowFunction(aColumn) = x

IT's pretty obvious that MySlowFunction is being run agains ALL the rows
instead of just those defined by 'cutstherecordsdowndrastically'

I can show this by running the function on a query that's already cut the
rows down drastically.

BUT it would be a sight more elegant to persuade Jet to follow the same
stratergy.

[and possibly easier as the query is generated in code]

 

[Ed Metcalfe]

Is there any way of persuading Jet to execute queries in a specific
manner.

My specific issue is that I have a function included in an sql statement

eg MySlowFunction([columnName]) as ABitSlow

The general form of the query is
SELECT aColumn
FROM LotsOfRecords
WHERE cutstherecordsdowndrastically
AND mySlowFunction(aColumn) = x

IT's pretty obvious that MySlowFunction is being run agains ALL the rows
instead of just those defined by 'cutstherecordsdowndrastically'

I can show this by running the function on a query that's already cut the
rows down drastically.

BUT it would be a sight more elegant to persuade Jet to follow the same
stratergy.

[and possibly easier as the query is generated in code]

Jim,

What code do you have in mySlowFunction()?

Ed Metcalfe.

 

[Jim Bunton]

Hi Ed - thanks for the reaponse
The function code checks the character content of the data in the column
((is it an alphabetic character or number - and a couple of others and
returns true/false) - I think another issue is that the
"cutstherecordsdowndrastically is a between date and date statement. I've
tried other types of WHERE eg col value = "xxx" and this does seem to hurry
things up.

Jim

Is there any way of persuading Jet to execute queries in a specific
manner.

My specific issue is that I have a function included in an sql statement

eg MySlowFunction([columnName]) as ABitSlow

The general form of the query is
SELECT aColumn
FROM LotsOfRecords
WHERE cutstherecordsdowndrastically
AND mySlowFunction(aColumn) = x

IT's pretty obvious that MySlowFunction is being run agains ALL the rows
instead of just those defined by 'cutstherecordsdowndrastically'

I can show this by running the function on a query that's already cut the
rows down drastically.

BUT it would be a sight more elegant to persuade Jet to follow the same
stratergy.

[and possibly easier as the query is generated in code]

Jim,

What code do you have in mySlowFunction()?

Ed Metcalfe.

 

[jacksonmacd]

Build a query that CutsTheRecordsDownDrastically, and join it to your
base table in a second query that includes the mySlowFunction. Of
course, that will result in two queries to manage...

You can work around *that* problem with some creative SQL by following
these general steps:
- Alias the first query as it's represented in the second query. I
usually alias it as "q" so it's short
- open the first query in SQL view and copy its SQL statement
- open the second query in SQL view
- find where the first query is aliased in the sql statement and
replace its name with [xxxxxx]. where xxxxx is the sql statement
from the first query MINUS the trailing semi-colon. Notice the use of
square brackets and a trailing period. The actual sql statement must
not contain any square brackets of its own, so you may need to do some
manual editing and managing of field names

This results in a single SQL statement that is a combination of two
queries.

HTH

Is there any way of persuading Jet to execute queries in a specific manner.

My specific issue is that I have a function included in an sql statement

eg MySlowFunction([columnName]) as ABitSlow

The general form of the query is
SELECT aColumn
FROM LotsOfRecords
WHERE cutstherecordsdowndrastically
AND mySlowFunction(aColumn) = x

IT's pretty obvious that MySlowFunction is being run agains ALL the rows
instead of just those defined by 'cutstherecordsdowndrastically'

I can show this by running the function on a query that's already cut the
rows down drastically.

BUT it would be a sight more elegant to persuade Jet to follow the same
stratergy.

[and possibly easier as the query is generated in code]

 

[Stefan Hoffmann]

hi Jim,

The general form of the query is
SELECT aColumn
FROM LotsOfRecords
WHERE cutstherecordsdowndrastically
AND mySlowFunction(aColumn) = x

IT's pretty obvious that MySlowFunction is being run agains ALL the rows
instead of just those defined by 'cutstherecordsdowndrastically'

Jet cannot know that 'cutstherecordsdowndrastically' is a superset of
mySlowFunction(aColumn) = x.

The first thing I would try is a sub-select:

SELECT q.aColumn
FROM (
SELECT aColumn
FROM LotsOfRecords
WHERE cutstherecordsdowndrastically
) q
WHERE mySlowFunction(q.aColumn) = x


mfG
--> stefan <--