Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use correlated sub-queries to check the state of flags in other tables to determine Eligibility, or am I better of creating Views that utilize Joins that represent the valid Records? As a more concrete example, is this:

Select 
  Col1, Col2, Col3
From
  Table1
Where
  (Select RefCol From Table2 Where Table2.PK = Table1.FK) = "Condition"

Superior or Inferior to something like this:

Select
  T1.Col1, T1.Col2, T1.Col3
From
  T1
Inner Join
  T2
    On T1.FK = T2.PK
Where
  T2.RefCol = "Condition"

-- Edited --

As a corollary question: Is it productive to create Views which contain Intermediary validations? IE, if I repeatedly need to check if T1.Col1 = 1 and T1.Col2 = 0 is it worthwhile to create the following view:

Create View T1Validated As
Select
  Col1, Col2
From
  T1
Where
  Col1 = 1
And
  Col2 = 0

And then later use the existence of a record in T1Validated in subsequent checks, or is that likely to produce additional database page retrievals and/or table scans?

share|improve this question
    
I should point out that I'm testing both, but having wildly errant behaviors at times depending on the cross-sections of my data. IE, if Table1 has many rows I sometimes see huge numbers of page reads, but other times (especially after validating indices) it seems fine. Other times, large numbers of rows in Table2 causes spikes in activity or execution time. I'm looking for a best practice to try to minimize the overhead I'm experiencing that's less dependent on row distribution between the tables. –  g.d.d.c Jul 30 '13 at 17:30
1  
Which DBMS are you using? Postgres? Oracle? –  a_horse_with_no_name Jul 30 '13 at 18:13
    
@a_horse_with_no_name - Firebird 2.5. –  g.d.d.c Jul 30 '13 at 18:38
add comment

1 Answer

Since you are selecting fields from Table1 only, it is pointless to get the data from a join applied on table 1 and table 2. After testing both queries on my server, i have noticed that the processing time has reduced using the first query (nested selects). hope this answers your question.

share|improve this answer
    
Do you know if that holds true if there are multiple sub-queries per record that comes out of table1? I have some situations where Records in Table1 have to have a column checked against five or six other tables. I'm half-wondering if I've got a circular dependency somewhere or something else amiss. During monitoring I've seen instances where a procedure will retrieve a million+ pages, which seems wrong to me, considering I have ~ 500 rows in one table, and maybe a couple hundred in another. –  g.d.d.c Jul 30 '13 at 17:51
    
As I told you earlier, it makes no sense to join 2 or more tables in case you wont use the fields of the other tables. but here is another thing, why don't you Include the Actual Execution Plan and the Client Stats, run both queries for more than 1 time and compare the outcome on both queries. My approach is to avoid Joins on selection from only 1 table, but this is only my point of view. –  Hani El Mouallem Jul 31 '13 at 6:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.