Announcement Announcement Module
Collapse
No announcement yet.
Hibernate-mapping of a view is extarordinary slow - what to do? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Hibernate-mapping of a view is extarordinary slow - what to do?

    Hi!
    I am mapping several Tables from a MS-SQL Server 2012 and as long as I just map a few data everything works pretty fast. I was able to map two Tables: Orders and Groups (n:1 - every order has a group, but a group can be assigned to different orders).

    So far everything is straight forward and even with the output via
    Code:
    System.out.println
    I am able to display 121 rows of data in 1.5sec. (And that's just a Testserver).

    But now the problem occurs: Every Order has several order-lines which I fetch from the view OrderLineView:
    Code:
        @OneToMany(mappedBy = "ol", fetch = FetchType.LAZY)    
        private List<Orderlineview> orderlines = new ArrayList<Orderlineview>();
    Due to it is not possible to join Tables with FK from OrderLines -->PK from Articles I decided to build an updateable view and map it. As seen below the View consists of a Table with the order-lines (updateable) and the article-view (For those values I've set
    Code:
    @Column(name = "Price", updatable = false)
    ).
    Actually it worked, but the time it takes is a catastrophe. (By the way: I have no need for the
    Code:
    private Orders ol
    , I never call it, but I haven't seen a way to get rid of it)

    (I tried to change from
    Code:
    FetchType.LAZY
    to
    Code:
    FetchType.EAGER
    in Order but the result is pretty much the same.)

    An order-line has the following "columns":

    Code:
    @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name = "ol_ID")
        private Integer ol_ID;
        private String artnr;
        private Integer amount;
        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "orderline_ID_FK", referencedColumnName = "orderline_ID_PK")
        private Orders ol;
        @Column(name = "Description", updatable = false)
        private String description;
        @Column(name = "Price",updatable = false)
        private Double price;
    I did some research already and found an issue at Stackoverflow: http://stackoverflow.com/questions/6...ng-very-slowly.
    But just adding a constructor didn't solve the problem for me...

    All together I have about ~300 order-lines which fetch their data from the Article-View (which much bigger, I avoid mapping it).

    In my NetBeans-Output I get for the output of everything:
    Code:
    End after 1154017ms
    BUILD SUCCESSFUL (total time: 19 minutes 14 seconds)
    So how am I able to increase the efficiency of my Application? Is there something else necessary which has to be done?

    So far some other (maybe) relevant information: I use the jTDS-Driver and c3p0.
    My query-function is quite simple (but maybe deprecated?):

    Code:
    private static void query() {
            Session session = InitSessionFactory.getSessionFactory().getCurrentSession();
            Transaction tx = session.beginTransaction();
            String hql = "SELECT O FROM Orders O";
            Query query = session.createQuery(hql);
            List order_elements = query.list();
            for (Iterator iter = order_elements.iterator(); iter.hasNext();) {
                Order element = (Order) iter.next();
                //log.debug(element);
                System.out.println(element);
            }
            tx.commit();
        }
    Thank you!
Working...
X