0

I have a big database which is created by entity framework core. This database stores round about 5 million datasets. To improve the query speed i'd like to aggregate the data of the days before.

In this case i would like to execute a SQL command once every day at 00:00 o'clock and aggregate the data of yesterday.

In the past i created stored-procs which are executed by a database-job in mssql. But these databases were created manually and now i'd like to get a similar functionallity by using the entity framework.

I read that there shouldn't be any logic in the database. So how could i do this instead? (The article where i get the base information is: Can you create sql views / stored procedure using Entity Framework 4.1 Code first approach)

So i'm searching a good solution to execute every day a "aggregation" function and store the aggregation data in the database.

Lukas Hieronimus Adler
  • 1,063
  • 1
  • 17
  • 44
  • you may try to learn about how you can use stored proc with entity framework. but to schedule it, you may try to use azure webjob or widows scheduler to hit certain function at your preferred time – Md. Tazbir Ur Rahman Bhuiyan Feb 18 '19 at 09:19
  • Could you give me some sample? The code is a .net mvc web-api and i don't prefere to create a Windows Scheduler-Job for doing this! – Lukas Hieronimus Adler Feb 18 '19 at 09:22
  • awesome. if you want to get started with azure webjob follow this link: https://learn.microsoft.com/en-us/azure/app-service/webjobs-create. i used webjob sdk: To learn more about Microsoft Azure WebJobs SDK, please see http://go.microsoft.com/fwlink/?LinkID=320976 – Md. Tazbir Ur Rahman Bhuiyan Feb 18 '19 at 09:28

1 Answers1

0

You use the method you used before! It's ideally solved by SQL Agent and a proc, almost anything else will have more issues and worse performance.

If you really wanted to do it differently then you need two parts

  1. a scheduler, this will most likely be the OS one, but has no where near as many features as SQL Agent.
  2. the actual program, a .NET app using EF will do this but EF is not required, simple ADO will work, as will any other library.

The only reason you'd choose this route, is if you had further requirements that SQL would be inappropriate for, so you needed a more general language.

cjb110
  • 1,310
  • 14
  • 30