Optimal way to structure a table for a VA dashboard

[Renan_Crepaldi]

Hello!

I have a doubt that's more related to optimization by itself than programming. I want to structure a script to extract a table that will be used as the source of a VA dashboard.

The point is that I want to use a great deal of historical data, and if I make a project that process all of it every time it refreshes will take loads of time.

What's the best way to optimize this type of script? I thought about making a first table with all the historical data, and programming the automated project only to process the recent data, appending them together. Is that possible/ideal?

Thank you!

[Renan_Crepaldi]

Hey, everyone.

I was thinking by myself and I came up with an idea. I could structure a table with all historical data and save it on my library.

After that, I would build a process that would append the data of the last day. This way, the performance should be really better.

My only doubt is how to build this query, since this append should keep the historical data and overwrite the original table (creating a cycle)

If anyone knows and could help, I'm available to give more details (if necessary)

Thanks!

[Renan_Crepaldi]

Hello, everyone!

After doing some research on the web and talking with my working partners, I ended up finding a way to structure this table.

First, I constructed a table with all the historical data, adding the calculated fields I needed. Then I made a query that consults the max date of the historical table, filtering all the data that's greater than this max date, and inserts them on the historical table.

I don't know if this is the best way in performance, but it's running pretty fast.

Anyway, if someone knows a better format to do this type of script, I'm open for suggestions.

Thanks!