Немного о повышении производительности БД: Практические советы

Неплохо, в закладки. спасибо

А как же всё связанное EXPLAIN? Без этого получается однобоко — результат был бы более наглядно.
А что насчет хаков по принуждению тупого оптимизатора к использованию конкретных индексов?

Но спасибо, людей носом тыкать в одну ссылку гораздо удобнее, перечислены ключевые вещи.

Со временными таблицами нужно быть осторожным. Во-первых, СУБД может сама решить создать временный индекс и(или) будет использовать знания о сортировке. Во-вторых, в какой-то момент СУБД может решить изменить in-memory таблицу на дисковую. В третьих, размер временной таблицы может неожиданно превысить размер раздела для временных файлов, а установление этого факта может оказаться не тривиальным.

Извините, конечно, но это рекомендации начального уровня.

Если в системе, напротив которой выполняется нагрузочное тестирование, они не были изначально выполнены — то это значит, что перед нами — однопользовательский проект студенческого уровня, а не бакенд системы массового обслуживания.

1. Осторожно с индексами. Они должны быть продуманы. Бывает ускоришь один селект, а десять упадут.

2. Bulk Insert'ы для загрузки таблицы. Если это еще и процесс например миграции, то желательно снести все индексы перед инсертами.

6. На счет count vs exists можно ограничить count по rownumber = 1. Так же exists может быть намного быстрее чем конструкция IN (), но может и не быть.

7. Писать такие запросы, которые кэшируются и не требуют парсинга каждый раз, когда выполняются. Как пример preparedstatement

В целом надо смотреть на планы.

Еще подготовленные запросы — очень полезная штука.
И про лимиты не стоит забывать.

А вообще, если требуется серьезно поднять производительность работы БД(без повышения производительности аппаратной части), лучшим будет изучить хотя бы поверхностно, как ведет себя конкретная БД в определенных случаях(в транзакции; при первичной записи; что и в каких случаях попадает в кэш БД; как работает журналирование;… прочее...). Чуточку в настройках покопаться. И разобраться, как все вышеперечисленное между собой связано.

Да и в общем особенности реализации конкретной БД.

Многие оптимизации сами придут в голову, при понимании работы конкретной БД.

//------------------------
Ну и на мой взгляд, под производительностью стоит подразумевать не только скорость обработки запросов, но и объем потребляемых ресурсов. Бывает и такое, что ваш не самый частый запрос стал обрабатываться чуток быстрее, но выбил из кэша много полезного и часто используемого.

Оптимизатор может делать все это за вас, а вы пытаясь переписать код в пустуе потратите время. Оптимизатор зачастую отлично справится с COUNT и построит такой же план как и для EXISTS.
IF (SELECT count(*) FROM Users WHERE active = 1) > 0

|--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1006] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1006] = [PROBE VALUE]))
|--Constant Scan
|--Index Scan(OBJECT:([TestDB].[dbo].[Users].[IX_Users_Email]), WHERE:([TestDB].[dbo].[Users].[Active]=(1)))

IF EXISTS (SELECT 1 FROM Users WHERE active = 1)

|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1005] = [PROBE VALUE]))
|--Constant Scan
|--Index Scan(OBJECT:([TestDB].[dbo].[Users].[IX_Users_Email]), WHERE:([TestDB].[dbo].[Users].[Active]=(1)))

Свободно преобразует подзапрос к джойну:
select top 100 CourseRegistrationID, (SELECT Email FROM Users WHERE UserID = cr.UserID) FROM CourseRegistrations cr

|--Top(TOP EXPRESSION:((100)))
|--Compute Scalar(DEFINE:([Expr1007]=[TestDB].[dbo].[Users].[Email]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([cr].[UserID], [Expr1008]) WITH UNORDERED PREFETCH)
|--Index Scan(OBJECT:([TestDB].[dbo].[CourseRegistrations].[IX_CourseRegistrations_UserID] AS [cr]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([TestDB].[dbo].[Users].[PK_Users]), SEEK:([TestDB].[dbo].[Users].[UserID]=[TestDB].[dbo].[CourseRegistrations].[UserID] as [cr].[UserID]) ORDERED FORWARD)

Оптимизировать не имея статистики и не глядя в планы исполнения изначально является глупой затеей. Соберите статистику, проанализируйте план запроса и только после этого начинайте оптимизацию.

Если условия слишком сильно зависят от параметров, а параметры могут влиять на логику, в таком случае иногда выгоднее иметь не preparedstatement, а динамически сформированный запрос.

--Пример выше можно переделать, используя один INSERT или UPDATE

И тут вы быстро упретесь в ограничение на размер запроса в 8 Кб.

Лучше всего пихать в xml и передавать как параметер, тогда и 2 Тб можно вставить одним запросом.

C#:

                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = cnn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "INSERT INTO CoreAccount SELECT X.C.value('@name', 'nvarchar(255)') FROM (SELECT @data AS XML_DATA) DATA CROSS APPLY DATA.XML_DATA.nodes('//value') as X(C) ";

                        cmd.Parameters.Add("@data", SqlDbType.Xml);

                                cmd.Parameters[0].Value = userList;

                                cmd.ExecuteNonQuery();
}

Очень много споров и обсуждений касательно COUNT(1) or COUNT(*)?
Я заметил, что вы используете COUNT(1).
Прокомментируйте пожалуйста, что же всё таки предпочтительнее/быстрее? * or 1 ?)