Linq to PostgreSQL Extremly slow

Linq to PostgreSQL Extremly slow

Postby MichaelH » Tue 31 Jan 2012 11:09

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.
Posts: 6
Joined: Tue 22 Nov 2011 20:57

Postby StanislavK » Wed 01 Feb 2012 16:57

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:
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Return to LinqConnect (LINQ to SQL support)