Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 4 of 4 total |
Slow ALTER TABLE commands in ADO.NET |
Mon, Feb 16 2009 12:04 PM | Permanent Link |
"Hedley Muscroft" | 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? |
Mon, Feb 16 2009 3:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Feb 16 2009 6:00 PM | Permanent Link |
"Hedley Muscroft" | 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(); } } |
Tue, Feb 17 2009 11:57 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Friday, May 3, 2024 at 08:07 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |