Large dataset comparison timeouts in DbFit

Question

Query is sum up the top 500 account balances grouped by currencies, used in comparison query from a new SQL 2012 server and existing server to validate no changes in the new 2012 server.

Set timeout property to |set option|command|900| and to no avail.

Below is extract of the error:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at dbfit.DatabaseTest.GetDataTable(Symbols symbols, String query, IDbEnvironment environment, Int32 rsNo) at dbfit.fixture.StoreQuery.DoTable(Parse table) at fitSharp.Fit.Operators.InterpretFlow.DoTable(Tree`1 table, Interpreter activeFixture, Boolean inFlow)

I've attempted to reduce the number of rows to check to 300 from 500 and that seems to work, however a large dataset would be ideal to highlight any discrepancies.

I'm also looking into changing the query to something else to either break it up to a smaller resultset or create an alternative

The query is below, if anyone has a better solution to optimise the query:

       select 
       TOP 1000 fab.DateKey,be.BK_ActOId,be.PtId,be.PDesc,
       be.OId,be.ODesc,be.SubOId,be.SubODesc,
       rc.Currency,SUM(CASE WHEN rc.Currency = '_NA' THEN FAB.Balance ELSE 0 END) as       bal_OrigCcy
       ,SUM(CASE WHEN rc.Currency = 'AUD' THEN FAB.Balance ELSE 0 END) as bal_AUD
       ,SUM(CASE WHEN rc.Currency = 'GBP' THEN FAB.Balance ELSE 0 END) as bal_GBP
       ,SUM(CASE WHEN rc.Currency = 'SGD' THEN FAB.Balance ELSE 0 END) as bal_SGD
       ,SUM(CASE WHEN rc.Currency = 'USD' THEN FAB.Balance ELSE 0 END) as bal_USD 
       from olap.v_FactAccB fab 
       inner join OLAP.v_DimCur dc on dc.CurrencyKey = fab.BalanceCurrencyKey 
       inner join olap.v_DimReportingCur rc on rc.CurrencyKey =          fab.ReportingCurrencyKey 
       inner join OLAP.v_DimBusinessEntity be on be.BusinessEntityKey = fab.BusinessEntityKey 
     and rc.Currency in ('_NA', 'AUD', 'GBP', 'SGD','USD') 
     and fab.DateKey = 20130912 
     and fab.PlatformKey = 1 
     group by fab.DateKey, be.BK_ActOId, be.PId, be.PDesc
    ,be.OId, be.ODesc, be.SubOId, be.SubODesc, rc.Currency 
     order by fab.DateKey, be.BK_ActOId, be.PId, be.PDesc, be.OId, be.ODesc, be.SubOId,    be.SubODesc, rc.Curr

Does anyone have alternatives other than the one above

podiluska podiluska · Accepted Answer · 2013-10-09T11:06:05

I would rewrite your query to use a PIVOT instead of the repeated SUM(CASE... )

eg:

 select * from yourtable
pivot (SUM(balance) for currency in (_NA,AUD,GBP,SGD,USD)) p

Large dataset comparison timeouts in DbFit

1 Answers