asked on August 11, 2022
Hi,
I have been trying out Flexmonster Data server and i have query which gets 600k records and it takes 1:30 minute. This is beyond the command timeout and it seems i cannot change it by adjusting the connection string.
Is there a way the adjust the command timeout value within the Data Server itself, in the config?
Regards Jaco
⋅ August 12, 2022
Hello, Jaco,
Thank you for reaching out to us.
We will research possible solutions for this issue and provide you with results ETA September 5th.
Feel free to contact us if other questions arise.
Kind regards,
Nadia
⋅ September 5, 2022
Hello, Jaco,
Thank you for giving us some time.
Our team did research on using the Command timeout property, and we are ready to share the results with you.
The CommandTimeout prosperity's default value is 30s, but it seems not to work as expected. It can be changed through the code and be defined as less than 30 seconds, but it still doesn't affect the data loading. Our team has tested queries that took 40, 50, 60, and 80 seconds to load, which is more than the CommandTimeout value. Similarly to your case, we checked the query with 8 million rows, which took 318 seconds and didn't fail.
To sum up, we suppose that the described issue is not connected with the CommandTimeout property. Please consider running the SQL Server Profiler and check the cause of the issue there. You can also check your connection string.
We hope it helps. You are welcome to write to us in case further questions arise.
Kind regards,
Nadia
⋅ September 6, 2022
Hi Nadia,
Thanks for getting back to this issue.
The error im receiving is the following:
Please check query or connection string. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at Flexmonster.DataServer.Core.DataLoaders.DataLoader.Load(String index) at Flexmonster.DataServer.Core.DataStorages.DataStorage.GetOrAddAsync(String cacheKey) at Flexmonster.DataServer.FlexmonsterPrepopulatingCacheService.TryLoadIndex(IServiceScope scope, String indexName)
So to me this still looks like a timeout expiration error.
I have done some further testing in SQL Manager and SQL Server Profiler.
- I noticed that SQL Server Profiler also after 30 seconds stops processing the query.
- The query has a lot of joins and therefor it takes some time before it flushes the data and the first flush occurs at about 50 seconds and after 1 minute and 15 seconds the entire query is loaded (IN SQL Manager)
- When i remove some joins the first flush is at 24 seconds and the entire query is loaded in about 1 minute.
- When i use this last query, then the query is loaded in FM Data server.
So it seems that when the first flush of data takes longer than 30 seconds, than FM Data Server returns a time out expires error
I hope this extra information helps to reproduce the error im getting and making a fix for this.
Regards
Jaco
⋅ September 7, 2022
Hello, Jaco,
Thank you for the quick response and for providing additional information.
Our team will look at the described issue one more time, and we will provide you with the package containing the beta version for testing as soon as it is ready.
Feel free to contact us if other questions arise.
Kind regards,
Nadia
