c# - Filter, merge, sort and page data from multiple sources -


at moment i'm retrieving data db through method retrieves iqueryable<t1>, filtering, sorting , paging (all these on db basically), before returning result ui display in paged table.

i need integrate results db, , paging seems main issue.

  • models similar not identical (same fields, different names, need map generic domain model before returning);
  • joining @ db level not possible;
  • there ~1000 records @ moment between both dbs (added during past 18 months), , grow @ same (slow) pace;
  • results need sorted 1-2 fields (date-wise).

i'm torn between these 2 solutions:

  1. retrieve data both sources, merge, sort , cache them; filter , page on said cache when receiving requests - need invalidate cache when collection modified (which can);
  2. filter data on each source (again, @ db level), retrieve, merge, sort & page them, before returning.

i'm looking find decent algorithm performance-wise. ideal solution combination between them (caching + filtering @ db level), haven't wrapped head around @ moment.

i think can use following algorithm. suppose page size 10, page 0:

  1. get 10 results database a, filtered , sorted @ db level.
  2. get 10 results database b, filtered , sorted @ db level (in parallel above query)
  3. combine 2 results 10 records in correct sort order. have 20 records sorted, take first 10 of them , display in ui

then page 1:

  1. notice how many items database , b used display in ui @ previous step. example, used 2 items database , 8 items database b.
  2. get 10 results database a, filtered , sorted, starting @ position 2 (skip 2), because 2 have shown in ui.
  3. get 10 results database b, filtered , sorted, starting @ position 8 (skip 8).
  4. merge same way above 10 records 20. suppose used 5 item , 5 items b. now, in total, have shown 7 items , 13 items b. use numbers next step.

this not allow (easily) skip pages, understand not requirement.

the perfomance should same when querying single database, because queries , b can done in parallel.


Comments

Post a Comment

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more