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

Question

I’m using a Clientdataset for consultation in a remote database, the table has more than 100 thousand records and this 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

Answer 1

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

Create indexes for some fields can help a lot, but it is no use to go out creating a lot of indexes, this can harm performance. It is necessary to analyze the consultation plan to evaluate the best indexes to be created.

Answer 2

Use the property PacketRecords to carry his TClientDataSet incrementally.

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

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

Answer 3

If there is no index you are doing full scan table and that is the reason for the delay. Create indexes for each field in Join and Where and run the query again