#115 Update OrderQueries to use linq queries

[Anonymous]

Originally created by: AlmarAubel

• Changed all plain SQL queries in OrderQueries to LINQ queries.
• Made BuyerId an auto-property to enable its use in LINQ queries.
• Used DbContext to query card types. Uncertain whether it's better to create a new repository or add to an existing one.

This addresses one of the points mentioned by @adityamandaleeka in issue [#23].

[Anonymous]

Originally posted by: AlmarAubel

@dotnet-policy-service agree

[Anonymous]

Originally posted by: adityamandaleeka

Tagging @roji @ajcvickers for best practices review here.

Would be great if we could see a before/after perf comparison for this sort of thing.

[Anonymous]

Originally posted by: AlmarAubel

Benchmark Comparison of Raw SQL vs EF Core Queries for GetOrdersAsync

I've completed the benchmarking as requested to assess the performance implications of transitioning from raw SQL queries to EF Core, followed by further optimizations. Initially, the queries were naively translated to EF Core with minimal modifications to the classes used in querying. Subsequently, I implemented optimizations, primarily modifying certain getter methods with a private backing field to properties with a private setter. This adjustment aids EF Core in better optimizing queries, especially for expressions like total = (double)ob.order.GetTotal() which is replaced in the optimized version by ob.order.OrderItems.Sum(oi => oi.UnitPrice* oi.Units).

Results

Here are the benchmark results for GetOrdersAsync:

Method	Mean	Error	StdDev	Ratio	RatioSD	Allocated	Alloc Ratio
RawSql	1.215 ms	0.0237 ms	0.0263 ms	1.00	0.00	6.84 KB	1.00
Naive	90.802 ms	9.7250 ms	11.1993 ms	75.93	7.66	1580.34 KB	230.88
Optimized	1.123 ms	0.0295 ms	0.0340 ms	0.93	0.04	21.56 KB	3.15
Optimized_Compiled	1.033 ms	0.0267 ms	0.0307 ms	0.85	0.03	7.05 KB	1.03
OptimizedAsNoTracking	1.105 ms	0.0309 ms	0.0330 ms	0.91	0.03	23.86 KB	3.49

Analysis:

• Raw SQL to Naive EF Core: The transition from raw SQL to a naive EF Core implementation resulted in a significant increase in execution time and memory allocation. This is expected as the naive approach does not take advantage of EF Core's optimization capabilities.

• Naive to Optimized EF Core: Post optimization, there's a dramatic improvement, bringing the performance almost on par with the raw SQL, both in terms of execution time and memory usage. The optimizations primarily involved restructuring getter methods for better EF Core query translation.

• NoTracking Variants: The 'AsNoTracking' variants show similar performance to their tracking counterparts, with a slight increase in memory usage. This indicates that tracking does not significantly impact performance in this context.

• Compiled Queries: The introduction of compiled queries further enhances performance, demonstrating their effectiveness in query optimization.

This comparison clearly illustrates the importance of proper query structuring and optimization in EF Core to achieve performance close to raw SQL. I believe these optimizations represent a more effective approach for this scenario.

SQL

For reference, here are the queries:

Raw SQL:

SELECT o."Id" AS ordernumber, o."OrderDate" AS date, os."Name" AS status, SUM(oi."Units" * oi."UnitPrice") AS total
   FROM ordering.orders AS o
   LEFT JOIN ordering."orderItems" AS oi ON o."Id" = oi."OrderId"
   LEFT JOIN ordering.orderstatus AS os ON o."OrderStatusId" = os."Id"
   LEFT JOIN ordering.buyers AS ob ON o."BuyerId" = ob."Id"
   WHERE ob."IdentityGuid" = @userId
   GROUP BY o."Id", o."OrderDate", os."Name"
   ORDER BY o."Id"

Optimized:

-- generated by EF core
SELECT o."Id" AS ordernumber, o."OrderDate" AS date, o0."Name" AS status, (
    SELECT COALESCE(sum(o1."UnitPrice" * o1."Units"::numeric), 0.0)
    FROM ordering."orderItems" AS o1
    WHERE o."Id" = o1."OrderId")::double precision AS total
FROM ordering.orders AS o
         INNER JOIN ordering.buyers AS b ON o."BuyerId" = b."Id"
         INNER JOIN ordering.orderstatus AS o0 ON o."OrderStatusId" = o0."Id"
WHERE b."IdentityGuid" = @___buyerId_0

Naive:

-- generated by EF core
SELECT o."Id", o."BuyerId", o."Description", o."OrderDate", o."OrderStatusId", o."PaymentMethodId", o."Address_City", o."Address_Country", o."Address_State", o."Address_Street", o."Address_ZipCode", b."Id", o0."Id", o1."Id", o1."OrderId", o1."ProductId", o1."UnitPrice", o1."Units", o1."Discount", o1."PictureUrl", o1."ProductName", o0."Name", o2."Id", o2."OrderId", o2."ProductId", o2."UnitPrice", o2."Units", o2."Discount", o2."PictureUrl", o2."ProductName"
FROM ordering.orders AS o
         INNER JOIN ordering.buyers AS b ON o."BuyerId" = b."Id"
         INNER JOIN ordering.orderstatus AS o0 ON o."OrderStatusId" = o0."Id"
         LEFT JOIN ordering."orderItems" AS o1 ON o."Id" = o1."OrderId"
         LEFT JOIN ordering."orderItems" AS o2 ON o."Id" = o2."OrderId"
WHERE b."IdentityGuid" = @___buyerId_0
ORDER BY o."Id", b."Id", o0."Id", o1."Id"

Next steps

Based on these insights, I propose further refactoring to enhance our application's performance and maintainability. If your agrees with this approach, I plan to:

1. Refactor all private fields and getter methods into auto-properties with private setters. This will further align with the EF Core optimization strategies and potentially improve performance.
2. Adjust the EF configuration and other related code accordingly. These changes will ensure our codebase remains consistent and optimized for EF Core usage.

Disclaimer :)

Please let me know if you agree with this direction. Upon confirmation, I'll proceed with the necessary modifications and update the PR accordingly.

Finally, I'd like to emphasize that benchmarking is a complex area, often with nuanced results. I've tried to ensure that the methodology I chose is suitable for this context, but I'm open to feedback or alternative approaches that could refine our understanding of the performance impacts.

You can find the code used for the benchmarks here.

edit 2023-12-10:
I've revised the benchmarking to evaluate the performance implications of transitioning from raw SQL queries to EF Core, incorporating further optimizations and a new approach to data retrieval.
• I have updated the approach so that we no longer retrieve the same order from the database every time. Instead, we now select one from 1000 items randomly. It appears there is a significant difference between items. For example, the 50th item is always faster with EF Core, but the 24th item is consistently quicker with the raw SQL variant.
• Moreover, added a compiled query variant, which is faster and allocates less memory.

[Anonymous]

Originally posted by: AlmarAubel

Benchmark Comparison of Raw SQL vs EF Core Queries

I've recently conducted a benchmark to compare the performance of raw SQL implementations against EF Core queries.

Firstly, it's important to note that I excluded the connection setup time from the benchmark. This decision was made to focus solely on the query execution performance. Including connection setup could introduce variability unrelated to the query performance itself, especially in scenarios where connection pooling or other optimizations are in play. By excluding this, we get a clearer picture of how the queries themselves are performing.

Here are the results:

Method	Mean	StdDev	Error	Gen0	Allocated
EfCore_GetCardTypesAsync	847.6 us	20.88 us	35.09 us	-	7.44 KB
RawSql_GetCardTypesAsync	877.0 us	34.58 us	52.28 us	-	2.89 KB
EfCore_GetOrdersAsync	1,212.7 us	70.12 us	134.07 us	3.9063	25.75 KB
EfCore_Compiled_GetOrdersAsync	1,222.5 us	130.94 us	197.96 us	-	6.95 KB
RawSql_GetOrdersAsync	1,295.3 us	58.67 us	98.59 us	-	7 KB
EfCore_GetOrderAsync	1,753.1 us	384.06 us	580.65 us	1.9531	18.11 KB
RawSql_GetOrderAsync	3,538.1 us	2,208.06 us	3,338.28 us	3.9063	27.41 KB

The results show that EF Core queries generally perform better in terms of execution time, although they sometimes allocate more memory. This trade-off might be worth considering based on the project's specific needs and constraints.

SQL

GetOrderAsync

Raw

SELECT o."Id" AS ordernumber, o."OrderDate" AS date, o."Description" AS description, o."Address_City" AS city,
o."Address_Country" AS country, o."Address_State" AS state, o."Address_Street" AS street,
o."Address_ZipCode" AS zipcode, os."Name" AS status, oi."ProductName" AS productname, oi."Units" AS units,
oi."UnitPrice" AS unitprice, oi."PictureUrl" AS pictureurl
FROM ordering.Orders AS o
LEFT JOIN ordering."orderItems" AS oi ON o."Id" = oi."OrderId"
LEFT JOIN ordering.orderstatus AS os ON o."OrderStatusId" = os."Id"
WHERE o."Id" = @id

Ef core generated

SELECT t."Id", t."BuyerId", t."Description", t."OrderDate", t."OrderStatusId", t."PaymentMethodId", t."Address_City", 
t."Address_Country", t."Address_State", t."Address_Street", t."Address_ZipCode", t."Id0", 
o1."Id", o1."OrderId", o1."PictureUrl", o1."ProductId", o1."ProductName", o1."UnitPrice", o1."Units", o1."Discount", t."Name"
FROM (
    SELECT o."Id", o."BuyerId", o."Description", o."OrderDate", o."OrderStatusId", 
     o."PaymentMethodId", o."Address_City", o."Address_Country", o."Address_State", 
     o."Address_Street", o."Address_ZipCode", o0."Id" AS "Id0", o0."Name"
    FROM ordering.orders AS o
    INNER JOIN ordering.orderstatus AS o0 ON o."OrderStatusId" = o0."Id"
    WHERE o."Id" = @__id_0
    LIMIT 1
) AS t
LEFT JOIN ordering."orderItems" AS o1 ON t."Id" = o1."OrderId"
ORDER BY t."Id", t."Id0"

GetOrderAsync

See previous post

GetCardTypesAsync

Raw

SELECT * FROM ordering.cardtypes

Ef core generated

SELECT c."Id", c."Name"
FROM ordering.cardtypes AS c