Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently doing some hibernate performance test. My requirement is to search for data in a particular column of a table containing 8 million rows and 350 columns. Currently, I don't have the access to that database, however, the test database that I'm now using also contains 8millions+.

My question is, is there a significant difference in terms of scan speed when searching for a data in a table that containts 350 columns than in table containing 25 columns?

By the way, the way I check for data is :

sessionFactory.getCurrentSession().createSQLQuery("SELECT COL_5 from MY_TABLE where COL_5='" + theColValuePassedFromParameter+ "'").list();

Where in if the returned list is empty, the boolean method will return true, else false; I would also appreciate If someone can point me to a better, performant way of searching for a non-primary key data in hibernate.

Thanks in advance...

share|improve this question

migrated from stackoverflow.com Jul 12 '13 at 9:18

This question came from our site for professional and enthusiast programmers.

add comment

3 Answers 3

up vote 5 down vote accepted

If someone can point me to a better, performant way of searching for a non-primary key data [...]

Add an index in your database on the column(s) you are filtering on - in this case COL_5. The resulting execution plan should be a single index seek and the performanze difference negligible (or even zero).


Also rather than getting the entire result set, just check if there is at least one resulting row. You can either add a TOP 1 to your query (or whatever SQL equivalent), or add a .setMaxResults(1).

share|improve this answer
    
Noted. I will google your suggestion further. –  onepotato Jul 12 '13 at 5:45
add comment

You ask two quesions, firstly:

is there a significant difference in terms of scan speed when searching for a data in a table that containts 350 columns than in table containing 25 columns?

If you have an index defined, then no, there should be no difference. If you don't have an index, then there may be a difference. Assuming you don't have enough RAM for your database to hold all 8 million rows in a cache, then it has to go to the disk to fetch each row, in which case there will be some IO overhead. The more data that has to be fetched from disk, the more overhead this will be. Obviously there's likely to be some correlation between the number of columns and the total size of the row's data.

Secondly:

I would also appreciate If someone can point me to a better, performant way of searching for a non-primary key data in hibernate.

As lc. mentioned in his answer, using an index and, if all you are really interested in is the existence of any row that matches the criteria, either 'TOP 1', or select the number rows directly in SQL:

SELECT COUNT(COL_5) FROM MY_TABLE WHERE COL_5=?
share|improve this answer
    
Thanks for the input –  onepotato Jul 12 '13 at 6:15
    
I modified my query like you suggested but it seems more slower than hibernate's criteria.list with setMaxResults. –  onepotato Jul 12 '13 at 6:58
    
@onepotato, having thought about it, that does make sense. Using a row limit of 1, the DB query processor can stop after it finds a single row, whereas using COUNT, every single row still needs to be processed. –  SimonC Jul 12 '13 at 9:14
add comment

In response to your question about the performance difference in querying a table with 300 vs 25 columns, it really depends on the specs of your database server. In general, the greater the number of columns the more likely is that the rowsize exceeds the blocksize and rows are stored in a chained way.

There are two types of row chaining: Intra-Block and Inter-Block, the latter generating more performance issue than the former.

This is an interesting page about the performance impact of Intra-Block chaining.

As for your query, note that building an index on col_5 is only worth it if this column has a high cardinality (holds a great number of different values), otherwise the optimiser will disregard the index and opt for a table scan regardless.

share|improve this answer
    
We use Oracle. I will also research on that. –  onepotato Jul 12 '13 at 6:27
    
Oracle 11g Row Chaining and Migrating –  bajy Jul 12 '13 at 6:37
    
Can you give me a good article in Oracle 10g? –  onepotato Jul 12 '13 at 6:39
    
The documentation on this point has not changed between 10g and 11g. –  bajy Jul 12 '13 at 6:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.