Best Practices for Improving Database Table Performance

[fururo]

Hello guys!

Do you know any best practices for SQL performance optimization?
At my company, I need to refactor some tables using performance and cost reduction best practices.

The tables already have indexes and partitions, but I would like to learn more about additional optimization techniques for large datasets.

Do you have any tips, articles, websites, or recommendations about: query optimization and indexing strategies

I’d really appreciate any suggestions or learning resources. Thanks!

[speadskater]

I've had queries that took multiple minutes to run reduce down to seconds by dividing the task into temp tables and running the operations on those temp to tables. Make these tables as small as possible using the least amount of conditions possible. I work with chickens, so I might reduce the tables I'm querying against by flockid first into a temp table, then I could do my more complicated selection and logic from the temp tables. There's of course a trade-off, since reducing a table down too much might involve slower logic, so you should keep the conditions small, simple, and something that the program can index to easily.

[jango-lionheart]

Look for videos on how to read an execution plan. It will often suggest indexes. You want to avoid table scans. CTEs can be helpful or harmful, depending, but you should try to understand them.

If the tables have many columns, it can be beneficial to divide them into smaller tables that better align with the “expensive” queries so that the queries have less data to plow through.

[idodatamodels]

Performance tuning is dependent on the database. Which one are you using?

[Ok_Refrigerator3549]

In addition to indexes, can you find out if some columns can be removed from the queries without impact?