Original message from: Raul Chirea
>Anyway, try to learn some SQL bases before asking that kind of things
>(especially the "join between two or more tables" notion and what is an
>"index" and how to use it) !
>You'll save a lot of time, yours and other's.
1) I intensively use indexes
2) I already used joins but my problem is a lot more complicated than this one, I have
to do around 20 intersects between very complicated SQL queries, so rewriting this in
joins is very difficult if not near impossible.
3) I think I have given some very strong arguments in my message.
I have to write a database system for an online employment site and there is a
search with more than 20 criteria. There should be around 10000 candidates after 1
year, but I prefer to be sure the search is very fast with 100000 or 1000000 so that
in the future there will be no problems. For each one of the search criteria I have done simple
tables with 2 columns, one being the index and the other an integer indicating the
candidate identifier. After having done multiple selects, I need to do an intersect.
Of course I can't say it is impossible to write the same thing in joins, but believe me
it would be a lot slower, here's my idea:
Each of the selects returns around 2000 integers. I have a 400 Mhz pc for the
development, but the final machine will be an IBM Netfinity server with several
pentium 3 processors. What I do is a very high quality work and the server must
be able to handle a huge demand.
Now of course every people in this forum will tell me to rewrite the query in a join,
because I gave a simple example, but I could tell you a SQL request that's near
1 page long, between multiple intersects and unions.
Now that's ONE thing that I think nobody here will be able to excuse:
Sorting integers on today's 400+ Mhz pc's, especially 10000 ones, is really fast.
Doing an unique on sorted integers is really fast too.
Doing an intersect on sorted, unique integers is really fast.
So intersecting 2000x2000x3000x2000x5000 on today's 400+ Mhz pc's should
always take less than 1 second (a lot less).
Nobody really answered my question. I did not ask you to tell me how to rewrite
my question, because I already know that, don't think I do not read the docs,
tutorials, etc. I need this fast intersect because if I did not have it the complexity
of the problem would have been multiplied by at least a factor of 10 believe me.
Now listen to me: 13 seconds for intersecting two 2000 element arrays it is a
SHAME.
Dragos Stoichita.
Don't blame a poor student in electronics that's only 19 years old, and look at
the real arguments he gives.