Hi Mark
My answer is - it depends - on what you are trying to achieve
As it happens - an SQL query that I just wrote - was sending me nuts
I had "finished" it - resulting in approximately 25,000 records and the results look good
Then I was asked to include some more information using a join to another file
This should have been a one to one situation, so the numbers should have been 25,000 still
The numbers jumped to approximately 250,000
So naturally - I looked at the join
Couldn't see anything wrong with that
To cut a long story short, I tried looking at the data, sorted down in numerous ways
I f I had said look at these 100 records, then sort, I would not have found out that (for whatever reason) we had an invoice with an invoice number of zero (shouldn't have happened, but there it is)
And new orders ALL have an invoice number of zero - hence the jump
Long winded explanation - but apropos, I believe
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Mark Villa
Sent: Thursday, September 24, 2020 6:09 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] help with SQL performance when using ORDER BY
Hi all,
I have been doing a lot of "SQL checking" recently and without breaking out the manuals, I am just curious what you guys do when you use ORDER BY. With tables greater than 10 or 20 mil rows it really tanks. Otherwise, my responses are usually subsecond.
It makes the SQL engine (and myself) look dumb when I can't say "select these 100 rows" then ORDER THEM, don't look at 20 million rows, don't need anything sorted but the final set.
--
Thank You,
Mark Villa
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
