Click here to Skip to main content
15,743,541 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
Hi to all...

I have a unique problem regarding searching records from huge table which contains at-least 45,00,000 to 50,00,000 records.

Desktop application.
Development tools: SQL Server 2005, Framework 2.0
Language: VB.NET

Now the problem is, when I suppose to insert new record in table, there are 1,00,000 numbers of records which have to check for duplication. For the same I have created a function:
Public Function ICheck(ByVal ID As String)
        Query = "SELECT ID FROM Table WHERE ID = '" & ID & "'"
        Dim c As String = executeScalar(Query)
        If c <> Nothing Then
            Return (True)
            Exit Function
        End If
        Return (False)
End Function

Function return true or false & according to that record get added in datatable.
This function called in loop for each record, and this is very time consuming.
Is there any way to save the time for checking ID in table.
If any, please let me know...
Thanks in Adv...
Updated 10-Dec-11 1:22am

Hi Harshad,

You can create a StoredProcedure and do the whole duplicate checking and insertion in that StoredProcedure and just call that procedure in you code.

By the way, let SQL server handle IDs. Why you are bothering yourself ?

Good Luck
Share this answer
I don't see the reason for checking the uniqueness at all. If the ID field is unique in nature, you should define it as unique in the database. See: UNIQUE Constraints[^].

Using unique constraints, you would just insert the data. If everything goes fine there were no duplicates. If a duplicate is found, the insert statement generates an error and you can react on that.
Share this answer
Share this answer
Share this answer
Abhinav S 10-Dec-11 9:17am    
Another good suggestion. 5.
RaviRanjanKr 10-Dec-11 9:23am    
Thanks Abhinav :)
You could merge it into 1 query. Use stored procedure instead. Here is the code.

   -- You can do update here, or simply prompt a message saying that item already exists
   -- Item is not yet existing so you can do INSERT query here

Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900