Hi,

My company is developping an application which neeeds to retrieve data from a database which has a table containing about 4 millions rows.

If I write the query using raw SQL like this, it's immediate.

            var dataAccessAdapter = CreateDataAccessAdapter();
            var query =
                $@"SELECT
                INF_Label AS ReferenceSuperviseur,
                INF_Date AS DateMesure,
                INF_Value AS ValeurMesure
                FROM View_Informations
                INNER JOIN View_ArchivedInformations ON View_ArchivedInformations.ID = View_Informations.ID
                WHERE LTRIM(RTRIM(INF_Label)) = '{tache.ReferenceSuperviseur}'";

            if (tache.DateLimiteInferieureMesures.HasValue)
            {
                query = query + "\r\n" + $@"AND INF_Date > '{tache.DateLimiteInferieureMesures.Value.ToString("yyyy/MM/dd hh:mm:ss")}'";
            }

            var results = await dataAccessAdapter.FetchQueryAsync<MesuresAImporterDto.MesureDto>(query);

If I convert this raw SQL using Linq provider, the query now takes more than 10 seconds and sometimes a Timeout exception is thrown.

            LinqMetaData linqMetaData = new LinqMetaData(CreateDataAccessAdapter());
            var query = from archivedInformation in linqMetaData.ViewArchivedInformation
                        join viewInformation in linqMetaData.ViewInformation on archivedInformation.Id equals viewInformation.Id
                        where viewInformation.InfLabel == tache.ReferenceSuperviseur
                        select new MesuresAImporterDto.MesureDto
                        {
                            DateMesure = archivedInformation.InfDate,
                            ValeurMesure = (decimal)archivedInformation.InfValue.Value,
                            ReferenceSuperviseur = viewInformation.InfLabel
                        };

            if (tache.DateLimiteInferieureMesures.HasValue)
            {
                query = query.Where(o => o.DateMesure >= tache.DateLimiteInferieureMesures);
            }

            var results = await query.ToListAsync();

Is there a way to enable consumed time log to permits me to find the reason of the poor performance?

Notes : 1) I tried with QuerySpec => same matter 2) I can't give you the database because I'm not the owner of this database. 3) The same matter occurs with an Oracle database (not the same tables) 4) Environment : * LLBLGEN 5.6.2 hotfix 20200207 * .NET Core 3.1

1) please check if the query you generate from linq/queryspec is identical to the query you pass to the plainsql API. I suspect it's not, hence the performance difference. (the where you append in the linq query is wrapping the previous select into a derived table). The Where clause of the plain sql uses a literal compare, not a compare using a parameter. This might cause a different plan altogether. (it shouldn't but alas... optimizers) 2) please use a profiler to check where things are slow. I suspect the delay is solely inside the DB (also as you state there are timeouts), and not because you're pulling millions of rows. 3) please show the queryspec query you tried, as one can stay close to the sql query using queryspec. I'll try to give a queryspec query below in a minute.

the projection pipeline is the same with plainsql and linq/queryspec so I don't think that's the culprit. I really think the sql you're sending to the DB using linq/queryspec is different from the plainsql query.

QuerySpec query I'd use: (I hope it compiles, not sure of all the names used )

IPredicate predicate = ViewInformationFields.InfLabel.Equal(tache.ReferenceSuperviseur);
if (tache.DateLimiteInferieureMesures.HasValue)
{
    predicate = predicate.And(ViewInformationFields.InfDate.GreaterEqual(tache.DateLimiteInferieureMesures));
}
var qf = new QueryFactory();
var q = qf.Create().
            .From(qf.ViewInformation.InnerJoin(qf.ViewArchivedInformation)
                            .On(ViewInformationFields.ID.Equal(ViewArchivedInformationFields.ID)))
            .Where(predicate)
            .Select(()=>new MesuresAImporterDto.MesureDto()
                    {
                        DateMesure = ViewArchivedInformationFields.InfDate.ToValue<DateTime>(),
                        ValeurMesure = ViewArchivedInformationFields.InfValue.ToValue<Decimal>(),
                        ReferenceSuperviseur = ViewInformationFields.InfLabel
                    });

This should get close to the sql query in the plain sql api.

If you have indices defined on the tables used in the views, be sure they're used by the sql queries produced. You can check this by requesting the query plan in e.g. ORM Profiler.

Using QuerySpec, the query are fast but for some value, timeout are thrown by SQL Server. If I manually convert the QuerySpec in raw SQL to launch it directly in SSMS, the query is immediate. It could be something like binding parameters

Concerning indexes, the matter is that I haven't the rights to modify the database if needed but I will try to look at it in a profiler.

TIMEOUT

    Query: SELECT [ScadaNetDb].[dbo].[View_ArchivedInformations].[INF_Date] AS [InfDate], [ScadaNetDb].[dbo].[View_ArchivedInformations].[INF_Value] AS [InfValue], [ScadaNetDb].[dbo].[View_Informations].[INF_Label] AS [InfLabel] FROM ([ScadaNetDb].[dbo].[View_Informations] INNER JOIN [ScadaNetDb].[dbo].[View_ArchivedInformations] ON [ScadaNetDb].[dbo].[View_Informations].[ID] = [ScadaNetDb].[dbo].[View_ArchivedInformations].[ID]) WHERE ( ( [ScadaNetDb].[dbo].[View_Informations].[INF_Label] = @p1 AND [ScadaNetDb].[dbo].[View_ArchivedInformations].[INF_Date] >= @p2))
    Parameter: @p1 : AnsiString. Length: 254. Precision: 0. Scale: 0. Direction: Input. Value: "CPVP2NEP02214".
    Parameter: @p2 : DateTime2. Length: 0. Precision: 7. Scale: 0. Direction: Input. Value: 24/01/2018 00:00:00.

IMMEDIATE

SELECT [ScadaNetDb].[dbo].[View_ArchivedInformations].[INF_Date] AS [InfDate], [ScadaNetDb].[dbo].[View_ArchivedInformations].[INF_Value] AS [InfValue], [ScadaNetDb].[dbo].[View_Informations].[INF_Label] AS [InfLabel] FROM ([ScadaNetDb].[dbo].[View_Informations] INNER JOIN [ScadaNetDb].[dbo].[View_ArchivedInformations] ON [ScadaNetDb].[dbo].[View_Informations].[ID] = [ScadaNetDb].[dbo].[View_ArchivedInformations].[ID]) 
WHERE ( ( [ScadaNetDb].[dbo].[View_Informations].[INF_Label] = 'CPVP2NEP02214' 
AND [ScadaNetDb].[dbo].[View_ArchivedInformations].[INF_Date] >= '2018-01-24 00:00:00'))
        

The two SQL are identical, besides the parameter biding. I think we should look at your results in the profiler test to see where is the slow process.

Hi,

I thought I would be able to request a trial license on ORM Profiler but unfortunately I can't (I probably already ask one trial license long time ago) so ...

I finally use Express Profiler application to look at the queries sent to the database and so that the query (generated from QuerySpec) is wrapped with the sp_executesql keyword.

When executed from application, this query takes (sometimes) more than 30 seconds => timeout If I takes this query and execute it directly on SSMS, query returns directly => no timeout

exec sp_executesql N'SELECT [ScadaNetDb].[dbo].[View_ArchivedInformations].[INF_Date] AS [InfDate], [ScadaNetDb].[dbo].[View_ArchivedInformations].[INF_Value] AS [InfValue], [ScadaNetDb].[dbo].[View_Informations].[INF_Label] AS [InfLabel] FROM ([ScadaNetDb].[dbo].[View_Informations] INNER JOIN [ScadaNetDb].[dbo].[View_ArchivedInformations] ON [ScadaNetDb].[dbo].[View_Informations].[ID] = [ScadaNetDb].[dbo].[View_ArchivedInformations].[ID]) WHERE ( ( [ScadaNetDb].[dbo].[View_Informations].[INF_Label] = @p1 AND [ScadaNetDb].[dbo].[View_ArchivedInformations].[INF_Date] >= @p2))',N'@p1 varchar(254),@p2 datetime2(7)',@p1='CBTP2NEP02214',@p2='2018-01-24 00:00:00'
go

Note : the same query (with different parameters) is sent about 100 times to the database and only a few failed with timeout exception.

I finally find a way to resolve the matter of timeout using SetDefaultCompatibilityLevel(SqlServerCompatibilityLevel.SqlServer2005) instead of SetDefaultCompatibilityLevel(SqlServerCompatibilityLevel.SqlServer2012).

My SQL Server is a 2016 so I don't really understand the reason but...

In fact, it only solves the matter on the parameters that I used but if the date parameter change, the matter come back so forget my last message.

I remark that the queries that failed are a important number of "reads". My problem is that : * I can't optimize the query because it uses view defined by the provider * I can't add indexes because view contains subquery, unions,...

If I increase the DataAccessAdapter.CommandTimeout property to 60, the queries didn't failed. Not really happy with this.

(You can download ORM Profiler v2.0.2 for free from the 'My account -> Downloads section, it's at the top right. Just use your llblgen pro v5.6 subscription license with orm profiler).

Please keep in mind that SSMS isn't a good way to test whether a query is fast. It keeps the connection open and when you execute the same query again, it often returns the same set, so it's not the same as executing the query using the SqlClient api.

It's odd it is slow with parameters and fast without, as sqlserver will try to parameterized the query with constants, so it actually has to do extra work. It's also not really a big query that it falls into the problem which is called 'parameter sniffing' which is a problem with the sql server optimizer which sometimes can occur when it tries to determine if it can use an execution plan based on parameter values and fails to do so and therefore recompiles the query every time or uses a slow plan.

However this clashes with the fact you're saying it also happens on Oracle.

If you use parameters with the plain sql query it should be as slow as the query spec query I think, (as the slowness is in the db itself). Not sure how big the resultset is too, I recon you're fetching the data in the same app, and connect to the same database? (so the plain sql query is in the same code/connecting to the same database as the queryspec/linq one) ?

One thing you could try is to use Microsoft.Data.SqlClient instead of System.Data.SqlClient. Microsoft.Data.SqlClient is the recommended new ADO.NET provider for SQL Server from Microsoft. You can use it with our runtime without a problem, just specify its factory in the RuntimeConfiguration instead of the System.Data.SqlClient one.

1) I downloaded the ORM Profiler (I thought is was not included by default). I attached the snapshot file.

2) For SSMS, ok thanks for the information

3) "It's odd it's slow with parameters and fast without" I probably wasn't clear so I will try to explain. My application connects to a 3rd party database to retrieve so data using views defined by the 3rd party.

The same query is executed to retrieve data, the only difference is the parameters (Inf_Label = ReferenceSuperviseur and Inf_Date = DateMesure). With some parameters, the query is fast and for some others, slow (> 30 sseconds) and so failed as timeout.

4) "It's also not really a big query" The query that I write is really little but the view is big and contains multiple UNION (and so can't be indexed)

5) "this clashes with the fact you're saying it also happens on Oracle." In Oracle, the query is completely different but I thought the poor performance was due to LLBLGEN code because in both cases, using raw SQL improved the performance.

Find below the ORACLE query (just to see the difference with SQL Server)

var query =
                $@"SELECT /*+ INDEX(int_topkapi INT_TOPKAPI_IMPORT) */
                LIBELLE AS ReferenceSuperviseur,
                TO_DATE(dateenr || ' ' || heureenr, 'YYYYMMDD HH24MISS') AS DateMesure,
                VALEUR_NUM AS ValeurMesure
                FROM TOPKAPI.INT_TOPKAPI
                WHERE TRIM(LIBELLE) = '{tache.ReferenceSuperviseur}'";

            if (tache.DateLimiteInferieureMesures.HasValue)
            {
                query = query + "\r\n" + $@"AND TO_DATE(dateenr || ' ' || heureenr, 'YYYYMMDD HH24MISS') > TO_DATE('{tache.DateLimiteInferieureMesures.Value.ToString("yyyyMMdd hhmmss")}', 'YYYYMMDD HH24MISS')";
            }

            var results = await this.DataAccessAdapter.FetchQueryAsync<MesuresAImporterDto.MesureDto>(query);
            return results;

5) Not sure how big the resultset is too Even if the query returns no data, it could be slow (see image "slow_even_if_no_data.png")

6) I tried Microsoft.Data.SqlClient and it doesn't change anything.

The same query is executed to retrieve data, the only difference is the parameters (Inf_Label = ReferenceSuperviseur and Inf_Date = DateMesure). With some parameters, the query is fast and for some others, slow (> 30 sseconds) and so failed as timeout.

I'm not sure I understand you correctly. With different parameters, do you mean different values for the same parameters used for filtering? Or do you mean filtering on different fields?

Sorry, I want to say different values for the same parameters.

INF_Label = 'CBHP2NEP0159', INF_Date >= '2018-02-30 00:00:00'

=> OK

INF_Label = 'CPVP2NEP02214', INF_Date >= '2018-01-24 00:00:00'

=> Timeout

gilles.marceau wrote:

1) I downloaded the ORM Profiler (I thought is was not included by default). I attached the snapshot file.

Thanks. This is very helpful. If you open this snapshot and scroll down to the query with the huge time taken (at the bottom, with the alert), and then look at the query plan statistics you obtained, you can see when opening the tree that it does a couple of table scans. These can be very slow. (the table scan is done for the where clause, the ones with the parameters).

Table Scan(OBJECT:([ScadaNetDb].[dbo].[NumericInformations]), WHERE:([ScadaNetDb].[dbo].[NumericInformations].[category]<>(5) AND [ScadaNetDb].[dbo].[NumericInformations].[category]<>(6) AND [ScadaNetDb].[dbo].[NumericInformations].[label]=[@p1]))

Could you also look at the plan for the query which uses different parameter values and which is fast? I think the plan for that query is much different.

3) "It's odd it's slow with parameters and fast without" I probably wasn't clear so I will try to explain. My application connects to a 3rd party database to retrieve so data using views defined by the 3rd party.

The same query is executed to retrieve data, the only difference is the parameters (Inf_Label = ReferenceSuperviseur and Inf_Date = DateMesure). With some parameters, the query is fast and for some others, slow (> 30 sseconds) and so failed as timeout.

ok, so it might be sql server chooses different plans based on the parameter value. Like I said above, could you check the query plan of the same query with parameters which result in a fast response and see if it there too does table scans ?

If it doesn't, try to swap the where clauses, so the parameters are in a different order. This sound stupid, and it is, but it might trigger sql server to pick the right plan as what you're seeing looks like the parameter sniffing problem.

4) "It's also not really a big query" The query that I write is really little but the view is big and contains multiple UNION (and so can't be indexed)

that's ok, the view is expanded in-place in the main query it is used in, the indexes you need are the ones where it currently does a table scan (see the plan of the slow query) and with an index you can avoid these. But that would require an addition to the schema (index), not sure if you're allowed to create these.

5) "this clashes with the fact you're saying it also happens on Oracle." In Oracle, the query is completely different but I thought the poor performance was due to LLBLGEN code because in both cases, using raw SQL improved the performance.

The slow query is taking its time inside the DB (.NET time is 0.25ms) so it's not the runtime where things are grinding to a halt...

Find below the ORACLE query (just to see the difference with SQL Server)

var query =
                $@"SELECT /*+ INDEX(int_topkapi INT_TOPKAPI_IMPORT) */
                LIBELLE AS ReferenceSuperviseur,
                TO_DATE(dateenr || ' ' || heureenr, 'YYYYMMDD HH24MISS') AS DateMesure,
                VALEUR_NUM AS ValeurMesure
                FROM TOPKAPI.INT_TOPKAPI
                WHERE TRIM(LIBELLE) = '{tache.ReferenceSuperviseur}'";

            if (tache.DateLimiteInferieureMesures.HasValue)
            {
                query = query + "\r\n" + $@"AND TO_DATE(dateenr || ' ' || heureenr, 'YYYYMMDD HH24MISS') > TO_DATE('{tache.DateLimiteInferieureMesures.Value.ToString("yyyyMMdd hhmmss")}', 'YYYYMMDD HH24MISS')";
            }

            var results = await this.DataAccessAdapter.FetchQueryAsync<MesuresAImporterDto.MesureDto>(query);
            return results;

You can specify the index hint as a query optimizer hint in queryspec for Oracle, not sure if that will help, but as you specify it in the plain sql, you have to specify it in linq/queryspec as well to make the query the same: https://www.llblgen.com/Documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_generalusage.htm#specifying-query--optimizer-hints-sql-server-mysql-oracle

Hi,

Sorry to not answer to your message before.

SQL Server I called the database's provider and he told me to use the ID column which as an index. I originally didn't write the query with this column because I thought the customer wasn't able to see it (and he needs too). => Now, the query is fast so no more matter.

Oracle I originally thought the ORACLE database was a generic one and would be the same for each of our customer. I discover it's not the case so I think I will call the database's provider to find if it can provide us view or something like.

=> I close the issue and perhaps, I needed, I will reopen another one later if I have some performance matter.

Thanks for all.

Otis wrote:

If it doesn't, try to swap the where clauses, so the parameters are in a different order. This sound stupid, and it is, but it might trigger sql server to pick the right plan as what you're seeing looks like the parameter sniffing problem.

I just fixed a similir strange performance problem with just swapping the order of two parameters. Thought I'd let you know, for future reference.

(using LLBLGen v5.3, SQL Server 2012R2)

Thanks!

Thanks Joep