Increasing the speed of SQL queries

Immediately make a reservation, the queries in the examples are Transact SQL, it is somehow more familiar to me =)

But principles, in general, should work everywhere.

The article does not claim to be new, and even more so, to be complete. I just tried to recall common errors or omissions in queries that lead to slow work with the database.



The search showed that the article partially overlaps with this topic , but not in all =)





')

Types of data in the fields

The most obvious thing is not to use data types "with a margin". That is, if we have an “ICQ” field of the VarChar type, it makes no sense to make it longer than 10 characters. Similarly, if there is a foreign key to the directory, in which there are only a few entries, it makes no sense to give it the Int type, SmallInt will suffice. Despite the obvious error, is ubiquitous.

Use * in request

Generally speaking, there was a lot of controversy on this topic, but I try not to use “*” in SQL queries.

First, an explicit enumeration of selectable fields improves the readability of the code.

Secondly, the sample does not always need all the fields in the table. And if we link several tables in a query, then almost always the “Select *” construction will pull a bunch of unnecessary fields from the database into the sample, for example, the keys for which the tables are linked. I once faced a situation where file names were stored in a table in a text field, and their contents were stored in a binary field. And the request, which was just to issue a list of files, also loaded their contents into the server’s memory. Braked it is godless.

Use cursors

People accustomed to imperative programming languages, cursors in SQL - like a balm for the soul. After all, the cycle of records is so familiar and understandable. Sometimes because of this, a slow code is born. Example:

Suppose we have a table with 4 fields in it: ID, Value1, Value2, and Summa. Task: recalculate the table, that is, calculate the value of Value1 + Value2 and put in the Summa field.

Option 1, with a cursor:

1. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
2. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
3. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
4. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
5. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
6. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
7. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
8. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
9. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
10. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr
11. DECLARE @ID int DECLARE @Val1 int DECLARE @Val2 int DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT ID, Value1, Value2 from Table1 OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2 WHILE @@FETCH_Status=0 BEGIN UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID FETCH #curr INTO @ID, @Val1, @Val2 END CLOSE #curr DEALLOCATE #curr

Option 2, no cursor:

1. UPDATE Table1 SET Summa = Value1 + Value2

It is clear that in such a simple example, option 2 is obvious. But with more complex calculations, for ease of implementation, the programmer chooses the option with the cursor - and noticeably loses in speed.

Using indexes

No comments. About indexes forget pretty often, especially beginners.

Using stored procedures

When performing complex calculations that use many values ​​from the database, it is better to arrange them as stored procedures on the server, rather than compute on the client side - why transfer the initial data to the client for calculations when you can only transfer the result.

Using temporary tables

Periodically, especially when working with calculation results or summary data, it is easier and faster to run a complex query once for retrieving all the necessary data, save its results into a temporary table and continue to work with it rather than launch a complex and slow query each time.



Perhaps this is all that came to mind offhand, if the article is of interest to anyone, you can still remember.