Problemas de desempenho para iterar resultados com C # SQLite DataReader e banco de dados anexado

9

Estou usando System.Data.SQLite e SQLiteDataReader no meu projeto C #. Estou enfrentando problemas de desempenho ao obter os resultados de uma consulta com bancos de dados anexados.

Aqui está um exemplo de uma consulta para pesquisar texto em dois bancos de dados:

ATTACH "db2.db" as db2;

SELECT MainRecord.RecordID,
((LENGTH(MainRecord.Value) - LENGTH(REPLACE(UPPER(MainRecord.Value), UPPER("FirstValueToSearch"), ""))) / 18) AS "FirstResultNumber",
((LENGTH(DB2Record.Value) - LENGTH(REPLACE(UPPER(DB2Record.Value), UPPER("SecondValueToSearch"), ""))) / 19) AS "SecondResultNumber"
FROM main.Record MainRecord
JOIN db2.Record DB2Record ON DB2Record.RecordID BETWEEN (MainRecord.PositionMin) AND (MainRecord.PositionMax)
WHERE FirstResultNumber > 0 AND SecondResultNumber > 0;

DETACH db2;

Ao executar essa consulta com SQLiteStudio ou SQLiteAdmin, isso funciona bem, estou obtendo os resultados em alguns segundos (a tabela Record pode conter centenas de milhares de registros, a consulta retorna 36000 registros).

Ao executar essa consulta em meu projeto em C #, a execução também leva alguns segundos, mas leva horas para percorrer todos os resultados.

Aqui está o meu código:

// Attach databases

SQLiteDataReader data = null;

using (SQLiteCommand command = this.m_connection.CreateCommand())
{
    command.CommandText = "SELECT...";
    data = command.ExecuteReader();
}

if (data.HasRows)
{
    while (data.Read())
    {
        // Do nothing, just iterate all results
    }
}

data.Close();

// Detach databases

Chamar o método Read do SQLiteDataReader uma vez pode levar mais de 10 segundos! Eu acho que isso é porque o SQLiteDataReader é preguiçoso carregado (e por isso não retorna o conjunto de linhas inteiro antes de ler os resultados), estou certo?

EDIT 1:

Eu não sei se isso tem algo a ver com carregamento lento, como eu disse inicialmente, mas tudo que eu quero é conseguir TODOS os resultados assim que a consulta terminar. Não é possível? Na minha opinião, isso é realmente estranho que leva horas para obter resultados de uma consulta executada em poucos segundos ...

EDIT 2:

Acabei de adicionar um COUNT(*) na minha consulta de seleção para ver se conseguia o número total de resultados no primeiro data.Read() , só para ter certeza de que era apenas a iteração dos resultados que estava sendo realizada tanto tempo. E eu estava errado: este novo pedido é executado em poucos segundos no SQLiteAdmin / SQLiteStudio, mas leva horas para executar no meu projeto C #. Alguma idéia por que a mesma consulta é muito mais longa para executar no meu projeto c #?

EDIT 3:

Graças a EXPLAIN QUERY PLAN , notei que havia uma pequena diferença no plano de execução para a mesma consulta entre SQLiteAdmin / SQLiteStudio e meu projeto C #. No segundo caso, ele está usando um AUTOMATIC PARTIAL COVERING INDEX no DB2Record em vez de usar o índice de chave primária. Existe uma maneira de ignorar / desabilitar o uso de índices automáticos de cobertura parcial? Eu sei que é usado para acelerar as consultas, mas no meu caso, é bem o contrário que acontece ...

Obrigado.

    
por Morgan M. 20.05.2015 в 12:18
fonte

3 respostas

1

Além de encontrar registros correspondentes, parece que você também está contando o número de vezes que as strings foram correspondidas. O resultado dessa contagem também é usado na cláusula WHERE .

Você deseja o número de correspondências, mas o número de correspondências não importa na cláusula WHERE . Você pode tentar alterar a cláusula WHERE para:

WHERE MainRecord.Value LIKE '%FirstValueToSearch%' AND DB2Record.Value LIKE '%SecondValueToSearch%'

Isso pode não resultar em nenhuma diferença - especialmente se não houver um índice nas colunas Value - mas vale a pena uma chance. Índices em colunas de texto exigem muito espaço, então eu não recomendaria cegamente isso.

Se ainda não o fez, coloque um índice na coluna RecordID do DB2.

Você pode usar EXPLAIN QUERY PLAN SELECT ... para fazer o SQLite cuspir o que faz para tentar executar sua consulta, e a saída disso pode ajudar a diagnosticar o problema.

    
por C.Evenhuis 22.05.2015 / 16:02
fonte
1

Tem certeza de que usa a mesma versão do sqlite em System.Data.SQLite , SQLiteStudio e SQLiteAdmin? Você pode ter grandes diferenças.

    
por tafia 27.05.2015 / 11:37
fonte
0

Um motivo mais comum pelo qual a consulta SQL pode levar uma quantidade diferente de tempo quando executada com o ADO.NET e a partir do utilitário nativo (como SQLiteAdmin) são parâmetros de comando usados no CommandText (não está claro se o parâmetro é usado ou não ). Dependendo da implementação do provedor ADO.NET, os seguintes valores CommandText idênticos:

SELECT * FROM sometable WHERE somefield = ?   // assume parameter is '2'

e

SELECT * FROM sometable WHERE somefield='2'

pode levar a um plano de execução e desempenho de consulta absolutamente diferentes.

Outra sugestão: você pode desabilitar o journal (especificando "Journal Mode = off;" na string de conexão) e o modo síncrono ("Synchronous = off;"), pois essas opções também podem afetar o desempenho da consulta em alguns casos.

    
por Vitaliy Fedorchenko 27.05.2015 / 12:32
fonte