0

I have an SQL Server query that runs in milliseconds on the SQL Server Management Studio or on a TADOQuery, but takes 17 seconds to run on a TFDQuery.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

if OBJECT_ID('tempdb..#DADES') is not null DROP TABLE #DADES

SELECT 'O' tipus,l.numero as Comanda, o.NumeroOrdre as Ordre, ag.nom as Client,
       c.Referencia, o.estat, max(a.Descripcio) descripcio, ag.provincia, c.ProvinciaEnvio,c.poblacioEnvio,c.codiPostalEnvio,c.adresaEnvio,c.telefonoEnvio,
       c.Transportista, o.ObservacionsTransport, o.DataCarrega, o.DataEntrega,
       case when tipusOrdre=1 then 'PANEL' else case when tipusOrdre=2 then 'TAPAJUNT' else 'ALTRES' end end Tipus2,
       dbo.f_palets_pendents(o.NumeroOrdre) as paletsPendents ,
       dbo.f_palets_assignats(o.NumeroOrdre) as paletsAssignats,
       c.ports,
       sum(dbo.f_pes_linia_comanda(l.numero,L.linia)) pes_lin,
       c.envio
INTO #DADES
FROM OrdresFabricacio o
     LEFT OUTER JOIN LiniesComandesVendes l ON o.NumeroOrdre=l.numeroordre
     LEFT OUTER JOIN CapsaleraComandesVendes c ON c.Numero=l.numero
     LEFT OUTER JOIN CapsaleraAgenda ag ON ag.codi=c.client
     LEFT OUTER JOIN Articles a ON a.codi=l.article
WHERE o.Transport=63163
GROUP BY l.numero,o.NumeroOrdre,ag.nom,c.Referencia, o.estat,ag.provincia,c.ProvinciaEnvio,c.poblacioEnvio,c.codiPostalEnvio,c.adresaEnvio,c.telefonoEnvio,
         c.Transportista, o.ObservacionsTransport, o.DataCarrega, o.DataEntrega,o.tipusOrdre,c.ports,c.envio
OPTION(RECOMPILE)

I run them on a new TFDConnection and TFDQuery with all their options by default. With SET TRANSACTION ISOLATION LEVEL READ COMMITTED I ensure that it runs on the same isolation level that the SQL Server Management Studio. With into #DADES I force that no data is returned (instead it's copied to a temporary table), so I can discard any potential problem and delays fetching the data to local buffers. And finally, with WITH(RECOMPILE) I force SQL Server to re-evaluate the execution plan for the query (to prevent using some old plan with outdated statistics).

Additionally I have also tried disabling all the Command Text Processing options on the ResourceOptions of the TFDConnection, with no improvement.

The problem seems to be related to the dbo.f_pes_linia_comanda() function, because replacing it by an expression makes the same query to run correctly on milliseconds. Something like :

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

if OBJECT_ID('tempdb..#DADES') is not null DROP TABLE #DADES

SELECT 'O' tipus,l.numero as Comanda, o.NumeroOrdre as Ordre, ag.nom as Client,
       c.Referencia, o.estat, max(a.Descripcio) descripcio, ag.provincia, c.ProvinciaEnvio,c.poblacioEnvio,c.codiPostalEnvio,c.adresaEnvio,c.telefonoEnvio,
       c.Transportista, o.ObservacionsTransport, o.DataCarrega, o.DataEntrega,
       case when tipusOrdre=1 then 'PANEL' else case when tipusOrdre=2 then 'TAPAJUNT' else 'ALTRES' end end Tipus2,
       dbo.f_palets_pendents(o.NumeroOrdre) as paletsPendents ,
       dbo.f_palets_assignats(o.NumeroOrdre) as paletsAssignats,
       c.ports,
       sum(l.Quantitat) pes_lin,
       c.envio
INTO #DADES
FROM OrdresFabricacio o
FROM OrdresFabricacio o
     LEFT OUTER JOIN LiniesComandesVendes l ON o.NumeroOrdre=l.numeroordre
     LEFT OUTER JOIN CapsaleraComandesVendes c ON c.Numero=l.numero
     LEFT OUTER JOIN CapsaleraAgenda ag ON ag.codi=c.client
     LEFT OUTER JOIN Articles a ON a.codi=l.article
WHERE o.Transport=63163
GROUP BY l.numero,o.NumeroOrdre,ag.nom,c.Referencia, o.estat,ag.provincia,c.ProvinciaEnvio,c.poblacioEnvio,c.codiPostalEnvio,c.adresaEnvio,c.telefonoEnvio,
         c.Transportista, o.ObservacionsTransport, o.DataCarrega, o.DataEntrega,o.tipusOrdre,c.ports,c.envio
OPTION(RECOMPILE)

So my question is if anyone knows why FireDAC runs queries slower when they have some scalar functions ?, why do they use a different execution plan than when sending directly the exact same query to the engine on SQL Server Management Studio ?, alternatively, is there a way to get the execution plan used for the TFDQuery so I can see what it got wrong and try to work around it ?.

Thank you.

Marc Guillot
  • 6,090
  • 1
  • 15
  • 42
  • 1
    You can run SQL Profiler and get execution plan from there. Also with profiler you can actually see how your statement is executed from different tools. In general MS SQL Server have many performance issues regarding scalar functions. Also you can try to convert your function to table valued with single row single column - there are many reports that this fix performance issues. – Miroslav Penchev Oct 08 '20 at 08:46
  • Scalars UDF are always reducing speed of query because this type of objects need a sequential scan and disable any paralkellize acces. On a second hand, the driver uses by the two solutions can be different, and when different the session parameters are not the same. Delphi is a very old technology (I was a mentor in Delphi) in the 90's) and I think the driver uses is not adequate ! – SQLpro Oct 08 '20 at 08:52
  • 1
    @SQLPro Delphi is technology up to date! Period ;-) Fact that it exists from long time doesnt mean it's out-of-date. Mentioned tools and components use up to date drivers and tools. – Miroslav Penchev Oct 08 '20 at 09:05
  • @SQLpro I have added a TFDPhysMSSQLDriverLink FDConnection to use both SQL Server Native Client 11.0 and ODBC Driver 17 for SQL Server on the very last Delphi release, both resulting on slow execution times. The problem is not related to the drivers. Yes, scalar UDFs are not the fastest solution, but these run fine on SQL Server Management Studio, so there is something that FireDAC is doing that confuses the engine. :-( – Marc Guillot Oct 08 '20 at 09:42
  • @MiroslavPenchev I can't replace the scalar function with a table valued function. When I replace `sum(dbo.f_pes_linia_comanda(l.numero,L.linia))` with `sum((select Resultat from dbo.f_pes_linia_comanda(l.numero,L.linia)))` it tells me that I can't apply an aggregate function on an expression containing a subquery. I'm analyzing the execution plans showed on the Profiler. Thank you. – Marc Guillot Oct 08 '20 at 10:13
  • @MiroslavPenchev I have joined the table valued function with an OUTER APPLY, so I can sum its result, and it's a bit faster (12 seconds) but not much. it also uses a wrong execution plan, because the new query still runs in milliseconds on the Management Studio. – Marc Guillot Oct 08 '20 at 10:17
  • @MarcGuillot we also had such cases in the past, just SQL query optimizer sometimes get confused by a lot of different cases and produce bad execution plans. Have you tried to update stats `exec sp_updatestats` just before you run your query? Also worth to try to make SQL Table valued function which will return your whole report and from Delphi just execute your function `SELECT * FROM ReportFunc(report params)` and see how optimizer will react. – Miroslav Penchev Oct 08 '20 at 11:14
  • 1
    Perhaps [Erland's discusson](http://www.sommarskog.se/query-plan-mysteries.html) will help you find the issue - solving problems like this begin with examining the execution plans. – SMor Oct 08 '20 at 12:14
  • @MiroslavPenchev that's weird, I have tried putting the query both on a table-valued function and an stored procedure, and I get the same result: around 17 seconds from FireDAC and just milliseconds when calling them from the Management Studio. There has to be something configured different on the session when it comes from FireDAC. – Marc Guillot Oct 08 '20 at 14:52
  • @MiroslavPenchev The only thing that speeds up the query on FireDAC is to change `SET ARITHABORT OFF` as the SMor's document says NOT to do. Now the query runs in milliseconds. I guess I have to study why this setting changes the execution, because it doesn't use any parameters, so the problem won't be parameters sniffing, as the document says. – Marc Guillot Oct 08 '20 at 15:23
  • @SMor Thanks, I don't understand why my problem is anywhere related to parameter sniffing, but setting `SET ARITHABORT OFF` makes it run in milliseconds. Your recommended discussion says NOT to do that, so I guess I have to study why this setting is affecting my query, which doesn't have any parameter. – Marc Guillot Oct 08 '20 at 15:26
  • SSMS retrieves results in a unidirectional read only manner. It doesn't retrieve a result set / cursor you can navigate and/or update. – Brian Oct 08 '20 at 16:24
  • Since this is obviously FireDAC-specific, have you tried comparing what the SSMS Profiler logs from FireDAC when it opens the query with what the SSMS Query window sends? – MartynA Oct 08 '20 at 17:44
  • 1
    @MarcGuillot That's strange. I found one more discussion where exactly the opposite effect is discussed - turning that option ON drastically speed up the query. https://dba.stackexchange.com/questions/9840/why-would-set-arithabort-on-dramatically-speed-up-a-query#9843 – Miroslav Penchev Oct 09 '20 at 05:16
  • 1
    @MartynA Thanks Martyn. Now it seems to not be related to FireDAC at all. Setting the ARITHABORT ON (the default value on Management Studio) makes it run in milliseconds on FireDAC, and accordingly setting it OFF on Management Studio makes it very slow there. I will delete this question and start a new one, next week, asking why that settings affects so strongly the performance. It seems to be related to parameters sniffing (which I never heard of before), but I don't understand why they behave differently when doing the exact same call. – Marc Guillot Oct 09 '20 at 08:37
  • @MiroslavPenchev Yes I wrote it wrong. Setting it ON is what speeds it up. I guess I have work to do to understand what is happening but at least now the problem is well delimited. – Marc Guillot Oct 09 '20 at 09:16
  • @MarcGuillot Thanks. Please keep us informed of your investigations. A new q sounds like a great idea. – MartynA Oct 09 '20 at 10:14
  • It is exactlys that I have say at the first moment.... "the driver uses by the two solutions can be different, and when different the session parameters are not the same" some drivers have the ARITHABORT = ON, which I think it is the case in Delphi default configuration, resulting in a bad execution plan.... Old technologies !!! – SQLpro Oct 09 '20 at 16:26
  • @SQLpro No, you are wrong, I'm using the very latest ODBC Driver for SQL Server 17. This is not Delphi or the driver's fault, different settings just result in different behaviors. The links we have been discussing show how this happens with all languages. – Marc Guillot Oct 10 '20 at 08:46
  • I mean that Delphi uses the ODBC driver the old way... so session parameters are obsolete ! – SQLpro Oct 11 '20 at 12:49

0 Answers0