Active Query Builder support area

Query very slow with large tables MSSQL

Avatar
  • updated
  • Completed

Hi!

I have one query with top 0, for MSSQL database, but this query is very slow when I use Active Query Builder.
I have source code and when I was debugging, I've seen that my query is "partitioned". More details as follows:

QueryBuilder.SyncSQL := receives my SQL (with top 0).

"SyncSQL" calls "SetSyncSQL" in acQBBase.pas unit .
"SetSyncSQL" calls "ParseSQL" receiving "Value" and "Value" is my SQL (once again, with top 0).
Even using top 0, the Query Builder takes long time to show up.
Is this known bug?
My SQL below.

Select TOP 0

W_Calcula_Fet.CBW_FUN_ID,
CBW_FUNCIONARIOS.CBW_DEP_ID,
CBW_PES_PROGRAMACAO_DE_ESCALAS.CBW_PES_DATA_PROGRAMACAO,
W_Calcula_Fet.CBW_FET_TIPO_PONTO,
dbo.CONVERTE_MINUTO(W_Calcula_Fet.[TOTAL MINUTOS]) As [Total Horas],
dbo.CONVERTE_MINUTO(W_Calcula_Fet.[ADN MINUTOS TOTAL]) As [Total ADN],
dbo.CONVERTE_MINUTO(dbo.CALCULO_HORA_PONTO(dbo.MIN_HR(W_Calcula_Fet.CBW_FET_HR_02, W_Calcula_Fet.CBW_FET_HR_01, W_Calcula_Fet.CBW_FET_HR_04, W_Calcula_Fet.CBW_FET_HR_03), 1)) As [Total Horas Extra],
dbo.CONVERTE_MINUTO(dbo.CALCULO_HORA_PONTO(dbo.MIN_HR(W_Calcula_Fet.CBW_FET_HR_02, W_Calcula_Fet.CBW_FET_HR_01, W_Calcula_Fet.CBW_FET_HR_04, W_Calcula_Fet.CBW_FET_HR_03), 2)) As [Total Horas Excedentes],
dbo.CONVERTE_MINUTO(dbo.CALCULO_HORA_PONTO(dbo.MIN_HR(W_Calcula_Fet.CBW_FET_HR_02, W_Calcula_Fet.CBW_FET_HR_01, W_Calcula_Fet.CBW_FET_HR_04, W_Calcula_Fet.CBW_FET_HR_03), 3)) As [Total Horas Debito],
W_Calcula_Fet.VALES,
CBW_FUNCIONARIOS.CBW_FUN_ESCALA,
dbo.CALCULO_HORA_PONTO(dbo.MIN_HR(W_Calcula_Fet.CBW_FET_HR_02, W_Calcula_Fet.CBW_FET_HR_01, W_Calcula_Fet.CBW_FET_HR_04, W_Calcula_Fet.CBW_FET_HR_03), 1) + dbo.CALCULO_HORA_PONTO(dbo.MIN_HR(W_Calcula_Fet.CBW_FET_HR_02, W_Calcula_Fet.CBW_FET_HR_01, W_Calcula_Fet.CBW_FET_HR_04, W_Calcula_Fet.CBW_FET_HR_03), 2) As [Corte Minuto],
W_Calcula_Fet.CBW_FET_ID,
CBW_TMP_TIPOS_MARCACOES_PONTO.CBW_TMP_DESC_MARCACAO,
W_Calcula_Fet.CBW_FET_HR_01,
W_Calcula_Fet.CBW_FET_HR_02,
W_Calcula_Fet.CBW_FET_HR_03,
W_Calcula_Fet.CBW_FET_HR_04
From W_Calcula_Fet
Inner Join CBW_PES_PROGRAMACAO_DE_ESCALAS On W_Calcula_Fet.CBW_PES_ID = CBW_PES_PROGRAMACAO_DE_ESCALAS.CBW_PES_ID
Inner Join CBW_FUNCIONARIOS On W_Calcula_Fet.CBW_FUN_ID = CBW_FUNCIONARIOS.CBW_FUN_ID
Inner Join CBW_TMP_TIPOS_MARCACOES_PONTO On W_Calcula_Fet.CBW_FET_TIPO_PONTO = CBW_TMP_TIPOS_MARCACOES_PONTO.CBW_TMP_ID
Where (W_Calcula_Fet.CBW_FUN_ID Not In (999999)
And CBW_FUNCIONARIOS.CBW_DEP_ID = :depto
And CBW_PES_PROGRAMACAO_DE_ESCALAS.CBW_PES_DATA_PROGRAMACAO Between (:dtInicial) And (:dtFinal)
And W_Calcula_Fet.CBW_FET_TIPO_PONTO Not In (99)
And W_Calcula_Fet.VALES = 0
And dbo.CALCULO_HORA_PONTO(dbo.MIN_HR(W_Calcula_Fet.CBW_FET_HR_02, W_Calcula_Fet.CBW_FET_HR_01, W_Calcula_Fet.CBW_FET_HR_04, W_Calcula_Fet.CBW_FET_HR_03), 1) + dbo.CALCULO_HORA_PONTO(dbo.MIN_HR(W_Calcula_Fet.CBW_FET_HR_02, W_Calcula_Fet.CBW_FET_HR_01, W_Calcula_Fet.CBW_FET_HR_04, W_Calcula_Fet.CBW_FET_HR_03), 2) > :corte
And W_Calcula_Fet.[EVENTO OPERACAO] Not In (99))
/*WHERE_ADD*/
Order By W_Calcula_Fet.CBW_FUN_ID, CBW_FUNCIONARIOS.CBW_DEP_ID, W_Calcula_Fet.CBW_PES_DATA_PROGRAMACAO
/*ORDER_ADD*/

Even using top 0, the Query Builder takes long time to show up.


Avatar
Sergey Kraikov

Hello Ronélio,

unfortunately, I still cannot reproduce this issue using the latest version, seems the
latest version works fine.

Could you please test your query using our precompiled demo application from
http://activequerybuilder.com/files/aqb_demo.zip ?

Avatar
Ronélio Oliveira

Hi,

We tried with new version of QueryBuilder and We got the same problem.

When I'm debugging and freezes at this point SelectStatement := TacQueryBuilderAccess(QueryBuilder).ParseSQL(SQL, false, false));

Avatar
Ronélio Oliveira

Ok!

We're trying the new version to check if this issue were fixed.

Avatar

Hi Ronélio,

the version you using is 2 years old, in these 2 years we implemented many improvements in the MS SQL parser.

I tried your query on the latest version - and QueryBuilder loads this query almost instantly. Could you please test the latest version?

×

Avatar
Ronélio Oliveira

According to "acQueryBuilder.ver" file, my version is 1.23.9.0 Standard Edition.

Syntax provider is acMSSQLSynProvider

Avatar
Andrey Zavyalov, PM

Please indicate your Active Query Builder version and edition, syntax provider being used.