Solved

Join vs. Where for speed  - help me explain why I do it!

Posted on 2013-06-03
6
202 Views
Last Modified: 2013-06-04
I'm in a bit of a bind.  I've been designing databases for quite a while and I've always tended to use joins to restrict data rather than having a bunch of indexes in the same table.

Currently, one of my clients hired their own "consultant", who's been nagging me about every design decision and it's driving me crazy trying to explain "why" I do things.

Here is the most recent example:

Our inventory system does a lot of crazy analysis on stock, but some products are just unimportant.  For example, You don't need to do a lot of analysis on office supplies when you're tracking the inventory of o a factory that makes cameras, even if they're in the same table.

I have a table where I have product_features, which simply has a product_id and a feature code.  When I save the product, I insert a record in the product features table that says, product_id, "trackstock".  Then, when I want to pull the inventory for an analysis report, I start with a view like this:  

select p.* from products p inner join product_features on product_id = 123 and feature_code='trackstock'


however, the obvious alternative would be to add a bit column in the products table for track_stock, so you could just do this:

select p.* from products p where track_stock=1

In regards to load, this table is going to have 5 million + records in it.  I'm mostly concerned that we're going to end up with a table that's got 100 columns

In this case, I'd say the guy has a point, and I told him to go ahead and make yet another new column, and another new index on that table, but honestly, am I crazy?  Is my constant use of joins to restrict data just a bizarre habit, or is there some justification to it??

Another example of my use that he disagrees with is for security.  I have procedures where you send a request for orders, but you only want to get the orders from the offices where that user has access, so instead of doing:

select * from orders where office_id in (select office_id from office_security where user_id=123)  - and a bunch of other ifs if the guy is an admin...

I actually use a table function so I can do this:

select o.* from orders o inner join security(@user_id) sec on sec.office_id = o.office_id

I dunno.  Maybe I'm crazy.  Maybe I'm sticking to my guns so hard because I'm not used to someone inexperienced, yet tells everyone he's a genius, questioning my every decision.

anyone with recommendations for my sanity?
0
Comment
Question by:Danielcmorris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 39216903
Hi Daniel,

Based on your descriptions, I'm on your side on both of these things.

Your "product_features" table is a form of normalization that simply removes this item from the table.  It allows you to easily define multiple features for any product.

The security test is absolutely the right way to go.  The host (database) controls the query and the results.  This has the least chance of SQL injection or hacking to obtain unauthorized information.


Good Luck,
Kent
0
 
LVL 4

Author Closing Comment

by:Danielcmorris
ID: 39217592
thanks Kent.  

I wish you could have been in the room when I got politically outmaneuvered by this smarmy consultant.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39217780
Yeah.  Me, too.  I don't get outmaneuvered often.  Especially by puppies.

I'll be glad to offer a more official 2 cents if it helps.


Kent
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 4

Author Comment

by:Danielcmorris
ID: 39219676
I shouldn't get so pissy.  I actually own the code, and they've put over 250k in modifications that are resellable.  

Still, I just don't know how someone gets to be CEO and be so gullible.  

Maybe we should get a few decent developers together and write a For Dummies book titled: "How to spot a corporate consultant who's full of crap"
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39219880
You don't have to know a thing about technology to be a CEO.  But you DO have to know how to run a business.  One organization that I work with actually has a CIO that is a technology neophyte.

>> Maybe we should get a few decent developers together and write a For Dummies book titled: "How to spot a corporate consultant who's full of crap"

Great idea, but:

- Decent developers are hard to find.  The newbies in the industry have a good idea of which box to check or button to push, but they're clueless about what is actually going on.
- Decent developers (the old timers) don't even document.  They certainly won't write a book.  ;)
0
 
LVL 4

Author Comment

by:Danielcmorris
ID: 39219888
awesome.  That's the first smile I've had on my face all day.  Thanks Kent.
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question