• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Search query is taking too long to search

 
Ranch Hand
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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


 
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 99
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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?
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Farakh khan
Ranch Hand
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot to all. Your tips are very important. Appreciated!!
 
chris webster
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 144
Oracle Fedora Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Still waiting......
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
reply
    Bookmark Topic Watch Topic
  • New Topic