aspose file tools*
The moose likes Performance and the fly likes select query faster performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "select query faster performance" Watch "select query faster performance" New topic
Author

select query faster performance

karthick sambanghi
Greenhorn

Joined: Sep 25, 2013
Posts: 17
Dear All,

select XMLRECORD xmlrecord "+" from"+" "+tablename+" where recid >= ? and recid <= ? order by recid

How to increase the select query faster(Performance wise)

i need the above query to extract to work for huge volume in DB.

Any help will be much appreciated.

Thanks
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2505
    
    8

You may want to use the between keyword to replace the "recid>=? and recid<=?" part. Not sure if this will improve your performance.

How is your table designed? Is the data partitioned? Is there an index for the searched columns?


K. Tsang JavaRanch SCJP5 SCJD/OCM-JD OCPJP7 OCPWCD5 OCPBCD5
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

K. Tsang wrote:You may want to use the between keyword to replace the "recid>=? and recid<=?" part. Not sure if this will improve your performance.

I'd be very surprised. These are just different ways to express the same condition, any decent database should handle them in the same way.

If the query returns only a few rows, an index should help. If it returns a lot of rows, then it's going to be slow, but the right partitioning might help a bit. The best thing to do now, however, would be to see what's happening in the database with your current query. Most databases allow to display an execution plan (which will reveal whether an index is used or not, for example), and some databases allow to trace or profile the execution of the query, which would reveal in detail how the query was executed and where was the time spent.
vamsi ever
Greenhorn

Joined: Oct 18, 2013
Posts: 2
Each operator exhibits its own speed with the index when used in where clause.
Operator(s) Celerity
=     10
> >= <= <     5
LIKE     3
<> != NOT     0
Operators with celerity 0 don't invoke index.

select <col_list> from <tablename> where <indexedcolumn> = value;
is always faster than
select * from <table_name>;
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 8008
    
  22

karthick sambanghi wrote:select XMLRECORD xmlrecord "+" from"+" "+tablename+" where recid >= ? and recid <= ? order by recid
How to increase the select query faster(Performance wise)
i need the above query to extract to work for huge volume in DB.

There are any number of ways you might be able to increase performance, but my advice would be:
1. Work out whether the query is ACTUALLY too slow first - ie, run it on life size samples and measure it.

2. Understand that any query on huge amounts of data will take time. Try out a few of the suggested solutions on life-size samples and see if they actually deliver the performance you need. It's quite likely that any or all of the suggested techniques might save you 10%, but it's highly UNLIKELY that they'll halve it.

3. Think about whether there is another way to do this:
  • Do you really need to query X zillion rows in order to get the information you want?
  • Could you pre-process the query to cut down on the amount of scanning?
  • Could a judicious index help out? (This is a double-edged sword, as it may well slow down processing for other people)
  • Could you load some "clever stuff" into temporary tables before you start?
  • (Finally, and possibly most importantly) Is this a design requirement; and if so, does the current design cater for it?

  • "Performance" is something we puny humans almost always get wrong, because we tend to:
    (a) Think that it must be "as fast as possible", when the correct target should be "fast enough".
    (b) Think only about the process, not the result.
    (c) Worry only about our process, and forget other people's.
    (d) Assume that there is only ONE (usually SQL-based) way to do what we want.

    If any of the above strikes a note, think very carefully before you act.

    HIH

    Winston

    Isn't it funny how there's always time and money enough to do it WRONG?
    Articles by Winston can be found here
    Ron McLeod
    Ranch Hand

    Joined: Feb 12, 2013
    Posts: 328
        
        6

    If it is an option, you might also want to consider the hardware that your database and application are running on. You may be able to get huge performance boosts by increasing the amount of RAM and number of cores, using RAID and/or SSDs for the disk system, caching disk controllers, physical machine rather than virtual machine, etc.
     
    Don't get me started about those stupid light bulbs.
     
    subject: select query faster performance