database connection - need it to be left open

[jonnytabpni]

Hi folks,

I am currently making a piece of software in c# which uses a very large .dbf (yuk!) database (about 120MB). I have a "Remote" class which is used to do the database operations. I use ODBC to connect to this database. All works, except that due to the size of the database, it takes very long for each lookup. For the use of this software, waiting up to 10 seconds for each item to display is not acceptable.

I'm kinda hoping that the problem lies in the fact that the database has to always be opened and closed so I would like to try and see if I could open the database at program startup and then just run the command.

The problem is, no matter what I try, I always get the error Error "The name 'oCmd' does not exist in the current context" and "The name 'oConn' does not exist in the current context".

This is the remote class which works:

public static class Remote
    {
 
public static string GetData(string data,int c)
        {
            System.Data.Odbc.OdbcConnection oConn = new System.Data.Odbc.OdbcConnection();
            oConn.ConnectionString = @"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=c:\database\;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
            oConn.Open();
            System.Data.Odbc.OdbcCommand oCmd = oConn.CreateCommand(); 
oCmd.CommandText = @"SELECT * FROM c:\database\database.dbf WHERE CODE LIKE '" + data + "'";
            DataTable dt = new DataTable();
            dt.Load(oCmd.ExecuteReader());
            oConn.Close();

//bla-bla--bla--following statements just extract the data from the datatable.

}

This is the remote class which doesn't work:

public static class Remote
    {

        public static void openDatabase()
        {
            System.Data.Odbc.OdbcConnection oConn = new System.Data.Odbc.OdbcConnection();
            oConn.ConnectionString = @"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=c:\database\;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
            oConn.Open();
            System.Data.Odbc.OdbcCommand oCmd = oConn.CreateCommand(); 
        }

        public static string GetData(string data,int c)
        {
            oCmd.CommandText = @"SELECT * FROM c:\database\database.dbf WHERE CODE LIKE '" + data + "'";
            DataTable dt = new DataTable();
            dt.Load(oCmd.ExecuteReader());
            oConn.Close();
//bla-bla--bla--following statements just extract the data from the datatable.

}

The idea was to make the main Form call Remote.openDatabase() as startup. Then, some other class are to make use of Remote.GetData. But the 2 errors I mentioned appear.

Any ideas?

Your help is appreciated thanks

[sknake]

I doubt that holding the connection open will improve performance, it actually can work against you in some cases. However, you didn't paste enough of your Remote class so I could see what the problem is but you can use this logic to hold a connection open and query against it:

using System.Data.SqlClient;
using System.Data;

namespace canvasTesting
{
	public static class Remote
	{
		//fields
		private static SqlConnection _conn;
		//properties
		public static SqlConnection Connection { get { return _conn; } }
		//methods
		public static void ApplicationStartup()
		{
			if (_conn != null)
				_conn.Dispose();
			_conn = new SqlConnection("Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;");
			_conn.Open();
		}
		public static DataTable GetData(string Query)
		{
			DataTable result = new DataTable();
			using (SqlCommand cmd = new SqlCommand(Query, _conn))
			{
				using (SqlDataReader dr = cmd.ExecuteReader())
				{
					result.Load(dr);
				}
			}
			return result;
		}

	}
}

You need to call the initialization method when the application starts, probably in the main form's load.

private void frmMain5_Load(object sender, EventArgs e)
		{
			try
			{
				Remote.ApplicationStartup();
			}
			catch
			{
				MessageBox.Show("Connection failed the server. Application terminating");
				this.Close();
				Application.Exit();
			}
		}

Then to call it:

private void simpleButton2_Click(object sender, EventArgs e)
		{
			DataTable dt = Remote.GetData(@"Select * From SystemReg");
			MessageBox.Show(dt.Rows.Count.ToString());
		}

[jonnytabpni]

Thanks for your help.

As a side (but highly relavant note), I change my orignal code from odbc to ole(which uses the microsoft visual foxpro driver) and there is a huge improvement. Times for each query are down from 7 seconds to about 3 seconds. Still too long but an improvement non the less.

I'm gonna try your way with my ole driver to make it static

Thanks

[jonnytabpni]

Oh, i forgot to mention. You asked for the rest of my Return class. You have a very good point. The database seems to be VERY responsive when no match is found (indicating that search is fast..)

if (dt.Rows.Count > 0) return (dt.Rows[0]["DESC"].ToString());
                else return ("");

The rest of my code knows how to handle an empty return (Displays a message). Cheers

[sknake]

Now you're on the right track. Drivers make all the difference in the world and ODBC is by far the worst, OleDb is not too bad (depending on the DB listening), and native SQL is excellent for MSSQL.

Also -- Just change the SqlCommand to OleDbCommand, SqlConnection to OleConnection, etc. I just didn't have any oledb or odbc connections lying around so i used SQL, but the class will work identically if you change the types.

[jonnytabpni]

Thanks for your help! Using your logic, I managed to make my ole connection always open!

There does seem to be an improvement. Time is down to about 1.5 - 2.0 seconds! We're making progress.

Any other tips on how to speed up access?

Cheers

[sknake]

Speeding up access depends on the drivers, amount of data being returned, the database, indexes on the database, the way your query is written, etc etc.

Make sure you have the fields indexed that you are searching on and returning only the data you need, not select *. That causes a lot more disk IO returning data that is never used.

Beyond that it depends on FoxPro which I am not familiar with so I can't really think of any more tips to give you off hand...

Please mark the thread solved if I answered your question, and good luck!

[jonnytabpni]

Cheers!

How do I make the database indexed? Actually, I do have an "index file" for this database (Im moving all this stuff from an old system). How do I make use of it in my program? Thanks

[jonnytabpni]

Also, slightly dissapointing news is that when I moved this software to a less powerful machine (As typical as it will be used), times are way up again :(

[sknake]

To be honest I have no idea how FoxPro works, I have never used it... you might point that question over to the database forums to see if anyone knows. I use MSSQL almost exclusively.

I will explain how indexing works for MSSQL because it is the same concept for all database for the most part:

Lets say I have a table "Customers" and they all have a unique ID "CustomerId", I would use the query:

Select CustomerId, CustomerName, Address From Customer Where CustomerId = 12345

Now if the CustomerId column is indexed then the database will automatically use that index. However if I search on the customer name:

Select CustomerId, CustomerName, Address From Customer Where CustomerName Like '%Smith%'

It will not be using an index because one is not specified for that column. Looking at your example it looks like you need FTI/Full Text Indexing of the database since you're searching the entire database for a string? Thats more of a foxpro question that I can't answer.

Please mark the thread solved if I answered your question, and good luck!

[sknake]

How is the database set up? From your first post your connection string points to a file on the local harddrive. Are you using the less powerful machine to connect to the other machine across the network, or do they both have the same file locally?

Again ... You're doing very data intensive operations so reduce the Select *, but regardless you're searching on the entire database so it will be slow

[jonnytabpni]

Thanks for your fantastic help. Hopefully i'll get this solved soon :)