Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2015
    Posts
    2

    Unanswered: Need Query optimization

    I am trying to execute Sql query. which is taking long time to execute.(there are millions of records are there).
    my query is

    Select * from sdeetlsrc.vcr-adjd-clmsf-ref inner join
    sdeetlsrc.vcr-adjd-clmsf-polptnt
    on vcr-adjd-clmsf-ref.partn-id=vcr-adjd-clmsf-polptnt.partn-id
    and vcr-adjd-clmsf-ref.partn-val=vcr-adjdclmsf-polptnt.partn-val
    and vcr-adjd-clmsf-ref.invn-ctl-nbr=vcr-adjdclmsf-polptnt .invn-ctl-nbr
    and vcr-adjd-clmsf-ref.suf-cd =vcr-adjdclmsf-polptnt . suf-cd
    and vcr-adjd-clmsf-ref.proc-dt =vcr-adjdclmsf-polptnt.proc-dt
    and vcr-adjd-clmsf-ref. oblig-id not in('02')
    and vcr-adjd-clmsf-ref. shr-arng-cd not in('02')
    and vcr-adjd-clmsf-polptnt.prdct-cd in
    ('hm','pos','hr','ep1','epo','hm1','hm8','plp','tc p','ppo','ind','pp1')

    need to optimize the query.

  2. #2
    Join Date
    Apr 2015
    Posts
    2

    need query optimization

    I need to optimize sql query which has millions of rows in that. we need to minimize the execution time such a way so that performance will increase.
    But my query is taking long time execute. what is the way to optimize the query.


    my sql query is
    Select * from sdeetlsrcrep.vcr-adjd-clmsf-ref inner join
    sdeetlsrcrep.vcr-adjd-clmsf-polptnt
    on vcr-adjd-clmsf-ref.partn-id=vcr-adjd-clmsf-polptnt.partn-id
    and vcr-adjd-clmsf-ref.partn-val=vcr-adjdclmsf-polptnt.partn-val
    and vcr-adjd-clmsf-ref.invn-ctl-nbr=vcr-adjdclmsf-polptnt .invn-ctl-nbr
    and vcr-adjd-clmsf-ref.suf-cd =vcr-adjdclmsf-polptnt . suf-cd
    and vcr-adjd-clmsf-ref.proc-dt =vcr-adjdclmsf-polptnt.proc-dt
    and vcr-adjd-clmsf-ref. oblig-id not in('02')
    and vcr-adjd-clmsf-ref. shr-arng-cd not in('02')
    and vcr-adjd-clmsf-polptnt.prdct-cd in
    ('hm','pos','hr','ep1','epo','hm1','hm8','plp','tc p','ppo','ind','pp1')

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What you posted isn't valid SQL syntax for Microsoft SQL Server, so it can't possibly run in this environment.

    Please specify what brand of SQL database engine (DB2, Microsoft, MySQL, Oracle, PostgreSQL, etc.) you are using and I'll move this thread to the proper forum so that you get a better answer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Code:
    SELECT * 
    FROM sdeetlsrc.vcr-adjd-clmsf-ref 
    INNER JOIN sdeetlsrc.vcr-adjd-clmsf-polptnt 
    ON vcr-adjd-clmsf-ref.partn-id=vcr-adjd-clmsf-polptnt.partn-id 
    and vcr-adjd-clmsf-ref.partn-val=vcr-adjdclmsf-polptnt.partn-val
    and vcr-adjd-clmsf-ref.invn-ctl-nbr=vcr-adjdclmsf-polptnt.invn-ctl-nbr
    and vcr-adjd-clmsf-ref.suf-cd=vcr-adjdclmsf-polptnt.suf-cd
    and vcr-adjd-clmsf-ref.proc-dt=vcr-adjdclmsf-polptnt.proc-dt
    WHERE vcr-adjd-clmsf-ref.oblig-id not in('02')
         and vcr-adjd-clmsf-ref.shr-arng-cd not in('02')
            and vcr-adjd-clmsf-polptnt.prdct-cd in
              ('hm','pos','hr','ep1','epo','hm1','hm8','plp','tcp','ppo','ind','pp1')
    I TRIED LOL

    Not sure you need all those join fields in your join, but I added them assuming you need them.

    After that you had conditions which looks like you want them in a Where clause.

    HTH

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yeah, the field names indicate something other than SQL. It will not run "as is" without correctly delimiting each identifier (both table names and column names). After that is done, you need to convert "[NOT]IN" to a "LEFT OUTER JOIN" against either physical tables (like temporary tables) or CTE's. Also, I'd advise against the usage of "*". Be explicit and list every field you intend to return.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Tags for this Thread

Posting Permissions

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