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

    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
    name :

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

    Department Table

    #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
    from employee emp, department dept
    emp.dept_id = dept.dept_id
    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

    from employee emp
    where = '100';

    :: Returns one row as is unique.

    Query #2

    select dept.dept_id,
    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
    Toronto, Canada
    have you ever heard of joins?

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

  3. #3
    Join Date
    Jan 2009
    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
    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