1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Performance tuning

Discussion in 'SQL PL/SQL' started by jagadekara, Jun 26, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I have a select statement which displays 70 columns from 15 tables.

    Actually I need only 30 columns from that. But for future purpose i took remaining columns also.

    So is there any performance issue?

    ex:

    Q1) select ename,empno,sal
    from scott.emp;

    Q2) select ename,empno,sal,deptno,mgr,hiredate
    from scott.emp;


    In above queries Q1 have more performance or Q2 or both have same performance?

    Please Suggest me....
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    The quantity of columns can affect request productivity as a whole since it is the volume of returned data.
    Response time from system can will increase since the volume of transmitted data will increase.

    It is especially necessary to consider if there are columns with the CLOB/BLOB type.

    Everything is specific to a specific situation.

    Note : I wouldn't add columns on the future.
    The new field is necessary - we add a column in the user view.
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Sergey,

    I don't have blob/clob columns.

    So in my example Q1 is more performance, right?
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    To be realistic, if I take your example, the performance difference would not be noticeable. As far as database query is concerned, there would be no difference; but in terms of data transmitted over the network, yes it may differ though I wonder if transmitting 6 columns instead of 3 would be done in one and same packet, so no difference also.

    However, if it's comparing a query which is returning 3 columns instead of, for ex., 100 (assuming from same table), then definitely the load on the network traffic would be higher and latter would be less performing.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    In the request text simply it is impossible to tell that one request is more optimum than another.

    First of all it is necessary to watch request plans to understand, what costs of input-output the system will have executing request of Q1 and Q2.

    Data acquisition is one process.
    Trasport of data is a process another.

    Very high probability of that from the technical point of view these requests of Q1 and Q2 will have one order of expenses for system input-output on receiving result of requests.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Any appreciable performance issue would stem from the 15-table join rather than the number of column values you're returning. For a non-Exadata system that's where the performance issues would lie; the join order and data access paths will affect performance more than the number of values returned from the database perspective. Of course there's the network aspect, and the longer the 'row' the more work the network needs to do to get that transmitted from server to client. Your example is not a very good one, to be honest, since emp and dept aren't large tables nor do they have tremendous amounts of data.


    Any non-Exadata database accesses the data in the 'usual' way, reading data blocks from disk or memory and returning full rows which match the criteria, then letting the database end filter the results. Exadata is a bit different since the storage cells access the disks and the database accesses data through the storage cells. These cells provide optimizations such as Predicate filtering, column projection and bloom filters to 'pre-process' the data at the storage cell level before it gets passed on to the database level. Such optimizations reduce the inter-server network traffic by returning only the columns in the select list.


    I suspect in your case you are not using Exadata so the performance difference between the two versions of the query (if you modify your current query to return only the 30 columns of current interest) will be minimal. Look beyond the select list if you feel the query needs performance improvements.
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks All,
    For your valuable suggestions....
     
  8. vprog

    vprog Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Ghaziabad
    Depends on how many colums you want to show to the user. If you want to show all 30 columns then Q2 is right. Otherwise to increase performance you can divide the table into columns of 6 only and increase the number of tables. Then use the Q2 query on any one. Showing 70 columns from 15 table is a tedious task. You can do so by dividing them.