All that I really really care about in the current project is that the user be able to import an Excel File full of statistics ultra fast into the database.
Note in all cases selects were WICKED fast. But inserts .... well.
So I tried loading 27397 rows (a random excel file I had lying around) into the database .. one insert at the time.
Here are the results:
InnoDB:
Starting Insert:2/28/2010 4:58:50 PM
Insert Completed:2/28/2010 5:02:36 PM
Insert Duration:00:03:46.9059177
MyIsam:
Starting Insert:2/28/2010 4:53:07 PM
Insert Completed:2/28/2010 4:55:13 PM
Insert Duration:00:02:05.8441979
This confirms to official notes
Faster Selects: InnoDB
Faster Inserts: MyIsam
A basic simple reason is that for v5.1 of MySQL MyIsam does a table lock for writing :)
So MyIsam it is .... but I STILL need something faster..... 2 minutes to import 200 rows of an excel file (with 141+ columns ... data is to be imported with column names as row entries :) ) is simply UNACCEBTABLE! .
I i need further optimizations ... perhaps a DB redesign!
But the reason for the current strategy is that the user can use any excel file and if certain columns are present the values in other columns are stored against them. Maybe there is a better way.
Anyways, here are some optimizations I tried:
Lower load collation (moved to ascii) : No Effect
Lesser / More Indecies : No drastic effect
PackKeys to zero : No drastic effect
Row Format :Fixed .... slight improvement
Delay Key Write : checked gives slight better performance ... but I kept it disabled.
Finally a breakthrough (well not really) :
Starting Insert:2/28/2010 6:54:19 PM
Insert Completed:2/28/2010 6:55:26 PM
Insert Duration:00:01:06.8178218
A fundamental observation. My code for insertItems was further calling in a loop code that insert/updated the item and called save changes. I modified it to call save changes only once. Also since I need to check if entry already exists I open the connection before the loop and close it afterwards. Thus:
Starting Insert:2/28/2010 7:08:25 PM
Insert Completed:2/28/2010 7:09:11 PM
Insert Duration:00:00:45.4445993
Next Move columns with strings to new column linking back as a key:
Starting Insert:2/28/2010 8:33:51 PM
Insert Completed:2/28/2010 8:34:25 PM
Insert Duration:00:00:34.2109567
further things can be tried:
Bulk Import
Dynamic Table Based on columns
In All cases no ORM for this table :)
Either ADO.NET it is ... or I put up with this!
Update: I redesigned to recreate a table as per the excel file. 7000 row , 141 columns ..... 10 seconds ... enough said :)
Sunday, February 28, 2010
Setting up a portable MySQL with VS 2008
Okay so I tried out firebird. It was good but SQL Lite was just plain TOO well integraded so didn't make me want to move (e.g. integrated table designer). Also, the ADO.NET entity framework provider for firebird made only NOT NULL entries (i.e entries that were not primary and marked as NOT NULL) come out as Primary (DDEX version 2.0.4). This was going to be too much hassle everytime I update model from database.
Now trying out MySQL. Results later.
As mentioned before I basically want a HIGH performance yet portable database.
PORTABLE MYSQL
For a portable MySQL i downloaded the noinstall zip file from mysql downloads for windows and just extracted it to a folder.
PS: I only downloaded the 32 bit version and ran it on 64 bit. It runs fine. I did this because of easier distribution and lesser user headache.
VS INTEGRATION
Just downloaded the zip mysql-connector-net-6.2.2.zip from my sql site and installed the MSI therein. Awesome!
Managing MySQL
As a service
Just install MySQL as a service by running
mysqld --install
And start the service using: net start mysql
Stop the service using:
net stop mysql
and Uninstall the service:
mysqld --remove
As a standalone server
Tracking down failure in a service can be tricky. You can start the standalone version using :
mysqld --standalone
If it is running this way you can shut it down using the following:
msyqladmin -u root shutdown
I went with the standalone server since it need to run it embedded :)
Setting up VS 2008 Connection Dialog
Now trying out MySQL. Results later.
As mentioned before I basically want a HIGH performance yet portable database.
PORTABLE MYSQL
For a portable MySQL i downloaded the noinstall zip file from mysql downloads for windows and just extracted it to a folder.
PS: I only downloaded the 32 bit version and ran it on 64 bit. It runs fine. I did this because of easier distribution and lesser user headache.
VS INTEGRATION
Just downloaded the zip mysql-connector-net-6.2.2.zip from my sql site and installed the MSI therein. Awesome!
Managing MySQL
As a service
Just install MySQL as a service by running
mysqld --install
And start the service using: net start mysql
Stop the service using:
net stop mysql
and Uninstall the service:
mysqld --remove
As a standalone server
Tracking down failure in a service can be tricky. You can start the standalone version using :
mysqld --standalone
If it is running this way you can shut it down using the following:
msyqladmin -u root shutdown
I went with the standalone server since it need to run it embedded :)
Setting up VS 2008 Connection Dialog
- Just select MySQL Database as you datasource.
- server:localhost
- username:root
Saturday, February 27, 2010
Humour in Firebird 2.1 Quickstart
From the docs that came with firebird :
Important notice for 64-bit Windows users
On 64-bit Windows systems, the “Program Files” directory is reserved for 64-bit programs. If you try to install a 32-bit application into that folder, it will be auto-redirected to a directory which – in English versions – is called “Program Files (x86)”. In other language versions the name may be different.
In the same vein, the System32 directory is reserved for 64-bit libraries. 32-bit libraries go into SysWOW64.
That's right: 64-bit libraries are in System32, 32-bit libraries in SysWOW64.
If you're not aware of this, you may have a hard time locating your 32-bit Firebird components on a 64-bit Windows system.
(Incidentally, WOW stands for Windows on Windows. Now you can also work out what LOL means.)
Important notice for 64-bit Windows users
On 64-bit Windows systems, the “Program Files” directory is reserved for 64-bit programs. If you try to install a 32-bit application into that folder, it will be auto-redirected to a directory which – in English versions – is called “Program Files (x86)”. In other language versions the name may be different.
In the same vein, the System32 directory is reserved for 64-bit libraries. 32-bit libraries go into SysWOW64.
That's right: 64-bit libraries are in System32, 32-bit libraries in SysWOW64.
If you're not aware of this, you may have a hard time locating your 32-bit Firebird components on a 64-bit Windows system.
(Incidentally, WOW stands for Windows on Windows. Now you can also work out what LOL means.)
Setting up firebird development with VS 2008 / VS 2010 with ADO.NET Entity Framework designer support
Here is how to integrate firebird in VS 2008. Tested now with VS 2010 as well.
From
http://www.firebirdsql.org/index.php?op=files&id=netprovider
Download the following:
PS: For visual studio 2010 I also needed to add the same to the machine.config in the following folder: "C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config" Since it runs on .NET 4.0 runtime. Otherwise you will get an error as mentioned in the errors section below.
Now you can play with Firebird using VS 2008 / VS 2010 to your heart's content!
Just go to visual studio server explorer window and add a new connection to Firebird.
Errors:
Here are some additional firebird notes:
Default Username / Password
Given in the quickstart in the docs folder. Mine was "SYSDBA" with password "masterkey"
Define Aliases / Databases
modified aliases.conf in your installation directory to add the following line :
employee = F:\FIREBIRD\examples\empbuild\EMPLOYEE.FDB
not required but useful
VS Connection Dialog
Datasource = 127.0.0.1
Port = 3050
Database = employee
User=SYSDBA
Password=masterkey
And I changed nothing more. Test connection and it works.
From
http://www.firebirdsql.org/index.php?op=files&id=netprovider
Download the following:
- Zip DDEX
- Zip FirebirdClient
- Extract zip file 2 (FirebirdClient) and copy paste the file "FirebirdSql.Data.FirebirdClient" to GAC (c:\windows\assembly)
- Right click the item in "FirebirdSql.Data.FirebirdClient" in c:\windows\assembly and copy the version, culture and public key token. For my version of the dll 2.5.2.0 , culture = neutral, Public key token "3750abcc3150b00c"
- Modify the machine.config (only edit the 32 bit file since vs.net is 32 bit) file at "C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG" adding the lines as given in readme.txt from zip file 1 (DDEX) . However I only added the DbProviderFactories and not the configSections. The following is what I added:
PS: For visual studio 2010 I also needed to add the same to the machine.config in the following folder: "C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config" Since it runs on .NET 4.0 runtime. Otherwise you will get an error as mentioned in the errors section below.
- Take the two DLL files in the zip file 1 (DDEX) and place them at : "C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE" Modify this as per you installation of VS. (for VS 2010 : C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE )
- In zip file 1 (DDEX) edit the reg file for you system (32 bit or 64 bit) setting %path% to the location of the dll file in the previous step. e.g. in our case : "CodeBase"="C:\\Program Files (x86)\\Microsoft Visual Studio 9.0\\Common7\\IDE\\FirebirdSql.VisualStudio.DataTools.dll" (again as per your VS version). Merge the reg file. (My windows was 64 Bit and I only added the VS 2010 FirebirdDDEXProvider64.reg file)
Now you can play with Firebird using VS 2008 / VS 2010 to your heart's content!
Just go to visual studio server explorer window and add a new connection to Firebird.
Errors:
- When you add a new connection from Visual Studio's server explorer, a customized Firebird Data tools dialog should open. If all you see is a Connection String option double check your Reg file since visual studio did not get the DataTools dlls. You will also probably get the following error :
- Error: "Failure has occurred while loading a type" it means that the .reg file contained some error. In my case the CodeBase path was incorrect.
- If you type anything in any field in the new connection dialog, the dialog box closes. If you click OK you get the following error: "Unable to find the requested .NET Framework Data Provider. It may not be installed". This occurs when you have invalid / missing configuration in machine.config. Remember that VS 2010 needs you to update machine.config in V4 folder and VS 2008 needs you to update in V2 of the runtime. (mentioned above)
Here are some additional firebird notes:
Default Username / Password
Given in the quickstart in the docs folder. Mine was "SYSDBA" with password "masterkey"
Define Aliases / Databases
modified aliases.conf in your installation directory to add the following line :
employee = F:\FIREBIRD\examples\empbuild\EMPLOYEE.FDB
not required but useful
VS Connection Dialog
Datasource = 127.0.0.1
Port = 3050
Database = employee
User=SYSDBA
Password=masterkey
And I changed nothing more. Test connection and it works.
Reviewing .NET ADO .net entity Framework free databases
The top 7 free databases are generally :
http://blog.taragana.com/index.php/archive/top-7-free-open-source-database-server/
I am personally biased towards Firebird right now. This is for the same project for which I was considering SQLLITE :
http://basaratali.blogspot.com/2010/02/sql-server-compact-vs-sqllite.html
Turns out even SQLLite does not measure up to the extreme level of performance I demand of my application. So I need some easy to distribute yet high performance SQL for Windows Database.
MySQL
Normaly I would turn to mysql but since its oracle acquisition I don't trust it. (not that I hate oracle ... it's just a conflict of interest in my opinion). Check out the following to see what I mean:
http://lwn.net/Articles/329626/
Note that the original creator of MySQL forked into MariaDB so that should be something to look out for ... but for now its just too much hassel for being RAD :). Not to mention that its not free (GPL) for commercial use.
Firebird
Firebird is supposed to have good design time support for .NET:
http://www.firebirdsql.org/index.php?op=files&id=netprovider
However its .NET portal seems a bit dated (feel free to check me ... i hope to be wrong here) :
http://www.firebirdsql.org/dotnetfirebird/blog/2005/01/firebird-can-be-easily-embedded.html
And I couldn't find the embedded version it speaks of.
Anyways I will try out the server version and try to get it to work portably. Will update on it later :)
http://blog.taragana.com/index.php/archive/top-7-free-open-source-database-server/
I am personally biased towards Firebird right now. This is for the same project for which I was considering SQLLITE :
http://basaratali.blogspot.com/2010/02/sql-server-compact-vs-sqllite.html
Turns out even SQLLite does not measure up to the extreme level of performance I demand of my application. So I need some easy to distribute yet high performance SQL for Windows Database.
MySQL
Normaly I would turn to mysql but since its oracle acquisition I don't trust it. (not that I hate oracle ... it's just a conflict of interest in my opinion). Check out the following to see what I mean:
http://lwn.net/Articles/329626/
Note that the original creator of MySQL forked into MariaDB so that should be something to look out for ... but for now its just too much hassel for being RAD :). Not to mention that its not free (GPL) for commercial use.
Firebird
Firebird is supposed to have good design time support for .NET:
http://www.firebirdsql.org/index.php?op=files&id=netprovider
However its .NET portal seems a bit dated (feel free to check me ... i hope to be wrong here) :
http://www.firebirdsql.org/dotnetfirebird/blog/2005/01/firebird-can-be-easily-embedded.html
And I couldn't find the embedded version it speaks of.
Anyways I will try out the server version and try to get it to work portably. Will update on it later :)
Monday, February 22, 2010
New Laptop:Good Graphics + Good Processor+Good Price = Good Luck
Well .... I am thinking of getting a new highend laptop but not finding the right one.
Anyways for graphic cards classification I highly trust:
http://www.notebookcheck.net/Comparison-of-Graphic-Cards.130.0.html
Anyways for graphic cards classification I highly trust:
http://www.notebookcheck.net/Comparison-of-Graphic-Cards.130.0.html
Thursday, February 18, 2010
Compiling C code using Visual Studio .Net
This applies to older version of Visual Studio .Net (before 2008) as well but I haven't tested.
I recently got some code in C that needed to be setup in a visual studio project. Its not hard but I thought I'd still share the information:
Enjoy!
I recently got some code in C that needed to be setup in a visual studio project. Its not hard but I thought I'd still share the information:
- Start with a new project.
- Select Visual C++ "Win32 Console Application".
- Check the "Empy Project" in Application Settings along with leaving the default radio "Console Application" checked
- Add your files to the newly created project
- Select "Project Properties -> c/c++ -> Advanced" and set the option "Compile As" to "Compile as C Code" (/TC)
- Click OK
Enjoy!
Thursday, February 11, 2010
SQL Server Compact vs. SQLLite
Been working on SQL Server compact 3.5 sp1 for quite some time. Here are a couple of limitations I came across:
Enter SQLite for the rescue : http://sqlite.phxsoftware.com/ Especially check out their video : http://sqlite.phxsoftware.com/sqlite.wmv
One word : AWESOME . Script tables , modify keys, Reorder columns and a whole lot more! Even if the designer does not work in the next version of VS (for a while it will not ... but I will upgrade VS as soon as it comes out stable!) .. I know (and love) python ... so creating my own little script lib to fix it will not be a problem.
Update : An awesome third party open source tool for modification of DB Files: http://sqlitebrowser.sourceforge.net/ Instead of going thorough the headache of providing a data migration tool with EVERY new iteration of my software I can just point my users to this to selectively migrate the data from the old db file as per their demand :)
Not to mention indices / triggers / 2TB official tested.
Maybe : SQL Listening http://www.sqlitening.com/ (But i will hold on to my dear SQL Server knowledge)
Enjoy!
- 4GB ... well I knew of it to begin with
- Linq To SQL No designer support. You can use sqlmetal to generate the dbml file for you though so not a big issue.
- Poor Designer. Really this is what i found most annoying. You cannot even reorder columns (lets not get into the "Its relational and doesn't matter debate... I like my key's on top... enough said). SDF Viewer exists ... but why buy another software?
- Deployment failed. I do not know why. Even when I put all the dll files from the installation into the client machine it failed to work. Even a simple test application purely for connecting does not work. I have no idea why. So I uninstalled my Sql Server compact 3.5 sp1 and installed it again on the development machine. Now even SSMS will not connect to it. Error : "Could not load file or assembly 'Microsoft.SqlServerCe.Client, PublicKeyToken .... ". After trying to install everything from http://www.microsoft.com/Sqlserver/2005/en/us/compact-downloads.aspx it still will not work. Ah well
- didn't know back then ... right now I think it was probably because of not configuring the DbProviderFactories ... I did it for sqllite. There might be something similar required for SQL Server Compact
Enter SQLite for the rescue : http://sqlite.phxsoftware.com/ Especially check out their video : http://sqlite.phxsoftware.com/sqlite.wmv
One word : AWESOME . Script tables , modify keys, Reorder columns and a whole lot more! Even if the designer does not work in the next version of VS (for a while it will not ... but I will upgrade VS as soon as it comes out stable!) .. I know (and love) python ... so creating my own little script lib to fix it will not be a problem.
Update : An awesome third party open source tool for modification of DB Files: http://sqlitebrowser.sourceforge.net/ Instead of going thorough the headache of providing a data migration tool with EVERY new iteration of my software I can just point my users to this to selectively migrate the data from the old db file as per their demand :)
Not to mention indices / triggers / 2TB official tested.
Maybe : SQL Listening http://www.sqlitening.com/ (But i will hold on to my dear SQL Server knowledge)
Enjoy!
Tuesday, February 9, 2010
I Love .NET 4.0
Okay ... its old news now but I just have to scream it at the top of my voice. For completeness sake here are a couple of my endeavors on .NET:
.NET 1.1 Visual studio 2002 : After being a c++ programmer (Visual studio 6! a classic in my mind) for quite some time and making my final year project in Java ( and not loving netbeans ... still don't ... but still use it ... i still have a solaris / oracle machine that I manage in the office :) Loved Flex though ... end of rambling) I tried out converting an old java P2P messenger to C# ... first surprise ... the rich edit control had NO way of scrolling to the end without interop win32 dll calls! ... not that I don't understand it (I was a c++ programmer first remember) but I thought swing was more mature.
.NET 2 : Visual studio 2003. Working in Ufone I thought i'd give it a try. I was pleasantly surprised. Winforms was now Miles ahead of Swing! And its weird but even the anchoring layout system was getting the job done (and better/faster than swing). I moved all my code to it immediately!
.NET 3.5 : Visual Studio 2008 : I moved mainly because of the threading debugger support in VS (and I thought that javascript intellisense was awesome). I did a lot of back end MSC/HLR network based automation code in Ufone and this was a GOD send!
So : When I heard about WPF I was highly eager to see what it could do. BUT on my home (old) machine my animation experiments always came out choppy. So had to refrain. Not to mention +200 MB offline .NET installer was simply not going to get a lot of my users to install my software. Just telling that its awesome (if you have a graphics card) AND you need to install this ADDITIONAL file was not going to win over anyone.
2 years down. Every other computer has a decent graphics card (thanks vista :) ... it was a bitter pill but it still paved the way ... no business laptops come with xp and need better hardware!). So I thought I'd give .net 3.5 SP1 another try. Now it is much better (but still slower than winforms) but I believe that microsoft achieves anything it sets it mind to ... and having a controlled platform is what microsoft is all about (and I love the effort it puts in ... makes my life easier ... and saves me from dll hell!). Not to mention the child heirarchy system is the BEST thing I can imagine. You can make a templated image button in seconds ... and a Control out of it in Minutes! Not to mention simple polygon mathematics (due to design surface polygon + click event handler + easy scaling + .... I LOVE IT!) are a breeze. Not to mention Linq! BUT .NET 3.5 SP1 installer (size) is a pain for the client!
Enter the reason for this post:
.NET framework 4.0 for X86+X64 == 54.5 MB Awesome ... I can't wait ... and its going to be on time too .. the product I am making is going to take a while ... I will delay its release till I have stable .NET 4.0 ;)
Update: Well took .net 4.0 for a spin. Its Awesome! PS: you might want to check out smallestdotnet.com/ to see the download size that .net will have for you :) ... was more useful before .net 4 (good job MS) but still fun.
Enjoy!
Monday, February 8, 2010
Developer career platform by microsoft
Called "Thrive for Developers". Heres the link:
http://www.microsoft.com/click/thrivedev/default.aspx
Funny I don't remember how I landed on this page but looks interesting :)
http://www.microsoft.com/click/thrivedev/default.aspx
Funny I don't remember how I landed on this page but looks interesting :)
.NET Obfuscators
It has been quite some time since I worked on desktop only applications. Now that I am working on a new project that is desktop only I was looking at .NET Obfuscators out there. Also my application is in WPF.
I used to love smartassembly back when I did winforms. Its simple yet powerful. But it crashed on me (verified by below review as well)
Okay so ... let see what I can find on the internet:
Highly informative Silverlight obfuscators review: http://www.olsonsoft.com/blogs/stefanolson/post/Selecting-an-obfuscator-for-Silverlight.aspx
I am feeling biased about trying out : http://www.foss.kharkov.ua/g1/projects/eazfuscator/dotnet/Default.aspx
Simply because it is Free! and I LOVE FREE. The least people can do is use it and leave the developer a comment. Working on home laptop right now ... Will update if it worked out for me later on. (well it didn't ... but I got my old smart assembly to work as per the silverlight review : i.e. disable class / property renaming etc)
Would recommend EazFuscator ... you can get it to work by using attributes.
I used to love smartassembly back when I did winforms. Its simple yet powerful. But it crashed on me (verified by below review as well)
Okay so ... let see what I can find on the internet:
Highly informative Silverlight obfuscators review: http://www.olsonsoft.com/blogs/stefanolson/post/Selecting-an-obfuscator-for-Silverlight.aspx
I am feeling biased about trying out : http://www.foss.kharkov.ua/g1/projects/eazfuscator/dotnet/Default.aspx
Simply because it is Free! and I LOVE FREE. The least people can do is use it and leave the developer a comment. Working on home laptop right now ... Will update if it worked out for me later on. (well it didn't ... but I got my old smart assembly to work as per the silverlight review : i.e. disable class / property renaming etc)
Would recommend EazFuscator ... you can get it to work by using attributes.
Subscribe to:
Posts (Atom)