My application has user definable 'forms' (i.e. tables) which allow user to
add/edit and remove fields.

I'm finding that ElevateDB is very slow when performing a simple :-

ALTER TABLE sometbl DROP COLUMN somecol;

The table I'm working with is not very large (50 records and about 30
fields).

The code which executes the ALTER TABLE... command is as follows :-

     EDBCommand cmd = new EDBCommand(SQL, Connection);
     return cmd.ExecuteNonQuery();

The strange thing is that when I do the exact same thing in the EDB Manager,
it takes a fraction of a second but when I step over the .ExecuteNonQuery()
line in my C# code, it takes 10-15 seconds to drop a column.

The same is true of ADD COLUMN.

Any thoughts?

Hedley,

<< The strange thing is that when I do the exact same thing in the EDB
Manager, it takes a fraction of a second but when I step over the
..ExecuteNonQuery() line in my C# code, it takes 10-15 seconds to drop a
column. >>

Could you send me the database catalog and table files ?  There's no way it
should take 10-15 seconds to drop a column.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

You can use the one I previously uploaded here :-
http://files.pioneersoftware.co.uk/temp/edb_testdb.7z

In ElevateDB Manager, the following 2 lines take <1 second each :-

alter table per add column "mytest" varchar(100);
alter table per drop column "mytest";

I created a test app to try it in .NET and oddly the test app is not as slow
as when run from my app (will have to look into it), but each command still
takes about 4-5 seconds in the simple test app.

Here's the C# code :-

     EDBConnectionStringBuilder sb = new EDBConnectionStringBuilder();
     sb.ConfigPath = "c:\\cov4_data\\_settings";
     sb.Type = "Local";
     sb.LargeFiles = true;
     sb.UID = "Administrator";
     sb.PWD = "EDBDefault";
     sb.Database = "EDB_TestDB";
     sb.ReadOnly = false;

     using (con = new EDBConnection(sb.ToString()))
     {
       con.Open();
       using (EDBCommand cmd = new EDBCommand("alter table per add column
\"mytest\" varchar(100);", con))
       {
         cmd.ExecuteNonQuery();
       }
       using (EDBCommand cmd = new EDBCommand("alter table per drop column
\"mytest\";", con))
       {
         cmd.ExecuteNonQuery();
       }
     }

Hedley,

<< In ElevateDB Manager, the following 2 lines take <1 second each :-

alter table per add column "mytest" varchar(100);
alter table per drop column "mytest";

I created a test app to try it in .NET and oddly the test app is not as slow
as when run from my app (will have to look into it), but each command still
takes about 4-5 seconds in the simple test app. >>

I'm seeing ~3 secs for each alter in a test application with the same code
that you posted (3.2 for the first, and 3.0 for the second).  Considering
that the table has a row size of over 3k (53 columns) and contains 2117
rows, those timings aren't bad.   The more columns that you have in a table,
the longer an alteration will take due to having to deal with each column
mapping.  It takes about ~2 secs to execute the same ALTER TABLE statements
in the native Unicode ElevateDB Manager here.  Also, there's a hard flush on
the database catalog during any ALTER TABLE, so you have to factor that into
the performance also.

I'll do a profile on this when I get a chance, but this is probably about as
good as you're going to get for .NET when compared to native code.  You have
to remember that the garbage collection in .NET can make performance be
particularly non-deterministic.  On one run you may get good performance, on
another not so much.  It really depends upon what else is going on in the
application and how much the application is stressing the memory
allocation/garbage collector.  This may account for the difference in the
simple application vs. the more complex application.

--
Tim Young
Elevate Software
www.elevatesoft.com