Hi,

We have several hundreds of thousands (700,000) of records in a table. I am using LLBLGen Pro TypedView to fetch these records. I am filtering the records by 10,000 at a time using the pageSize parameter in the Fill method.

In this I am getting ORMQueryExecutionException that says "timeout expired".

Kindly help.

Thanks, Natraj

Hi,

Can you go to Troubleshooting and debugging section in the manual in order to get the executed SQL query, and try to run it on your SQL managment app ?

btw what DBMS you you use ?

"timeout expired".

If you are using adapter you can set a command time out before the fetch. Don't use zero. That means infinite in Sql server, but means don't set commandtimeout to llblgen.

Don't use that kind of sizes for pages, use pages of size 1000 max. that keeps the memory consumption low

Thanks for your reply. I applied 1000 per page in this case the I’m getting only the 1000 records. The problem is getdbcount() while run time it throws the "time out expired" Exception.

I'm using self-servicing method how I can set the command time out property.

Can you suggest how to accomplish this.

Would you please post the code snippet that calls the getdbcount().

This is my code which uses the getdbcount()

public int getLogCount(int transformationUID)
        {
            int tCount = 0;
            VwTransformationLogTypedView objlogcollection = new VwTransformationLogTypedView();
            try
            {
                FieldCompareValuePredicate objFilter = new FieldCompareValuePredicate(VwTransformationLogFields.Transformationuid, ComparisonOperator.Equal, transformationUID);
                IPredicateExpression filter = new PredicateExpression();
                filter.Add(objFilter);
                tCount = objlogcollection.[colorvalue="FF0000"]GetDbCount[/color](false, filter);

            }
            catch (Exception ex)
            {
                CDMS.COMMON.ExceptionLog.Log.Error(ex.Message, ex);
            }
            return (tCount);


        }

Would you please examine the generated SQL query of GetDBCount() and try to run it manually against the database and see if it times out? (refer to the manual's section -> "Using the generated code -> Troubleshooting and debugging", for more info about getting the generated query)

You can adjust the timeout in the DbUtils class: DbUtils.CommandTimeout = 60;

for 60 seconds.

Though, a getdbcount is a scalar query, it does select count(*) from table where <your filter>, which should be pretty quick.

I remembered facing similar kind of problem with huge..really huge amount in data. we started using paging so address the problem.

i used similar to following code to implment paging Collection.GetMulti(filter, maxCount, sortExpression, relations, prefetchPath, PageIndex, PageSize);

but i have to have dbcount so that i can show that paging on the UI

Collection.GetDbCount(filter, relations); now this particular piece of code took the longest to execute.

If i am not wrong then i think for paging you do create some tem table...etc... so i think at that time only you can potentially get the no. of records should not hit the DB twice for that....can this function be upgraded to behave as follow???? e.g. int recordCount = Collection.GetMulti(filter, maxCount, sortExpression, relations, prefetchPath, PageIndex, PageSize,true); if true then return recordcount else not...

i may be entirely wrong in my perception though..waiting for some valuable comments from your side

Ammit,

you are absolutely correct.any how we are trying make changes in functionality to avoid the loading of large volume data.thanks for your reply.

m.natarajan wrote:

Ammit,

you are absolutely correct.any how we are trying make changes in functionality to avoid the loading of large volume data.thanks for your reply.

true... you can implement paging on UI as well just like google search paging mechanism where you allow user to enter max number of record per page and have them update it [make sure you set a limit on that as well .e.g. max number of record allowed per page = 1000] then let user brows through pages...it will be very efficient with LLBLGen thnough where you can jump to a specific page with numebr of record you want to see per page..thats freaking sleek but the question remains the same... dbCount().. you got to know the dbcount to determine number of pages...

waiting for some valuable inputs from LLBLGen staff...

If a select count() from table takes a long time to execute, you should profile the query in the db server and see if you need indexes somewhere. Especially if you have a WHERE clause which accesses fields which don't have an index. All LLBLGen Pro does is execute the 'select count() from table' scalar query so all time spend is in the DB.