My advice is to start an SQL performance monitor, run your statement, end the monitor, and then analyze. The optimizer should be asking for some indexes. The indexes it needs for record selection/sorting are your biggest hitters. You could also create those that are needed for statistics as an option. My procedure is to sometimes create everything its asking for, and then pairing it back on the things I think it doesn't really need, watching for performance to tank.
-----Original Message-----
From: MIDRANGE-L <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: help with SQL performance when using ORDER BY
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
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
