Блог

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

• до 6 параметров запроса, например. МЕЖДУ «созданным» И «покинутым», МЕЖДУ X И Y, В (1,2,3…..25), В («A», «B», «C»….»Z»)
• Соединения/подзапросы между 2-5 таблицами
• возврат от 50 тыс. до 5 млн записей (LAT и LNG)

В настоящее время я использую JOIN, чтобы найти lat, lng для записи и некоторые параметры запроса. Должен ли я присоединиться по-другому (слева, справа)? Должна ли таблица » ОТ » быть записью или отношением? Подзапросы?

Является ли эта производительность запросов приемлемой с точки зрения пользовательского интерфейса? Это находится на локальном хосте (docker) на довольно высокопроизводительном ноутбуке с WSL (16 ГБ оперативной памяти / 6-ядерный процессор [2,2 ГГц]).

 -- [2547.438ms] [rows:874731] 
SELECT  "Longitude","Latitude" 
FROM    Wells
JOIN    Well_Reports ON Well_Reports.Well_ID       = Wells.Well_ID 
JOIN    Lithologies  on Lithologies.Well_Report_ID = Well_Reports.Well_Report_ID 
where   Lithologies.Colour IN 
        (
           'NULL',
           'Gray','White','Dark','Black','Dark Gray','Dark Brown','Dark Red','Dark Blue',
           'Dark Green','Dark Yellow','Bluish Green','Brownish Gray','Brownish Green','Brownish Yellow',
           'Light','Light Gray','Light Red','Greenish Gray','Light Yellow','Light Green','Light Blue',
           'Light Brown','Blue Gray','Greenish Yellow','Greenish Gray'
        );
 

• Пользовательский интерфейс представляет собой тепловую карту. Я действительно не столкнулся с проблемами производительности, возвращающими 1 миллион строк.
• Угловой — это каркас. Im разбивает HTTP-ответ на 10 тыс. фрагментов записей

Мое первоначальное впечатление состояло в том, что 3 секунды-это слишком долго для пользовательского интерфейса, чтобы начать заполнять данные. Я уже разбил ответ на пользовательский интерфейс на куски, эта часть была эффективной и асинхронной. Мне никогда не приходило в голову просто разбить SQL-запросы на более мелкие фрагменты с ОГРАНИЧЕНИЕМ и СМЕЩЕНИЕМ, чтобы сервер мог немедленно начать отвечать на данные (

Я напишу ответ на этот счет.

Спасибо и с наилучшими пожеланиями, шморрисон

Несколько вещей.

 where someColumn in (null, ...)
 

Это не вернет строки, в которых значение someColumn равно null потому что , x in (a, b, c ...) переводится в x = a or x = b or x = c и null не равно null .

Вместо этого вам нужна такая конструкция, как эта

 where someColumn is null or someColumn in (...)
 

Во-вторых, вы упомянули, что возвращаете в пользовательский интерфейс от 50 до 5 тысяч строк. Я сомневаюсь в здравомыслии этого… как пользовательский интерфейс отображает 5 миллионов наборов координат для просмотра/использования пользователем? Я полагаю, что могут быть некоторые крайние случаи, когда это действительно то, что вам нужно сделать, но это просто кажется маловероятным.

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

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

Как указал Цепеш,

«Если вам нужна хорошая производительность для динамических запросов, возвращающих 5 миллионов строк, вам нужно будет изучить тонкости движков баз данных, а не только SQL. Знания высокого уровня, к сожалению, не помогут».

и я ожидал этого.

Я просто запрашивал базу данных SQL для всего набора сразу, это неправильно, и я это знаю.

Изменения, которые я внес, генерируют следующие утверждения (я знаю, что особенности изменились):

 -- [145.955ms] [rows:1]
SELECT     count(*) 
FROM       "tblWellLogs" 
WHERE      (
             DateWellCompleted BETWEEN 
             '1800-01-01 06:58:00.000' AND '2021-09-12 06:00:00.000'
           ) 
AND        "FinalStatusOfWellL" NOT IN 
           (
             5,6,7,8,9,16,27,36
           ) 
AND        WaterUseL IN 
           (
             1,29,3,8,26,4,6
           ) 
AND        (
             wyRate BETWEEN
             0 AND 3321
           ) 
AND        (
             TotalOrFinishedDepth BETWEEN
             0 AND 248
           );
 

 Calculated total(69977)  chunk (17494)   currentpage(3)
 

 -- [117.195ms] [rows:17494]
SELECT     "Easting","Northing" 
FROM       "tblWellLogs" 
WHERE      (
             DateWellCompleted BETWEEN 
             '1800-01-01 06:58:00.000' AND '2021-09-12 06:00:00.000'
           ) 
AND        "FinalStatusOfWellL" NOT IN 
           (
             5,6,7,8,9,16,27,36
           ) 
AND        WaterUseL IN 
           (
             1,29,3,8,26,4,6
           ) 
AND        (
             wyRate BETWEEN
             0 AND 3321
           ) 
AND        (
             TotalOrFinishedDepth BETWEEN 
             0 AND 248
           )
ORDER BY   (SELECT NULL)
OFFSET     34988 ROWS
FETCH NEXT 17494 ROWS ONLY;
 

 Chunk count: 17494
JSON size: 385135
Request time: 278.6612ms
 

Ответ на пользовательский интерфейс выглядит следующим образом:

 GET /data/wells/xxxx/?page=3amp;completed.start=1800-01-01T06:58:36.000Zamp;completed.end=2021-09-12T06:00:00.000Zamp;status=supply,research,geothermal,other,unknownamp;use=domestic,commercial,industrial,municipal,irrigation,agricultureamp;depth.start=0amp;depth.end=248amp;rate.start=0amp;rate.end=3321amp;page=3 HTTP/1.1 200 385135
 

 {
  "data":[[0.0, 0.0],......],
  "count": 87473,
  "total": 874731,
  "pages": 11,
  "page": 1
}
 

В этом случае базы данных по существу неизменяемы (я могу получать обновленный набор данных один раз в год). Я полагаю, что могу заранее определить набор вариантов размера блока для каждой БД на основе размера результата запроса, а не только размера БД. Я также извлекаю следующую страницу до того, как клиент ее запросит. Я кэширую запросы на уровне браузера и клиента. Я выполняю Count(*) запрос только в том случае, если клиент его не предоставляет, и его нет в кэше.

Я обнаружил, что одновременное выполнение запроса просто перегрузило мой процессор, все запросы возвращались почти одновременно, но каждый раз занимал 5 секунд, а не ~1 секунда.