Unidac with SQL server very slow

[bluedragon]

hello,
good afternoon,

UniDAC after updating to the latest version 5.2.7,
mssql server querys were too slow.

I do not understand very well because if they are implemented in design are fast but when executed in runtime are super slow! ...

to have an idea, running the same query with the same parameters and macros in design and runtime gives the following results on the monitor 3.0.0.4:

designtime: 0.608 and 1.560
runtime: 54.9 and 60.63

I am running these queries in sqlserver 2008 with the native client 2008 installed on delphi IDE XE2 without additional parameter in Uniconection.

the first time, the installation has been updated,
as everything was very slow, uninstalled everything and went back to install but the problem continues...
can you give little help please,

thank you

best regards.

[AlexP]

Hello,

Please make sure the same driver is used in both design and run time. Also, please send the script for creating and filling the table to alexp*devart*com, and provide the SQL query, on which performance loss is reproduced

[bluedragon]

hello,

first let me ask you, how you change the driver in design vs runtime,
i do not change any property in connection,
i simple use connected method to connect server.

the query is this:


SELECT Q.DATA,SUM(Q.SALDO) QTT,SUM(Q.VSALDO) CUSTOMP,SUM(Q.TF) MOOP
FROM
(
select cast(P.BUDAT as DATE) DATA,P.MATNR,CAST(CAST(P.AUFNR AS NUMERIC(10)) AS VARCHAR(10)) AUFNR,D.MAKTG,
SUM(case when P.SHKZG='S' then P.MENGE ELSE 0 END) ENT,
SUM(case when P.SHKZG='S' then P.DMBTR ELSE 0 END) VEN,
SUM(case when P.SHKZG='H' then P.MENGE*-1 ELSE 0 END) SAI,
SUM(case when P.SHKZG='H' then P.DMBTR*-1 ELSE 0 END) VSAI,
SUM(case when P.SHKZG='S' then P.MENGE ELSE P.MENGE*-1 END) SAL,
SUM(case when P.SHKZG='S' then P.DMBTR ELSE P.DMBTR*-1 END) VSAL,
ISNULL((SELECT sum(
case when TP.ILE01='H' and TP.ISM01>0 and TP.LTXA1='' THEN TP.ISM01
when TP.ILE01='H' and TP.ISM01>0 and TP.LTXA1<>'' THEN TP.ISM01*-1 ELSE 0 END)
from mtp.AFRU TP (NOLOCK)
WHERE TP.MANDT=P.MANDT AND TP.AUFNR=P.AUFNR),0) TF,
CAST(0 AS NUMERIC(12,2)) TM,
CAST(0 AS NUMERIC(12,2)) TT,
cast(TP.DESCRICAO as varchar(40)) TP,cast(LP.DESCRICAO as varchar(40)) LP
from mtp.AUFM P (NOLOCK)
inner join mtp.AUFK OP (nolock)
on P.MANDT=OP.MANDT AND P.AUFNR=OP.AUFNR
LEFT JOIN mtp.MARA M (NOLOCK)
ON P.MANDT=M.MANDT AND P.MATNR=M.MATNR
LEFT JOIN mtp.ZTABTIPOPRODUTO TP (NOLOCK)
ON P.MANDT=TP.MANDT AND M.TIPOPRODUTO=TP.TIPOPRODUTO
LEFT JOIN mtp.ZTABLINHAPRODUTO LP (NOLOCK)
ON P.MANDT=LP.MANDT AND M.LINHAPRODUTO=LP.LINHAPRODUTO
LEFT JOIN mtp.MAKT D (NOLOCK)
ON M.MANDT=D.MANDT and M.MATNR=D.MATNR and D.SPRAS='P'
WHERE P.MANDT=:mandt and P.WERKS='BM01' AND LGORT= '01' /*:armazem*/ AND OP.AUART<>'ZP02' AND
D.MAKTG not like '%PROD%' AND D.MAKTG not like '%PRD%' AND
P.BUDAT BETWEEN :datai AND :dataf
GROUP BY P.MANDT,P.BUDAT,P.MATNR,P.AUFNR,D.MAKTG,TP.DESCRICAO,LP.DESCRICAO
) Q
GROUP BY Q.DATA
ORDER BY Q.DATA

can you help me, please.

thanks

[bluedragon]

hello AlexP,

best for you analyze,

I think something missing because it also coincided with the installation of sql express 2012 uninstalled it because the server is SQL 2008 R2, i go to connection properties and changed the native client for 2008 but pressiste the problem,
when I run this query i design mode or in analyzer the result is expected,
however in runtime is 60 s.

i love this component but this situation drive me crazy,
what you think?

SELECT Q.DATA,SUM(Q.SALDO) QTT,SUM(Q.VSALDO) CUSTOMP,SUM(Q.TF) MOOP
FROM
(
select cast(P.BUDAT as DATE) DATA,
SUM(case when P.SHKZG='S' then P.MENGE ELSE P.MENGE*-1 END) SALDO,
SUM(case when P.SHKZG='S' then P.DMBTR ELSE P.DMBTR*-1 END) VSALDO,
ISNULL((SELECT sum(
case when TP.ILE01='H' and TP.ISM01>0 and TP.LTXA1='' THEN TP.ISM01
when TP.ILE01='H' and TP.ISM01>0 and TP.LTXA1<>'' THEN TP.ISM01*-1 ELSE 0 END)
from mtp.AFRU TP (NOLOCK)
WHERE TP.MANDT=P.MANDT AND TP.AUFNR=P.AUFNR),0) TF
from mtp.AUFM P (NOLOCK)
inner join mtp.AUFK OP (nolock)
on P.MANDT=OP.MANDT AND P.AUFNR=OP.AUFNR
inner JOIN mtp.MARA M (NOLOCK)
ON P.MANDT=M.MANDT AND P.MATNR=M.MATNR
inner JOIN mtp.MAKT D (NOLOCK)
ON M.MANDT=D.MANDT and M.MATNR=D.MATNR and D.SPRAS='P'
LEFT JOIN mtp.ZTABTIPOPRODUTO TP (NOLOCK)
ON P.MANDT=TP.MANDT AND M.TIPOPRODUTO=TP.TIPOPRODUTO
LEFT JOIN mtp.ZTABLINHAPRODUTO LP (NOLOCK)
ON P.MANDT=LP.MANDT AND M.LINHAPRODUTO=LP.LINHAPRODUTO

WHERE P.MANDT=100 and P.WERKS='BM01' AND LGORT= '01' AND OP.AUART<>'ZP02' AND
D.MAKTG not like '%PROD%' AND D.MAKTG not like '%PRD%' AND
P.BUDAT BETWEEN '20140101' AND '20140131'
GROUP BY P.MANDT,P.BUDAT,P.AUFNR

) Q
GROUP BY Q.DATA
ORDER BY Q.DATA

[AlexP]

Please make sure you have installed the correct provider in the UniConnection.SpecificOptions.Values['OLEDBProvider'] property

[bluedragon]

hello AlexP,

thank you very much,
in the UniConnection.SpecificOptions.Values['OLEDBProvider'] property i Put : prSQL
now is faster then the query analyser!...

best regards.

[AlexP]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.