Запросы к файлам DBF с использованием OLEDB (VFPOLEDB) выполняются слишком медленно

@Sergiy, у меня есть решение для тебя:

string sqlEq = "SELECT utk_ved FROM prod WHERE Price_N = '641857'";
string sqlLike = "SELECT utk_ved FROM prod WHERE Price_N like '641857'";

TimeSpan timeSpanODBC;
DateTime timeODBC = DateTime.Now;

OdbcConnection odbcConnection = new OdbcConnection(@"Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=C:\Users\Vakshul\Documents\dbfs;Exclusive=No;Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;");
odbcConnection.Open();
OdbcCommand odbcCommand = new OdbcCommand(sqlEq, odbcConnection);
odbcCommand.ExecuteScalar();
timeSpanODBC = DateTime.Now - timeODBC;
double timeOdbcEqual = timeSpanODBC.TotalMilliseconds;
System.Console.WriteLine("Time spent via ODBC(milliseconds) using '=' to compare - {0}", timeOdbcEqual.ToString());


timeODBC = DateTime.Now;

odbcCommand = new OdbcCommand(sqlLike, odbcConnection);
odbcCommand.ExecuteScalar();
timeSpanODBC = DateTime.Now - timeODBC;
double timeOdbcLike = timeSpanODBC.TotalMilliseconds;
System.Console.WriteLine("Time spent via ODBC(milliseconds) using 'Like' to compare - {0}", timeOdbcLike.ToString());

TimeSpan timeSpanOLEDB;
DateTime timeOLEDB = DateTime.Now;

OleDbConnection oleDbCon = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=C:\Users\Vakshul\Documents\dbfs;Collating Sequence=MACHINE;Mode=Read");
oleDbCon.Open();
new OleDbCommand("set enginebehavior 80", oleDbCon).ExecuteNonQuery();
OleDbCommand oleDbcommand = new OleDbCommand(sqlEq, oleDbCon);
oleDbcommand.ExecuteScalar();
timeSpanOLEDB = DateTime.Now - timeOLEDB;
double timeOLEDBEqual = timeSpanOLEDB.TotalMilliseconds;
System.Console.WriteLine("Time spent via OLEDB(milliseconds) using '=' to compare - {0}", timeOLEDBEqual.ToString());

timeOLEDB = DateTime.Now;

oleDbcommand = new OleDbCommand(sqlLike, oleDbCon);

oleDbcommand.ExecuteScalar();
timeSpanOLEDB = DateTime.Now - timeOLEDB;
double timeOLEDLike = timeSpanOLEDB.TotalMilliseconds;
System.Console.WriteLine("Time spent via OLEDB(milliseconds) using 'Like' to compare - {0}", timeOLEDLike.ToString());

Обратите внимание на эту строку в том же соединении:

new OleDbCommand("set enginebehavior 80", oleDbCon).ExecuteNonQuery();

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

Мои тайминги без "Set EngineBehavior 80":

Time spent via ODBC(milliseconds) using '=' to compare - 4.0002
Time spent via ODBC(milliseconds) using 'Like' to compare - 1.0001
Time spent via OLEDB(milliseconds) using '=' to compare - 352.0201
Time spent via OLEDB(milliseconds) using 'Like' to compare - 659.0377

и с помощью "Set EngineBehavior 80":

Time spent via ODBC(milliseconds) using '=' to compare - 3.0001
Time spent via ODBC(milliseconds) using 'Like' to compare - 2.0002
Time spent via OLEDB(milliseconds) using '=' to compare - 15.0008
Time spent via OLEDB(milliseconds) using 'Like' to compare - 3.0002

Просто интересно ... ваш столбец "Price_n" - это NUMERIC или STRING столбец. Если числовой, то мне интересно, пытается ли VFP OleDb преобразовать все ваши числовые значения в эквивалент STRING для тестирования вместо преобразования цитируемой строки в числовой эквивалент типа данных Price_n, поскольку я ожидал бы, что естественный индекс будет основан на .

Если да, попробуйте протестировать и изменить все предложения WHERE соответственно на

ГДЕ price_n = 641857

WHERE price_n Нравится 641857

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

"1" LIKE "1"
"1" LIKE "10"
"1" LIKE "19302"... etc where they all start with a same value

@Sergiy, Есть большая разница в том, что ты делаешь. Драйвер ODBC не существует для версий после VFP6 (а именно 2.5 или 2.6 был последним пакетом, содержащим драйвер ODBC). IOW ODBC поддерживает только механизм VFP6. OTOH VFPOLEDB поддерживает движок VFP9 (и все эти дополнительные возможности SQL с наворотами).

Между этими механизмами возникала проблема, из-за которой запросы к текстовым полям замедлялись: если кодовая страница ОС отличается от кодовой страницы таблицы и поиск выполняется по индексу, выражение которого имеет символьный тип. Тогда он не использует индекс, а выполняет сканирование таблицы. Эта «ошибка» появилась после первоначального выпуска VFP9 и не исправила AFAIK.

Согласно = vs like, like неявно является ANSI, поэтому ведет себя как ‹g> с использованием оператора == (точное совпадение). С =, если ANSI выключен, вы получаете частичные совпадения как истинные.

PS: VFPOLEDB, даже после исправления кодовых страниц, немного медленнее, но незначительно.

Вот мои тайминги с вашим кодом на тестовой таблице с 1000000 строк:

Time spent via ODBC(milliseconds) using '=' to compare - 41.0023
Time spent via ODBC(milliseconds) using 'Like' to compare - 0
Time spent via OLEDB(milliseconds) using '=' to compare - 68.0038
Time spent via OLEDB(milliseconds) using 'Like' to compare - 2.0002
ODBC is faster than OLEDB 2 times using '=' to compare
ODBC is faster than OLEDB 2 times using 'Like' to compare

И это время после второго запуска:

Time spent via ODBC(milliseconds) using '=' to compare - 1
Time spent via ODBC(milliseconds) using 'Like' to compare - 1.0001
Time spent via OLEDB(milliseconds) using '=' to compare - 3.0001
Time spent via OLEDB(milliseconds) using 'Like' to compare - 0
ODBC is faster than OLEDB 3 times using '=' to compare
ODBC is faster than OLEDB 3 times using 'Like' to compare

@ Цетин Басоз,

Между этими механизмами возникала проблема, из-за которой запросы к текстовым полям замедлялись: если кодовая страница ОС отличается от кодовой страницы таблицы и поиск выполняется по индексу, выражение которого имеет символьный тип. Тогда он не использует индекс, а выполняет сканирование таблицы. Эта «ошибка» появилась после первоначального выпуска VFP9 и не исправила AFAIK.

Вы попали в суть дела!

Я не знал об этой особенности. Решил проверить, так ли это на самом деле. Если ваше предположение было верным, причина низкой скорости заключалась в том, что кодовые страницы файла DBF и моей ОС были разными. Чтобы проверить это, я установил Visual Fox Pro 9 (никогда раньше с этим не сталкивался) и перенес все данные в новую таблицу. Затем я открыл таблицу в конструкторе таблиц и создал обычный индекс для поля PRICE_N. Таким образом, кодовые страницы новой таблицы и моей ОС стали одинаковыми.

Затем снова запускаю тест. Результат резко изменился.

После первого запуска:

Time spent via ODBC(milliseconds) using '=' to compare - 12,5016
Time spent via ODBC(milliseconds) using 'Like' to compare - 3,5005
Time spent via OLEDB(milliseconds) using '=' to compare - 20,0025
Time spent via OLEDB(milliseconds) using 'Like' to compare - 3,0004
ODBC is faster than OLEDB 2 times using '=' to compare
ODBC is faster than OLEDB 2 times using 'Like' to compare

После второго запуска:

Time spent via ODBC(milliseconds) using '=' to compare - 3,0004
Time spent via ODBC(milliseconds) using 'Like' to compare - 2,5003
Time spent via OLEDB(milliseconds) using '=' to compare - 11,0014
Time spent via OLEDB(milliseconds) using 'Like' to compare - 3,5005
ODBC is faster than OLEDB 4 times using '=' to compare
ODBC is faster than OLEDB 4 times using 'Like' to compare

Спасибо, Цетин Басоз. Комментарий был потрясающим :)

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