[This is a repost. Looks like my first post never hit the group]

These damned things just keep getting worse.

I now have an index on a combined field using the following correlative...

A128(MR%5))

....and I have the same correlative in an attribute defining item called
CLDATE. If I try the following select...

SELECT AR.HIST WITH CLDATE "SH8813155"

....I get an instant response. However, if I try this...

SELECT AR.HIST WITH CLDATE >= "SH8813150" AND <= "SH8813180"

....the damned thing starts passing through the entire file! How lame is
that? I've given a starting and ending point with a b-tree index, and
there are no other complicating factors, yet ACCESS is too stoopid to
optimise this select via the the index.

I suppose I can get away with this...

SELECT AR.HIST WITH CLDATE "SH8813150" "SH8813151" "SH8813152" ...

....but why should I have to? And what if I wanted to select an entire
year's worth of data for a given client?

This is pathetic. I mean seriously pathetic.

Luke

Luke Webber wrote:

...and this is the new improved version. Btw - I had similar problems
dealing with indexes on large files once. I took the drasrtic action of
writing my own pre-processor. Replaced LIST and SORT with cataloged
programs which inserted a call to my pre-processor subroutine before
executing the RealList and RealSort verbs (renamed from LIST and SORT).
My pre-processor converted things like you have (">=... <=...
') in the selection criteria to "= ... ... ...". I was pleased to find
that there didn't appear to be a limit on the length of the
RealList/RealSort statement I ended up executing. There might be one -
just never found it. As I said though - a pretty drastic measure.

Mike.

Luke,

You still haven't shared wich version of D3/NT you are using - there
are "caveats" with when indices will beused in a query ... later
versions are "better", but you may still have to jump through some
hoops.

I would imagine that ou get a "quick" response with

SELECT AR.HIST WITH CLDATE "SH88]"

You can then use the results of this query (assuming there are any) to
feed a "normal" query using your original fields for customer code (?)
and transaction date

G'day Ross,

Ross Ferris wrote:
It's 7.4.3NT. Pretty recent.

That would work like a charm if the client only had a few thousand items
on file, but with better than half a million, you want to minimise the
file access. Scanning the index file isn't so bad, but when you start
with better than 500,000 records, the next select is going to drag a lot
of frames through memory.

I've made the mods now, and I think it should work OK, but I'm still
very browned off about it. It's a bloody poor showing.

Luke

Luke,

Withe structure you've indicated the

SELECT AR.HIST WITH CLDATE = "SH88]"

Should just return the records for customer SH88
This active select list should limit the search for transactions to
just for customer "SH88" (OK, and SH881, 2 ...928181, A etc - remember
that "*" seperator I suggested) .... must be A BIG client with 500,000
transactions ..... sounds like an outsourced debt collection system to
me ....

I'd also suggest you consider upgrading to 7.4.6 ASAP!

Luke:

Ahhhhh...this doesn't work in D3. You must access indexes with an exact
match (= "123]"). I don't believe the ">" or "<" work at all. This is the
way D3 indexes have always been. :-(

With this in mind, you'll have to design your indexes accordingly. For
instance, if you need some data based on year, create an index like
"YYYYMMDD" so you can access data with index = "YYYY]" or with index =
"YYYYMM]", etc.

Pathetic? Well, yes. But if you read some of the stuff Tony written here
you should wonder no longer. The Professional Services division used to
pass along all kinds of problems to engineering and management, but were
first ignored, then finally all let go (there are many stories in here
too)...a sort of "shoot the messenger" mentality. :-)

Bill

"Luke Webber" <luke (AT) webber (DOT) com.au> wrote ...

[snipped]