sqlite3 & joins

Discussion in 'Ruby' started by Dave Lilley, Jun 8, 2009.

  1. Dave Lilley

    Dave Lilley Guest

    this question goes on from my previous one about my 1st hurdle using
    SQLite3.

    what sort of performance hit will you get if you have 3 select
    statements (1 for each table/DB file)?

    tables are customer, Jobs, parts.

    customer has a unique field called customer number.

    Jobs has a field for the customer number in it and a unique field called
    Job number.

    lastly parts has a field called job number.

    not sure how many rows there might be for a customer and rows for parts
    at present.

    but really I'm more concerned with getting everything playing in tune
    and then when I'm more familiar wit SQLite look at optimizations like
    using joins.
    an yes ultimately i may move off sqlite to another DB but that's another
    step.

    Oh also all this is via DBI.

    Active record will be another stepping stone (Feel like I've got too
    much to cope with for now but perhaps ultimatly go to active record
    too).

    cheers,

    dave.
    --
    Posted via http://www.ruby-forum.com/.
     
    Dave Lilley, Jun 8, 2009
    #1
    1. Advertising

  2. I would always recommend using the database to perform joins. People
    have spent thousands of hours (perhaps incorporating the work of
    millions of other peoples' hours) in designing the database to optimise
    joins, so why would you possibly want to reinvent that wheel?

    From what I've seen of ActiveRecord, it has some way of expressing
    relationships but I'm a bit dubious about the whole object-relation
    mapping model. Database-centric approaches have worked for big complex
    systems for decades so why force the issue.
    --
    Posted via http://www.ruby-forum.com/.
     
    Mike Stephens, Jun 8, 2009
    #2
    1. Advertising

  3. I don't use SQLite, I'm more of a SQl heavy (DB2, Postgresql, Oracle)
    type.

    What you're asking for is a plain old, b-flat, vanilla join. I don't
    know why you'd be doing this in three separate queries.

    Without seeing your schema, or knowing exactly which columns you want
    to pull, I'll use '*', which cats all columns in all tables. I
    assuming you have a part_number in parts (or maybe a part_name.)

    Select *
    from customer, jobs, parts
    where customer.customer_number = jobs.customer_number
    and jobs.job_number = parts.job_number
    order by customer.customer_number,
    jobs.job_number,
    parts.part_number

    This should be quite efficient if there are indexes on
    jobs.customer_number and parts.job_number, otherwise a sequential scan
    will be needed for each of the two joins, and that will kill you.

    > but really I'm more concerned with getting everything playing in tune
    > and then when I'm more familiar wit SQLite look at optimizations like
    > using joins.


    I suggest you do the hard work up front. It will save you a lot of
    bad design decisions down the line.

    Good luck,

    Bob Schaaf


    On Jun 8, 2009, at 7:28 AM, Dave Lilley wrote:

    > this question goes on from my previous one about my 1st hurdle using
    > SQLite3.
    >
    > what sort of performance hit will you get if you have 3 select
    > statements (1 for each table/DB file)?
    >
    > tables are customer, Jobs, parts.
    >
    > customer has a unique field called customer number.
    >
    > Jobs has a field for the customer number in it and a unique field
    > called
    > Job number.
    >
    > lastly parts has a field called job number.
    >
    > not sure how many rows there might be for a customer and rows for
    > parts
    > at present.
    >
    > but really I'm more concerned with getting everything playing in tune
    > and then when I'm more familiar wit SQLite look at optimizations like
    > using joins.
    > an yes ultimately i may move off sqlite to another DB but that's
    > another
    > step.
    >
    > Oh also all this is via DBI.
    >
    > Active record will be another stepping stone (Feel like I've got too
    > much to cope with for now but perhaps ultimatly go to active record
    > too).
    >
    > cheers,
    >
    > dave.
    > --
    > Posted via http://www.ruby-forum.com/.
    >
     
    Robert Schaaf, Jun 8, 2009
    #3
  4. On second thought, it doesn't look like your schema does what it ought
    to.

    You'd think that any customer can contract for a certain job which
    requires specific parts which can be used in other jobs.

    If so, you'd need

    1. a customer table with unique records for each customer, identified
    by customer_number;

    2. a jobs table describing a job, with a unique key for job_number;

    3. a parts table describing a part, with a unique key for part_number;

    THEN you need the join tables.

    4. a contracts table, with a unique key on contract_number, and an
    index on customer_number;

    5. a bill_of_parts table, indexed uniquely on job_number.

    Then the join would be

    customers.customer_number = contracts.cusomer_number
    contracts.job_number = jobs.job_number
    jobs.job_number = bill_of_parts.job_number
    bill_of_parts.part_number = parts.part_number

    This too is trivial, and should be quick if your RDB isn't a complete
    dog. I don't know of SQLite permits multi-column keys and indexes, so
    perhaps, "Woof, woof!"

    BS


    On Jun 8, 2009, at 8:07 AM, Robert Schaaf wrote:

    > I don't use SQLite, I'm more of a SQl heavy (DB2, Postgresql,
    > Oracle) type.
    >
    > What you're asking for is a plain old, b-flat, vanilla join. I don't
    > know why you'd be doing this in three separate queries.
    >
    > Without seeing your schema, or knowing exactly which columns you
    > want to pull, I'll use '*', which cats all columns in all tables. I
    > assuming you have a part_number in parts (or maybe a part_name.)
    >
    > Select *
    > from customer, jobs, parts
    > where customer.customer_number = jobs.customer_number
    > and jobs.job_number = parts.job_number
    > order by customer.customer_number,
    > jobs.job_number,
    > parts.part_number
    >
    > This should be quite efficient if there are indexes on
    > jobs.customer_number and parts.job_number, otherwise a sequential
    > scan will be needed for each of the two joins, and that will kill you.
    >
    >> but really I'm more concerned with getting everything playing in tune
    >> and then when I'm more familiar wit SQLite look at optimizations like
    >> using joins.

    >
    > I suggest you do the hard work up front. It will save you a lot of
    > bad design decisions down the line.
    >
    > Good luck,
    >
    > Bob Schaaf
    >
    >
    > On Jun 8, 2009, at 7:28 AM, Dave Lilley wrote:
    >
    >> this question goes on from my previous one about my 1st hurdle using
    >> SQLite3.
    >>
    >> what sort of performance hit will you get if you have 3 select
    >> statements (1 for each table/DB file)?
    >>
    >> tables are customer, Jobs, parts.
    >>
    >> customer has a unique field called customer number.
    >>
    >> Jobs has a field for the customer number in it and a unique field
    >> called
    >> Job number.
    >>
    >> lastly parts has a field called job number.
    >>
    >> not sure how many rows there might be for a customer and rows for
    >> parts
    >> at present.
    >>
    >> but really I'm more concerned with getting everything playing in tune
    >> and then when I'm more familiar wit SQLite look at optimizations like
    >> using joins.
    >> an yes ultimately i may move off sqlite to another DB but that's
    >> another
    >> step.
    >>
    >> Oh also all this is via DBI.
    >>
    >> Active record will be another stepping stone (Feel like I've got too
    >> much to cope with for now but perhaps ultimatly go to active record
    >> too).
    >>
    >> cheers,
    >>
    >> dave.
    >> --
    >> Posted via http://www.ruby-forum.com/.
    >>

    >
    >
     
    Robert Schaaf, Jun 8, 2009
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. SKG

    faster joins

    SKG, Jun 24, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    409
    Kevin Spencer
    Jun 25, 2004
  2. Darrel

    Combining a SQL query (joins)

    Darrel, Nov 11, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    353
    Scott Allen
    Nov 11, 2004
  3. Matthi
    Replies:
    1
    Views:
    528
    =?Utf-8?B?Z2xlbm4=?=
    Mar 28, 2006
  4. Jeffrey 'jf' Lim
    Replies:
    5
    Views:
    538
    Jeffrey 'jf' Lim
    Apr 9, 2007
  5. SunSw0rd
    Replies:
    4
    Views:
    289
    SunSw0rd
    Jul 2, 2009
Loading...

Share This Page