Одна таблица 400 ГБ, один запрос - нужны идеи настройки (SQL2005)

При вызове через PHP запрос select возвращает Resource, который по существу является указателем на первую строку результата. Чтобы получить данные, вам нужно будет использовать mysql_fetch_array, в результате чего вы получите строку, указанную указателем.

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

Итак, ваш код будет чем-то вроде этого

$connection = mysqli_connect(...);
$query = ""; //put your query here
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {
    //$row will contain the data of the row in an array format. Use it here.
}

Кроме того, не используйте функции mysql, поскольку они устарели сейчас. Используйте MySQLi или PDO.

13
задан Andrew Barber 6 August 2013 в 14:06
поделиться

24 ответа

Это походит на хорошее развлечение.

Несколько вопросов:

  • , Почему Вы выбирали эти типы? varchar, деньги, bigint, интервал, bool? существует ли причина или просто готова добавить некоторое хорошее развлечение?
  • Случайно мы могли добраться, взглянули оператор вставки, или TSQL или bulkinsert?
  • можно ли сказать, как часто вставка происходит (действительно ли это - объем, или случайный?)
  • Делает поле DateTime, содержит дату вставки?
  • , Как, Вы приехали в это? (один человек/день, думающий или команда 20 человек, работающих как сумасшедший в течение прошлых трех месяцев?)

Несколько фактов кажется важным для меня:

  • Вы вставляете миллион строк каждый день
  • , Вы хотите только последний миллион данных

, Несколько комментариев прибыли ко мне:

  • , если Вы интересуетесь только последними данными, удаляя/архивируя бесполезные данные, мог бы иметь смысл (запускайте с нуля каждое утро)
  • , если существует только одна "вставка" и только один "читатель", можно хотеть переключиться на специализированный тип (hashmap/list/deque/stack) или что-то более разработанное на языке программирования.
0
ответ дан call me Steve 6 August 2013 в 14:06
поделиться
  • 1
    Привет Michael, Вы didn' t упомянутый о " ProcessBuilder" в Вашем ответе, который является другим способом создания процесса в Java. – mhshams 28 February 2011 в 06:44

Покажите вывод плана запросов - любое настраивающее приключение, которое не запускается существует несчастный случай.

1
ответ дан keithwarren7 6 August 2013 в 14:06
поделиться

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

Также я думаю, что могло помочь, отправили ли Вы несколько строк данных и демонстрационного запроса, если это возможно.

0
ответ дан Shawn 6 August 2013 в 14:06
поделиться

Я думаю, что кластерный индекс на K7 является единственной вещью любого значения. Остальная часть Вашего, где пункт имеет такую низкую селективность, что это - пустая трата времени.

, Если Вы не можете использовать в своих интересах некоторые специальные знания своих значений (возможно, k5 только верен если k4 < 0, или что-то), Вы в значительной степени смотрите на сканирование кластерного индекса. Мог бы также сделать его полем, которым Вы заказываете.

Рассмотрение небольших чисел отличных значений в k3 - k6, необходимо было бы, вероятно, только считать < 1,5 миллиона строк для получения главного 1 миллиона. Это является, вероятно, лучшим, Вы собираетесь сделать - тем более, что для любого другого плана были бы нужны Вы для упорядочивания k7 так или иначе для оценки пункта TOP.

0
ответ дан Mark Brackett 6 August 2013 в 14:06
поделиться
  • 1
    @HH: поскольку я сказал, you' d запускают его точно так же, как Вы запускаете любую программу Java через командную строку: " Java - путь к классу CP package.name. MainClass". но " скомпилируйте код как отдельный application" не имеет большого количества смысла, конечно, он должен быть скомпилирован, но Java действительно не имеет понятия " applications" как отличные объекты. – Michael Borgwardt 5 January 2010 в 12:58

Добавьте единственный индекс со столбцами k1-k6 в нем; это должно быть лучшим.

кроме того, если можно выполнить sp_updatestats перед каждым запросом.

0
ответ дан Booji Boy 6 August 2013 в 14:06
поделиться
  • 1
    @HH: не, что на *отклоняют машины, называя Runtime.exec и я предполагаю ProcessBuilder.start (), это - ветвление (). Это - Ваш процесс Java, подвергается ветвлению () для создания нового процесса. – Yaneeve 5 January 2010 в 16:56

Почему Вы кластеризировались на первичном ключе?
, Какие столбцы могут быть ПУСТЫМИ?
, Каковы длины VARCHAR?
, Что план запросов дает Вам теперь?

Вы затрудняете нас путем предоставления бессмысленных имен столбцов.

, Даже если кластерный индекс является надлежащим, более выборочное поле должно быть на первом месте.

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

3
ответ дан dkretz 6 August 2013 в 14:06
поделиться
  • 1
    Я просто узнал недавно, что regsvr32.exe знает, чтобы сделать " право thing" так Вы don' t на самом деле должен определить версии на 32 или 64 бита. Мысль I' d доля. – Kev 1 February 2011 в 12:47

Используйте SQL Profiler для разработки, какие индексы создать, он разработан, чтобы решить, что информация для Вас и предлагает улучшенные профили выполнения.

у Вас есть внешние ключи на k3, k4?

Попытка, поворачивающаяся k1, k2 в ints и делающая их внешние ключи, это будет использовать намного меньше устройства хранения данных для одного, я думал бы, и я думаю, что это должно быть более быстро (хотя я могу быть неправым там, я предполагаю, что SQL Server кэширует эти значения). Главное, легче, если когда-нибудь необходимо обновлять значение. Вы просто меняете имя строки внешнего ключа - Вы не должны тогда обновлять 100 миллионов первичных ключей, или что бы то ни было.

Один хороший совет для улучшения скоростей запроса должен вставить подзапрос, который сокращает recordset размер к более managable.

В:

SELECT TOP(g) d1 
FROM table WITH(NOLOCK)  
WHERE k1 = a  WHERE k2 = b  WHERE k3 = c  WHERE k4 = d  WHERE k5 = e  WHERE k6 = f  
ORDER BY k7

то, Которое, я предполагаю, должно быть

SELECT TOP(g) d1 
FROM table WITH(NOLOCK)  
WHERE k1 = a AND k2 = b  AND k3 = c AND k4 = d AND k5 = e AND k6 = f 
ORDER BY k7

, вероятно, будет некоторый набор данных, которые сразу сокращают recordset по сравнению с, скажем 10 миллионов строк, к 10 000.

, например,

SELECT TOP(g) d1 
FROM (SELECT * 
      FROM table k1=a AND k2=a WITH(NOLOCK)) 
WHERE AND k3 = c AND k4 = d AND k5 = e AND k6 = f 
ORDER BY k7

Это предполагает, что можно сократить начальный набор данных в широком масштабе один или два из, ГДЕ аргументы - который почти бесспорен.

DBAs, вероятно, имеют больше, лучшие решения!

3
ответ дан C B 6 August 2013 в 14:06
поделиться

Необходимо создать индекс, который сократит количество возможных строк, возвращенных как можно быстрее.

Поэтому самый простой индекс для создания был бы на столбце k4, как это может иметь к самому большому количеству различных значений. Только необходимо индексировать первоначальную подстроку k4, где математические ожидания k4 отличаются в рамках той подстроки. Это уменьшит размер индекса и ускорит доступ.

k7 должен также быть индексирован, поскольку это значительно увеличит скорость orderby пункта.

Вы, возможно, также должны экспериментировать (я знаю, я знаю, Вы сказали, не экспериментируют, но это может помочь...) с созданием нескольких индекс столбца в этом порядке: k4, k1, k2, k3. Это, снова, должно сократить количество возможных строк, возвращенных как можно быстрее.

0
ответ дан Chris 6 August 2013 в 14:06
поделиться
  • 1
    Системное свойство java.class.path не содержит java.exe, Вы, вероятно, имеете в виду java.home/bin. – Andreas Ågren 14 May 2013 в 09:09

Это, является трудным дать Вам очень значимый ответ. Вы посмотрели на дисковые затраты ввода-вывода? Где Вы сохраняете файлы базы данных - возможно, это - ввод-вывод, который останавливается? Существует столько переменных здесь, которые могут влиять на производительность. Возможно, они, время потрачено Вашим UI или отобразить ли данные, возможно, это время, взяты Сетью?

, Возможно, самый легкий путь - где Вы будете видеть, большинство усилений должно будет разделить таблицу - если Вы идете Enterprise Edition SQL Server 2005.

Снова, не имея доступа к фактическим планам запросов, perfmon статистика это является могущественным трудно, чтобы сказать Вам точно, что является проблемой. Ваш вопрос просто не дает нам достаточно для продолжения - и все - просто предположение.

3
ответ дан no_one 6 August 2013 в 14:06
поделиться
  • 1
    Слишком плохой there' s никакой путь невиртуальный метод может определить, что это нужно назвать без callvirt, так как было бы полезно позволить методам как string.IsNullOrEmpty быть применимыми на пустых строках. – supercat 14 May 2013 в 22:12

Ваш план запросов в основном показывает следующее:

  • первый op является кластерным индексом, ищут со сравнениями на k1, handId?, d1, 111-секундный k3-k6
  • является всем индексным сканированием на k1, handId? и Треть k7
  • является, конечно, соединением для создания набора конечного результата
  • Порядок сортировки
  • TOP n (Фильтр)

, план предлагает индекс, который должен улучшить перманент на 81% - k1, k4, k5, k6, k3 + включают d1 & k7. Я не знаю, сколько времени это взяло бы, чтобы создать такой индекс и видеть результаты, но как я прокомментировал здесь, это эффективно удвоит размер Вашей таблицы, просто потому что почти каждый столбец присутствует в индексе. Также вставки будут медленнее.

, Поскольку многие люди предложили, разделение является лучшей стратегией здесь, например, заставьте одну таблицу, например, иметь значения k3 от 1 до 3, другого от 4 до 7 и третье от 8 до 10. С SQL Server Enterprise разделение сделано с помощью ограничения CHECK на этот столбец, оптимизатор запросов определит который таблица из n сканировать/искать в зависимости от значения параметра для столбца.

0
ответ дан liggett78 6 August 2013 в 14:06
поделиться
  • 1
    Его вопрос не о java.lang. Процесс, но как запись приложение, которое может клонировать себя. – Aaron Digulla 5 January 2010 в 12:54

Прежде всего проведите день с SQL Profiler, работающим в фоновом режиме. В конце дня сохраните данные трассировки в файл и имейте поток мастера Оптимизации по нему и оцените свой текущий индекс. Это должно сказать Вам, что, если изменение индексируемых полей, порядка сортировки, и т.д. может дать Вам любые значительные усиления. Не позволяйте мастеру внести изменения. Если увеличение производительности процента выглядит значительным (> 30%, по моему скромному мнению), разрешение, и внесите изменение сами.

Ваш индекс должен входить в большую сторону. Можно хотеть запланировать задание (в течение ночи, пару раз неделя), чтобы сделать следующее:

  • данные Перемещения старше определенного возраста к таблице истории
  • дефрагментируются, индекс
  • повторно вычисляют метрики

, Который сохранит его быстрым, как только Вы настроили индексы.

4
ответ дан Rob Allen 6 August 2013 в 14:06
поделиться

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

Однако, если бы это был я, я попробовал бы индекс на K3, K4 (В порядке, который Вы обычно запрашиваете) (у Вас уже есть K1 и индексированный K2), и отдельное, индексированное на K7. Я не делаю живо, добавление булевых полей улучшило бы индексную производительность.

Помнят, чем больше индексов, тем медленнее вставки будут. С количеством вставок Вы имеете, этот ia реальное беспокойство. Так действительно единственный реальный ответ - то, что необходимо будет экспериментировать с собственными данными и аппаратными средствами и найти что работы лучше всего для персональной ситуации. То, что это не было тем, что Вы хотели услышать, не делает его никем менее верный, Индексация очень зависит от того, как Ваше приложение на самом деле работает и структура Ваших данных.

0
ответ дан HLGEM 6 August 2013 в 14:06
поделиться
  • 1
    @sleske: Aaron почти прав, я хочу выполнить класс в своем коде как процесс в то время как остальная часть классов как другой процесс. В некотором смысле его ветвление (). – HH. 5 January 2010 в 13:58

Я сказал бы, что 8 ГБ являются недостаточным количеством RAM для таблицы на 400 ГБ. Сервер не имеет никакого шанса сохранить соответствующие данные в памяти если один индекс один взятия 5-8 ГБ. Таким образом, существуют партии и много чтений жесткого диска, которые делают запрос медленным.

, По-моему, увеличение суммы RAM и наличия базы данных по быстрому RAID (возможно, splitted на нескольких НАБЕГАХ?) помог бы большинству.

РЕДАКТИРОВАНИЕ: быть уверенным, каково Ваше реальное узкое место, Монитор производительности запущенного Windows .

0
ответ дан VVS 6 August 2013 в 14:06
поделиться
  • 1
    @Aaron: Что заставляет Вас полагать, что плакат хочет приложение к " клон itself"? и что это даже означает? Что-то как ветвление () в Unix? – sleske 5 January 2010 в 13:06

Спасибо все для Вашей справки.

я сделал 3 редактирования к ошибкам в исходном сообщении.

1) то, ГДЕ, должно было быть ANDs.

2) k4 должны были быть ДЕНЬГИ не VARCHAR. Кроме того, k1 имеет длину 3.

3) k2 не должен быть в операторе Where. Как doofledorfer правильно указывает, не имеет никакого смысла иметь любого другого ГДЕ операторы кроме полного первичного ключа.

Вот ответы на Ваши вопросы:

, Почему Вы кластеризировались на первичном ключе?

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

, Какие столбцы могут быть ПУСТЫМИ?

Ни один.

, Каковы длины VARCHAR?

я сделал ошибку с типами столбца. Единственный остающийся VARCHAR имеет длину 3.

, Что план запросов дает Вам теперь?

Отправленный в следующем сообщении.

Помогают мне понять больше о таблице. если Ваш PK является k1, k2, Вам не придется выбрать никаким другим столбцом для получения абсолютно уникальной записи. Это было ошибкой. k2 часть PK не находится в операторе Where.

Знание, почему Вам нужен приблизительно миллион возвращенных записей, могло бы помочь мне предоставить лучшее решение.

база данных содержит ежедневные записи (d1 Столбец текста) или данные. Людям нужен доступ к большим суммам этих данных для выполнения их собственных отчетов. Они должны отфильтровать его многими значениями и иметь поставленный отсортированным по времени.

похоже, что Вы только хотите самые ранние записи "g"? Возможно, только новые записи "g"?

Да, последнее. Но я определенное число их. Я не знаю дату начала заранее.

у Вас есть внешние ключи на k3, k4? Нет. Это - единственный интервал таблицы DB.

Комментарии:

, Даже если кластерный индекс является надлежащим, более выборочное поле должно быть на первом месте.

более выборочный индекс не используется в операторе Where (постредактирование!). Таким образом, я беру его, это не должно быть на первом месте в этом случае?

можно хотеть Переместить данные через определенный возраст к таблице истории

В настоящее время, все данные используются настолько сокращающие, не опция.

можно хотеть дефрагментировать индекс

В настоящее время, у меня нет ни одного. Изучит его, если этот поток окажется плодотворным.

Добавляют единственный индекс со столбцами k1-k6 в нем; это должно быть лучшим.

кто-либо еще может прокомментировать это предложение? Liggett78 cammented, которому это удвоит размер DB, не помогая очень из-за сортировки столбца даты. Обратите внимание, что столбец DATE не находится в операторе Where, он только используется для упорядочивания данных.

Попытка, поворачивающаяся k1, k2 в ints и делающая их внешние ключи, это будет использовать намного меньше устройства хранения данных для одного, я думал бы, и я думаю, что это должно быть более быстро (хотя я могу быть неправым там, я предполагаю, что SQL Server кэширует эти значения).

k2 является bigint (ошибка в сообщении orig). Так изменение k1 к интервалу (от VARCHAR (3)) является опцией. Мы действительно думаем, что это будет иметь много значения. И люди действительно думают, что разделение таблицы в k1, k2, d1 и k1, k2, k3, k4, k5, k7 и использование внешних ключей улучшили бы вещи?

Один хороший совет для улучшения скоростей запроса должен вставить подзапрос, который сокращает recordset размер к более управляемому. Вероятно, будет некоторый набор данных, которые сразу сокращают recordset по сравнению с, скажем 10 миллионов строк, к 10 000.

, например, ИЗБРАННЫЙ TOP (g) d1 ОТ (ВЫБИРАЮТ * таблица FROM ГДЕ k1=a С (NOLOCK)), ГДЕ И k3 = c И k4 = d И k5 = e И k6 = f Очень интересный ORDER BY k7

. Это действительно помогло бы? Кажется, что SQL Server был бы очень глуп, если бы это не сокращало данные подобным образом сами.

, Возможно, это время потрачено Вашим UI или отобразить ли данные, возможно, это, время потрачено Сетью?

нет никакого UI. Конечно, существуют сетевые проблемы, перемещающие данные, но я только обеспокоен временем, потраченным для запроса, чтобы начать возвращать результаты (я использую средство чтения данных ADO.NET), в данный момент - одна вещь за один раз:)

.. [чтобы] видеть, что большинство усилений... делит таблицу

, разве кластерный индекс не будет иметь того же эффекта?

Оставляют Ваш первичный ключ в покое, но создают кластерный индекс на Вашем столбце даты, так как это - то, что Вы используете В ORDER BY. Тем путем механизм базы данных начал бы сканировать кластеризованный ключ, сравнивать столбцы с Вашими предоставленными значениями и производить строки, которые удовлетворяют условия.

Походит на звуковой план! Какие-либо другие покровители?

Для суммирования предложений:

1) Создают отдельные индексы на всех ключах: большинство людей голосует не по этому?

2) Создают отдельные индексы на ключах с большинством отличных значений.

3) Создают несколько индекс столбца на [1 116] [приблизительно 1 116] из столбцов, со столбцами с самыми отличными значениями сначала.

4) RAM Броска в нем.

0
ответ дан Tom H 6 August 2013 в 14:06
поделиться
  • 1
    Таким образом, это означает, что я должен скомпилировать код как отдельное приложение и назвать его? – HH. 5 January 2010 в 12:50

Когда я подсказал в комментарии, я сделал это с единственной таблицей Oracle приближающиеся 8 ТБ, состоящих из более чем двух миллиардов строк, растущих по курсу сорока миллионов строк в день. Однако в моем случае, пользователи были два миллиона (и растущий) клиенты, получающие доступ к этим данным по сети, 24x7, и буквально ЛЮБАЯ из строк подверглась быть полученным доступ. О, и новые строки должны были быть добавлены в течение двух минут после в реальном времени.

Вы - вероятно, связанный ввод-вывод, не ЦП или связанная память, так оптимизация доступа к диску очень важна. Ваша RAM прекрасна - более, чем соответствующая. Используя несколько ядер было бы полезно, но ограниченный, если ввод-вывод не параллелизируется.

Несколько человек предложили разделить данные, к которым нужно отнестись серьезно, так как это намного лучше и более эффективно, чем какое-либо другое решение (ничто не быстрее, чем не касание данных вообще).

Вы говорите, что не можете разделить данные, потому что все данные используются: НЕВОЗМОЖНЫЙ! Нет никакого способа, которым Ваши пользователи пролистывают один миллион строк в день или сто миллионов общих количеств строк. Так, узнайте, как Ваши пользователи НА САМОМ ДЕЛЕ используют данные - смотрят на каждый запрос в этом случае.

, Что еще более важно, мы не говорим, что необходимо УДАЛИТЬ данные, мы говорим для РАЗДЕЛЕНИЯ данных. Клонируйте структуру таблицы в несколько, названных таблицами, вероятно, на основе времени (один месяц на таблицу, возможно). Скопируйте данные в соответствующие таблицы и удалите исходную таблицу. Создайте представление, которое выполняет объединение по новым таблицам с тем же именем как исходная таблица. Измените свою обработку вставки для предназначения для новейшей таблицы (предполагающий, что это является соответствующим), и запросы должны все еще работать против нового представления.

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

у Вас теперь есть стратегия управления данными в форме архивации самой старой таблицы, и удаление его (обновите определение представления, конечно). Аналогично, необходимо будет периодически составлять новую таблицу и обновлять определение представления для того конца данных также.

Ожидают не быть в состоянии использовать уникальные индексы: они не масштабируются вне приблизительно один к двум миллиона строк. Вам, вероятно, также придется изменить некоторую другую тактику/совет также. В ста миллионах строк и 400 ГБ, Вы ввели другую область обработки.

Кроме того, используйте другие предложения - анализируют фактическую производительность с помощью многих инструментов, уже доступных в SQL Server и ОС. Примените много известных настраивающих методов, которые легко доступны в сети или в книгах.

Однако НЕ экспериментируют! С так большим количеством данных у Вас нет времени для экспериментов, и риск является слишком большим. Изучите тщательно доступные методы и Ваши фактические детали производительности, затем выберите один шаг за один раз и дайте каждому несколько часов дням для раскрытия его влияния.

17
ответ дан Rob Williams 6 August 2013 в 14:06
поделиться
  • 1
    Отключение unique_checks уже улучшило производительность, а также сортировку по первичному ключу. Спасибо! – DBa 12 April 2010 в 15:02

Раздел и параллелизирует - проверяют план запросов, если не показ, что запрос параллелизируется тогда, узнает, почему это не. Вы, возможно, должны повредить запрос в несколько шагов и затем объединить результаты.

, Если это - тогда parition данные через несколько физических дисков, добавьте больше ядер. Ее полученный большая работа, чтобы сделать, как только Вы индексировали ад из него сырые данные, физическая сила, является всем, которое это оставляют.

не предполагают, что SQL Server будет просто использовать все Ваши ядра. Обычно необходимо разработать запрос просто право так, чтобы могли использоваться несколько ядер. Проверьте свойства первого узла в плане запросов для наблюдения МЕДНОГО ЗАЖИМА (степень параллелизма). Если его 1 Вы тратите впустую ядра...

1
ответ дан Frustrating Developments 6 August 2013 в 14:06
поделиться
  • 1
    Я смотрел на всем протяжении того бита [приблизительно 110] необходимость быть установленным. Я сделал все остальное за исключением того, что и couldn' t понимают, почему мое приложение ASP пыталось войти в систему SQL Server как IUSR вместо Идентификационных данных Пула приложений. – Moshe Katz 28 December 2011 в 21:38

Вот то, что я сделал бы:

  • не создают единственные индексы на каждом столбце. Вы будете тратить впустую пространство, и они не помогут Вам очень (если вообще)
  • , Оставляют Ваш первичный ключ в покое, но создают кластерный индекс на Вашем столбце даты, так как это - то, что Вы используете В ORDER BY. Тем путем механизм базы данных начал бы сканировать кластеризованный ключ, сравнивать столбцы с Вашими предоставленными значениями и производить строки, которые удовлетворяют условия.
  • Вам не нужны никакие другие индексы для этого. Я полагаю, что даже 100 значений из 100 миллионов для k4 считал бы плохой селективностью оптимизатор (хотя можно попробовать это, по крайней мере).
  • , если Вы выбираете на основе некоторых диапазонов даты, например, только данных с прошлого месяца, недели, года и т.д., Вы могли бы хотеть посмотреть на разделение Вашей большой таблицы в "меньшие" на основе столбца даты. Те 10 столбцов значений были бы хорошими кандидатами на ключи раздела также.

BTW, Вы определяете Вас весь PK в запросе - принимающий AND'ing в том, ГДЕ - который выберет точно 1 строку.

5
ответ дан liggett78 6 August 2013 в 14:06
поделиться
  • 1
    То единственное объясняет callvirt для виртуальных методов. Но GetType isn' t виртуальный. It' s функция экстерна, реализованная где-нибудь глубоко в кишечнике CLR (вероятно, возвращающий поле that' s сохраненный в object' s vtable или что-то). It' s тот же метод для каждого объекта. – Niki 10 May 2009 в 17:40

Помогите мне понять больше о таблице. если Ваш PK является k1, k2, Вам не придется выбрать никаким другим столбцом для получения абсолютно уникальной записи.

Вы означаете говорить, что k1 до 7 является PK? Если так, объявите его как таковой, и это будет кластерный индекс. Производительность запросов должна улучшиться существенно.

порядок добавляет много издержек. Рассмотрите нахождение более оптимального варианта, который может возвратить меньший набор данных. Знание, почему Вам нужен приблизительно миллион возвращенных записей, могло бы помочь мне предоставить лучшее решение.

Редактирование: Я получаю смысл, что я не являюсь одним в своем подозрении, что лучшее место, чтобы начать оптимизировать находится в Вашем физическом дизайне таблицы. Вы имеете какой-либо контроль над этим? Не зная, что хранит каждый столбец, я не могу предложить очень определенные идеи, но очень общий подход следует: Поместите K1,3,4,5 & 6 (k2, кажется, непосредственно связан со значениями в Вашей таблице) в ее собственной таблице с единственным уникальным интервалом как PK. Тогда создайте отношения FK назад к этой таблице. Вы PK на основной таблице тогда включали бы это поле, k2 & k7. Теперь Ваш запрос будет оптимизатор выполнять довольно недорогой поиск в Вашей новой таблице, возвращать единственную запись и затем работать, индекс ищут в Вашу основную таблицу PK только.

8
ответ дан Bob Probst 6 August 2013 в 14:06
поделиться

Хорошо,

Позволяют нам попытаться решить эту проблему со статистикой. Прежде чем Вы попытаетесь создать любой индекс, необходимо спросить, какая комбинация ключей дает мне лучшую выборочность:

  1. K1: 10 различных значений
  2. K3: 100 различных значений
  3. k4: 10 различных значений
  4. k5: 2 различных значения
  5. k6: 2 различных значения

, Если мы делаем compund ключ k1, k3, k4, k5, и k6, который означает, что ключ будет только иметь 40 000 различных комбинаций (10 * 100 * 10 * 2 * 2). Это означает, что, если у нас есть 100 000 000 записей, делится на 40 000, статистически у нас будет подмножество 2 500 различных записей, на которые последовательный поиск будет применен для завершения других ограничений оператора Where.

, Если мы экстраполируем этот результат и сравниваем их с текущим временем выполнения (30 минут) с ключом (k1), который генерирует статистически подмножество 10 миллионов различных записей, которые мы получаем:

10,000,000 rec * X секунд = 30 * 60 секунд * 2 500 rec

=> X секунд = 0,45 секунды

Не плохо ха? Еще лучше. Что было бы, если мы устраняем k5 и k6 от индекса compund? Статистически у нас будет подмножество 10 000 различных записей, где последовательный поиск будет выполнен. В теории, Сколько времени, которое возьмет? позволяет см.:

10,000,000 rec * X секунд = 30 * 60 * 10 000 rec

=> X секунд = 1,8 секунды

, Так как мы хотим самое маленькое индексное место, обменянное с самой лучшей производительностью, я сказал бы индекс относительно k1 + K3 +, K4 так хорош, как это добирается.

Hope это помогает,

5
ответ дан Igor Zelaya 6 August 2013 в 14:06
поделиться

Похоже, что Вы только хотите самые ранние записи "g"? Возможно, только новые записи "g"?

В основном Вы хотите, чтобы Ваш запрос только считал новые / самые старые записи. Вы не хотите запрашивать всех 400 ГБ, делают Вас? Если это верно, Вы могли бы рассмотреть архивацию большинства 400 ГБ или ведение последний раз вставленного учета в "текущей" таблице, которую можно запросить. Можно вести учет в текущей таблице, текущей через двойные вставки, или через триггер на таблице (дрожь). Но основная предпосылка - то, что Вы выполняете свой запрос против максимально маленькой таблицы. Это - в основном разделение таблицы плохого человека.

5
ответ дан Bernhard Hofmann 6 August 2013 в 14:06
поделиться
  • 1
    Достаточно ярмарка - я предположил, что GetType был виртуальным. Мое плохое. Мне нравится Dustin' s ответ. – zildjohn01 10 May 2009 в 17:48

то, что является D1, является им десятичный, или длинный символ может Вы разрабатывать это. Моя рекомендация состояла бы в том, чтобы создать кластерный индекс как (K7, k2, k1, k4) и затем создать дополнительный индекс на (k3) (создание индекса на двух значениях bool главным образом бессмысленны, если распределение значений не является приблизительно 30%/70% между значениями, или если Ваша таблица очень широка, если d1).

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

0
ответ дан 7 August 2013 в 01:06
поделиться

Вы рассмотрели создание суррогатного столбца идентификационных данных (введите bigint), и использование, что как кластерный индекс? Затем создайте свой первичный ключ как некластеризованный уникальный индекс.

С таблицей этого размера, довольно возможно, что индекс и фрагментация страницы являются большой проблемой производительности. Суррогатный кластерный индекс гарантирует, что все вставляет, в конце таблицы, которая может почти полностью устранить фрагментацию страницы (если строки не удалены). Фрагментация страницы Less == больше страниц на IO, который является очень хорошей вещью.

Это также позволит Вам периодически дефрагментировать уникальный индекс, на котором Вы запрашиваете, который сделает его намного более эффективным. Делайте это часто или по крайней мере контролируйте индексную фрагментацию на этой таблице регулярно.

Эти повышения производительности могут быть довольно поразительными - если Ваш текущий PK высоко фрагментируется, индекс ищут, может включить гораздо больше IO, чем он должен.

, После того как Вы реализовали это, рассмотрите (иначе, попробуйте его и мера ;-) добавив некластеризованный индекс на столбце k7.

1
ответ дан Sean Reilly 7 August 2013 в 01:06
поделиться

Вы можете попробовать:

alter table MyTable
    add constraint PK_MyTable
        primary key nonclustered (k1, k2)
create clustered index IX_MyTable
    on MyTable(k4, k1, k3, k5, k6, k7)
    --decreasing order of cardinality of the filter columns

Это гарантирует, что ваши повторяющиеся вставки будут продолжать выводить ошибки.

Это может также указать SQL Server фильтровать по (k1, k3, k4, k5, k6) и заказывать по (k7 asc) за один проход, позволяя SQL Server передавать результаты запроса без промежуточного этапа сортировки сначала миллиона результатов. Как только SQL Server найдет совпадение первой строки (k1, k3, k4, k5, k6) , следующий миллион строк или около того будут соответствовать одному и тому же фильтру и уже будут отсортированы по (k7 asc) . Вся фильтрация и упорядочение будут выполняться вместе на основе кластерного индекса.

При условии, что страницы хранятся последовательно, и при условии, что SQL Server знает, как оптимизировать, это ' Несколько дисков пытается пройти по индексу, чтобы найти первую соответствующую строку, за которой следует одно большое последовательное чтение с диска из десяти тысяч или около того страниц. Это должно быть быстрее, чем просить SQL Server искать строки повсюду, а затем просить SQL Server отсортировать их в tempdb!

Вы должны быть бдительными и убедиться, что кластеризованный индекс вообще находится в хорошем состоянии раз. Возможно, вам также придется уменьшить коэффициент заполнения страницы, если время вставки слишком сильно замедляется.

0
ответ дан 1 December 2019 в 17:26
поделиться

Похоже, вы не используете свой кластерный индекс в полной мере и имеете МНОГО дублированных данных.

Ваш кластерный индекс выглядит примерно так:

create clustered index IX_Clustered on Table(k1 ASC, k2 ASC)

Однако другие ваши столбцы k * представляют только 40 000 возможных перестановок.

10 (k1) * 10 (k3) * 100 (k4) * 2 (k5) * 2 (k6) = 40 000

Вы должны вывести уникальные комбинации этих 4 ключей в отдельную таблицу и дать каждому из это уникальный int (первичный ключ "newPK").

Извините, пожалуйста, за псевдокод:

create table SurrogateKey(
  newPK int -- /*primary key*/
, k1, k3, k4, k5, k6
)

constraint: newPK is primary key, clustered
constraint: k1, k3, k4, k5, k6 is unique

Эта таблица будет иметь только 40 000 строк и будет очень быстро искать первичный ключ newPK. Затем вы можете найти одно целое число в своей большой таблице.

Ваша существующая таблица должна быть изменена так, чтобы в ней были следующие столбцы:

  • newPK
  • k2 (который на самом деле не является ключом, скорее всего, это просто порядковый номер)
  • d1
  • k7 datetime

Учитывая вышеизложенное, вы можете изменить свой кластерный индекс на:

create clustered index IX_Clustered on Table(newPK ASC)

И вы можете искать по нему. Это гарантированно будет быстрее, чем то, что делает ваш запрос сейчас (производительность эквивалентна сканированию индекса + поиск по ключу).

declare @pk int
select @pk = newPK 
from SurrogateKey
where
      k1 = @k1
  and k3 = @k3
  and k4 = @k4
  and k5 = @k5
  and k6 = @k6

select top(g1) d1, k2, k7
from Table with(read uncommitted)
where newPK = @pk
order by k7

Ваш оператор вставки также должен быть изменен для запроса / вставки таблицы SurrogateKey.

1
ответ дан 1 December 2019 в 17:26
поделиться
Другие вопросы по тегам:

Похожие вопросы: