Can I JOIN without sorting?

[Killer42]

Hi all.

Is there any way to create a JOIN without sorting on the joined field?

I'm sure I have asked something similar before, but can't seem to find it by searching TSDN. I have a big table, records stored in date/time sequence. To save space I pulled some repetitive text out to a lookup table, and "inner joined" them in a query to get the equivalent of the original table. But this introduced two problems.

• The query is returned sorted by the join field, while I still want them in date/time order.
• Sorting the millions of records takes too long.

Adding an ORDER BY clause is not really an option, as it then takes even longer. In fact I have had Access "order bying" the query as I typed this message, and it's still going. That is WAY too long for a response to a user query.

[MMcCarthy]

Truthfully, I haven't a clue. :D Very helpful I know. Ade is probably better at this kind of thing than me.

Mary

[NeoPa]

This is not something you can specify explicitly.
It is managed by the SQL engine optimisation logic.
Clearly, for the engine to process through two recordsets JOINed on a particular field or fields, it will need (at the very least prefer) for both recordsets to be sorted on that field.
I can't think of a way even to trick it into doing it any different way, I'm afraid.
I'm sure that if you tried to implement the logic in DAO recordsets in VB you would find yourself limited in the same way.

[nico5038]

Hmm, interesting problem :-)
Did you try a:

select * from tblX a, tblY b
where a.ID = b.ID

to see or that gives the same effect ?

Nic;o)

[Killer42]

Hmm, interesting problem :-)
Did you try a:

select * from tblX a, tblY b
where a.ID = b.ID

to see or that gives the same effect ?

Thanks for the idea, I'll give it a try.

It's not really a huge problem, just very annoying ("If I wanted them sorted that way, I would have said so").

[nico5038]

For the query execution you might check the ShowPlan JET engine:
http://support.microsoft.com/default...;en-us;Q162701

Nic;o)

[Killer42]

For the query execution you might check the ShowPlan JET engine:
http://support.microsoft.com/default...;en-us;Q162701

Thanks again, nico. I will definitely have a look at this. Would be doing so now if it weren't for the fact that it's an EXE and therefore blocked. :(

Will have to download it tonight at home.

[nico5038]

Try:
http://www.altict.nl/Backups/Showplan.zip

Nic;o)

[Killer42]

Try:
http://www.altict.nl/Backups/Showplan.zip

Nope, still blocked. Apparently zip files are dangerous. :(

Never mind, I'll download it tonight. There's no real rush, anyway.