Filtering Process...

Hi,

I am setting up a page to receive http requests. The page will process the info and then store all the values into SQL database. The problem is the other server may send multiple requests with the same unique ID (up to 10 whithin 5 minutes) and I have to filter out duplicate records.

What will happen is...thousands of requests will come in everyday and everytime the code will have to check the whole table to check if it's already stored before inserting.

1. Would it be a lot of strain on the server?
2. Which of the following SQL statement is more effective? (providing each entry will have a 6 numbers unique ID stored in vchar format and Rdate in date format)

a. strSql = "select RID from tb_message where RID = '" &strid &"'"
b. strSql = "select RID from tb_message where RDATE = '" &date &"'"
strSql = strSql &" and RID = '" &strId &"'"


3. I have never use indexing before, would it be an idea to use it to speed up things? I don't think it will work as the table may change every few seconds but I may be wrong.

Many thanks

James

Why do you have multiple servers submitting duplicate requests? Is it possible to set up one master and replicate out to your `slave` machines?
...
The strain will come only if you have the database server peforming the logic and error checking. Indexing and constraints will catch the violations and throw errors but that is not the most advisable way to handle this.

Hi,

Many thanks for your suggestion.

The other server is actually sending http request upon receival of a SMS message. The request will be made upto 10 times within 10 minutes to ensure we receive it ok and it's up to us to filter out duplicate records.

Each request will come with an unique SMS ID number.

My logic is :

1. get all the values in the querystring
2. Check if smsid already exist in DB table.
3. If not then insert the record.

What do you think? I am just worry that we may get over 10,000 message everyday, which mean upto 100,000 requests will be sent to our server (90,000 of them duplicated). It also means the DB table will need to be checked 100,000 times and doing the insertion 10,000 times.


What do you think?

James