10 Replies Latest reply: Oct 23, 2015 5:58 AM by Sergey Makushinsky

# S...L...O...W..... Min/Max

I have the below calculation running against a "telemetry" data set. That data set is about 300 million rows, and all time-series.

```LOAD
min([CalendarDate]) as minDate,
max([CalendarDate]) as maxDate
Resident telemetry;

```

Any thoughts on how to get the above query (or alternative) to run in say a couple of minutes or less instead of many hours?

Clarification: the above slowness occurs even if the number of records is limited to say 30M. The last run was, I believe, over 6 hours long.

Message was edited by: Chris Kelley

• ###### Re: S...L...O...W..... Min/Max

When you load Telemetry Table, you could order by CalendarDate

Then using Peek Function you could get the First and the last rows that It will give you the Max and the Mix values

I hope this is what you are looking for

Cheers

OR

• ###### Re: S...L...O...W..... Min/Max

Hi Chris,

Thanks to the great Rob Wunderlich and his blog Qlikview Cookbook | Recipes for Qlikview Success, I can provide you a solution, try this:

min(date(FieldValue('CalendarDate',RecNo()))) as minDate,

max(date(FieldValue('CalendarDate',RecNo()))) as maxDate

AutoGenerate FieldValueCount('CalendarDate') ;

I haven't test the performance personally but apparently it's a killer's trick.

Regards,

Vincent

• ###### Re: S...L...O...W..... Min/Max

As Vincent pointed out, the slowness comes from reading all rows of the table vs the faster method of reading only the distinct values. The example on the blog (since corrected) was missing a LOAD statement. Here's the corrected syntax:

min(CalendarDate) as minDate,

max(CalendarDate) as maxDate

;

FieldValue('CalendarDate',RecNo()) as CalendarDate

AutoGenerate FieldValueCount('CalendarDate') ;

I've also published an example qvw in the Cookbook Recipes:

-Rob

http://qlikviewcookbook.com

• ###### Re: S...L...O...W..... Min/Max

Hi Rob,

The one in a single load gives the expected result.

Is it faster with the preceding load?

Regards,

Vincent

• ###### Re: S...L...O...W..... Min/Max

Hi Vincent,

It's not faster with the preceding load. But in some cases, I get "?" for results without preceding load. Not sure why, but it always works with the preceding load.

-Rob

• ###### Re: S...L...O...W..... Min/Max

Ah OK, that's why I have used the Date() function in my code. It avoids the question marks.

Apparently the ? are here because QV cannot identify the format of the data.

• ###### Re: S...L...O...W..... Min/Max

Thank you Vincent! I was banging my head against the wall on that one!

-Rob

• ###### Re: S...L...O...W..... Min/Max

First store the calendar table into a qvd and then instead of taking the data from a resident table, try taking it from qvd.

•                min([CalendarDate]) as minDate,
•                max([CalendarDate]) as maxDate
• From <xxx.qvd(qvd)>;
• ###### Re: S...L...O...W..... Min/Max

Hi Chris,

Have you tried the different solutions we've proposed to you?

I'm very interesting to know the impact on the performance.

Regards,

Vincent

• ###### Re: S...L...O...W..... Min/Max

Hi Chris,

I used the following approach:

1. Get Min/Max Date from database, store it and use it. Usually it's much faster.

2. Common Sense:

2.1 If you know that you will always have dates in the future you can basically calculate Min value one time only and hardcode Min value.

2.2. If you know that you have values not bigger than Today OR Today plus one month OR Last day of current year, you also can calculate it without Min/Max, but from Today

Regards,

Sergey