• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

search

if i have millions of record in to my db2 table.


How i would search effectively.


could you explain briefly
0
Manikandan Thiagarajan
Asked:
Manikandan Thiagarajan
  • 5
  • 5
  • 3
4 Solutions
 
Mayank SAssociate Director - Product EngineeringCommented:
Perhaps this would be better explained in the data-base or DB2 topic-area (if there is one for DB2).

The best thing to do is create an index on the table for those columns on which searching is likely to be done (I'm not sure if DB2 supports creation of an index - I hope it does).
0
 
Manikandan ThiagarajanSenior consultantAuthor Commented:
is it needed to use collection interface? if yes, which collection i would implement
0
 
hoomanvCommented:
The physical storage of rows in a base table is not ordered. When a row is inserted, it is placed in the most convenient storage location that can accommodate it. When searching for rows of a table that meet a particular selection condition and the table has no indexes, the entire table is scanned. An index optimizes data retrieval without performing a lengthy sequential search

Note: Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mayank SAssociate Director - Product EngineeringCommented:
>> is it needed to use collection interface? if yes, which collection i would implement

In your Java code, it depends. You can get data in the form of pages and use a CachedRowSet to hold it in memory. Otherwise, you can also use collections. But to make the searching faster on the DB-side, there is nothing that Java can do. You need to optimize the DB/ tables/ indices for that.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> You can get data in the form of pages

Meaning, say 20 at a time, or 50 at a time, in one query. Process it in your Java code, then shoot another query for the next 20 or 50.

However, when the query is shot into the DB, after that your Java code loses control over what is being done and how it is being done. You could use a stored proc to improve some performance b/w the Java to DB calls, but I guess the main question is about searching *in the DB*. So while searching, you will get some results which will not contain all the million records that you have. So in your Java side, you would not be dealing with a million records but only at the DB-side (while searching).
0
 
Manikandan ThiagarajanSenior consultantAuthor Commented:
>>Meaning, say 20 at a time, or 50 at a time, in one quer

ok,i collect 50 records in one query., how coild i search in that query
0
 
hoomanvCommented:
> how coild i search in that query
linear search

not good for sreaching one million rows
0
 
hoomanvCommented:
why dont you use indexed tables ?

if you want to search in your own collected records, in an optimized way, try storing them in an structure like BTree or BinarySearchTree to improve the search speed
0
 
Mayank SAssociate Director - Product EngineeringCommented:
Nothing that you do on the Java side will affect the search performance on the DB side.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> how coild i search in that query

The search on the DB side cannot be controlled by Java. It will search based on the indices, etc it has. Once you have the records in a result-set/ cached-row set, you can scroll through them.
0
 
Manikandan ThiagarajanSenior consultantAuthor Commented:
>>an structure like BTree or BinarySearchTree to improve the search speed

could you explain that  one
0
 
hoomanvCommented:
an index in DB is implemented as BTree itself, no need to implement it yourself in your java app

http://en.wikipedia.org/wiki/Binary_search_tree
http://en.wikipedia.org/wiki/B-tree
0
 
hoomanvCommented:
what exactly do you want to extract from database ?
if you want some rows that meet a particular selection condition you use the select statmement like
> select columns from table where condition
this will search linearly (in the case where no index has been created) the entire DB to find what you want
but if you create an index for the table
> create index index-name on table-name (column-name1, column-name2, ...)
it improves the search time significantly
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now