Profile Picture

SQL timeout historical data 2

Posted By alpha23 3 Years Ago
Message
Posted Sunday April 06 2014
The following appears to be a different issue than from my post last month. The database is fairly small (the other was over a TB) and it is occurring under different historical data (6 years, 1 minute data, ~1000 symbols). The exception is below. An example of output from SQL logging is the following although there are probably about as many SELECT statements as there are symbols.

2 SELECT TOP (5000) [t0].[SymbolGuid], [t0].[Frequency], [t0].[BarStartTime], [t0].[Order] AS [Order], [t0].[Open] AS [Open], [t0].[Close] AS [Close], [t0].[High], [t0].[Low], [t0].[Bid], [t0].[Ask], [t0].[Volume], [t0].[OpenInterest], [t0].[EmptyBar]

FROM [dbo].[Bar] AS [t0]

INNER JOIN [dbo].[Symbol] AS [t1] ON [t1].[SymbolGuid] = [t0].[SymbolGuid]

WHERE ([t0].[BarStartTime] >= @p0) AND ([t0].[BarStartTime] <= @p1) AND ([t1].[SymbolUniqueID] = @p2) AND ([t0].[Frequency] = @p3)

ORDER BY [t0].[BarStartTime], [t0].[Order] 2 -- @p0: Input DateTime (Size = -1; Prec = 0; Scale = 0) [1/22/2007 14:52:00] 2 -- @p1: Input DateTime (Size = -1; Prec = 0; Scale = 0) [12/31/9999 23:59:59] 2 -- @p2: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [LSI!QQ!USD!0!0!NoContract!Stock] 2 -- @p3: Input Int (Size = -1; Prec = 0; Scale = 0) [1] 2 --

Any thoughts on how to fix/why this is occurring?

Thanks, Duane

An exception of type System.Data.SqlClient.SqlException was thrown.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator()
at RightEdge.DataStorage.LinqToSQLDataAccessor`2.Load(DateTime start, DateTime end, Int64 maxItems, Boolean loadFromEnd) in C:\Users\duane.webber\Documents\Investments\InvestmentModel\RightEdgePlugins\DataStorage\LinqToSQLStorage.cs:line 522
at RightEdge.Common.Internal.BarDataStreamer.SymbolBarStream.LoadMoreBars()
at RightEdge.Common.Internal.BarDataStreamer.SymbolBarStream.Update()
at RightEdge.Common.Internal.BarDataStreamer.GetNextBarEvent()
at RightEdge.Common.Internal.BarDataStreamer.GetNextItem()
at RightEdge.Shared.SystemWrapper.RunSystem(SystemData systemData, SharedSystemRunData runData, ServiceFactory brokerFactory)
at RightEdge.Shared.SystemWrapper.RunSystem(String filename, ServiceFactory brokerFactory, PluginSettings dataStoreSettings)
at RightEdge.Shared.SystemWrapper.RunSystem(String filename, ServiceFactory brokerFactory, PluginSettings dataStoreSettings)
at RightEdge.Shared.TradingModuleWrapper.Run(String filename)
at RightEdge.Shared.TradingModuleWrapper.RunSystem(SharedSystemRunData systemRunData)
at RightEdge.SystemProgress.InitAndRunSystem()
Posted Saturday April 12 2014
thoughts?
phg
Supreme Being (44,517 reputation)Supreme Being (44,517 reputation)Supreme Being (44,517 reputation)Supreme Being (44,517 reputation)Supreme Being (44,517 reputation)Supreme Being (44,517 reputation)Supreme Being (44,517 reputation)Supreme Being (44,517 reputation)Supreme Being (44,517 reputation)
Posted Sunday April 13 2014
Duane, I'm wondering if a bit more background might shed some light on this. What is the statement in your strategy that gets this started? It seems that gets converted into a LINQ statement in the data storage plugin? (Something is happening at line 522.) So the SQL is generated? As you can see, I don't have a solid grasp on this. But I would like to understand better how this got down this rabbit hole. Thanks.

-Pete

See also Yahoo group about applying RE.

Posted Sunday April 13 2014
Pete,

   There is no statement in my strategy that impacts this. Per the exception, it occurs when RightEdge gets the next item. Yes, it is from the DataStorage plugin that ships with RightEdge. The above may be due to a large number of symbols in conjunction with a using minute data but regardless of the size of the dataset, this exception shouldn’t be thrown.

I’m don’t understand how you are suggesting to help? Have you tested against a similar size dataset? Are there SQL debugging methods that you are thinking about?


Daniel, RightEdge Team,

   I need some assistance here. Has RightEdge been tested under similar size datasets? Thoughts on how to fix/why this is occurring?

It’s been a week since I initially posted. These delayed responses are very frustrating.

Duane
Posted Monday April 14 2014
Hi Duane,

We haven't tested the SQL Server store with data sizes approaching 1 TB. I wouldn't have expected timeout issues with your smaller data set.

In either case, I think the SQL data plugin needs to be modified so that it doesn't use joins in its queries. I haven't had time to try to do so recently.

Thanks,
Daniel
Posted Tuesday April 15 2014
Daniel,

   Just to clarify, the database from the exception above is fairly small. My question regarding the testing is if you’ve tested large simulation data sets (1000 symbols, 6 years, 1 minute)?

Thanks in advance for looking into as soon as possible.
   
Duane
Posted Saturday May 24 2014
Daniel,

Any luck in modifying the SQL data plugin so that it does not use joins in queries? I'm still running into this problem.

Please let me know as soon as possible.

Thanks, Duane
Posted Wednesday May 28 2014
Hi Duane,

No progress on this yet but it's near the top of my to-do list.

Thanks,
Daniel
Posted Tuesday June 10 2014
Daniel,

Has this reached the summit of your to-do list? I'm dead in the water w.r.t. testing.

Thanks, Duane
Posted Friday June 13 2014
I had a look at this and it looks like a pretty simple code change will get rid of the join. I'm still not entirely recovered from a hard disk crash I had so I didn't have SQL Server set up to test this yet.

Attached is the updated code and a compiled version of the plugin. The compiled plugin might need Build 51, but you should be able to compile the updated code for Build 50 if you need (the data storage source code is included in the RightEdge samples).

Let me know how this works.

Thanks,
Daniel

Attachments
DataStorage.zip (164 views, 95.00 KB)
LinqToSQLStorage.cs (240 views, 25.00 KB)


Similar Topics


Reading This Topic


2005-2017 © RightEdge Systems