I've narrowed some things down at this point.

What I've noticed is that my Firebird select queries actually execute
faster than the same queries in Sql Server Compact Edition, but when I
start iterating the IDataReader (returned by cmd.ExecuteReader()),
SSCE outperforms FB by a substantial margin.

My suspicion is that FB is not fetching all records from the database
at the initial query time. Can anyone confirm that? If I'm correct,
how do I force FB to retrieve all data at the time of query using the
.NET provider?

Thanks,
Matthew

On Tue, Feb 15, 2011 at 12:13 PM, Matthew Adams <matthew@...> wrote:
> Hi all,
>
> Note:  all of my tests are on .NET 4.0 on Windows 7 Ultimate 64-bit
> using Firebird 2.5 embedded with the Firebird .NET Provider 2.6.0.
>
> We're trying to get around Sql Server Compact Edition (SSCE) 3.5 SP2's
> 4 Gb size limitation by replacing SSCE with Firebird 2.5 embedded
> along with the Firebird 2.6.0 .NET provider (FB).  The .NET (C#) code
> that is executing is identical -- the only difference is the .NET
> database provider.
>
> Unfortunately, I'm seeing some significant performance differences
> between SSCE & FB, with SSCE winning:  SSCE at ~10.7 seconds versus
> FB's ~16.7 seconds.  I've created SSCE & FB databases that are as
> identical as I can make them.  Same table structures, same id columns,
> same indexes, everything.  I've also confirmed that FB is picking the
> correct index by running a sample query in IBExpert and observing the
> plan messages at the bottom of the screen, which indicate that FB is
> using the correct index.
>
> One thing that I noticed (only as judged by my eyeballs) is that the
> query comes back very fast in IBExpert (by issuing an execute and
> fetch all), but not as fast via the .NET provider.  I have also
> profiled my .NET app to no avail.  It indicates that my database
> operations are taking the longest time -- no real surprises there.
>
> Is there anyone out there that might point me in the right direction
> to pinpoint the bottleneck?  I'm wondering if it's the database or the
> .NET provider.  Perhaps, since this is essentially a single-user
> embedded database, are there transaction isolation levels that I can
> set that would increase the performance?
>
> Thanks,
> Matthew
>
> --
> mailto:matthew@...
> skype:matthewadams12
> yahoo:matthewadams
> aol:matthewadams12
> google-talk:matthewadams12@...
> msn:matthew@...
> http://matthewadams.me
> http://www.linkedin.com/in/matthewadams
>



--
mailto:matthew@...
skype:matthewadams12
yahoo:matthewadams
aol:matthewadams12
google-talk:matthewadams12@...
msn:matthew@...
http://matthewadams.me
http://www.linkedin.com/in/matthewadams