Hi,

We have a SKU object, and would like to implement SKU.IsUsed() as a read-only Boolean property. This would be based on some pseudo-code like this:

Public ReadOnly Property IsUsed() As Boolean Get 'NOTE: This is fake, and we know it Return Order.Exists(SKU.SKUNumber) End Get End Property

Currently, we are doing an Orders.GetMulti(pred), where pred is a FieldCompareValuePredicate based on SKU.SKUNumber, applying a limit of 1 on the NumberOfRecordsToReturn, but surely there is a better way?

Thanks, cardplayer

You could use GetScalar. This way only the result is sent instead of the whole resultset (the entity or entities).

daelmo,

Thanks for the advice. I prefer not to use GetScalar(), because I believe that its performance will be poor. I was only able to see that I could use the Count, but it in this case, a SKU might be used millions of times, and query performance will not be very good if the code is issuing COUNT() on my database.

Any other ideas that we could try?

Is the GetScalar(someFilter) that poor-performance, even if the someFilter is built on the appropiate indexed fields? What would be the perfect generated SQL for you in this case?

daelmo,

The goal is to simply check existence, not to COUNT() over millions of rows. As I mentioned, even with a proper predicate, this will be extreme I/O.

Something like this:

IF EXISTS(SELECT * FROM dbo.Orders ORD WHERE ORD.SKU = @sku) BEGIN RETURN 1 END ELSE BEGIN RETURN 0 END

This way, the first row found terminates the query. I can do this by mapping my IsUsed() property to a stored procedure call, but it seems like I should be able to create a predicate and issue an EXISTS() like this, maybe something like Collection.IfExists(Predicate) or something. If I can already have DBCount and other sweetness, IfExists() does not seem that tough.

Thanks! cardplayer

I think you are mistaken GetScalar() with GetDBCount().

By using GetScalar() you may have the following query:

SELECT ORD.SKU FROM dbo.Orders ORD WHERE ORD.SKU = @sku

Walaa,

Thank you for your reply. I am not mistaking anything. Quite the contrary. I mentioned DbCount by way of showing a different approach that I could use with the generated code. However, both are inappropriate in my situation.

Regardless of whether I do a GetMulti(Predicate) and check the count, or I just do a DbCount(), or what have you, there is way too much I/O being incurred in the server. The best solution I found to check mere existence was to set the maximum rows returned to 1, and to use GetMulti(Predicate, 1), or whichever overload that was. (I do not have the documentation in front of me).

I am sorry that I am not explaining this adequately. What we need is to merely check the existence of some rows, say, to see if a SKU has ever been used in relationship to an Order, for example.

This example would illustrate it:

Assume: Order SKU OrderSKU (association table).

To see if a SKU exists on any Order, we would need a Predicate for the SKU number, and a Relations collection to show how to apply it.

Please excuse code sample errors in this forum

Dim sku As SKUEntity = New SKUEntity(3)

Dim skus As SKUCollection = New SKUCollection()

Dim predExp As PredicateExpression = New PredicateExpression(EntityType.SKU) predExp.AddWithAnd(New FieldCompareValuePredicate(SKUFields.SKU, CompareValue, SKU.SKUNumber)

Dim rels As RelationCollection = New RelationCollection rels.Add(SKUEntity.Relations.OrderSKUByOrderId) rels.Add(OrderSKUEntity.Relations.SKUBySKUNumber)

If SKU.Exists(OrderPredicate, Relations) would be excellent. That would do something like this:

IF EXISTS ( SELECT * FROM dbo.Order INNER JOIN dbo.OrderSKU ON dbo.OrderSKU.OrderId = Order.OrderId INNER JOIN dbo.SKU ON dbo.SKU.SKUNumber = dbo.OrderSKU.SKUNumber WHERE dbo.SKU.SKUNumber = 3 ) BEGIN RETURN 1 END ELSE BEGIN RETURN 0 END

This way, the server checks for EXISTENCE, not COUNT. This is a _massive _difference. The server can stop after the first row that meets the condition. It does not have to COUNT() over millions upon millions of rows. This is why my GetMulti(Predicate, 1) works so much better than COUNT() to satisfy this case. Regardless, that feels like a hack, as much as I try to cope with it mentally.

Using GetScalar() or GetDbCount(), or what have you, is not the same as a straight EXISTS(). My feeling is that this would not be hard to do, given the generated code and being able to apply predicates and relations.

Does that explain it better? My idea is to just check existence of any rows that meet the criteria.

cardplayer

GetScalar won't perform a Count() unless you specify so. GetScalary() as the name implies returns one value rather than the entire row. So instead of doing "SELECT *" It can do "SELECT SKU"

Anyway if you don't want to use GetScalar(), you might want to look at using DynamicList, with only one field to return.

Hi Cardplayer -

I haven't done this, so best of luck to you! Here are my initial thoughts:

• Could use FieldCompareSetPredicate - uses EXISTS
• Could use ScalarQueryExpression to either do a Subselect or just return a single value. Look at forceRowLimit, which uses TOP
• http://llblgen.com/tinyforum/Messages.aspx?ThreadID=11117
• http://llblgen.com/tinyforum/Messages.aspx?ThreadID=14125
• Could use GetMulti with maxNumberOfItemsToReturnPlease let us know if you get this working. Thanks!

Ryan

rdhatch,

Thank you for the ideas. I will look at the first two approaches.

I hope I am being clear here; it seems that I am not, based on the third idea. I am already doing the third thing, as mentioned in the long-winded post, and also in my original.

Ref: "Currently, we are doing an Orders.GetMulti(pred), where pred is a FieldCompareValuePredicate based on SKU.SKUNumber, applying a limit of 1 on the NumberOfRecordsToReturn, but surely there is a better way?"

^^ That is where I mention that I am already using a limit of 1. Subsequent clarification explains that I feel that this is a hack. I will see what I can do with the other tactics to get this to work properly.

Walaa, in no case is GetScalar() appropriate here - the functions offered are not sufficient to execute a straight EXISTS(); the closest thing I found in there was COUNT(). Please explain how I am wrong, because I really would like to know.

Thanks, cardplayer

Use the GetScalar without specifying an aggregate method nor an expression.

Please check this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8975