Dynamically Choose Database

[Troy Bull]

Greetings

I have several simple applications that work well, except for one thing.
The database seems to be hard coded. Normally what I do is go in to
Settings.settings, then change the value of my connect string to point
to the database I want, then redeploy. I have to change this string
every time I want a different database. Is there a better way to do
this? Maybe programatically somehow? Or something else? Any tips
greatly appreciated..

thanks
troy

 

[Troy Bull]

Greetings

I have several simple applications that work well, except for one thing.
The database seems to be hard coded. Normally what I do is go in to
Settings.settings, then change the value of my connect string to point
to the database I want, then redeploy. I have to change this string
every time I want a different database. Is there a better way to do
this? Maybe programatically somehow? Or something else? Any tips
greatly appreciated..

thanks
troy

One thing I forgot to say, My number 1 preferred solution would be to
have my program just use an ODBC datasource say "mysource", so that
whatever database "mysource" points to is the database that my app would
go against. That way I can setup "mysource" on different client
machines different ways with different default databases and my app
would work without being redeployed for each different client.

 

[Mark Rae]

My number 1 preferred solution would be to have my program just use an
ODBC datasource

ODBC is *extremely* old technology by now.

A native .NET data provider will run rings round ODBC in terms of
performance...

Do you need to support legacy RDBMS for which there is no .NET provider...?

 

[AlexS]

Mark,

any hard data on performance gains/losses?

I am inclined to believe for standard 10/100Mb networks any difference is
negligible for client. Are any measurements available?

Thx
Alex

 

[Troy Bull]

ODBC is *extremely* old technology by now.

A native .NET data provider will run rings round ODBC in terms of
performance...

Thanks for the tip. The database I use is MSSQL 2005 so no need for odbc.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I have several simple applications that work well, except for one thing.
The database seems to be hard coded. Normally what I do is go in to
Settings.settings, then change the value of my connect string to point
to the database I want, then redeploy. I have to change this string
every time I want a different database. Is there a better way to do
this? Maybe programatically somehow? Or something else? Any tips
greatly appreciated..

Changing a config file if you change database should not be a
problem.

In fact I can not imagine any way where you would not need
to change something.

As long as your code does not need to be changed then you are
OK.

Arne

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

any hard data on performance gains/losses?

It would be easy to test.

But besides the performance there are a couple
of other disadvantages with ODBC:
- missing functionality like where parameters are
purely positional in ODBC
- the challenges in trying to guess what an ODBC
error message really means

Arne

 

[Mark Rae]

Mark Rae wrote:

Thanks for the tip. The database I use is MSSQL 2005 so no need for odbc.

Hurrah!

 

[Mark Rae]

any hard data on performance gains/losses?

I am inclined to believe for standard 10/100Mb networks any difference is
negligible for client. Are any measurements available?

Loads - do a Google search.

Native .NET providers get the performance gain because they don't use the
additional layers that OleDb and ODBC have to use:
http://msdn2.microsoft.com/en-us/library/ms998569.aspx

 

[AlexS]

MSDN Link has no data on this kind of performance issues you mentioned.
Recommends to use Odbc objects against ODBC sources and that's it.

Do you have in mind some specific googled source of information, which has
hard data on difference in performance?

I would like to find out what is current industry marker - what is max
slowdown to expect when using ODBC instead of .Net provider? My guess it
should be around 0-1% in average, but this is only a guess

However, I googled also this
http://ourworld.compuserve.com/homepages/Ken_North/ODBCPERF.HTM
which basically confirms that ODBC might be fastest solution when used
properly.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

It would be easy to test.

So I did.



A completely randomly picked test gives:

SQL Client (2.0): INSERT 20000 rows: 9,109375 seconds
SQL Client (2.0): 50 SELECT 20000 rows: 1,75 seconds
OLE DB (2000): INSERT 20000 rows: 20 seconds
OLE DB (2000): 50 SELECT 20000 rows: 62,140625 seconds
ODBC (2000): INSERT 20000 rows: 16,65625 seconds
ODBC (2000): 50 SELECT 20000 rows: 47,8125 seconds
OLE DB (2005): INSERT 20000 rows: 17,28125 seconds
OLE DB (2005): 50 SELECT 20000 rows: 61,484375 seconds
ODBC (2005): INSERT 20000 rows: 13,96875 seconds
ODBC (2005): 50 SELECT 20000 rows: 47,734375 seconds

I can post the code if anyone wants to see it.

Arne

 

[Mark Rae]

SQL Client (2.0): INSERT 20000 rows: 9,109375 seconds
ODBC (2000): INSERT 20000 rows: 16,65625 seconds
ODBC (2005): INSERT 20000 rows: 13,96875 seconds
OLE DB (2000): INSERT 20000 rows: 20 seconds
OLE DB (2005): INSERT 20000 rows: 17,28125 seconds

That seems about right i.e. native .NET data provider is between 1.5 and 2
times faster than OleDb / ODBC for database writes...

SQL Client (2.0): 50 SELECT 20000 rows: 1,75 seconds
ODBC (2000): 50 SELECT 20000 rows: 47,8125 seconds
ODBC (2005): 50 SELECT 20000 rows: 47,734375 seconds
OLE DB (2000): 50 SELECT 20000 rows: 62,140625 seconds
OLE DB (2005): 50 SELECT 20000 rows: 61,484375 seconds

Nuff said...