Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    22

    Unanswered: what different in SQL-statement

    My OS is linux
    I execute one SQL-statement in different way and got different performence.
    1)In OS environment
    biw:@db2 /u/biw>db2 select count\(*\) from table_name
    2)In db2sql92
    DB2SQL92> select count(*) from table_name;

    Resultuse command "vmstat")
    IN 1), CPU wait nearly zero and Process block nearly zero
    IN 2), CPU wait nearly 50 and Process block nearly 10

    who can tell me what different in these?
    thanks a lot
    **************************************
    Make progress everyday
    Mail: hoo.jimmy@gmail.com
    **************************************

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by huyuhui
    My OS is linux
    I execute one SQL-statement in different way and got different performence.
    1)In OS environment
    biw:@db2 /u/biw>db2 select count\(*\) from table_name
    2)In db2sql92
    DB2SQL92> select count(*) from table_name;

    Resultuse command "vmstat")
    IN 1), CPU wait nearly zero and Process block nearly zero
    IN 2), CPU wait nearly 50 and Process block nearly 10

    who can tell me what different in these?
    thanks a lot
    In 1 - did you mean to leave the * un-escaped?
    db2 "select count(*) from table_name"
    works better... might be the issue.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by J Petruk
    In 1 - did you mean to leave the * un-escaped?
    db2 "select count(*) from table_name"
    works better... might be the issue.
    Also, could be a bind issue, maybe db2 is trying to auto-bind? Although I believe db2sql92 auto-binds at start-up.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Apr 2004
    Posts
    22

    dynamic SQL and static SQL?

    Quote Originally Posted by J Petruk
    Also, could be a bind issue, maybe db2 is trying to auto-bind? Although I believe db2sql92 auto-binds at start-up.
    I don't understand the relations of BIND and DB2SQL92.
    Is about dynamic SQL or static SQL?
    The following statement:
    1).db2 "SELECT empno,edLevel FROM employee"
    2).DB2SQL92>SELECT empno,edLevel FROM employee
    The two statements are all static SQL-statement,I think so.
    I dont know why i got bad performance(such as CPU wait and Process Blocks) when i used DB2SQL92.
    Is any way to turn the performance?
    thanks a lot
    **************************************
    Make progress everyday
    Mail: hoo.jimmy@gmail.com
    **************************************

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by huyuhui
    I don't understand the relations of BIND and DB2SQL92.
    Is about dynamic SQL or static SQL?
    The following statement:
    1).db2 "SELECT empno,edLevel FROM employee"
    2).DB2SQL92>SELECT empno,edLevel FROM employee
    The two statements are all static SQL-statement,I think so.
    I dont know why i got bad performance(such as CPU wait and Process Blocks) when i used DB2SQL92.
    Is any way to turn the performance?
    thanks a lot
    In both cases this is dynamic SQL, not static, but DB2SQL92 is a program that has an associated bind file. I thought maybe it was pausing due to autobind, but that would only happen once.

    They both eventually return a proper result set, right? Is the database local to where you're issuing the select?

    The same query should generate the same access plan, especially a query as simple as that. A few things could impact it, ie. degree, optimization, but not typically for a straight select.

    Anyone else have any ideas?
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Mar 2004
    Posts
    448
    db2sql92 checks the statement for sql92 compliance.It do some thing.

    1.check for sql92 takes CPU time.
    2. use RR isolation level.
    3. writes time information also take CPU time.

    regards,

    mujeeb

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by bmujeeb
    db2sql92 checks the statement for sql92 compliance.It do some thing.

    1.check for sql92 takes CPU time.
    2. use RR isolation level.
    3. writes time information also take CPU time.

    regards,

    mujeeb
    You might be right about RR... the CPU time for the sql92 check is tiny, especially for a query that simple, and the elapsed time measurement should be trivial as well.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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