Another way?

[RobCPA]

We have developed a merge purge application where we attempt to match one incoming name and address to records in another table (4 million records). The address matches consists of 10 different checks with different point levels for each type of match. Currently, we have stored procedures that take each incoming record and run these 10 stored procedures. Of course, that means that we're querying the same 4 million records 10 times which is a real performance hit especially when I have 250,000 incoming records.

These address matches all have zip code as one of the criteria. What I would like to do is to set a particular zip code which would result in records for only that zip, say 30,000 records, and then run the 10 stored procedures against that recordset. I don't want to actually copy those records to a temp table because I think that would take quite a while as well. I had thought of setting up a view, but again the view would run each time for each of the stored procs. Does anyone know if this is possible?

Thanks!

 

[rsinj]

if your table has zip code as an index, using a view (for example) you wouldn't be taking a big hit at all.

moving 30,000 records into a temp table would definitely take some hit.

 

[NoCoolHandle]

I agree with rsinj.. Look into indexing. I would suggest that you "CLuster" on your ZipCode if you are always doing a search with it as part of the query, you should also look to see if there is any way you can test all 10 values in one statement (using an "and" or "join" to get all the results once - and test to see it is a saving).

If you have alot of update activity be carefull of excessive indexing, but if it is for Decision Support.. Multi Column Indexes covering all columns in a query will bring data back at an amazing rate! And you can have a few of them (don't go crazy on indexes - too many don't help, just make a few good ones)

Use the index tuning wizard and run your querys.. see what it suggests..

My 10c

Rob