Aggregate functions RRS feed

  • Question

  • I'm trying to use grouping to find out how many files there are for certain attributes.  The query runs fine, but I can't find any way to move through the result set.  I execute:


    GROUP ON System.ItemTypeText

    AGGREGATE COUNT() as 'Total'

    ORDER BY System.ItemTypeText

    OVER (SELECT System.ItemTypeText FROM systemindex WHERE CONTAINS(System.Kind, 'Document') and System.ItemTypeText is not null and system.Size > 20000)

    I can read the first row of the result set (OleDbDataReader) but when I call Read() a second time, I get a NullReferenceException on the Read() call itself.  The stack trace shows the actual error to be at "System.Data.OleDb.RowBinding.FreeChapter" which I can't even call through directly.  At this time, I'm not doing anything with the aggregate Chapter column, just using it as a means to reduce the result set.  If I try to do *anything* with the Chapter column (GetData, GetValue) I get an OleDbException "Row handles must all be released before new ones can be obtained."  In other words, even if I wanted to read the chapter data, apparently I can't.  Is there some step I need to perform to close out the row?  If I suck it into an OleDbDataAdapter, it works fine (I get an ordinal number for the value of the Chapter DataReader column.  What do I need to do to handle this the right way?





    Thursday, April 26, 2007 4:21 PM

All replies

  • I have this issue as well.  We use Windows Search to pull back pretty large searches, so I've been trying to page the results, but the SQL primitives provided don't give me an easy way to do it.  If anyone can help out with that, I'd appreciate it because right now I use an ADO Recordset with caching and in 3.0 that's causing me some grief.  The first thing I noticed is that when we moved from WDS 2.6.5 to WS 3.0, the provider no longer supports RecordCount in the Recordset so I used a GROUP ON / COUNT() to get counts by type and then put it back together.


    That worked in my initial implementation, but now I have a Recordset pointed at the search result and when I go to grab the record count with an OleDbCommand, I get your exception, which appears to be because I have two readers trying to look at the index.  So my suggestion is, do you already have a reader looking at the query result in your OVER clause?


    Secondly, if anyone can tell me a way to page the darn WS 3.0 results so I can pull pages into .NET as needed, that would be super.  Otherwise, I'll just stew for a few more days.

    Thursday, May 10, 2007 9:16 PM
  • Sorry I didn't see this sooner.  I never got an alert on this message!


    I am actually trying to ignore the nested result sets.  I have no need for them.  I'm using the aggregate only to get the counts.  I did try to open the nested result, just so I could close it, but it gave me the same error.  Maybe I need to iterate over the entire nested set, but that doesn't really make sense.  Plus, for performance reasons, I really don't want to dig into the results!

    As for paging, I've thought about this too and haven't come up with any perfect idea.  You could just keep the connection open for a minute or two based on a timeout, or read in the entire result into an in-memory cache (also on a timeout), but that might be more expensive than you want.

    One other option for paging, which works perfectly *IF* you don't need to order your results, is to use the System.Search.EntryID field.  I think that this is some internal key used internally (maybe even just a primary key in the internal SQL table?).  If you don't order results, it orders based on this field by default.  If you retain the value of this property for the last item in your search, then use:

    WHERE System.Search.EntryID > {last high value}

    you will get paging without keeping the connection open.  LIke I said though, it's no good if you need to order by a different value for other purposes.  Sorry I don't have any other ideas.



    Monday, May 14, 2007 3:11 PM
  • I found a solution to the page count issue by using an ADONET.Recordset instead of my own caching scheme with an OleDB.OleDbCommand.  Not what I was hoping for, but it turned out if I opened a Recordset with CursorLocation set to adClient<something> the Recordcount property was then available and I could use the built in caching of the Recordset.


    About your query and the error, I initially wrote a nearly identical query for what I was doing.  I took the query I wanted the rowcount for and put it in the OVER clause and then aggregated/Count()ed on an arbitrary field.  The first time I did this, it worked fine.  The second time I did it, I did so after creating the Recordset object that pointed to the result.  With a Recordset object pointed to the result, running a separate connection/query to get the counts back as rows and then add them up was failing with the error you listed.


    But as you described the technique was to take the rows I was interested in and then aggregate them and count them by some column (System.ItemType for example).  Then get that recordset back (row count by item type) and Use a while read() loops to add them up.  That did work with exactly your syntax until I started pointing a second recordset to the subquery.


    This made me think that perhaps opening the recordset and pointing at the Windows Search index was locking it so the second query against the same data set to get the row counts was failing.  I don't know if that is actually true because I found the alternative and moved on, but it might be useful information.

    Wednesday, May 16, 2007 10:38 PM
  • Thanks a lot for your response.  I'm tempted to use Recordset, but I don't really want to add any non-BCL libraries if I can avoid it.  There must be some "proper" way to do this that's just eluding me!  It's frustrating since we can't peek under the hood to see what we really need to do.


    Any other ideas from anyone in the community?

    Wednesday, May 23, 2007 2:05 PM