Is there any way to make this query faster?

[Tareq Perez]

I need to retrieve the number of records from a Machine of a certain Type during the date of 7 am and 7 am the next day (24 hour period)

This is the structure of my document:

//Date is in Ticks
 public Long Date { get; set; }

        public string Process { get; set; }

        public string Type { get; set; }

        public int Index { get; set; }

        public string Machine { get; set; }

        public string Plant { get; set; }

        public string Body { get; set; }

public int Hour { get; set; }

public string key{ get; set;}

The query I'm currently using is 

session.Query<Warning>("GetWarningCountByTypeAndMachine").Statistics(out stats)

.Where(x => x.Machine.Equals(MachineName, StringComparison.OrdinalIgnoreCase) && x.Type.Equals(WarningType, StringComparison.OrdinalIgnoreCase) && x.Date >= date_begin.Ticks && x.Date <= date_end.Ticks).ToList();

where GetWarningCountByTypeAndMachine is just:

from doc in docs

select new { Machine = doc.Machine, Type = doc.Type, Date = doc.Date }

Is there any way to make a reduction or just any faster way to query this?

[Kijana Woodard]

How fast is fast?

How many docs?

You could try preprocessing/reducing the ticks into a "report date". So "6/22/2012" would be 7am 6/22/2012 - 6:59:59 6/23/2012. You could also just compress that to an long like round ticks back to the previous 7am ticks. Make sense?

Whatever you do, benchmark to make sure you're getting gains.

[Chris Marisic]

I agree with Oren, creating a key that you're actually going to reduce on seems to be a much more optimal solution.

I'd probably add a property to the model to reduce over, something like

public string ReportingDateGroup { return real date do something to create key that creates 1 value for the entire day you wish to cover; }

[Kijana Woodard]

Thanks for that compliment. ;-)

[Matt Warren]

Yeah that's a good idea, large range queries can be slow,

So this :

     x.Date >= date_begin.Ticks && x.Date <= date_end.Ticks 

will be slower than this:

     x.ReportHour == 7

[Oren Eini (Ayende Rahien)]

Note that in 1.2 we optimized this pattern:   x.Date >= date_begin.Ticks && x.Date <= date_end.Ticks  

So it would be faster, but doing an exact match is still faster.

Normalizing the values is probably the easiest way to get faster performance.

[Tareq Perez]

thanks for the tips. But is there a map reduction I can make for this?

The map function is the same as my original post, but is there some way to reduce this using the date.Ticks ranges? 

[Oren Eini (Ayende Rahien)]

I am not sure that I am following, can you explain in more details?

[Tareq Perez]

I'm trying to get a list of documents who's date falls in range with a specified date.

I have a Machine, Type, and begin/end Date.

I was wondering if there was a map reduction function I could write to speed up the query for finding the documents that fit the date range criteria.

[Oren Eini (Ayende Rahien)]

You want to get the total count for machine / type for a range?

What sort of range? Day/ month?

[Tareq Perez]

Nope, I want a List of each document grouped by Machine that fall within the range of begin and end date that are of the same Type. And the difference between my begin and end date is 1 day. In my original post you can see that I stored the date in Ticks, so some converting is necessary.

[Itamar Syn-Hershko]

Save all that 24-period info in one document, and give them IDs like infos/machine-name/2012-06-25, and then you can get them very efficiently directly from the document store using StartsWith

[nightwatch]

are you saying that a simple  'select' query is too slow and has to be replaced with some trickery? 

How 'slow' it is now and how fast does it need to be? If search criteria are always the same then maybe caching would be the solution?

rg

[Oren Eini (Ayende Rahien)]

It is NOT a simple select.

You want a group by on a dynamic range. This is specifically something that RavenDB doesn't do well.