How to construct query

Feb 15, 2011 at 10:02 PM

I have 2 columns that I would like to involve in a query, but I'm not sure how to construct it to get the result I want.

ColumnA is a single byte that is either 255 or a value 0-100. ColumnB is a 64-bit value (Currency).

I would like to retrieve columns where ColumnA is 255 and ColumnB is between X and Y (where X <= Y). The results need to be sorted ascending by ColumnB. Also would need to perform the same operation where ColumnA is not 255.

I am using ColumnA as an indicator as to whether the item is "processed" or not (255 = not processed, 0-100 = processed). I can change the schema if it makes things easier.

Any help would be appreciated.

Feb 15, 2011 at 10:18 PM

One good approach might be to create two indexes, one for processed items and one for not-processed items. You will need column that is null or non-null depending on the processed state (conditional indexes rely on null/non-null to determine if an item should be in the index).

The first index would be conditional on "IsProcessed = null" and would sort on ColumnB. This will contain all unprocessed items, sorted by ColumnB.

The second index would be conditional on "IsProcessed != null" and would sort on ColumnB. This will contain all processed items, sorted by ColumnB.

When you process and item you should set the IsProcessed column to a non-null value.

The ConditionalIndexFixture.cs file in EsentInteropTests (in the project source code) creates conditional indexes.

 

Feb 15, 2011 at 10:30 PM
Edited Feb 15, 2011 at 10:30 PM

Would it work to make ColumnA the conditional index? Null if not processed, non-null (0-100) if processed? I don't mind creating another column for the conditional, just wondering if this would work as well or if there would be some issue there.

I'll look into the ConditionalIndexFixture.cs source for how to setup conditional indexes.

BTW, thank you for the insanely quick response time :)

Feb 15, 2011 at 10:47 PM

Yes, that will work fine too -- a lot of applications do something like that.

Feb 16, 2011 at 3:41 PM

I'm not sure what is going wrong here. When I invoke JetCreateIndex2 on a new database to create the conditional indexes, the call hangs and the process uses near 100% CPU. Here is the code I am using to create the indexes:

const string ColumnBKey = "+ColumnB\0\0";

using (Table table = new Table(_session, _databaseId, "Table", OpenTableGrbit.DenyRead))
{
	JET_INDEXCREATE[] indexes = new[]
	{
		new JET_INDEXCREATE
		{
			szIndexName = "Processed",
			szKey = ColumnBKey,
			cbKey = ColumnBKey.Length,
			rgconditionalcolumn = new[]
			{
				new JET_CONDITIONALCOLUMN
				{
					szColumnName = "ColumnA",
					grbit = ConditionalColumnGrbit.ColumnMustBeNonNull
				}
			},
			cConditionalColumn = 1
		},

		new JET_INDEXCREATE
		{
			szIndexName = "NotProcessed",
			szKey = ColumnBKey,
			cbKey = ColumnBKey.Length,
			rgconditionalcolumn = new[]
			{
				new JET_CONDITIONALCOLUMN
				{
					szColumnName = "ColumnA",
					grbit = ConditionalColumnGrbit.ColumnMustBeNull
				}
			},
			cConditionalColumn = 1
		}
	};

	Api.JetCreateIndex2(_session, table, indexes, indexes.Length);
}

Feb 17, 2011 at 7:02 PM

The problem might be creating two indexes in one call. That puts JetCreateIndex2 into 'batch' mode, which can be quirky (it requires the caller not be in a transaction). Try making two separate calls to JetCreateIndex2. What OS are you using?

Feb 17, 2011 at 7:36 PM

Windows XP.

JetCreateIndex2 does raise an error if you create the index in a transaction, so I did move it out of the transaction.

OK, this is strange. Creating the two indexes in one call appears to be working today. Obviously, it was something I was doing that caused ESENT to hang, but I have no clue what it was. The ESENT code really hasn't changed. I create 3 tables in a transaction, commit the transaction, and then create the conditional indexes with the code I posted.