How to optimize the query in a remote bank using ClientDataset?

Question

How to optimize the query in a remote bank using ClientDataset?

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

I am using a ClientDataSet to query on a remote database, the table has more than 100,000 records and is getting very slow, the SQL I am using is:

select (coalesce (cast(A.con_baixa as date),current_date) - cast(A.con_vencto as date) )Diastraso,
        A.CLI_CODIGO, A.CON_DOCUMENTO, A.CON_PARCELA, A.CON_DOC_ORIGEM,cast(A.CON_LANCTO as date) CON_LANCTO,
        cast(A.CON_VENCTO as date)CON_VENCTO, A.CON_VALOR, A.PLA_CODIGO, A.CON_TIPO, A.CON_PREVISAO,  A.CON_ESPECIE,
        cast(A.CON_BAIXA as date)CON_BAIXA,A.CON_PAGO, (A.CON_JUROS + A.CON_MULTA) CON_JUROSMULTA, A.CON_SITUACAO,
        A.BANCO, A.AGENCIA, A.CONTA, A.CONTA_R, A.CHEQUE, A.EMITENTE, A.CIDADE, A.CLI_REPASSE_CODIGO, A.CLI_REPASSE_NOME,
        A.CON_VL_BRUTO, A.CON_ID, A.BOL_CONTA, A.BOL_NOSSONUMERO, A.BOL_LINHADIGITAVEL, A.BOL_LOTE, A.NOSSONUMERO,
        A.CON_HISTORICO, A.IDORDEMSERVICO, A.ECF_NUMERO,  A.STATUSCONTA, A.CON_FPAGTOID, A.CONTRATOLOC, A.CON_DESCONTO,
        A.CON_OUTROS, A.IDLOTE, A.IDPAI, A.MOVORIGEM, A.IDCARTAO, A.CON_DATA, A.IDBORDERO, A.CCUSTOID, A.IDDESCONTO,
        A.IDLOJA,B.cli_nome, C.PLA_DESCRICAO, D.DESCRICAO CENTROCUSTO, E.FDESCRICAO, NomeFantasia
from contas A
left join CLIENTES B on (B.cli_codigo = A.cli_codigo)
left join PLANOCONTAS C on A.PLA_CODIGO = C.PLA_CODIGO
left join CCUSTO D on A.CCUSTOID = D.CCUSTOID
left join FPAGTO E on A.CON_FPAGTOID = E.FPAGTOID
inner join Loja F on F.IdLoja = A.IdLoja
where  (A.CON_LANCTO >='03/05/2013 00:00:00' and A.CON_LANCTO <= '03/24/2015 23:59:59' )
        and A.CON_TIPO = 'R' and A.IdLoja in (1) and A.STATUSCONTA <> 'AGRUPAMENTO'
        order by A.IdLoja, A.CON_LANCTO

sql delphi

asked by anonymous 25.03.2015 / 18:04

3 answers

How to execute a method inside a textBox only if the user give enter? Copy struct C

score 1 · Answer 1

Have you been able to identify whether what is slow is the execution of the query or the loading in the dataset? how many records are returning in the query, so I understand, probably the problem is that many records are being returned, and as we know the clientdataset puts all in memory.

Creating indexes for some fields can help a lot, however, it does not help to create a lot of indexes, this can be detrimental to performance. It is necessary to analyze the query plan to evaluate the best indexes to be created.

score 0 · Answer 2

Use the PacketRecords property to load your TClientDataSet incrementally .

By experience, 500 is a good value. -1 serves to bring a single block (its case) and 0 only brings metadata about the query in question.

Avoid using values higher than 5000. By doing this, you will be transferring the memory bottleneck to the database query.

score 0 · Answer 3

0

If it has no index it is doing full scan table and this is the reason for the delay. Create indexes for each field in Join and Where and run the query again

25.03.2015 / 19:01