I'm having a serious performance problem when querying against a view in MS SQL.

The view (10 columns) joins 7 very small lookup tables with a large (200k rows) fact table.

All queries (returning from 1 to all rows) I execute against the view run very quick. Yet, when I use hibernate it's very slow.

I created a table with all the view columns and did a select into it (200k rows). Then pointed the hibernate mapping from the view to this table. And the performance problem vanished.

Does anyone have any ideas of what may be occuring?

Thanks!


Environment:
MS SQL Server 2000 (using MS JDBC drivers)
Hibrnate 2.03

driver problem?




executed via JDBC, or enterprise manager?

When I run the queries using enterprise manager either against the view or the table that i created containing the same data as return by the view ; i get the same quick performance.

In hibernate when i point the mapping to the table i get the same performance as in enterprise manager , when i point to the view i get the teribble performace.

So looks like the JDBC driver is at fault then, doesn't it?

As a test i decided to download an eval edition of a third party driver (ver 3.3) from datadirect (http://www.datadirect-technologies.com) and got the same results.


This is what i'll do:

I'll try another third party driver and will call the jdbc drivers directly (i.e without hibernate) doing selects againt both the view and the table to see if i get the performace difference. I'll let you know when i'm done.

thanks

Also try setting show_sql = true (in hibernate config/property file) to view the hibernate generated sql.

Thanks, however i'ved had show_sql = true maybe i'll set it to false and see if it makes a difference.

I now tried JSQLConnect(3.3019) drivers from netdirect. There was an improvement in going against the view, but it still was twice as slow.


Now going to take hibernate out of the picture and run the queries using jdbc.

It looks like hibernate has a performance problem -

Using several drivers - i executed over and over queries against both the table and the view using jdbc and i got the same performance.

when i switched to hibernate the queries against the view are 4x slower.


I don't know why?

But not slower against a table of the same size?

I can't see how this could make sense. As far as Hibernate is concerned there is no difference b/w a table and a view.

Now, it is possible to see quite large overheads when executing extremely simple queries against tables that are small enough that the database can completely cache them in RAM, (only when the database is local). But I've never seen this add up to 4 x before. (Actually there is a performance bug in Hibernate 2.1, which is fixed in CVS but you are using 2.0.3, so that doesn't affect you.) Is your database doing any disk access when you run the test?

Are you sure you are doing things in a loops so that the JVM and database are completely warmed up? (Make sure you don't do something very silly like: test Hibernate once then test JDBC once!)

The table and view are the same size - each returns the exact same 200k rows. And i executed the queries multiple times.

When i query single row using hibernate


loanRo = (LoanRo) session.load(LoanRo.class,loanId);



generates:
Hibernate: select loanro0_.SAXON_LOAN_ID as SAXON_LOAN_ID, loanro0_.BOR_LAST_NM as BOR_LAST2_, loanro0_.BOR_FIRST_NM as BOR_FIRS3_, loanro0_.BUSINESS_CHANNEL_DS as BUSINESS4_, loanro0_.LIEN_POSITION_DS as LIEN_POS5_, loanro0_.PRODUCT_DS as PRODUCT_DS, loanro0_.SVCR_STATUS_DS as SVCR_STA7_, loanro0_.SVCR_STATUS_MOD_DS as SVCR_STA8_, loanro0_.UW_STATUS_DS as UW_STATU9_, loanro0_.SAG as SAG, loanro0_.FUNDED_DT as FUNDED_DT, loanro0_.LOAN_AMOUNT_ORIGINAL as LOAN_AM12_, loanro0_.LOAN_AMOUNT as LOAN_AM13_, loanro0_.WIRE_DT as WIRE_DT, loanro0_.CUSTODIAN_ACCEPT_DT as CUSTODI15_, loanro0_.INVSTGN_STATUS_DS as INVSTGN16_, loanro0_.LENDER_CD as LENDER_CD, loanro0_.LENDER_NM as LENDER_NM from SCRAPS_LOAN_RO loanro0_ where loanro0_.SAXON_LOAN_ID=?


it takes less than a second when pointing to the table
5 - 8 seconds when pointing to the view.



When I recreate the query in sql and run it using JDBC as
preStmt = con.prepareCall(QUERY)
if (preStmt.execute())

it excutes in less than a second regardless of pointing to the table or view.

When i execute in enterprise manager (query analyser) their also no difference in performance.


I tried different drivers and still got slower peerformance when accessing the view from hibernate and no performance difference from outside hibernate.

I dunno. This is very wierd. Hibernate treats a view just like a table. It doesn't know the difference.


You use a CallableStatement to execute a query????

No i used a prepared, i'll try to pinpoint the code that is having the problem. Thanks!