I have a query that takes 14 mins to run. It has functions, a cross join, Distinct sort, and geometry type STIntercepts in the where clause.
I am fairly new to SQL, so had never heard of spatial SQL and have never seen a cross join used in an actual query. I am wondering if there is a "no need to reinvent the wheel" approach to optimizing queries. Like "first thing most people do is view the execution plan and find what has the highest cost, then start there", or "you always remove the distinct first", etc.
This is a very broad question, so just going to give you some advices from my experience.
1) As you said, look at the query plan and see where the times is being spent. Table Scans is the first thing that should raise an alarm
2) Check the columns you are WHEREing and JOINin on are indexed and that you are not using functions on those columns when doing the WHERE or JOIN as they will prevent the index to apply
3) Also check you are not doing implicit type conversions in WHEREs and JOINs, again they will stop indexes to work
4) Are you using views? A high nesting level of views can stop the query planner to do its work. Try "unrolling" your views
5) Are you using ORs in your conditions? ORs can also cost a lot. You may try to remove the ORs and run multiple queries and UNION them
6) Try to simplify your query and use temp tables (or variable tables) to keep subquery results. If you are not working with big results it could speed things up too.