Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > General> GetDbCount problem
 

Pages: 1
General
GetDbCount problem
Page:1/1 

  Print all messages in this thread  
Poster Message
happyfirst
User



Location:

Joined on:
28-Nov-2008 16:36:09
Posted:
204 posts
# Posted on: 13-Feb-2009 20:52:24.  
I'm working on paging in my middle tier and I'm trying to call GetDbCount to get a count of all the records that exist for a given filter. I'm also building a sort expression and an includefields collection that I pass to GetMulti.

For some of my views, when I call the GetDbCount, I get an oracle error regarding one of my fields in the select clause that you're building (that I don't seem to have any control of). Invalid identifier. When I do the actual select, it works, and that select contains the very same field that the count query failed on.

Why is the GetDbCount building such a huge select? All I need is a count of rows. Is there any way to limit that? I don't see an overload for passing an includeFields to GetDbCount.
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
7618 posts
# Posted on: 13-Feb-2009 21:38:02.  
Could you please post some relevant information? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

- The exact exception message and stack trace of the exception.
- The LLBLGen version and runtime library version.
- The "generated sql" of the problematic fetch or dbcount.


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
happyfirst
User



Location:

Joined on:
28-Nov-2008 16:36:09
Posted:
204 posts
# Posted on: 13-Feb-2009 21:56:15.  
LL 2.0, Oracle, Self-Servicing.

Never mind on the oracle error. DbAdmin replied about a new column on table. Although the select from the view wouldn't generate an error, only the count, interesting....

Anyways, only remaining issue is is there a way to restrict the select clause that is generated when using GetDbCount? Ideally, I'd think it would only add the primary keys or just whatever the first column is. Seems like a waste to select all columns.

thanks
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
7618 posts
# Posted on: 14-Feb-2009 03:43:51.  
The duration of the two queries are almost equal. However if you don't want that, you could perform a myCollection(someFieldIndex, AggregateFunction.CountRow);.

David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
happyfirst
User



Location:

Joined on:
28-Nov-2008 16:36:09
Posted:
204 posts
# Posted on: 10-Aug-2009 14:58:17.  
daelmo wrote:
The duration of the two queries are almost equal. However if you don't want that, you could perform a myCollection(someFieldIndex, AggregateFunction.CountRow);.


We discovered that the performance is significately different for view based entities due to calculated fields, etc.

So I tried your suggestion. I'm calling GetScalar. But it looks like it's still referencing all fields. I'm calling the GetScaler that takes FieldIndex,IExpression,AggregateFunction,and Predicate.

I'm setting FieldIndex to the first field of the primary key,IExpresssion is null, AggregrateFunction is CountRow and predicate is my filter. It's returning the right count but I'm worried it's just doing the same thing as before. I would have expected Count(NameOfFieldIndexThatISupplied) as RowCount or something like that.

It's generating a query that includes

SELECT COUNT(*) AS "NameOfFieldIndexThatISupplied" FROM ....

Am I doing something wrong or is this the intended behaviour? If intended, then how can I accomplish what I want?
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
13698 posts
# Posted on: 10-Aug-2009 16:32:49.  
Please submit the following:
1- The exact code snippet you are using.
2- The exact generated SQL.
3- The LLBLGen Runtime library version number (please check the link posted by daelmo).


  Top
happyfirst
User



Location:

Joined on:
28-Nov-2008 16:36:09
Posted:
204 posts
# Posted on: 10-Aug-2009 16:52:18.  
Code is part of a framework that is using reflection to execute:

string fieldIndexTypeName = this.BusinessObjectCollectionType.FullName.Replace("CollectionClasses.", "").Replace("Collection", "FieldIndex");
                        Type fieldIndexType = this.BusinessObjectType.Assembly.GetType(fieldIndexTypeName);

                        Type[] types = new Type[] { fieldIndexType, typeof(IExpression), typeof(AggregateFunction), typeof(IPredicate) };
                        MethodInfo getScalarMethod = this.BusinessObjectCollectionType.GetMethod("GetScalar", types);

                        List<string> pKeys = GetDataKeyNames();
                        System.Reflection.FieldInfo fieldPropInfo = fieldIndexType.GetField(pKeys[0]);
                        object field = fieldPropInfo.GetValue(null);

                        object[] parameters = new object[4];
                        parameters[0] = field;
                        parameters[1] = null;
                        parameters[2] = AggregateFunction.CountRow;
                        parameters[3] = searchConfig.Filter;
decimal rowcount = (decimal)getScalarMethod.Invoke(collection, parameters);


Count query with no search criteria entered:

    Query: SELECT * FROM (SELECT COUNT(*) AS "UserId" FROM "DATA"."USER_PROFILE") WHERE rownum <= 1


Count query with search criteria:

Query: SELECT * FROM (SELECT COUNT(*) AS "UserId" FROM "DATA"."USER_PROFILE" WHERE ( ( ( "DATA"."USER_PROFILE"."USER_ID" LIKE :UserId1)))) WHERE rownum <= 1
    Parameter: :UserId1 : String. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: "%AD%".


Version Number: 2.6.08.1125
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
13698 posts
# Posted on: 11-Aug-2009 08:19:53.  
Please try AggregateFunction.Count, instead of AggregateFunction.CountRow

AggregateFunction.CountRow ----> Count(*)
AggregateFunction.Count        ----> Count(field)


  Top
happyfirst
User



Location:

Joined on:
28-Nov-2008 16:36:09
Posted:
204 posts
# Posted on: 11-Aug-2009 15:23:43.  
Walaa wrote:
Please try AggregateFunction.Count


Thanks! That worked.
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.