Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Performance really dfiferent if using Linq provider or raw sql
 

Pages: 1
Bugs & Issues
Performance really dfiferent if using Linq provider or raw sql
Page:1/1 

  Print all messages in this thread  
Poster Message
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
35 posts
# Posted on: 06-Mar-2020 12:42:23.  
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.

Code:

            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.

Code:

            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
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38047 posts
# Posted on: 06-Mar-2020 15:18:53.  
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.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38047 posts
# Posted on: 06-Mar-2020 15:27:54.  
QuerySpec query I'd use: (I hope it compiles, not sure of all the names used Regular Smiley )

Code:


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.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
35 posts
# Posted on: 06-Mar-2020 17:52:28.  
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
Code:
    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    
Code:
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'))
        


  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8138 posts
# Posted on: 09-Mar-2020 05:45:15.  
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.
David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
35 posts
# Posted on: 09-Mar-2020 10:08:49. Goto attachments  
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

Code:

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.



  Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
35 posts
# Posted on: 09-Mar-2020 10:21:42. Goto attachments  
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...



  Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
35 posts
# Posted on: 09-Mar-2020 10:46:59.  
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.



  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38047 posts
# Posted on: 09-Mar-2020 15:53:43.  
(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.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
35 posts
# Posted on: 09-Mar-2020 17:08:50. Goto attachments  
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)
Code:

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.




  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14626 posts
# Posted on: 10-Mar-2020 01:44:41.  
Quote:
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?
  Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
35 posts
# Posted on: 10-Mar-2020 09:24:26.  
Sorry, I want to say different values for the same parameters.


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

=> OK

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

=> Timeout
        


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38047 posts
# Posted on: 10-Mar-2020 10:38:44.  
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).

Code:

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.

Quote:

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.

Quote:

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.

Quote:

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...

Quote:

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

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

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
35 posts
# Posted on: 13-Mar-2020 14:51:08.  
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.






  Top
Joepsel
User



Location:
Limburg, Netherlands
Joined on:
16-Oct-2007 08:53:31
Posted:
2 posts
# Posted on: 01-May-2020 16:57:22.  
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!
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38047 posts
# Posted on: 02-May-2020 11:01:47.  
Thanks Joep Regular Smiley

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.