| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2008-11-19 : 21:27:31
|
| Okay, so i've been around these forums for a while, and you guys are always very helpful with some issues i run into, i was wondering if i could pick users brains on how to re-design my database. my database is currently one table, about 150 fields wide, and 224 million records strong, i would like to try to get it to the point where running queries takes less then one minute so that users can tick what criteria they want via a web site, and the site will execute the query and bring back results.so first off, if i want crazy fast (atleast in my opion) how should i structure the tables do you think? should i break it up into like 5 separate tables with PK, and FK's? i've never really used PK/FK's so please dont slap me :(.then start setting up indexing from there?also, if you say yes, that's what i should do, is there a way i can auto-create a unique field? or should i just use the Identity option?also, fyi, this is a read-only table once it's set up.Sorry for being so long winded, i am humbled by all of your assistance. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-19 : 22:07:27
|
| 1)Understand Normalization and apply it .2)Take advantages of Table partition 3)Make sure every table has PK. |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-11-19 : 22:22:51
|
| the only reason why i havnt tried to normalize the database yet is because then pulling data off of a number of joined tables would take alot of code...i guess if i can split it only into like 5-10 tables (kinda catagorized) then i'll only have to do up to 10 joins if the user requests EVERY option, and what's the changes of that.does it REALLY increase performance though? i've got my current table tuned to the point where it'll return some results in less then 10 seconds, but most take between 2-15 minutes... do you think normalization will cut that back alot?Also, when i imported the data, i added a UID field that incraments and is unique. should i make that my pk do you think? thanks! |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-11-19 : 22:31:18
|
| also, do you think i'm being un-realistic by wanting a single machine to bring back results after querying 224 million rows? the server is 4gb memory, two quad core 2.8ghz xeons (64x), with 8 150gb 10krpm hard drivers.... is under 1min doable if the database is configured right? |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-11-20 : 06:20:56
|
| For read-only, denormalisation can be a good option depending on your queries and your data. Only you will know that though unless you provide a huge amount of info to us. Can you provide some more?One thing I would say though is that the amount of code you have to write should not affect your decision to do things properly if that's what is required.I'm confused by your statement that your DB has only 1 table and yet you have a PK. Unless your query uses the PK it's useless so won't affect the performance or anything. You should consider a natural PK as well though. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-11-20 : 08:43:14
|
| what kind of info would be more helpful? i can post the giant tables structure if you'd like. when i got the database, i added the UID (kinda sorta pk field) because i used to split the table into sub-tables (common queries) and then remove any duplicates because the compiler of the data ships it with multipul records per address. the database is basically a compiled comsumer database.Hope this helps a little Loz, also, what do you guys think? am i being un-reasonable wanting quries back in <1min providing my circumstances? i guess that's really the big question. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-11-20 : 11:43:20
|
| Okay, so my data is already 1N normalized (i think that's the lingo) i'm not totally comfortable doing the 2N one, (where you basically key certain columns) and i'm working on the 3N.i've split my 150ish columns into 6 tables: magmerch,lifestyle,homeinfo, geography, contactmisc, and contactinfo.every one of the six tables has a UID column with a unique identity. so from here should i start inserting data, or should i set up relationships?if i set up relationships, can i get some pointers? never done that before.Thanks! |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-20 : 11:58:31
|
| I'm looking for a new job, you could hire me! :)Since we no nothing about your existing table it is hard to say anything about the 6 tables you have created. It sounds like you are headed in the right direction. But, as Sodeep pointed out understanding normalization is vital to doing database design. It looks like you are moving forward with your design, so a quick suggestion is to define all the entites and how they relate. That might be how to came up with your 6 tables, but if not then you might be able to normalize it better. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-11-20 : 12:11:58
|
| you wouldnt want to work for us, not insurance, low pay, and discustingly long hours (hence the post late at night, and early morning replies) :)so defining the relationships would be my next step then? any good blogs you fine people can refer me to? i've never set up replationships before.Thanks! |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-11-20 : 17:11:25
|
| Okay, so update, i got the tables set up, i figured out how to set the relationship with FK's, how is there any easier way to actually populate the tables w/o having to insert into 6 separate tables 6 separate times?or is that just one of the pleasures of normalization? also, i assume i want to establish my FK's after i insert the data because if i dont i'll run into constraint problems correct? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-11-20 : 17:25:29
|
| You could drop the FKs, load data and then recreate them, which speeds things up. But, I would suggest you load the tables in the proper order to allow referential integrity to do it's thing (which it to prevent you/me/us from loading bad data :)) . |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2008-11-20 : 17:31:11
|
| that's what i was doing, just thought there might be a one-foul-swoop kinda insert i could do... guess it's just wishful thinking :(i'll post back later, appreciate the help so far!!! |
|
|
|
|
Help Setting up a High-Speed Database