Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    2

    Unanswered: Query optimization for huge tables

    Hi All,

    I need help on optimizing query runtime.
    I am using huge tables around 90 lacs,5 lacs, etc records table.
    I have tried all indexes , etc. normal stuff to optimize.
    Please guide me on this..


    The tables which i am using has so many fields, but the join on which fields i m doing, and thats the only which i can apply join.
    common fields are as below:

    Table 1 - Company No, Item No (43,000 records)
    Table 2 - Company no., Facility no, Item No, Order No,Work Center No (90 Lacs Records)
    Table 3- Company No, Facility No, Order No (2 Lacs Records)
    Table 4- Company No,Facility No, Work Center No(1500 records)

    I've Applied all proper joins on all tables.
    Can u plz help me on this?

    I am just preparing Query in Crystal SQL Designer.

    and as a database I am using DB2.

    awaiting for your reply.



    Regards,
    Dhaval J

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what indexes do you have on those tables? What inputs are you giving in the query?
    Dave

  3. #3
    Join Date
    Nov 2010
    Posts
    2
    HI Dave,

    I m using indexing on transaction date of Table 2(having 90 lacs records) as i m passing date range in report parameters.
    as well as i have used indexes on other table on fields on which i have applied join in where condition in query.

    Hope this will help u.


    Thanks,
    Dhaval J

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Sounds like you have what is needed. One thing we have used in the past was a UDF that turned a date range into a table of all the dates in the range. This allowed us to match on columns past the date on the index of our starting table.

    For instance, the index had columns of date, type, status. With the following SQL we got matching 1 col on the index:

    Code:
    WHERE dt_col between date1 and date2
      AND type = 'A'
      AND status = 'C'
    Whereas, something like the following SQL would get us matching all 3 columns on the index:
    Code:
    WHERE dt_col IN (select date from (udf(date1,date2)))
      AND type = 'A'
      AND status = 'C'
    Dave Nance

Posting Permissions

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