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.
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.
Bats fly at night, 'cause they aren't we. And if we tried, we'd hit a tree -- Ogden Nash (or should've been).
Articles by Winston can be found here
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.
Joined: Oct 18, 2013
Please find my SQL Tuning papers at following links