Hi all, Newbie Firebird user, first post to the Firebird .NET Provider list.
Environment: Firebird 2.5 EMBEDDED, Firebird .NET Provider 2.6.0, Windows 7, .NET 4.0. SHORT VERSION: Does FB fetch all rows at the time cmd.ExecuteReader() is called? If so, is there some way to force all rows to be fetched on cmd.ExecuteReader()? If not, what is the fastest way of reading data from the data reader returned by cmd.ExecuteReader()? LONG VERSION: We are trying to switch from SQL Server Compact Edition 3.5 SP2 to Firebird to get around SSCE's 4 Gb database size limit. The code that I'm executing against the two databases is the very same code -- all that I change is the DbProviderFactory. So far, I'm happy with insert performance of FB compared to SSCE. FB seems to be consistently faster than SSCE. However, I'm seeing a significant performance difference between FB & SSCE when getting values from the IDataReader, via cmd.ExecuteReader(). The FB data reader is consistently 3 to 6 times slower than the data reader from SSCE. However, another interesting thing to note is that FB consistently executes queries faster than SSCE. Since the FB query execution is faster than SSCE, but actually pulling the data out of the reader is faster using SSCE than FB, I'm wondering if FB is actually retrieving all of the data at the time of the query. It appears as though some kind of lazy reading is going on. Can anyone confirm this? My code is your basic stuff: get a connection from the DbProviderFactory, start a transaction, create a command from the connection, set the command's transaction to the connection's transaction, call ExecuteReader() on the command, then read values from the data reader in a "while(reader.Read())" loop, then commit the transaction if there are no exceptions, otherwise rollback. All resources are closed, since they're used in .NET's "using(IDisposable)" blocks. If my hunch is correct, is there any way to force all rows to be fetched at the time of the query execution (that is, upon cmd.ExecuteReader())? Is there some default behavior I don't know about & can tweak? If my hunch is wrong, what is the fastest way of reading data from the data reader returned by cmd.ExecuteReader()? Note that I'm already using the data reader's int indexer (reader[int]), not the string indexer (reader[string]) to get the values. I have tried setting the FetchSize on both the FbCommand object and in the connection string to no avail. Time is running short as we're nearing release, so I could REALLY use the community's help. Thanks in advance, Matthew -- mailto:matt...@matthewadams.me skype:matthewadams12 yahoo:matthewadams aol:matthewadams12 google-talk:matthewadam...@gmail.com msn:matt...@matthewadams.me http://matthewadams.me http://www.linkedin.com/in/matthewadams ------------------------------------------------------------------------------ The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE: Pinpoint memory and threading errors before they happen. Find and fix more than 250 security defects in the development cycle. Locate bottlenecks in serial and parallel code that limit performance. http://p.sf.net/sfu/intel-dev2devfeb _______________________________________________ Firebird-net-provider mailing list Firebird-net-provider@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
