1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Application help

Discussion in 'SQL PL/SQL' started by simona007, Nov 17, 2008.

  1. simona007

    simona007 Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Am working on a .Net web application which uses Oracle as database.

    Its a "Job search" application through which end users can search for a job in my database using comma separated search keywords.My database could be having tens of thousands of jobs.Each job would have a title and a description.

    I need to return all the matching jobs to the user based on his search criteria and also give an appropriate "rating" for each job with respect to the percentage of matched keywords found.Users would have a feature of sorting the returned jobs results on "Rating","Date posted", etc.

    I guess, using a sql cursor in the stored procedure to loop through each matched job and then calculating & assigning rating to each job would be pretty resource intensive..more so, if the number of records are too high.

    So, any ideas on how to go about efficiently designing this kind of application?...how do i optimally search and fetch all the relevant records alongwith "rating" from the database without having a significant performance hit??

    I am not looking at absolute solutions, i would appreciate some good ideas :)

    Thanks.

    Simona
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I dont think so . That is a good method even. There are some methods .

    1. Use a refcursor . Open it with appropriate select statement and return the refcursor. See Example

    2. Use a cursor . Open it with appropriate select statement and store in the pl/sql table and process the records.

    3. Use Table FUNCTION to return the record set.