• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Find Exact match

 
Arun Kumar
Ranch Hand
Posts: 133
Chrome Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The requirement is to receive 20 fields and match the fields against 20 field in data base and if a perfect match is found ..BINGO else need to reply to user ...no match found

On a high level its simple...but looking into the combinations 20 factorial = 2.43290201 × 10(18)

In reality there may not reach this figure but chances of the table grow int o Millions of records, is there, is there a way to find out the match with out a direct comparison
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The number of combinations doesn't come into play. At worst, you'd have to do 20 SELECT statements to find out if all elements are contained in the table. If the table gets large, you'll want to make sure that the table has an index that can be (and actually gets) used for the SELECT.
 
srinivas srinivasmeenavalli
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you'll want to make sure that the table has an index that can be (and actually gets) used for the SELECT

i agree with this.Indexing gives the best performance. You can have a dynamic query builder logic based on search criteria.



this would help you . you can prefer ibatis to create dynamic query . Ibatis suits this kind of requirements if you are coding in server side java application.
 
David Newton
Author
Rancher
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any ORM/JDBC will have a mechanism for creating queries.

Be *very* cautious about using the code above as given, however, unless you've already made sure the variables being used to create the query are cleaned of anything that can be used to create an SQL injection attack.
 
Arun Kumar
Ranch Hand
Posts: 133
Chrome Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Guys

the problem here is not the select query, the table maintenance, if the table grows into say 40-50 thousand rows then the user who maintains this one will go mad

so i was hoping in suct a scenario is there any alogorithm or mathematical formula which will alow me to split the tables into multiple small tables and join then at run time to find a match
 
David Newton
Author
Rancher
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What does the number of rows have to do with the query? We have tables with tens of millions of rows--the queries are the same.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic