When I am executing a simple query for the first time using linq to postgresql it takes aprox 600 ms. I assume that caching is used since on any further executions the time reduces drastically, for same query 50ms.
I need to know exactly why this is so slow, our application is time critical and we can't afford such a delay.
P.S: Can't use compiled queries because 80% of our queries are dynamic.
Linq to PostgreSQL Extremly slow
-
StanislavK
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
There are several possible reasons for the differences in the first an subsequent executions of the query:
- Opening a connection. By default, dotConnect for PostgreSQL uses connection pooling, so opening a connection should be essentially faster after this connection was opened the first time.
- Caching entities. Each DataContext instance caches the entities being loaded with it. Hence, if a second query should return the same entity objects, this is done faster because these objects are got from the cache instead of being materialized again.
- Caching queries. LinqConnect implicitly compiles queries and saves them in a special cache. If you do execute the same query twice in this test, at the second execution the compiled query is actually got from this cache (saving the time for being parsed and translated).
You can find some tips on increasing the query performance in the corresponding topic in our documentation:
http://www.devart.com/linqconnect/docs/Performance.html
- Opening a connection. By default, dotConnect for PostgreSQL uses connection pooling, so opening a connection should be essentially faster after this connection was opened the first time.
- Caching entities. Each DataContext instance caches the entities being loaded with it. Hence, if a second query should return the same entity objects, this is done faster because these objects are got from the cache instead of being materialized again.
- Caching queries. LinqConnect implicitly compiles queries and saves them in a special cache. If you do execute the same query twice in this test, at the second execution the compiled query is actually got from this cache (saving the time for being parsed and translated).
You can find some tips on increasing the query performance in the corresponding topic in our documentation:
http://www.devart.com/linqconnect/docs/Performance.html