parse a file and return results - C# & Oracle 8i

[timmoser]

I have to build a site where a user can pass a file of zip codes and gets a result set of the closest office to each zip. The problem I have is performance. No matter what server side language I use, I will have to fire off a query for each zip code in the file. So if I receive a file of 100 zip codes I will have to fire off 100 queries to build the results.

I did a demo, it ran around 4 mins I have experience with a UPS site that receives up to 100 UPS airbills and returns tracking information. This process is very similar to mine, however the UPS site returns records almost as quickly as you request them. Unfortunately UPS won’t let me see their code J.

Another experience I have was with SQL2000 I could pass an xml file to an SP and parse it on the server, this would probably improve performance, but I am using Oracle 8i for this project.

Any ideas on how I could improve performance? Maybe a better way to go about parsing the zips and getting the results?

Thanks for your thoughts.

 

[cappmgr]

Why a hundred queries? Couldn't you pass in the zip codes as a csv into a stored procedure? Check out Oracles DBMS_UTILITY.COMMA_TO_TABLE.
Marty

 

[obislavu]

I put the same question as cappmgr.
1. Send all zip codes in a string to a stored procedure
2. Perform all required tasks in that sp and return the selected records. In that sp you also can call other sprocs if there are complex search.
3. For performance I think you should take a look at:
- the tables on which search is and create indexes as nedded.
- review the select statements and optimize.

-obislavu-