Which one is faster? Query 1 Entity and make roundtrip on other related entities? or just left join it with 1 roundtrip?

c# dapper database-performance sql sql-server

Question

public class Item
{
    public int Id { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
    public decimal Cost { get; set; }
    public Department ItemDept { get; set; }
    public Category ItemCat { get; set; }
    public List<Supplier> Suppliers { get; set; }
    public List<Barcode> Barcodes { get; set; }
    public List<Discount> Discounts { get; set; }

}

Im using Dapper so my query is plain SQL talking to Sql Server 2008

how do I improve performance on this? assuming the query is get Item by Id(Primary key) which one is faster or better? - with one query full of joins(left join, inner join..etc) (1 roundtrip only) or - query the item first then execute another query for related tables(many roundtrips) or do you guys have any recommendations?

Popular Answer

This is too long for a comment.

The best way to answer a performance question is to try it out on your data on your systems. You can then check what really works better.

Informed speculation says that letting the database do the joins is the right thing to do. Databases are designed for this operation and they should implement joins effectively. Perhaps more importantly, there is overhead for each query that you run. Overhead to compile the query. Latency is passing the query to the database. Database overhead in processing the results and getting them back to the application.

That also suggests that doing more work in the database is a good thing. But, you should check on your system.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why