Timeline for Speed up query to rank team sales by aggregating data from two databases
Current License: CC BY-SA 3.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 22, 2014 at 22:38 | comment | added | James Wilson | Your last edit seemed to cut it down by about 40% from about 23 seconds to about 13. Part of me is wondering if i can write it faster in sql and just use pure sql. But that would drive my ocd nuts. =( | |
| May 22, 2014 at 22:30 | history | edited | Jesse C. Slicer | CC BY-SA 3.0 |
more stuff, more things.
|
| May 22, 2014 at 22:22 | comment | added | Jesse C. Slicer | Three things will be your friend: SQL Profiler (to see the SQL queries LINQ-to-Entities is generating), SQL Tuning Advisor (to make sure the indices on the tables are proper for aforementioned queries) and lastly any .NET profiler to see if there's any particular code here that's a hot spot. I'm betting the DB is your latency point. | |
| May 22, 2014 at 22:17 | comment | added | James Wilson | I found the issue. .Select(o => (decimal?)o.OrderTotal) .Sum() ?? 00.0M However this didn't speed it up. =( | |
| May 22, 2014 at 22:14 | history | edited | Jesse C. Slicer | CC BY-SA 3.0 |
stuff and things.
|
| May 22, 2014 at 22:14 | comment | added | Jesse C. Slicer | Welp, without seeing those classes, I can't really do much other than guesswork. So, roll that part back out. Updating one more time to hold DB open shorter. | |
| May 22, 2014 at 22:06 | comment | added | James Wilson | I tried to update the class for TotalSales from decimal to decimal? but same isues. | |
| May 22, 2014 at 22:05 | comment | added | James Wilson | I tried that edit. But it gives an error saying cannot ?? cannot be applied to operands of decimal and decimal. I then tried to cast it as (decimal?) which would allow it to build but it then errors out on the page saying The cast to value type 'Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type. pesky errors. | |
| May 22, 2014 at 21:58 | comment | added | Jesse C. Slicer |
Made an edit to coalesce nulls to 0.00 in the Select.
|
|
| May 22, 2014 at 21:58 | comment | added | James Wilson | From poking around it is erroring out on an individual who exists in the ProPit_User database but has no sales in the sales database. When running a query on the Orders table it comes up with 0 rows. | |
| May 22, 2014 at 21:57 | history | edited | Jesse C. Slicer | CC BY-SA 3.0 |
Added null-coalescing operator.
|
| May 22, 2014 at 21:53 | comment | added | James Wilson | This provided the following error on the AddRange line. The cast to value type 'Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type. | |
| May 22, 2014 at 21:49 | history | answered | Jesse C. Slicer | CC BY-SA 3.0 |