Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Query very fast but mapping slow with dapper

Tags:

sql

dapper

I'm using dapper for a new project and love it but I don't understand why my queries are really slow. The execution time is very fast, almost instant, but the connection stays open much longer while dapper is mapping the result to my object I guess.

Here is an example in glimpse :

Glimpse result

This query is just a SELECT on something like 15 fields with a where on the primary key, so it's really fast to execute and it doesn't return that much of data. My code to execute it is :

 using (var conn = GetConnection())
 {
    obj = conn.Get<T>(id);
 }

And the object is a very basic poco with Strings and Ints. So why do I waste 220 ms doing this while the query execution itself takes 3 ms ? Where is the difference ?

Thanks for your help !

like image 727
Dawmz Avatar asked Aug 06 '14 18:08

Dawmz


People also ask

Why is my database query slow?

Slow queries are frequently caused by combining two or more large tables together using a JOIN. Review the number of joins in your query, and determine if the query is pulling more information than is actually needed.

Why is my SQL Server query suddenly slow?

Here's one way to track down the cause of the problem: Find out the most expensive queries running in SQL Server, over the period of slowdown. Review the query plan and query execution statistics and wait types for the slowest query. Review the Query History over the period where performance changed.


2 Answers

UPDATE

There was one field that was causing problems for me in the selection part of my SQL statement. I just went by removing each field one by one and then found the one which was causing the problem.

I had to cast one of my fields to nvarchar like this:

CAST(my_field AS nvarchar(max)) as my_field

ORIGINAL ANSWER

It has to do something with the mapping. Because if I change it from "Strongly Typed" (which is taking for ever, almost 1 minute):

var products = connection.Query<Product>(sql).ToList();

to "Anonymous":

var products = connection.Query(sql).ToList();

then it executes really fast (1 second).

I tried and executed the SQL statement directly in "SQL Server Management Studio" as a query and it finishes in less then 1 second.

So my suggestion is, that you use the "anonymous mapping" until dapper guys fix this if they will be able to.

like image 157
Tadej Avatar answered Sep 16 '22 11:09

Tadej


I had a similar experience with Dapper as I was trying to project from a View to an POCO object.

The problem ended up being for me that I did not have a column for each property on my object, so the Convert.ChangeType() was very slow, I added a column to my View that would always return NULL, and the Query<T>() call sped up dramatically.

like image 35
Neil McGuire Avatar answered Sep 19 '22 11:09

Neil McGuire