GoGreen PC Tune-Up™
Learn More

Insta-Install™
this is how ssl encrypt our websites
MTNCOMP | List View | Table View | myBlog (1786 Entries)
myBlog Home

Blog


Faster SQL Queries, 3 tips

SQL

by Mountain Computers Inc., Publication Date: Monday, October 21, 2019

View Count: 1390, Keywords: SQL Queries, Faster, 3 rules, Hashtags: #SQLQueries #Faster #3rules



The following I was just reading online from developer.com news (see below at the link and text below that), and wanted to comment; #1 true, #2 no so sure, #3 sort of makes sense.
 
For me, my rule of 3 is easier:
 
1. no more than three indexes per table... max... now if JSON, keep bloat out of your nest...
2. use strict data typing and strict camel code in SQL queries. don't let someone turn off options explicit per se.
3. use native connections (e.g. OLE-DB) ... 1-2-4-8 seconds or less to pull 1mil records versus 45 seconds or more using other methods. use raw connections for good reason; hit the database once, not multiple times, and keep your connections limited, closed when done, and watch your query performance...
 
more to come...
 
http://www.devx.com/DevX/tip-3-more-hints-for-building-faster-sql-queries.html

For building faster SQL queries, follow these hints.

  1. 1. Use Smaller batches

    Always try to delete data or update data in smaller batches. If you try to delete thousands of records in one go, it will impact performance. If you try to update large batches of data in one go, it will also negatively impact performance and speed. Break these up in smaller chunks.

  2. Stop being trigger-happy!

    By using triggers, you may end up locking more than one table at the same time, until the trigger has completed its work. Sometimes a trigger is unavoidable, but only sometimes. Rather, try to split the logic so that only limited resources are locked at a given time.

  3. Avoid nesting views

    As the name implies, nested views are views inside existing views. The more nested a view becomes, the more data has to be returned for a single query and this can really cause your whole database to lag quite quickly. Do not nest views.

 
 

if you found this article helpful, consider contributing $10, 20 an Andrew Jackson or so..to the author. more authors coming soon
FYI we use paypal or patreon, patreon has 3x the transaction fees, so we don't, not yet.

© 2025 myBlog™ v1.1 All rights reserved. We count views as reads, so let's not over think it.