File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Search query is taking too long to search Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Search query is taking too long to search" Watch "Search query is taking too long to search" New topic
Author

Search query is taking too long to search

Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
Hello,

I written the following code. When job is inserted then search CVs starts and according to the fields of (requirements+title) query fetch CVs and insert to another table.
1) its taking too long mostly I am seeing page not found error
2) its inserting not accurate CVs even some CVs are not relevant at all

Please advise how can I refine my code or is there any other way to search and insert CVs that are very accurate?

Thanks in anticipation


Philip Thamaravelil
Ranch Hand

Joined: Feb 09, 2006
Posts: 99
First step, add a printStackTrace() and re-test to give more information or see if there is an error.


Secondly, IMO I would add print statements/logging to watch where it is dragging.


Finally,
Better to put your close statements in a finally block. This way you don't have mysteriously open connections.
Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
Hi,

There is no error and printStackTrace()+finally block are in the main method

I need help to refine my SQL query or method to optimize the insert time period. In DB there is 100K+ records and are taking minutes to be searched and then insert. Looking for any suggestion as to how can I change the strategy to have best possible results.

Please note: the code is working fine
Philip Thamaravelil
Ranch Hand

Joined: Feb 09, 2006
Posts: 99
Farakh khan wrote:Hi,

There is no error and printStackTrace()+finally block are in the main method

I need help to refine my SQL query or method to optimize the insert time period. In DB there is 100K+ records and are taking minutes to be searched and then insert. Looking for any suggestion as to how can I change the strategy to have best possible results.

Please note: the code is working fine



Is there an index on the table? 100k+ records is a modest size from a performance perspective.

Also, have you checked the database server to see if it's the bottleneck?

What does the DB server CPU/Mem utilization look when you run your application?
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1476
    
  11

It's usually much quicker to do this all in one SQL statement if your database permits it, as there seems to be no reason for your code to fetch all the CV data from your database across the network, then just write it all back to the database again:

INSERT INTO mytable (col1, col2, col3)
SELECT ....

You need to make sure your SELECT is returning exactly the rows you want to write into the target table, and that the columns are fetched in the same order as they appear in your INSERT INTO statement.

Also, your SELECT looks like it might be more complicated than it needs to be - which might be why you are getting the wrong records - but I don't have time to unpick it. Start simple and work up from there.

One thing you could try is simply running your SELECT directly in your SQL interpreter to see how fast it runs on the database without the extra INSERT step or all the unnecessary network traffic. This will tell you if the SELECT is intrinsically slow (quite likely), or if the time is being taken on the INSERT (less likely) or in transit across the network (quite likely). You could also try running a few INSERTs directly on the database as well, using hard-coded values, to see how quickly the rows are inserted.

If the SELECT is slow, re-work it to make it simpler, and test it directly on your database via the SQL interpreter to see if it runs faster. If the network is slow, re-write the SQL so it does the SELECT+INSERT in one step, as described above. If the INSERT is slow, talk to your DBA as there may be some problem with things like excessive indexing or allocating tablespace.

In general, you should always develop and test your SQL first on your database, because that's the only way you can tell if it works and performs properly before you add all the external Java plumbing and network latency etc.

No more Blub for me, thank you, Vicar.
Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
Thanks a lot to all. Your tips are very important. Appreciated!!
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1476
    
  11

Hi Farakh,

I think you need to look at making your SELECT simpler. The syntax in your post is unclear to me e.g. what is the REGEXP_LIKE clause doing? Also, you seem to have column names that are the same as table names, which is usually a bad idea. But it looks like your SELECT is like this:



If I've understood this correctly, it seems you are basically just doing a simple join between the detailResume table and the jobDetails table, although I can't figure out what you're doing with the REGEXP_LIKE comparison:



You need to be careful to include something that specifically matches a Job to one or more Resumes in the WHERE clause, or you may end up with a Cartesian join i.e. all possible Job/Resume combinations. I'm guessing that's what the comparison between detailResume (column) and job title/requirements is supposed to be doing in your original SQL, although I wonder if this is actually working the way you want.

Assuming I haven't completely misunderstood your query (which is entirely possible!), I recommend you re-build your SELECT and run it on your database using the SQL interpreter (e.g. SQL*Plus on Oracle) to get it working properly and giving you the results you want. Then add whatever you need to make it part of a single SQL INSERT statement as described in my earlier post.




Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
Thanks a lot Chris but still taking too much time to insert the form. Now I used very simple query:


Its just working, working.... and working nothing happened.

how can I run it in batch by showing message to user that data inserted?

Thanks again
Mike Zal
Ranch Hand

Joined: May 04, 2011
Posts: 144

Here is an example of How to batch calls with a Prepared Statement. You should use it inside your while loop. Your current code try to make a network round trip for each result in your first query.

Note: The executeBatch call returns an int array so you know how many rows were affected by each query.


OCJP6, OCWCD5
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18113
    
    8

Here's another thing: you have no idea what is happening in that code because you're running it in a servlet. That is clearly not a good choice for testing the code, then. I suggest you test the code in a plain old Java application where you can see how long things take to run.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

Yet another consideration: generic LIKE (the one with '%' at the beginning) cannot be answered using standard indexes and the database has to scan the whole table to find matching records. Depending on the database, there might be specific indexing methods that allow to efficiently do a text search, but even so, it might not apply to the LIKE clause - a specific function might be required to be used.
Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
Martin Vajsar wrote:Yet another consideration: generic LIKE (the one with '%' at the beginning) cannot be answered using standard indexes and the database has to scan the whole table to find matching records. Depending on the database, there might be specific indexing methods that allow to efficiently do a text search, but even so, it might not apply to the LIKE clause - a specific function might be required to be used.


Yes Martin you are right '%' is searching whole table. I used regex based query stated in my first post but same problem.. What other function I can use?

Thanks
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1476
    
  11

1. If you're still running this via Java, then you still don't really know where the time is being spent. Your database will have a SQL shell interface e.g. SQL*Plus for Oracle, mysql for MySQL, psql for PostgreSQL, and so on. You should run your SQL in there first, so you can be sure any problems are due to SQL only.

2. Check how fast the query runs in your SQL shell - limit it to say 100 or 1000 rows if necessary. As Martin says, the LIKE clause is certainly slowing it down. Try using an "=" comparison with a fixed value that you know is in the database, just for testing it. Then do what you can to optimise the query performance e.g. if you're on Oracle you might be able to use text indexing. But LIKE '%x%' is always going to have do a lot of work, so maybe you need to think of a different way to find/organise your data e.g. store keywords separately and search those.

3. Check how fast the INSERT runs in your SQL shell. Hard-code the values for testing initially. Put e.g. 20 or 50 INSERTs into a SQL script file and run that via your SQL interpreter to make sure the INSERT is not the slow bit. If it is slow, talk to your DBA e.g. about indexing or tablespace allocation.

4. Most databases allow you to do INSERT INTO .... SELECT... in a single SQL statement as I described previously. This is the quickest way to do this kind of thing, because everything runs in one place at one time. In your example, there is no need to fetch records individually out of the database into Java across the network, only to write them back to the database individually, because you are not doing anything with the data inside Java anyway. You're just wasting resources. Use the INSERT...SELECT with your optimised query, but try it in your SQL shell first.

5. Learn how to use your SQL shell when you are developing SQL. That's what it's there for.

6. Take a piece of paper and write the following on it:

"Java is not a database programming language.
SQL is a database programming language.
Use the right tools for the job."

7. Pin the piece of paper above your desk so you and your colleagues can see it.

Good luck.
Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
Hello,

Sorry for late reply as I was working on Oracle text. I provided required privileges to the user and created index.


This query works fine in oracle SQL command prompt but when am using in prepared statement then its giving following error:


Please advise

Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
Still waiting......
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

Disclaimer: this is a rough guess. I've no direct experience with Oracle Text.

I assume you've double checked that you're using the same user in JDBC as in SQL*plus. There might be a difference in national settings, though. As far as I know, you might need to create a separate index for different code pages.

I'd suggest to display contents of the nls_session_parameters view from SQL*plus and from JDBC application (they may boot up with different NLS settings, I've seen that happening). Compare the two instances of the view, and try reconcile the differences using ALTER SESSION (you should be getting identical response with identical NLS). It might be a good time to think about NLS in your application in general..
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1106

I don't know if it still does, but putting the to_lower function around any string used to stop it using that index. so we had lower_xxx columns
Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
Hello,

The following is the result:



my calling class content types are:


I removed charset=UTF-8 and tried but same error;

Please advise
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3433
    
  47

Run that query (SELECT * FROM NLS_SESSION_PARAMETERS) from inside of your application and display or log it somewhere, then compare the output to this output you got from SQL*plus. I assume there will be differences, if so, we can work on from that. If there are no differences, I was mistaken and this would be a blind path.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1476
    
  11

For future reference, if you have Oracle-specific questions, you might also want to consult the Oracle Technology Network (OTN) forums e.g. Oracle TechNet SQL Forum.

It's free to register with OTN, and it's a good place to get help on Oracle-specific questions. OTN members can also download free developer-only copies of many of Oracle's core products.
Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
I think this is JDBC problem as this statement is running in oracle sql very fine but when am trying with prepared statement then its throwing error. Please correct me if I am wrong
Farakh khan
Ranch Hand

Joined: Mar 22, 2008
Posts: 726
Thanks to all who participated in this post. The problem was my end. There are two databases one is online and one offline but with same username. I created index on offline db but my jdbc driver was connecting me to the online server that was the actual problem.

Best regards
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Search query is taking too long to search
 
Similar Threads
Want to convert my code in method
Problem of inserting java array into DB
java.lang.NullPointerException
Showing resultSet
Displaying record not exists