Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: Splitting complex SQL select query in to independent sequence of small queries.

    =====================
    Problem Description
    =====================
    Consider I have the following table structures with approx no. of records in each.

    Employee Table
    -------------------
    emp_id:
    name :
    dept_id:

    #Approx no. of rows in the table: 1 million

    Department Table
    ---------------------
    dept_id:
    dept_name:

    #Approx no. of rows in the table: 1 Hundred

    =====================
    Target Result: is to get the employee name who's id is '100' and the department name he belongs to.
    =====================

    =====================
    Ideal Query : Consider it as COMPLEX QUERY
    =====================
    select emp.name,dept.name
    from employee emp, department dept
    where
    emp.dept_id = dept.dept_id
    and
    emp.emp_id = '100';

    =====================
    Optimized Independent Queries : Consider it as SIMPLE QUERIES
    =====================

    Now i want to split the above query in to two independent queries.

    Query #1

    select emp.name
    from employee emp
    where emp.id = '100';

    :: Returns one row as emp.id is unique.

    Query #2

    select dept.dept_id, dept.name
    from department dept

    :: Returns 100 rows as there are only 100 rows.

    =====================
    Need Your Suggestions Here
    =====================

    If you compare the COMPLEX QUERY against SIMPLE QUERIES, i prefer to use SIMPLE QUERIES, get the results
    and compare department id against two results programmatically and then get the final result. I know the database
    query engines will do similar query optimization like dividing COMPLEX QUERY to SIMPLE QUERIES and run them
    in some specific order to get results faster.

    Now is it possible to get the exact sub queries which the database query engine (ANY database query engine) generates
    out of complex select query? I mean i don't want to do the splitting manually but i still need those sub-queries.

    Can any one help me out please?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    have you ever heard of joins?

    the database will almost always perform a single join query much faster than two separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    Yes,I know about joins.
    But for some other purpose i need those split of queries. Is is possible to get those queries in any way?

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    What's complex about your COMPLEX QUERY?

    Assuming the correct indexes then the COMPLEX QUERY should return results almost instantly. Have you actually tried running the queries and timing them?

    Is it school work?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •