Archive for June, 2007

« Older Entries

How big is your GIG-abyte?

Saturday, June 30th, 2007

After my post about MySQL and the the reason for going that route being the size limit of MS SQL Server Express 2005, I started thinking how big is 4GB?

Will it really be a limitation for the application we’re planning to develop? So, I’ve designed a little experiment to determine just how much data can fit into a 4GB MS SQL database. I wanted an easy way to quantify the amount of data, just populating it with a bunch of random values would not serve the purpose. With this in mind, I designed an app that writes date values to a table. You can specify the range using datepickers and you can set the interval for each record i.e. each day, month or year.

The table design looked like this:

I deliberately made it nice large fields.The table size without any data looked like this(using sp_spaceused):

First thing was to get the size of just one record, which looked like this:

Afterwards I add a entry to the table for each day for a thousand years(This scenario could be used to test transaction history sizes). The size after that looked like this :

Not bad…1000 Years of data and we’re only at about 38MB…now, my aim was to get the tablesize to 1GB to give an indication of the amount of data we’re looking at and at this rate things we’re looking good with regards if whether we would be able to use SQL Express.

Now to get 1GB of data in the table, I did a quick computation. If 1000 years = 38MB then 26000 years = 1GB. That’s a whole lot of years. Remember we’re adding one entry for every day for 26000 years. That’s a whole-lotta-transactions!

But OK, I doubt we’re going to use this application for a 1000years, so I wanted to get a more real life scenario. So I’ve added a 1000 entries for every day for 5 years. That should be a pretty good scenario for order movements or transactions over 5 years.
Unbelievably the data is not that large at all:

With this information, we’ve decided to stick with SQL Express. 4GB should be more than enough!

Tags:
Posted in Uncategorized | No Comments »

MySQL and .Net

Tuesday, June 19th, 2007
I’m a bit behind with my "5 things I’ve learned" series, and for that I apologize. I’ve been a bit busy at a client site. Will get back to that a.s.a.p.
What I want to chat to you about today is MySQL and how well it works with .Net(so far, it works well, touch wood)…
First, how did I come about using MySQL instead of MS SQL Server? Initially we’ve planned to use SQL Express, but we’ve quickly realized that with the amount of data, that will be moving through the system will probably be more than what SQL Express can handle(4 GB).
Sooo…I figured the client would have to go the full MS SQL Server direction, but working on a contract and already costing the client, I did not want to burden them with any additional cost if it could be avoided.

Enter MySQL.

I’ve always known about it, but never really had a good look at it, all I knew was that it was free, and for the moment that was all that mattered.
There is 2 what you could call editions of MySQL, one is the Enterprise edition and the other the Community edition, the latter is the one that is free. There are 3 downloads with a combined size of about 80MB. After installing that I read the Migrating From Microsoft SQL Server to MySQL pdf document and was delightfully surprised to see that the SQL syntax between MySQL and MS SQL Server is not all that much different. Nothing you could not get used to within a week or so of actively writing some SQL.
Ok, that done, I wanted to start storing and retrieving my data from the MySQL db by using VB.Net. One way is ODBC, but I did not really want to go the ODBC route, considering any performance and ODBC issues that I could possibly encounter. The other, and the option I used, is to use the MySQL Connector for .Net.
It’s a quick download, and you then add it to your project references. You then have access to the following:
  • MySqlCommand
  • MySqlConnection
  • MySqlDataAdapter
  • MySqlDataReader
  • and more…
I set forth to write a quick little app that use a datareader to pull data from a table, using normal .Net techniques, and it worked brilliantly. So from a .Net point of view I did not need to learn any new objects, Great!
The tools you get to manage your MySQL is not too shabby, the Query Browser(The MySQL equivalent of Query Analyzer), is not the friendliest app but serves it’s purpose. The MySQL Administrator does a good job at the administrave part, and is easy to use.
All and all an enjoyable experience and I would recommend MySQL to any .Net developer that needs a decent datastore for their application.
Until next time, keep coding!

Posted in Uncategorized | No Comments »

Code Karma

Wednesday, June 13th, 2007

I’ve read an article in Time magazine about Bill Gates finally getting his degree after 30 years. Say what you will about Bill, but the man must’ve done something right. One interesting paragraph that struck me as very interesting was the following:

For all his drive and intelligence, Gates doesn’t see things with an artist’s eye for those human intangibles. In May, Gates made a rare and instructive public appearance with his longtime frenemy Steve Jobs. An audience member asked each of them what he had learned from the other. "Well, I’d give a lot to have Steve’s taste," Gates said. "You know, we sat in Mac product reviews where there were questions about software choices, how things would be done, that I viewed as an engineering question. That’s just how my mind works. And I’d see Steve make the decision based on a sense of people and product that is even hard for me to explain. The way he does things is just different, and you know, I think it’s magical." The audience cracked up. But Gates wasn’t joking.

This got me thinking that most of us in the IT industry always take to user problems from an engineering viewpoint and hardly ever from the human/user viewpoint. This more often than not leads to frustrated users. For example, I wrote an ACCPAC customization a few years ago, that the users still use today.
Almost 3 years later I’m back looking at a rare but very frustrating bug with this application. In very specific scenarios, the app loses some of its logic, overwriting values and then refusing to post and order. The result is the user having to recapture the order and to make things worse this almost always happens on big orders.

Even though this is a rare occurrence, when it happens it causes a lot of frustration….Now, from an IT perspective, what will the developer do?

  • There is a work around so no big rush to fix it.
  • Fix it, sort of, just to make the client happy for now.
  • Sit with the user that has to try and get the order posted, feel their pain, even help getting it done…and then after a good understanding of the problem and the frustration it causes, fix it and test it thoroughly.

The point I’m trying to make is, that we as developers, must start to understand the implications of our work. It’s not the data that will be wrong or orders not completed on time. It will be the people having to do these tasks that will suffer.

Think of it as Code Karma, do good coding and good things will come of it.
You will at least not be the reason a poor clerk has to work late yet again.

Posted in Uncategorized | No Comments »

« Older Entries
Google Analytics integration offered by Wordpress Google Analytics Plugin