By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Can I JOIN without sorting?

Expert 5K+
P: 8,435
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.
Feb 6 '07 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Truthfully, I haven't a clue. :D Very helpful I know. Ade is probably better at this kind of thing than me.

Mary
Feb 6 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 6 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Feb 7 '07 #4

Expert 5K+
P: 8,435
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").
Feb 7 '07 #5

nico5038
Expert 2.5K+
P: 3,072
For the query execution you might check the ShowPlan JET engine:
http://support.microsoft.com/default...;en-us;Q162701

Nic;o)
Feb 7 '07 #6

Expert 5K+
P: 8,435
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.
Feb 7 '07 #7

nico5038
Expert 2.5K+
P: 3,072

Expert 5K+
P: 8,435
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.
Feb 8 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.