Hi, I am on LLBLGen 5.8 using SelfServicing in dotnet 4.8 website.

I have a piece of code that runs very fast in a console app but gives a timeout in ASP.NET web application: I am running the following code:

        StortingLogPrcRcptTypedView tv = new StortingLogPrcRcptTypedView();
        PredicateExpression filter = new PredicateExpression(new FieldBetweenPredicate(StortingLogPrcRcptFields.LogDateTimeCreated, from, to));
        SortExpression sorter = new SortExpression(StortingLogPrcRcptFields.LogDateTimeCreated | SortOperator.Ascending);
        tv.Fill(0, sorter, true, filter);

        return tv.CreateDataReader();

The field between predicate is a period of 1 day. It should result in a collection of app. 180 rows of 52 fields. When I run this in a console app, it finishes in less than a second ( {00:00:00.7600039} ).

In the web application, after waiting for at least 30 seconds I get the following error: Details van uitzondering: System.ComponentModel.Win32Exception: Time-out van wachtbewerking

Regel 243:  // __LLBLGENPRO_USER_CODE_REGION_START AdditionalFields 
Regel 244:  // __LLBLGENPRO_USER_CODE_REGION_END 
Regel 245:          return DAOFactory.CreateTypedListDAO().GetMultiAsDataTable(fieldsInResultset, this, maxNumberOfItemsToReturn, sortClauses, selectFilter, null, allowDuplicates, groupByClause, transactionToUse, pageNumber, pageSize);
Regel 246:      }
Regel 247:

And the stacktrace:

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Er is een time-out opgetreden tijdens de uitvoering. De time-outperiode is verstreken voordat de bewerking was voltooid of de server reageert niet.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.8\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:119
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IEntityFields fieldsToReturn) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.8\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:1941
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAsDataTableAction(DataTable tableToFill, ITransaction transactionToUse, QueryParameters parameters) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.8\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:1388
   HITc.SBSLog.TypedViewClasses.StortingLogPrcRcptTypedView.Fill(Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IPredicate selectFilter, ITransaction transactionToUse, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in F:\DevelopmentMeppel\DataAccessLayer\SBSLog\Domain\TypedViewClasses\StortingLogPrcRcptTypedView.cs:245
   HITc.SBSLog.TypedViewClasses.StortingLogPrcRcptTypedView.Fill(Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IPredicate selectFilter) in F:\DevelopmentMeppel\DataAccessLayer\SBSLog\Domain\TypedViewClasses\StortingLogPrcRcptTypedView.cs:199
   HITc.SBSLog.TypedViewClasses.StortingLogPrcRcptTypedView.StortingDetails(DateTime from, DateTime to) in F:\DevelopmentMeppel\DataAccessLayer\SBSLog\Domain\HITcTypedViewClasses\StortingLogPrcRcptTypedView.cs:18
   HITc.PAX.SBS.Stortingen.StortingenoverzichtDetails.btnExport_Click(Object sender, ImageClickEventArgs e) in F:\DevelopmentMeppel\Portal\HITcPortal\PAX\SBS\Stortingen\StortingenoverzichtDetails.aspx.cs:61
   System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +143
   System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +188
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1959

Timeouts are usually caused by deadlocks, e.g. a row in the resultset is locked by an update that is waiting on your fetch to complete. Try to see if you have an update/insert going at the same time and if so, try to add the typedlist fetch to the same transaction to share the connection. If the same connection is used for fetch and update this won't happen.

There are no updates done on the date used in the view. The typed view is based on data copied from the production environment. It consist of data that is logged during a manufacturing process.

I have 2 VM's. One for the database server and one for development. For testing purposes I also use a seperate Windows PC that runs a deployed version of the website I am working on in IIS. I just started the database server and then tested again (using the deployed website). I got the same error.

Then I started my development machine, deployed the console program to the Windows PC and run it. It works fine:

D:\test\Release>ReportsRenderer True - 00:00:00.6914398 (True is the result of HasRows)

Both the website and the console program are using exactly the same data .

JayBee wrote:

There are no updates done on the date used in the view. The typed view is based on data copied from the production environment. It consist of data that is logged during a manufacturing process.

I have 2 VM's. One for the database server and one for development. For testing purposes I also use a seperate Windows PC that runs a deployed version of the website I am working on in IIS. I just started the database server and then tested again (using the deployed website). I got the same error.

Then I started my development machine, deployed the console program to the Windows PC and run it. It works fine:

D:\test\Release>ReportsRenderer True - 00:00:00.6914398 (True is the result of HasRows)

Both the website and the console program are using exactly the same data .

Do other queries get through in the asp.net site? if there are no updates/inserts going on, then it might be a tcp / sql server configuration issue. The error suggest there's no issue with the connection, but sqlserver connections are done in 2 steps: one is connecting to the server, and the other step is connecting to the data. If no query is getting through from the asp.net site, there might be an issue on that side. (Also make sure the connection string is 100% identical between console app and asp.net so we can rule that out as the issue). If you log in as a windows user on the console app side, you have to make sure you use the same user on the asp.net side, and iis websites run under a different user by default.

On the machine where you have asp.net running, start the SQL Server profiler from SMSS (the sql server management studio), if you have that installed. It could give insight in what's arriving at the sqlserver instance.

The website functions normal. Other IO to the databases / tables involved works fine.

When I copied the connection string the website is using to the config file of the console program, the console program also stops with the SQL Exception.

Working: data source=SQL001\Meppel;initial catalog=XYZ;User ID=.....;Password=......;persist security info=False

Not working: data source=SQL001\MEPPEL;initial catalog=master;User ID=......;Password=......;persist security info=False;packet size=4096

When I changed master into XYZ the program run again.

As far as I know LLBLGen does not use the catalog name but always the name of the database containing the tables or views involved. Isn't this true anymore?

JayBee wrote:

The website functions normal. Other IO to the databases / tables involved works fine.

When I copied the connection string the website is using to the config file of the console program, the console program also stops with the SQL Exception.

Working: data source=SQL001\Meppel;initial catalog=XYZ;User ID=.....;Password=......;persist security info=False

Not working: data source=SQL001\MEPPEL;initial catalog=master;User ID=......;Password=......;persist security info=False;packet size=4096

When I changed master into XYZ the program run again.

As far as I know LLBLGen does not use the catalog name but always the name of the database containing the tables or views involved. Isn't this true anymore?

It generates the catalog name into the table references, correct, tho perhaps you're using catalog name overwriting? What's odd is that other queries work fine but this particular one doesn't...

Could you enable tracing https://www.llblgen.com/Documentation/5.11/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_troubleshootingdebugging.htm#dynamic-query-engine-tracing To see what the sql query looks like with this particular query and with a query that does work with the original connection string?

The initial catalog is the catalog used when there's no catalog specified in the query, e.g. select * from dbo.customers. If you do select * from XYZ.dbo.customers, it will work with the not working connectionstring.

For my understanding: the asp.net site works when you use the xyz connectionstring?

The website works when I change 'master' into another database name. I have changed it into a name that is different from the database where the stored procedure is in. Apparently using 'master' is the problem.

After installing the visualizer and switching to VS 2019 I was able to have a look at the query executed when the error occurs. It ends with

FROM [HITc_Logging].[dbo].[SBS_StortingLogPrcRcpt] WHERE ( [HITc_Logging].[dbo].[SBS_StortingLogPrcRcpt].[LogDateTimeCreated] BETWEEN @p1 AND @p2) ORDER BY [HITc_Logging].[dbo].[SBS_StortingLogPrcRcpt].[LogDateTimeCreated] ASC Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2024-02-01T00:00:00.0000000. Parameter: @p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2024-02-01T23:59:59.0000000.

And this is what it should be.

Strange! Especially as it only blocks on this query if I understood you correctly.

Looking into it, the initial catalog is the database the connection makes its connection with, and master is a database that every user can access (readonly), iirc. https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder.initialcatalog?view=dotnet-plat-ext-8.0&redirectedfrom=MSDN#System_Data_SqlClient_SqlConnectionStringBuilder_InitialCatalog

For several reasons we have decided to use multiple databases instead of using one big database. I started using master as the initial database so I could use that in all the different apps. Just convenience.

JayBee wrote:

For several reasons we have decided to use multiple databases instead of using one big database. I started using master as the initial database so I could use that in all the different apps. Just convenience.

Ok. Well, regardless, the query will use the catalogs that are used in your project, so the initial catalog specified is really just for the connection, you can specify one of the catalogs used in the project as well. But as I said, it's weird that it fails in this particular case and not in other queries in your site.

Otis wrote:

JayBee wrote:

For several reasons we have decided to use multiple databases instead of using one big database. I started using master as the initial database so I could use that in all the different apps. Just convenience.

Ok. Well, regardless, the query will use the catalogs that are used in your project, so the initial catalog specified is really just for the connection, you can specify one of the catalogs used in the project as well. But as I said, it's weird that it fails in this particular case and not in other queries in your site.

For the time being I changed the string in the web.config. That is fine. When there is a solution to this specifc issue, I will happy to do some testing.

I don't see what is wrong with our code, or that there is a bug at all. It's an aspect of authentication with an IIS application with sql server.