[решено] поиск по большой таблице большого количества строк

[Griboed0ff]

Доброго времени суток! Есть большая таблица csv 1.2ГБ, которая постоянно дополняется данными в конец таблицы. В таблице 52 столбца и уже более 20 миллионов строк. Каждая строка имеет столбец с именем ПК, по которому и происходит поиск. Как и у всех больших таблиц имеется проблема с производительностью обработки данных Частично получается решить проблему с использованием фреймворков напрямую, но не хватает знаний, чтобы добиться приемлемой скорости.
Сама задача такова:
1) Взять список имен ПК из AD, их примерно 14000. -решено
2) найти для каждого имени ПК в таблице строку с последними данными,ориентируясь по дате. Так же избежать строки, которые в столбце пинг имеют значение "NO". - ищу решение
3) экспортировать строки найденные ранее в файл csv, с теми же заголовками и без изменения данных. -решено
Проблема возникает именно со вторым пунктом, так как поиск данных для одного имени ПК и выбор самой старшей строки, занимает от 0.1-10сек. Обработка всего списка занимает ~8 часов.
По второму пункту:
Решил отзеркалить таблицу снизу вверх, чтобы вверху таблицы оказались самые свежие данные- это занимает примерно 15-20 минут на это действие. Как перебирать таблицу с конца при поиске так и не понял. Но данное действие значительно укорило процесс, так как нужное значение почти всегда находится среди первых ~20000 строк.
"Data";"Ping";"Region";"B_S_TT";"System_Unit_Name";"System_Unit_Manufacturer";"System_Unit_Model";"Motherboard_SN";"Adapter_IPv4";"Adapter_Mac";"Adapter_Name";"Adapter_Type";"Adapter_Speed";"Monitor_1_model";"Monitor_1_SN";"Monitor_2_model";"Monitor_2_SN";"Router_Manufacturer";"Router_Model";"Router_SN";"Processor";"DDRx";"kolichestvo_Slots_all";"kolichestvo_Slots_free";"PhysicalMemory_all";"Slot_1_size";"Slot_1_speed";"Slot_1_manufacturer";"Slot_2_size";"Slot_2_speed";"Slot_2_manufacturer";"Slot_3_size";"Slot_3_speed";"Slot_3_manufacturer";"Slot_4_size";"Slot_4_speed";"Slot_4_manufacturer";"OS";"x64_x86";"disk_1_name";"disk_1_size";"disk_1_type";"disk_2_name";"disk_2_size";"disk_2_type";"disk_3_name";"disk_3_size";"disk_3_type";"disk_4_name";"disk_4_size";"disk_4_type";"shk"
"07.09.2021";"Yes";"msk";"b";"GO-DD1155";"Hewlett-Packard";"HP Compaq 6200 Pro MT PC";"RUA23101TK";"10.243.78.51";"A0:B3:CC:F7:8A:B7";"Intel(R) 82579LM Gigabit Network Connection";"Ethernet 802.3";"1000";"HP E231 ";"3CQ4271J2H ";"";"";;;;"Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz";"DDR3";"4";"3";"4";"4";"1333";"Samsung ";"0";"";"";"0";"";"";"0";"";"";"Майкрософт Windows 10 Pro";"64-разрядная";"ST3500413AS";"466";"HDD";"";"0";"";;"0";"";;"0";"";"no info"
"07.09.2021";"Yes";"msk";"b";"GO-DG584";"Hewlett-Packard";"HP ProDesk 400 G2 MT";"CZC44843RZ";"10.243.78.25";"A0:D3:C1:50:07:54";"Realtek PCIe GBE Family Controller";"Ethernet 802.3";"100";"";"";"";"";;;;"Intel(R) Core(TM) i5-4590S CPU @ 3.00GHz";"DDR3";"2";"0";"8";"4";"1600";"Micron";"4";"1600";"Micron";"0";"";"";"0";"";"";"Майкрософт Windows 10 Pro";"64-разрядная";"ST500DM002-1BD142";"466";"HDD";"";"0";"";;"0";"";;"0";"";"no info"
"07.09.2021";"Yes";"msk";"b";"GO-DG208";"Hewlett-Packard";"HP ProDesk 400 G2 MT";"CZC44843LL";"10.243.72.16";"A0:D3:C1:4D:53:B0";"Realtek PCIe GBE Family Controller";"Ethernet 802.3";"100";"HP E231 ";"3CQ4360H20 ";"";"";;;;"Intel(R) Core(TM) i5-4590S CPU @ 3.00GHz";"DDR3";"2";"0";"8";"4";"1600";"0000";"4";"1600";"0000";"0";"";"";"0";"";"";"Майкрософт Windows 10 Pro";"64-разрядная";"ST500DM002-1BD142";"466";"HDD";"";"0";"";;"0";"";;"0";"";"no info"
"07.09.2021";"No";"msk";"b";"go-nd6671";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" "
"07.09.2021";"Yes";"msk";"b";"GO-DG404";"Hewlett-Packard";"HP Compaq Pro 6300 MT";"RUA2470C98";"10.243.66.171";"B4:B5:2F:DA:14:13";"Intel(R) 82579LM Gigabit Network Connection";"Ethernet 802.3";"100";"HP E231 ";"3CQ4360FRM ";"";"";;;;"Intel(R) Core(TM) i5-3470 CPU @ 3.20GHz";"DDR3";"4";"2";"8";"4";"1600";"Samsung";"4";"1600";"Kingston";"0";"";"";"0";"";"";"Майкрософт Windows 10 Pro";"64-разрядная";"Hitachi HDS721050CLA660";"466";"HDD";"";"0";"";;"0";"";;"0";"";"no info"
"07.09.2021";"No";"msk";"b";"GO-NG433";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" "

часть скрипта где все описанное происходит
# Считает количество строк в файле
[int]$linesInFile = 0
$reader = New-Object IO.StreamReader $outfile
while($reader.ReadLine() -ne $null){$linesInFile++}
$linesInFile

# отзеркаливание таблицы снизу вверх
$lines = [IO.File]::ReadAllLines($outfile)
for ($str = $linesInFile;$str -ne 0;$str--){
# Обращается к строке по номеру и экспортирует
$str
$lines[$str]|Add-Content -Encoding UTF8 $outfile2}

$OU | ForEach {
$all = get-adcomputer -Filter {ObjectClass -eq 'Computer'} -SearchBase $_ -Properties CanonicalName,SamAccountName,CN,lastLogonTimeStamp,whenChanged,memberof
ForEach ($all in $all){
$rt= $all.CN
$rt
select-string -path $outfile2 -list -pattern "$rt" | add-Content $outfile3 -Encoding utf8
}}
Я пытался отсортировать таблицу по дате стандартными способами, но они занимают намного больше времени и полностью занимают ОЗУ. Способ, который выбрал я не кушает ОЗУ, но я был бы рад избежать шага сортировки вообще и начинать поиск нужных данных с конца таблицы.
Возможно есть способ, который позволит искать сразу множество имен ПК, например 14000 имен, чтобы не перебирать всю таблицу снова для каждого значения. МБ время поиска сократить за счет поиска значения не во всей строке, а только в указанном столбце этой строки, а потом уже отправлять эту строку на экспорт. Ищу любые способы ускорения процесса, на данный момент ОЗУ не страдает и ЦПУ тоже. Ну и буду рад, если кто-нибудь поможет оптимизировать код, а скорее всего есть не нужные моменты.

[Iska]

Griboed0ff, по возможности, работайте с csv-файлом, как с базой данных посредством OLE DB. Это наиболее быстрый способ доступа к произвольным данным (за исключением Log Parser'а).

P.S. В конце-концов — почему Вы не перейдёте от CSV к любой базе данных?!

Ищу любые способы ускорения процесса, »
Приложите Ваш образец:
Пример данных таблицы
в виде файла, упаковав его в архив.

[DJ Mogarych]

любой базе данных »
Тем более, что она уже есть, называется Active Directory.

[Griboed0ff]

В конце-концов — почему Вы не перейдёте от CSV к любой базе данных?! »
База данных уже есть в 1С, просто в нее еще не спарсили нужные поля и не настроили отчеты в нужном виде. В данный момент специалист 1с в отпуске и сделать этого нет возможности, а данные нужны сейчас. Да и в общем надо понимать как в крайнем случае работать с таким объемом данных, такие случаи бывают редко, но метко. Таблица, про которую сейчас идет речь, хранится как резервный вариант хранения исторических данных, который мне сейчас и пригодился. Есть и другие таблицы огромного объема, которые возможно будут использоваться только раз или два, поэтому самый простой, но бывает медленный вариант -это накидать скрипт. Файл прикладываю.

[Griboed0ff]

Тем более, что она уже есть, называется Active Directory. » У нас в AD не хранятся такие данные и вмешаться в его работу нет возможности. Да и другие таблицы есть, которые мне присылают, но там я не могу поменять способ хранения данных остается просто научиться более или менее быстро работать с этими таблицами. Такие случаи бывают крайне редко, поэтому прибегать к каким-то сторонним решениям нецелесообразно. Если нет возможности как-то оптимизировать данный процесс, то просто буду ожидать по 8 часов своих данных.

[DJ Mogarych]

Можно попробовать для начала запускать скрипт на PS7, в отдельных случаях он работает быстрее PS5.

[Griboed0ff]

попробовать для начала запускать скрипт на PS7 » Данный метод ускорил процесс, но не глобально. МБ select-string может искать не одно значение, а сразу много, или он может искать сразу с конца таблицы? Может есть какой-нибудь фреймворк, который умеет искать значения как select-string?

[Serguei Kouzmine]

@Griboed0ff попробуйте поставить sqlite3.exe https://zametkinapolyah.ru/zametki-o-mysql/chast-1-3-ustanovka-i-zapusk-sqlite3-v-windows-7.html и в sqlite создайте таблицу с индксом

[Griboed0ff]

попробуйте поставить sqlite3.exe »Я даже попытался сделать как вы сказали, но это поставило меня в тупик еще больше. Я поставил sqlite3, создал базу, импортировал туда свою csv, все данные видно через sqlitestudio, но что мне делать с этим далее я даже не представляю. Я так понял мне надо создать какой то запрос, который сделал бы выборку уникальных значений по столбцу name, выбирая при этом самую старшую дату из столбца дата. Есть ощущение, что язык запросов sql для меня еще сложнее чем powershell, потому как я ранее никогда не встречался с ним. Возможно вы знаете, что-либо о запросах?! Индексирование тоже вызывает у меня вопросы..

[Griboed0ff]

Все-таки было решено с помощью sqlite в соседней теме теме (http://forum.oszone.net/thread-349632.html).
Пару запросов к базе через модуль PSSQLite, завернул в скрипт powershell. Итог - теперь моя выборка создается за минуту!
Скрипт запрашивает у меня аушку или набор аушек по коротким псевдонимам и я всегда получаю таблицу с выборкой.
Кусок скрипта для примера:


$time = Measure-Command -Expression {
# блок про аушки
[Parameter(Mandatory=$true)][String]$Region
$ofs=''
$Region= Read-Host "введите одно из значений: All_ALL, All_TT, All_S, All_B, MSK_TT, MSK_B, MSK_S и т.д."
$Region2 = $Region
$date= [datetime]::now.tostring("ddMMyyyy")
$outfile = "D:\SQlite3\result\$Region2`_$date.csv"

$myscripname=$myInvocation.ScriptName
Remove-Item $outfile -ErrorAction SilentlyContinue
if ($Region -eq "MSK_TT") {...}
#тут кусок с наборами аушек и короткими псевдонимами

$OUt = $OU | ForEach {get-adcomputer -Filter {ObjectClass -eq 'Computer'} -SearchBase $_ -Properties CN,LastLogon,lastLogon,lastLogonTimeStamp,CanonicalName,Enabled,whenChanged | Select-Object @{n='System_Unit_Name';e={$_.CN}},@{n='lastLogon';e={[DateTime]::FromFileTime($_.lastLogon)}},@{n='lastLogonTimeStamp';e={[DateTime]::FromFileTime($_.lastLogonTimeStamp)}},Enabled,CanonicalName,whenChanged }| Out-DataTable

$datasource = ".....info.db"
# Удаления прошлых результатов выполнения скрипта
$Query0 = "DROP TABLE AD_list;
DROP TABLE all_pc_result;

# команда создает таблицу в базе с заданными именами столбцов
$Query1 = "CREATE TABLE AD_list (System_Unit_Name,lastLogon,lastLogonTimeStamp,Enabled,CanonicalName,whenChanged)"
Invoke-SqliteQuery -Query $Query1 -DataSource $datasource

# Запись $OUt в таблицу
Invoke-SQLiteBulkCopy -DataTable $OUt -DataSource $DataSource -Table "AD_list" -NotifyAfter 100000 -force

# приводим список AD и список имен ПК к нижнему регистру
$Query2 = "UPDATE all_pc
SET System_Unit_Name = LOWER(System_Unit_Name);

UPDATE AD_list
SET System_Unit_Name = LOWER(System_Unit_Name);"
# выполнение запроса на нижний регистр $Query2
Invoke-SqliteQuery -Query $Query2 -DataSource $datasource

# запрос для создания выборки из таблицы all_pc по списку AD
$Query3 = "CREATE TABLE IF NOT EXISTS all_pc_result AS
SELECT *
FROM all_pc, AD_list USING(System_Unit_Name)
WHERE Ping <> 'No'
GROUP BY System_Unit_Name HAVING max(printf('%s-%s-%s', substr(Data, length(Data) + 1, -4), substr(Data, instr(Data, '.') + 1, 2), substr(Data, 1, 2)));"
# выполнение запроса выборки по списку из AD_list
Invoke-SqliteQuery -Query $Query3 -DataSource $datasource

# экспорт в таблицу csv
D:\SQlite3\soft\sqlite3.exe -header D:\SQlite3\base_test\all_pc_info.db -tabs "select * from all_pc_result" > $outfile
}
$time.ToString()

[DJ Mogarych]

Ужос. Как извращаться приходится в отсутствие нормального доступа к AD...

[Serguei Kouzmine]

SQL довольно дружелюбный к пользователю

вот пример выбоки наибольшего code для каждого name (для дат более менее то же самое)

CREATE TABLE data ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,"code" integer, "name" TEXT );


insert into data (name,code) values ('A',1);
insert into data (name,code) values ('A',2);
insert into data (name,code) values ('A',3);
insert into data (name,code) values ('B',1);
insert into data (name,code) values ('B',2);
insert into data (name,code) values ('B',3);
insert into data (name,code) values ('B',4);
insert into data (name,code) values ('C',1);
insert into data (name,code) values ('C',2);
insert into data (name,code) values ('D',1);


select distinct name, max(code) from data group by name order by name;

получится сразу ответ



A 3
B 4
C 2
D 1

[Iska]

Так доступ вроде ж есть?

[DJ Mogarych]

В смысле того, что в AD нет нужной информации.
Я у себя на работе предпочитаю хранить всю доп. информацию прямо в AD - туда стекаются и MAC-адреса, и местоположение компов из 1С, телефоны из базы SQL, кто последний зашёл на комп и т. д.
Поэтому нет необходимости создавать дополнительные костыли, а напрямую запрашивать AD, которая является такой же базой данных.

[Iska]

DJ Mogarych, спасибо, ясно.

…которая является такой же базой данных. »
Не совсем такой же: она всё-таки не реляционная (насколько я понимаю), посему не сильно шустрая.

[DJ Mogarych]

Может быть, я не спец по БД. Ну уж 14000 компов должна потянуть ))