I am connecting to an external database via a linked server. I am using a multithreaded console program in C#
to break up this SELECT
statement.
SELECT *
FROM (
SELECT
C.Part_Key,
C.Part_Operation_Key,
C.Net_Weight,
C.Plexus_Customer_No,
C.Serial_No,
ROW_NUMBER() OVER (ORDER BY C.Serial_No) AS row
FROM Part_v_Container AS C
) a
WHERE a.row > 0 AND a.row <= 1000000
It works like a charm and when I get ten threads to grab 100000 records each it cuts the time from eight minutes to a minute. But during testing I made just the one thread to get the whole 1000000 records. The basic code is as follows:
try
{
adapter = new OdbcDataAdapter(sqlStatement, connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
TimeSpan span = (timerStart - DateTime.Now);
Console.WriteLine("Data collected for thread " + threadNumber + " in the time of " + span);
timeTest(span);
Console.WriteLine("Rows collected = " + dataTable.Rows.Count);
retries = 0;
dataCollected = true;
}
Again it works so it's not really the problem. What is the problem is I keep getting a timeout error from plex when I try to get the whole 1000000 records:
There was a problem pulling data from the database for thread number 0 Error Msg was as follows: System.Data.Odbc.OdbcException (0x80131937): ERROR [HYT00] [Plex][ODBC ODBC Report Data Source driver][OpenAccess SDK SQL Engine]Query Timeout expired[10246] at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.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(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at ShippedContainerSettlement.DataCollectionThread.dataCollectionThread() in c:\Users\dkb\Source\Workspaces\Workspace\Danrex\ShippedContainerSettlement\ShippedContainerSettlement\DataCollectionThread.cs:line 39 and sqlQuery was: SELECT * FROM (SELECT C.Part_Key, C.Part_Operation_Key, C.Net_Weight, C.Plexus_Customer_No, C.Serial_No, ROW_NUMBER() OVER (ORDER BY C.Serial_No) AS row FROM Part_v_Container AS C) a WHERE a.row > 0 and a.row <=1005066
If I run this same query in SQL management studio using openquery though it can collect the entire 1000000 records.
I have no clue why because I'm not very knowledgeable on databases so I was hoping that someone can explain why this might be the case?
EDIT: This may be the answer that I found on this forum.
Openquery is performed on the server and it could be it's processing the rows much faster and is only returning one row over the network. Doing this in visual studio may be getting the complete resultset and then filtering it on the receiving (client) server. This might be why it times out with visual studio but not SSMS.
Seem reasonable?
I just tested an openquery in c# and get a timeout even for tables with 3000 records. So really not sure what is happening.
User contributions licensed under CC BY-SA 3.0