Tips and Tricks to Speed Up an SQL

Tips and Tricks to speed up an SQL

There are no "tricks".

Given the competition between the database vendors about which one is "faster", any "trick" that is always true would be implemented in the database itself. (The tricks are implemented in the part of the database called "optimizer").

There are only things to be aware of, but they typically can't be reduced into:

• Use feature X
• Avoid feature Y
• Model like this
• Never model like that

Look at all the raging questions/discussions about indexes, index types, index strategies, clustering, single column keys, compound keys, referential integrity, access paths, joins, join mechanisms, storage engines, optimizer behaviour, datatypes, normalization, query transformations, denormalization, procedures, buffer cache, resultset cache, application cache, modeling, aggregation, functions, views, indexed views, set processing, procedural processing and the list goes on.

All of them was invented to attack a specific problem area. Variations on that problem make the "trick" more or less suitable. Very often the tricks have zero effect, and sometimes sometimes flat out horrible. Why? Because when we don't understand why something works, we are basically just throwing features at the problem until it goes away.

The key point here is that there is a reason why something makes a query go faster, and the understanding of what that something is, is crucial to the process of understanding why a different unrelated query is slow, and how to deal with it. And it is never a trick, nor magic.

We (humans) are lazy, and we want to be thrown that fish when what we really need is to learn how to catch it.

Now, what specific fish do YOU want to catch?

Edited for comments:
The placement of your predicates in the where clause makes no difference since the order in which they are processed is determined by the database. Some of the things which will affect that order (for your example) are :

• Whether or not the query can be rewritten against an indexed view
• What indexes are available that covers one or both of columns NUMBER and DATE and in what order they exist in that index
• The estimated selectivity of your predicates, which basically mean the estimated percentage of rows matched by your predicate. The lower % the more likely the optimizer is to use your index efficiently.
• The clustering factor (or whatever the name is in SQL Server) if SQL Server factors that into the query cost. This has to do with how the order of the index entries aligns with the physical order of the table rows. Better alignment = reduces cost for higher % of rows fetched via that index.

Now, if the only values you have in column NUMBER are 156, 646 and they are pretty much evenly spread, an index would be useless. A full scan would be a better alternative.

On the other hand, if those are unique order numbers (backed by a unique index), the optimizer will pick that index and drive the query from there. Similarily, if the rows having a DATE between the first and second of January 2011 make up a small enough % of the rows, an index leading with DATE will be considered.

Or if you include order by NUMBER, DATE another parameter comes into the equation; the cost of sorting. An index on (NUMBER, DATE) will now seem more attractive to the optimizer, because even though it might not be the most efficient way of aquiring the rows, the sorting (which is expensive) can be skipped.

Or, if your query included a join to another table (say customer) on customer_id and you also had a filter on customer.ssn, again the equation changes, because (since you did a good job with foreign keys and a backing index) you will now have a very efficient access path into your first table, without using the indexes in NUMBER or DATE. Unless you only have one customer and all of the 10 million orders where his...

Best way to speed up MS SQL server

The question is quite general, so a general answer will fit:

• Use fast hardware. Have as much RAM as possible.
• If using a 32 bit OS, have AWE working, especially if the machine is a dedicated DB server.
• If using 64 bit OS - even better. Much more RAM could be put to good use.
• Analyze indexes and application performance regularly. When needed, rebuild indexes, create new ones, delete old ones, etc.
• Learn the different types of indexes - Clustered, Partitioned, etc.
• When necessary, use indexed-views.
• DB Tuning advisor could help.
• Use a LoadBalancing solution to have multiple servers running the DB.
• Understand the nature of the application - OLAP apps has other needs than DataWarehousing apps. This would affect the structure of the tables, disk spanning, etc.

What generic techniques can be applied to optimize SQL queries?

• Use primary keys
• Avoid select *
• Be as specific as you can when building your conditional statements
• De-normalisation can often be more efficient
• Table variables and temporary tables (where available) will often be better than using a large source table
• Partitioned views
• Employ indices and constraints

Ways to speed up SQL query on large table

For this query:

SELECT COUNT(*) 
FROM TABLE_NAME 
WHERE time > '2020-11-14T23:08:05.553752Z' AND num = '11112222222'

You want a multi-column index on (num, time). The ordering of the columns in the index matters. You want the column that has the equality predicate first, then the column that has the inequality predicate.

I am a bit suspicious about the datatypes. If num is of a numeric datatype (it looks like int), then you should filter it against a literal number:

num = 11112222222

SQL speed up performance of insert?

To get the best possible performance you should:

• Remove all triggers and constraints on the table
• Remove all indexes, except for those needed by the insert
• Ensure your clustered index is such that new records will always be inserted at the end of the table (an identity column will do just fine). This prevents page splits (where SQL Server must move data around because an existing page is full)
• Set the fill factor to 0 or 100 (they are equivalent) so that no space in the table is left empty, reducing the number of pages that the data is spread across.
• Change the recovery model of the database to Simple, reducing the overhead for the transaction log.

Are multiple clients inserting records in parallel? If so then you should also consdier the locking implications.

Note that SQL Server can suggest indexes for a given query either by executing the query in SQL Server Management Studio or via the Database Engine Tuning Advisor. You should do this to make sure you haven't removed an index which SQL Server was using to speed up the INSERT.

If this still isn't fast enough then you should consider grouping up inserts an using BULK INSERT instead (or something like the bcp utility or SqlBulkCopy, both of which use BULK INSERT under the covers). This will give the highest throughput when inserting rows.

Also see Optimizing Bulk Import Performance - much of the advice in that article also applies to "normal" inserts.

Tips to enhance the performance of the following simply SQL query

The substring function could be slowing your query down.

Would something like the below work?

time >= "20110101" 
and time < "20120101"

---

Related Topics