Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tighger
Starting Member
5 Posts |
 Posted - 2007-07-05 : 12:45:24
|
| I am working with a database that has a table with about 23 columns. About 5 of the columns just have PK_ID's from other tables in them. If someone were to want to look at all the information for a particular row, the 5 columns that have PK_ID's in them would have to be queried or joined to get a value. What do you think might be the most efficient way to handle this. Should I do seperate:Set conn.execute() for each column to get the data or do lots of joins?Any other thoughts. I am thinking it best to fill these columns with the actual data, rather than put just the PK_ID in and have to query multiple tables. Any thoughts. TThank You |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-07-08 : 21:29:14
|
| Like so many things, it depends.In a read-only data warehouse then denormalisation is common and often preferred. For OLTP I'd suck it up & do it properly. You can also create views to produce the same effect as storing it as a big table if the joins scare you.My advice to you based on what you have written would be to store the data properly. You appear to be shying away because you do not want to do joins, not because there is a particular reason not to. Model your data properly & let the database do what it was designed to do. |
 |
|
|
tighger
Starting Member
5 Posts |
Posted - 2007-07-09 : 11:11:44
|
| I have decided that because the other tables are small(like 400 records or less) I am going to just use the foreign key from the main huge database to go and grab out the values in the other tables as separate connections. Seems like it will be faster.Thank You |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-07-09 : 11:32:00
|
quote:
Originally posted by tighger
I have decided that because the other tables are small(like 400 records or less) I am going to just use the foreign key from the main huge database to go and grab out the values in the other tables as separate connections. Seems like it will be faster.Thank You
This will not only be much slower, but much more complicated for you to write as well. Just create a simple Stored procedure that returns what you need, and call it. JOINS are a fundamental operation of a relational database, don't ignore them and try to relate data in your client applications!- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-07-09 : 21:19:00
|
quote:
Originally posted by tighgerSeems like it will be faster.
So you're guessing?I bet it will be slower and get slower & slower as your data increases in size. Do it with joins - simpler & faster. |
|
|
|
tighger
Starting Member
5 Posts |
Posted - 2007-07-10 : 17:22:31
|
| Well not sure which method I will use yet, but I might just use a lot of joins in the sql statement to go and grab the values from the other tables that hold information like the manufacturer, the product type, and so on. Right now we just have a large database of products and then little tables with manufacturers and the fields I discussed above. In this fields we just put the primary key integer value in. Thank You |
|
|
|
|
|
|
Too Many Possible Joins