Oren Eini

CEO of RavenDB

a NoSQL Open Source Document Database

Get in touch with me:

oren@ravendb.net +972 52-548-6969

Posts: 7,488
|
Comments: 51,038
Privacy Policy · Terms
filter by tags archive
time to read 1 min | 127 words

After posting my solution to the problem I got some feedback about the possible performance of the query.
I just run the query over a table with 120,000~ rows, and it completed in less than second and change.
After playing with it a bit, I decided to skip the function and put the results in a table and map that, it is easier to handle it that way with NHibernate, and I can put an index on it that would save a table scan on the months result.
The major cost is a clustered index scan (95%) and nested loops join (5%). Fun stuff :-)

I am getting deep into NHibernate's queries recently, extremely powerful, especially if you combine it with your own customer dialect.
time to read 2 min | 349 words

As it turned out, this isn't that hard, all I needed to do was remember my trusty DateRange function, modify it to on months instead of days, and it was off to the races. This type of code does make my head hurt a tiny bit, it packs a lot into it.

DECLARE  @start DATETIME,
           @end DATETIME
               
SET @start = '2007-04-01'
set @end = '2007-10-01'

  SELECT YEAR(Currentdate) Year,
         MONTH(Currentdate) Month,
         COUNT(Bug.Id) OpenedBugCount
    FROM Dbo.MonthRange(@start,@end )
         JOIN Bugs Bug
           ON YEAR(Currentdate) >= YEAR(Bug.Openedat)
              AND MONTH(Currentdate) >= MONTH(Bug.Openedat)
              AND YEAR(Currentdate) <= YEAR(ISNULL(Bug.Closedat,Currentdate))
              AND MONTH(Currentdate) < MONTH(ISNULL(Bug.Closedat,DateAdd(MONTH,1,Currentdate)))
GROUP BY YEAR(Currentdate),MONTH(Currentdate)

I would get it into NHibernate on Sunday, should be fun, it is about the ninth select is the super report... :-)

FUTURE POSTS

  1. RavenDB Cloud Global Status vs. Product Status - about one day from now
  2. Recording: Technology & Friends - Oren Eini on the Corax Search Engine - 3 days from now
  3. RavenDB and Two Factor Authentication - 4 days from now

There are posts all the way to Mar 06, 2024

RECENT SERIES

  1. Recording (13):
    15 Jan 2024 - S06E09 - From Code Generation to Revolutionary RavenDB
  2. Meta Blog (2):
    23 Jan 2024 - I'm a JS Developer now
  3. Production postmortem (51):
    12 Dec 2023 - The Spawn of Denial of Service
  4. Challenge (74):
    13 Oct 2023 - Fastest node selection metastable error state–answer
  5. Filtering negative numbers, fast (4):
    15 Sep 2023 - Beating memcpy()
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats
}