Hello!
I am aware that if you sort a table per specific column before you run your query that uses that specific column, the speed will be much faster.
Are there any other techniques to optimise speed? I know SQL supposedly does that for you but would like to leverage concepts and techniques for Excel, Python etc too.
Thanks!
SQLor - sounds like there's a misunderstanding here. A table is 'sorted' by it's clustered key, but there can be only one.
The only way that running one Select query will make the next Select query run faster, is if the data in the first query went from disk to cache and the second query needed that data.
Adding indexes is a technique to optimize speed.
Let me clarify some more - tables are not sorted. If you do not specify an ORDER BY for the query - then the results will not be in any specific order. SQL Server can return the results in any order it determines - which sometimes appears to be in clustered index order.
As soon as your query goes parallel - all bets are off on the order of the results, unless you specify an ORDER BY for that query.
As for leveraging what works in SQL Server for Excel - no way that can be done. They are different products and handle data storage in a completely different manner. As for Python - that is a scripting language and not a database so there really isn't any comparison.
Thanks, do you know any tutorial to leverage that? I.e. what type of indexes, how to create them, how to use them in the query, etc?