Hi lads,
I'm trying to query a SQL table (which contains more than 100.000 recordsets) in the fastest possible way. This table contains IDs and customer details, similar to these ones:
ID1 ID2 Name Surname
1 1 John Walsh
1 1 Jamie Clint
1 2 Sue Sullivan
2 1 Tom Jacobs
2 2 Judith James
... ... ... ...
My queries are as follows:
SELECT Name FROM MyTable WHERE (ID1 = 2 AND ID2 = 1 AND Name = 'John') OR (ID1 = 2 AND ID2 = 1 AND Name = 'Judith') OR (ID1 = 2 AND ID2 = 1 AND Name = 'Ronan')
so as you can see, I always use the same ID1 and ID2 and the variable part comes to the Name column. To construct my final select statement, I'm looping though a big array which contains the names I'm looking for, ending up in a big select.
My first attempts were with a few thousands of "OR (ID1 = X AND ID2 = Y AND Name = 'Z')", and my SQL server 2000 just timed out after a couple of minutes. After that, I decided to divide the action into chunks, in such a way I did a query with the first one thousand names, then the second group of one thousand and so on. Now I'm getting results for each query but it's taking too long (about a minute) per group of 1000 statements "OR (ID1 = X AND ID2 = Y AND Name = 'Z')". So let's say, If I try to seek 4000 names, it takes about 4 minutes.
Then I thought if adding up autonumeric values to each of my 100.000 recordsets, that could speed up the search. Unfortunately, I'm getting roughly the same time.
The next step could be using stored procedures, but I've never used something like that before. Finally, my last fear is if this will be getting worse in case my table grows up (about 1000 new recordsets per day now). In that case I don't either know the possibility of using truncated tables, but again, I've never worked with such scenarios for DBs.
Could anybody point out the right direction to deal with this scenario?, how should I carry out this kind of selects?
If the final answer is about stored procedures, please, put down an example to show me how to construct this "lengthy" select, together with the DB connection (connection string) and the way to read back the values found. If possible, in VB.NET.
Cheers lads
Start Free Trial
