Top 20 Query Optimization Techniques

Some tips on query performance.
  1. Create an index on huge tables (>1.000.000 rows)
  2. Use EXISTS() instead of COUNT() to find an element in the table
  3. SELECT specific fields instead of using SELECT *
  4. Avoid subqueries in WHERE clause
  5. Avoid SELECT DISTINCT where possible
  6. Use WHERE clause instead of HAVING
  7. Create joins with INNER JOIN (not WHERE)
  8. Use LIMIT to sample query results
  9. Use UNION ALL instead of UNION wherever possible
  10. Use UNION where instead of WHERE ... OR ... query
  11. Run your query during off-peak hours
  12. Avoid using OR in join queries
  13. Choose GROUP BY over window functions
  14. Use derived and temporary tables
  15. Drop the index before loading bulk data
  16. Use materialized views instead of views
  17. Avoid != or <> (not equal) operator
  18. Minimize the number of subqueries
  19. Use INNER JOIN as little as possible when you can get the same output using LEFT/RIGHT JOIN
  20. Frequently try to use temporary sources to retrieve the same dataset
Other popular notes

Popular posts from this blog

SharePoint PowerShell Quick Guide

If you manage an online SharePoint tenant you will run into a lot of situations where PowerShell will be the surest way to accomplish your tasks. Permanently deleting a site via PowerShell Remove-SPOSite -Identity https://site.sharepoint.com Managing Hub Sites 2,000 hub sites per tenant A modern SharePoint site can be promoted to a Hub site As a team site As a communications site Global navigation added automatically Configure who is allowed to add sites to the hub Can also configure an approval workflow Hub site can be registered in the admin center Admins can choose who can associate sites with hub - Users, Security Groups, Office 365 Groups Hub site can be registed with PowerShell (to make it a hub site) Register-SPOHubSite -Site https://site.sharepoint.com/sites/hubsite -Principals $null To associate other sites to a hub Unregistering a hubsite disconnects it from the hub Unregister-SPOHubSite     -Identity https://sites.sharepoint.com/sites/an-existing-associated-site In ...
Read more

Dependency Injection in .NET

Here are some noteworthy takeaways from a course on Dependency Injection in .NET Core. DI Container Basics A container isn't a requirement but simplifies management of dependencies Services are registered at start up and resolve at runtime when they are required Container creates and disposes of instances while maintaining them for a specific lifetime Two primary interfaces for dependency injection in the .NET container IServicesCollection  - registers and configures a service IServiceProvider  - provides a mechanism for resolving a service at runtime Apply dependency via the constructor Register all services with the container Understand the service lifetimes Scoped Transient Singleton Identifying Dependencies to Register in the Container First, locate the  new  keyword usage. Is it the instance a dependency?  If a class instantiates another class whose methods are used, it's a dependency. Repeat the above approach throughout your app code (there should be code...
Read more

Git Guide

A few handy commands in one handy place. Updating .gitignore and enforcing it When you want to ignore additional files or folders, you have to update your . gitignore . After you make the additions or changes, you have to remove the cached files from the local repository, add them back, commit again to the local and push to the remote repositories: git rm -r --cached . git add . git commit -m "Ignore list update" To push the locally committed changes back to your remote repo: git push PowerShell script to sync with git This is a handy script I keep on my desktop (or an easy to find document folder) which I launch whenever I work with documents outside of a coding tool that need to be quickly pushed to Git. Write-Host 'Pushing changes to Git for my app!'; $dir = "C:\Users\username\source\repos\reponame\" Set-Location $dir Write-Output ''; Write-Output 'Adding locally...' git add . Write-Output ''; Write-Output "Committing locally....
Read more