Join Progress

I've worked on the Joining with attributes a little bit but I've run into a problem. Maybe it's not really a problem but I just think it is.

I can easily get the Join string from the attribute, and the new .WhereJoin uses it. The method gets all the tags for the user as expected.

So whats the problem?

The problem is that I have to specify the column instead of a property. I don't like that because it means I have to know the column name. On the other hand, I know all the column names because I set them myself, so it's not terrible

It still bothers me though.

Join is working

I have the join working but it's not pretty.

_tags = DBObject.WhereJoin("user_id", "=", this.ID, typeof(DBTag), "tag_user", "tag_id", "id", DBUser._conn);

Right now, in the Where you have to specify all the information about the Join, the table, the columns, etc. and it doesn't use a property but the column name (because there is no object for the join table.)

I think I'm going to try and move some of this information into the attribute. I also noticed that I need something like a Helper object, or something to help me get information about the object and prevent from calling the same methods over and over again every time I need the same bit of information.

For the Join, I need to know:

• Join Table name
• Join column on the join table.
• Join column on the normal table
• Where Column on the join table
• Where value

Do I want a static Search column for the join? Or do I want to be able specify that in the method?
In my example:
SELECT * FROM tag JOIN tag_user ON tag_user.tag_id = tag.id WHERE tag_user.user_id = 2;

This gets all the tags for the user, so this will always be the case. The easiest would be to assume this is the default until I have evidence to the contrary.

Back to bussness

Ok, so My top pirority for this right now is to get a list of object via a join table.

I have the Tag Object, which has 'id' and 'name'.

There is a join table tag_user to Join Users to Tags.

I need to pull a list of tag via tag_user.

LINQ

I've been avoiding LINQ for several reasons. One is that I'm afraid that it would mean all my code and effort went wasted. (Not a good reason I know.) Another was that it doesn't really work with MySql yet. I know there are ways to make it work, but it's not production ready or easy to use.

I read an article about LINQ which was very informative. The bottom line seemd to be that LINQ was great for small databases that didn't change offten. But once you started having large databases or complex data structures it becomes a hassle.

Another article seems to share some of my conserns with LINQ.

But these articles are old. Maybe I'm just looking for something to be wrong with LINQ. My frist reason for not using it so far can be a pretty powerfull modivator. Maybe I need to just give it a go.

idea

An idea has struck me. What if the DBObject could build its self from the database? You specify the table and it would take care of all the properties and accessors. The one problem with this would be that you lose type safety and intellisence features. The programmer would have to know what columns exist in each table without intellisence there to remind them. There would still be the join problems, but I'm already trying to work those out.

This kindof design would be more data driven and easer for other developers to use.

How do I tell the the object what type each column is? I don't. The developer would have to know this on their own. In a static typed language like c#, this could be very frighting.

[Posted with iBlogger from my iPod touch]

On the right path.

So I made an Index version of the DBObjects to test this new method. To say the least, it's been a very inlighting. 

For my test each object had to get 1,248 User records 100 times (so I could get an average). As far as just retraving the inital list, the index method worked considerablly faster. This didn't come as a surprise because it's just geting all the IDs for all of the user records in the system.

Normal method Retreave List: 3.8 seconds
Index method Retreave List:    0.43 seconds

As I said this isn't surprising because the index method only fetches 1 out of 5 columns. Now on to actually accessing a property for every item in the list

Normal method Access: 0.0 secionds (around 6,700 ticks)
Index method Access:    3:50.50 minutes (around 850,000,000 ticks)!

The index method lost horribly! It just takes too long to query the database 1,248 times. So far this is pretty much what I expected. Heres the total averages.

Normal Retreave & Access: 2.13 secionds (21,380,733 ticks)
Index Retreave & Access: 1:53.21 minuetes (around 1,132,100,354 ticks)

So far it looks like I've been on the right track. In my experiment I did create something neat. For the index I created the abliblity to get a list of objects from the DBList object, with a call like DBList users = new DBList(typeof(DBUser));

One thing I might want to try, is instead of making 1,248 calls to the database, to get batches at a time. So if I want user.id = 1, it'll actually load the first 100 or so items. That would reduce our class to 13. But still I just don't see how 13 calls is going to be any faster than our single call to get all 1,248 items.

Speed Questions

I've been messing around with Objective-C and developing in OSX. So I was reading a tutoral using SQLite3 and the tutoral did something I'd never seen or thought of with the database object. He had the list object populate only the ids (index/primary key) of the objects. Then when you tried to access any of the properties on the object, it would use that id to fetch the rest of the object.

I was intreged, this is better? I had assumed that I would want to get as much data as possible in the fewest calls as possible. But I never actaully tested this theory (or have any idea where it came from.)

So I'm going to test this. I'll perform three tests.

1. I'll try the way I've been developing the objects. Where I try to get as much informaiton in each SQL statement as possible.


2. I'll use the method I read about in the tutoral, where I'll get the ids from everything in the list and return a completed object as I call it.


3. I'll try a varation of the new method. I'll get all the ids, but I'll only return the property requested as it's requested instead of filling out the whole object.
   

The database is MySql 5.1 with InnoDB tables