Dealing with large SQL Views in Daily Load

Unknown

Hi, looking for advice/best practice on how we can improve our daily load.

Currently we have a couple of sql/datareader updates in our daily load which individually consume up to 10 minutes each. In total our daily load needs more than an hour, which is too long.

 

Most of our reports in Board are based on monthly figures, our setup for these datareaders at the moment is that we reload historical data (2015-2019) for reportings everyday. Is it somehow possible to load the historical data once and after that somehow define that only the last 3 months are updated daily. If so can I do it in Board or does it need to be done in the SQL server.

 

What kind of setups are you using for daily updates?

 

Thanks

 

Mathias

Unknown

Hi Mathias,

 

You could try "Use current Selection" when launching the Data Reader

 

 

More info here: Data Read

 

However, I am honestly a bigger fan of applying the restrictions in my dataset load directly in the SQL query defined.

All the best,
Jonathan

Björn Reuber

Hi,

 

as Jonathan had said, the best way is to limit the SQL Query, cause otherwise you'll have the whole traffic in SQL, cause the current selection is only applied on board side.

Furthermore I allways suggest to load "whole" Month (so allways starting from 1.x.) instead having a window of the last 90 days (also this is easier to implement)

 

Regards

Björn

Dietmar Jeschkeit

Hi Mathias,

 

in addition to what Björn said you should also have a look at this How to: Optimize Datareader performance. 

 

Regards

Dietmar

Unknown

Thanks everyone.

 

I have one more question to the setup in the SQL server. I am not an expert here and not the person in charge to create the tables and views so excuse me if this question is silly.

How do your views in the Server look like to be optimal for Board.

Assuming we have the following view in the server (of course with tons of more data and a longer history):

Date	Customer	Product	Sales
2018-10	A	a	100
2018-10	B	a	300
2018-10	C	b	200
2018-11	A	c	100
2018-11	B	d	200
2018-12	A	a	300
2018-12	B	b	100
2018-12	D	a	50
2019-01	A	c	600
2019-01	B	c	800
2019-01	C	c	900
2019-02	B	b	150
2019-02	D	b	250

Now I want to only update the most recent month in Board. What is the best way to define this already in the server?

Any tips of sources/manuals I can read to get more knowledge on this topic? 

 

Thanks so much.

Dietmar Jeschkeit

Hi Mathias,

 

as you can see in the link of my previous post the order of customer and product depends on the max item no of these entities.

If you have a date in your sql-view you can check it versus getdate() - gives you the current date and time. You can convert this into YYYYMMDD with the following code: convert(varchar,getdate(),112).

 

You can have a look here: SQL time functions