Я хочу получить данные из базы данных (MySQL) JPA, я хочу, чтобы она отсортировалась по некоторому значению столбца.
Итак, какова наилучшая практика:
ИЛИ
Заранее спасибо
Если вы извлекаете подмножество всех данных базы данных, например, отображая 20 строк на экране из 1000, лучше сортировать по базе данных. Это будет быстрее и проще, и вы сможете получать одну страницу строк (20, 50, 100) за раз, а не все.
Если ваш набор данных довольно мал, сортировка в коде может быть более удобной, если вы хотите реализовать сложный вид. Обычно этот сложный вид может быть выполнен в SQL, но не так легко, как в коде.
Короче говоря, эмпирическое правило сортируется через SQL, с некоторыми случаями краев к правилу.
В общем, вам лучше использовать ORDER BY в вашем SQL-запросе - таким образом, если есть применимый индекс, вы можете получать свою сортировку "бесплатно" (в худшем случае это будет то же самое количество работы, как это делается в вашем коде, но часто это может быть меньше работы, чем это!).
Это не совсем верно, но я недавно опубликовал что-то, что касается сортировки базы данных и приложений. Статья посвящена методу .net, поэтому большинство из них, вероятно, не будут вам интересны, но основные принципы остаются:
Отмена сортировки на стороне клиента (например, сортировка jQuery, Dataset/Dataview) может показаться соблазнительной. И это действительно жизнеспособный вариант для подкачки, сортировки и фильтрации, если (и только если):
1. набор данных мал, а
1. мало внимания уделяется производительности и масштабируемости
По моему опыту, систем, которые соответствуют этим критериям, мало и далеко друг от друга. Обратите внимание, что невозможно сопоставить и сопоставить сортировку/подкачку в приложении/базе данных - если вы запрашиваете базу данных для несортированных 100 строк данных, затем сортируйте эти строки со стороны приложения, вы, скорее всего, не получите набор данных вы ожидали. Это может показаться очевидным, но я видел ошибку, сделанную достаточно раз, что я хотел хотя бы упомянуть ее.
Гораздо эффективнее сортировать и фильтровать в базе данных по ряду причин. Во-первых, двигатели баз данных сильно оптимизированы для выполнения именно той работы, которая связана с сортировкой и фильтрацией; это то, что их основной код был разработан. Но даже запрет на то, что даже при условии, что вы можете написать код, который может соответствовать типу сортировки, фильтрации и производительности подкачки зрелого ядра базы данных, все же предпочтительнее выполнять эту работу в базе данных по той простой причине, что ее более эффективно ограничивать количество данных, которые передаются из базы данных на сервер приложений.
Так, например, если перед фильтрацией у вас есть 10 000 строк, а ваши параметры запроса равны 75, фильтрация на клиенте приводит к тому, что данные из всех 10 000 строк передаются по проводу (и в память серверов приложений), где фильтрация на стороне базы данных приведет к перемещению только отфильтрованных 75 строк между базой данных и приложением. его может оказать огромное влияние на производительность и масштабируемость.
Полный пост здесь: http://psandler.wordpress.com/2009/11/20/dynamic-search-objects-part-5sorting/
Я столкнулся с этим самым вопросом и решил, что я должен запустить небольшой тест, чтобы количественно определить разницу в скорости. Результаты удивили меня. Я хотел бы опубликовать свой опыт в этом вопросе.
Как и в случае с другими плакатами, моя мысль заключалась в том, что слой базы данных будет быстрее сортировать, потому что они якобы настроены для такого рода вещей. @Alex сделал хороший вывод, что если база данных уже имеет индекс в сортировке, то она будет быстрее. Я хотел ответить на вопрос, какая сырая сортировка выполняется быстрее для неиндексированных сортов. Заметьте, я сказал быстрее, не проще. Я думаю, что во многих случаях разрешение db делает работу проще и меньше подвержено ошибкам.
Мое основное предположение заключалось в том, что сортировка будет соответствовать основной памяти. Не все проблемы подойдут здесь, но хороший номер. Из-за недостатков памяти вполне может быть, что здесь сияют базы данных, хотя я не тестировал это. В случае сортировки в памяти все java/c/С++ превосходят mysql в моем неформальном тесте, если можно так выразиться.
Мне жаль, что у меня не было больше времени для более тщательного сравнения уровня базы данных и уровня приложения, но, увы, другие обязанности назывались. Тем не менее, я не мог не записать эту заметку для других, которые путешествуют по этой дороге.
Когда я начал по этому пути, я начал видеть больше препятствий. Должен ли я сравнивать передачу данных? Как? Можно ли сравнить время чтения db с временем, чтобы прочитать плоский файл в java? Как изолировать время сортировки и время передачи данных против времени на чтение записей? С этими вопросами здесь были методология и временные числа, которые я придумал.
Все времена в мс, если иное не указано
Все подпрограммы сортировки были по умолчанию предоставлены языком (они достаточно хороши для случайных отсортированных данных)
Вся компиляция была с типичным "профилем выпуска", выбранным через netbeans без настройки, если не указано иное
Все тесты для mysql использовали следующую схему
mysql> CREATE TABLE test_1000000
(
pk bigint(11) NOT NULL,
float_value DOUBLE NULL,
bigint_value bigint(11) NULL,
PRIMARY KEY (pk )
) Engine MyISAM;
mysql> describe test_1000000;
+--------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| pk | bigint(11) | NO | PRI | NULL | |
| float_value | double | YES | | NULL | |
| bigint_value | bigint(11) | YES | | NULL | |
+--------------+------------+------+-----+---------+-------+
Сначала вот небольшой фрагмент для заполнения БД. Могут быть более простые способы, но это то, что я сделал:
public static void BuildTable(Connection conn, String tableName, long iterations) {
Random ran = new Random();
Math.random();
try {
long epoch = System.currentTimeMillis();
for (long i = 0; i < iterations; i++) {
if (i % 100000 == 0) {
System.out.println(i + " next 100k");
}
PerformQuery(conn, tableName, i, ran.nextDouble(), ran.nextLong());
}
} catch (Exception e) {
logger.error("Caught General Exception Error from main " + e);
}
}
Результаты прямого CLI MYSQL:
select * from test_10000000 order by bigint_value limit 10;
10 rows in set (2.32 sec)
Эти тайминги были несколько сложными, поскольку единственная информация, которую я имел, - это время, указанное после выполнения команды.
из mysql prompt для 10000000 элементов примерно 2.1 - 2.4 для сортировки bigint_value или float_value
Java JDBC mysql call (аналогичная производительность для выполнения сортировки из mysql cli)
public static void SortDatabaseViaMysql(Connection conn, String tableName) {
try {
Statement stmt = conn.createStatement();
String cmd = "SELECT * FROM " + tableName + " order by float_value limit 100";
ResultSet rs = stmt.executeQuery(cmd);
} catch (Exception e) {
}
}
Пять прогонов:
da=2379 ms
da=2361 ms
da=2443 ms
da=2453 ms
da=2362 ms
Java Sort Генерирование случайных чисел на лету (на самом деле было медленнее, чем чтение IO диска). Время назначения - это время генерации случайных чисел и заполнения массива
Вызов
JavaSort(10,10000000);
Результаты синхронизации:
assignment time 331 sort time 1139
assignment time 324 sort time 1037
assignment time 317 sort time 1028
assignment time 319 sort time 1026
assignment time 317 sort time 1018
assignment time 325 sort time 1025
assignment time 317 sort time 1024
assignment time 318 sort time 1054
assignment time 317 sort time 1024
assignment time 317 sort time 1017
Эти результаты были для чтения файла с удвоением в двоичном режиме
assignment time 4661 sort time 1056
assignment time 4631 sort time 1024
assignment time 4733 sort time 1004
assignment time 4725 sort time 980
assignment time 4635 sort time 980
assignment time 4725 sort time 980
assignment time 4667 sort time 978
assignment time 4668 sort time 980
assignment time 4757 sort time 982
assignment time 4765 sort time 987
Выполнение переноса буфера приводит к значительно более быстрому времени выполнения
assignment time 77 sort time 1192
assignment time 59 sort time 1125
assignment time 55 sort time 999
assignment time 55 sort time 1000
assignment time 56 sort time 999
assignment time 54 sort time 1010
assignment time 55 sort time 999
assignment time 56 sort time 1000
assignment time 55 sort time 1002
assignment time 56 sort time 1002
Результаты C и С++ (см. ниже для источника)
Отладочный профиль с использованием qsort
assignment 0 seconds 110 milliseconds Time taken 2 seconds 340 milliseconds
assignment 0 seconds 90 milliseconds Time taken 2 seconds 340 milliseconds
assignment 0 seconds 100 milliseconds Time taken 2 seconds 330 milliseconds
assignment 0 seconds 100 milliseconds Time taken 2 seconds 340 milliseconds
assignment 0 seconds 100 milliseconds Time taken 2 seconds 330 milliseconds
assignment 0 seconds 100 milliseconds Time taken 2 seconds 340 milliseconds
assignment 0 seconds 90 milliseconds Time taken 2 seconds 340 milliseconds
assignment 0 seconds 100 milliseconds Time taken 2 seconds 330 milliseconds
assignment 0 seconds 100 milliseconds Time taken 2 seconds 340 milliseconds
assignment 0 seconds 100 milliseconds Time taken 2 seconds 330 milliseconds
Профиль выпуска с использованием qsort
assignment 0 seconds 100 milliseconds Time taken 1 seconds 600 milliseconds
assignment 0 seconds 90 milliseconds Time taken 1 seconds 600 milliseconds
assignment 0 seconds 90 milliseconds Time taken 1 seconds 580 milliseconds
assignment 0 seconds 90 milliseconds Time taken 1 seconds 590 milliseconds
assignment 0 seconds 80 milliseconds Time taken 1 seconds 590 milliseconds
assignment 0 seconds 90 milliseconds Time taken 1 seconds 590 milliseconds
assignment 0 seconds 90 milliseconds Time taken 1 seconds 600 milliseconds
assignment 0 seconds 90 milliseconds Time taken 1 seconds 590 milliseconds
assignment 0 seconds 90 milliseconds Time taken 1 seconds 600 milliseconds
assignment 0 seconds 90 milliseconds Time taken 1 seconds 580 milliseconds
Профиль выпуска Использование std:: sort (a, a + ARRAY_SIZE);
assignment 0 seconds 100 milliseconds Time taken 0 seconds 880 milliseconds
assignment 0 seconds 90 milliseconds Time taken 0 seconds 870 milliseconds
assignment 0 seconds 90 milliseconds Time taken 0 seconds 890 milliseconds
assignment 0 seconds 120 milliseconds Time taken 0 seconds 890 milliseconds
assignment 0 seconds 90 milliseconds Time taken 0 seconds 890 milliseconds
assignment 0 seconds 90 milliseconds Time taken 0 seconds 880 milliseconds
assignment 0 seconds 90 milliseconds Time taken 0 seconds 900 milliseconds
assignment 0 seconds 90 milliseconds Time taken 0 seconds 890 milliseconds
assignment 0 seconds 100 milliseconds Time taken 0 seconds 890 milliseconds
assignment 0 seconds 150 milliseconds Time taken 0 seconds 870 milliseconds
Профиль деления Чтение случайных данных из файла и использование std:: sort (a, a + ARRAY_SIZE)
assignment 0 seconds 50 milliseconds Time taken 0 seconds 880 milliseconds
assignment 0 seconds 40 milliseconds Time taken 0 seconds 880 milliseconds
assignment 0 seconds 50 milliseconds Time taken 0 seconds 880 milliseconds
assignment 0 seconds 50 milliseconds Time taken 0 seconds 880 milliseconds
assignment 0 seconds 40 milliseconds Time taken 0 seconds 880 milliseconds
Ниже приведен исходный код. Надеемся, минимальные ошибки:)
Источник Java Обратите внимание, что внутренняя среда JavaSort требует, чтобы runCode и writeFlag были скорректированы в зависимости от того, что вы хотите использовать. Также обратите внимание, что распределение памяти происходит в цикле for (таким образом, тестирование GC, но я не видел заметных различий, перемещающих выделение вне цикла)
public static void JavaSort(int iterations, int numberElements) {
Random ran = new Random();
Math.random();
int runCode = 2;
boolean writeFlag = false;
for (int j = 0; j < iterations; j++) {
double[] a1 = new double[numberElements];
long timea = System.currentTimeMillis();
if (runCode == 0) {
for (int i = 0; i < numberElements; i++) {
a1[i] = ran.nextDouble();
}
}
else if (runCode == 1) {
//do disk io!!
try {
DataInputStream in = new DataInputStream(new FileInputStream("MyBinaryFile.txt"));
int i = 0;
//while (in.available() > 0) {
while (i < numberElements) { //this should be changed so that I always read in the size of array elements
a1[i++] = in.readDouble();
}
}
catch (Exception e) {
}
}
else if (runCode == 2) {
try {
FileInputStream stream = new FileInputStream("MyBinaryFile.txt");
FileChannel inChannel = stream.getChannel();
ByteBuffer buffer = inChannel.map(FileChannel.MapMode.READ_ONLY, 0, inChannel.size());
//int[] result = new int[500000];
buffer.order(ByteOrder.BIG_ENDIAN);
DoubleBuffer doubleBuffer = buffer.asDoubleBuffer();
doubleBuffer.get(a1);
}
catch (Exception e) {
}
}
if (writeFlag) {
try {
DataOutputStream out = new DataOutputStream(new FileOutputStream("MyBinaryFile.txt"));
for (int i = 0; i < numberElements; i++) {
out.writeDouble(a1[i]);
}
} catch (Exception e) {
}
}
long timeb = System.currentTimeMillis();
Arrays.sort(a1);
long timec = System.currentTimeMillis();
System.out.println("assignment time " + (timeb - timea) + " " + " sort time " + (timec - timeb));
//delete a1;
}
}
Источник C/С++
#include <iostream>
#include <vector>
#include <algorithm>
#include <fstream>
#include <cstdlib>
#include <ctime>
#include <cstdio>
#include <math.h>
#include <stdio.h>
#include <time.h>
#include <stdlib.h>
#define ARRAY_SIZE 10000000
using namespace std;
int compa(const void * elem1, const void * elem2) {
double f = *((double*) elem1);
double s = *((double*) elem2);
if (f > s) return 1;
if (f < s) return -1;
return 0;
}
int compb (const void *a, const void *b) {
if (*(double **)a < *(double **)b) return -1;
if (*(double **)a > *(double **)b) return 1;
return 0;
}
void timing_testa(int iterations) {
clock_t start = clock(), diffa, diffb;
int msec;
bool writeFlag = false;
int runCode = 1;
for (int loopCounter = 0; loopCounter < iterations; loopCounter++) {
double *a = (double *) malloc(sizeof (double)*ARRAY_SIZE);
start = clock();
size_t bytes = sizeof (double)*ARRAY_SIZE;
if (runCode == 0) {
for (int i = 0; i < ARRAY_SIZE; i++) {
a[i] = rand() / (RAND_MAX + 1.0);
}
}
else if (runCode == 1) {
ifstream inlezen;
inlezen.open("test", ios::in | ios::binary);
inlezen.read(reinterpret_cast<char*> (&a[0]), bytes);
}
if (writeFlag) {
ofstream outf;
const char* pointer = reinterpret_cast<const char*>(&a[0]);
outf.open("test", ios::out | ios::binary);
outf.write(pointer, bytes);
outf.close();
}
diffa = clock() - start;
msec = diffa * 1000 / CLOCKS_PER_SEC;
printf("assignment %d seconds %d milliseconds\t", msec / 1000, msec % 1000);
start = clock();
//qsort(a, ARRAY_SIZE, sizeof (double), compa);
std::sort( a, a + ARRAY_SIZE );
//printf("%f %f %f\n",a[0],a[1000],a[ARRAY_SIZE-1]);
diffb = clock() - start;
msec = diffb * 1000 / CLOCKS_PER_SEC;
printf("Time taken %d seconds %d milliseconds\n", msec / 1000, msec % 1000);
free(a);
}
}
/*
*
*/
int main(int argc, char** argv) {
printf("hello world\n");
double *a = (double *) malloc(sizeof (double)*ARRAY_SIZE);
//srand(1);//change seed to fix it
srand(time(NULL));
timing_testa(5);
free(a);
return 0;
}
Я почти уверен, что будет быстрее разрешить базе данных сортировать ее. Там инженеры, которые проводят много времени, совершенствуя и оптимизируя свои алгоритмы поиска, тогда как вам придется реализовать свой собственный алгоритм сортировки, который может добавить еще несколько вычислений.
Я хотел бы, чтобы база данных делала вид, они, как правило, очень хороши в этом.
Пусть база данных выполняет сортировку. Он создан для выполнения "грязной" работы для вас...
Позвольте базе данных отсортировать ее. Затем вы можете легко находить пейджинг с JPA без чтения во всем наборе результатов.
Ну, на самом деле нет прямого способа ответить на этот вопрос; на это нужно ответить в контексте.
Является ли ваше приложение (средний уровень) запущено в том же node как база данных?
Если да, вам не нужно беспокоиться о задержке между базой данных и средним уровнем. Тогда возникает вопрос: насколько велика подмножество/результат вашего запроса? Помните, что для сортировки это средний уровень, вы возьмете список/набор размеров N и либо напишите пользовательский компаратор, либо воспользуетесь компаратором сравнения по умолчанию. Или, что угодно. Итак, с самого начала вы отстаете от размера N.
Но если ответ отрицательный, тогда вы попадаете на латентность, связанную с передачей вашего набора результатов из БД на средний уровень. И тогда, если вы выполняете разбивку на страницы, что последнее, что вам нужно делать, вы отбрасываете 90-95% этого набора результатов после резки страниц.
Таким образом, потерянная пропускная способность не может быть оправдана. Представьте, что вы делаете это для каждого запроса, через ваши организации-арендаторы.
Как бы вы ни смотрели на это, это плохая конструкция.
Я бы сделал это в базе данных, несмотря ни на что. Просто потому, что почти все приложения сегодня требуют разбиения на страницы; даже если они не отправляют массивные результаты по проводу вашему клиенту, это общая сумма отходов; тянет всех вниз всех ваших арендаторов.
Одна интересная идея, с которой я играю в эти дни, заключается в том, чтобы использовать мощь HTML5, привязку двухсторонних данных в рамках браузера, например Angular, и переместить некоторую обработку обратно в браузер. Таким образом, вы не ожидаете в очереди кого-то еще перед тем, как закончите. Истинная распределенная обработка. Но нужно принять меры, чтобы решить, что можно толкнуть, а что нет.
Зависит от контекста.
TL; DR
Если у вас есть полные данные на сервере приложений, сделайте это на сервере приложений.
Если у вас есть полный набор данных, который вам нужен на стороне сервера приложений, то лучше сделать это на стороне сервера приложений, потому что эти серверы могут масштабироваться горизонтально. Наиболее вероятными сценариями для этого являются:
Не делайте это на стороне клиента, если вы не можете гарантировать, что это не повлияет на клиентские устройства.
Сами базы данных могут быть оптимизированы, но если вы можете снять с них бремя, вы можете снизить свои затраты в целом, поскольку масштабирование баз данных более дорого, чем масштабирование серверов приложений.