Fast Searching in SQL Server from VB.NET

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

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:

VB.NET

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...

Posted 10-Dec-11 1:21am

Harshad-HBK

Updated 10-Dec-11 1:22am

v2

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amir Mahfoozi · Answer 1 · 2011-12-10T01:38:00

Solution 1

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

Posted 10-Dec-11 1:38am

Amir Mahfoozi

Wendelius · Answer 2 · 2011-12-18T11:20:00

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.

**Manoj K Bhoir** · Answer 3 · 2011-12-10T02:30:00

Solution 2

You can use SqlDataReader for Fast Searching in SQL DataBase.
For more help see : http://www.startvbdotnet.com/ado/sqlserver.aspx[^]
http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx[^]

Posted 10-Dec-11 2:30am

Manoj K Bhoir

RaviRanjanKr · Answer 4 · 2011-12-10T03:01:00

Solution 3

Try
Creating Full Text Catalog and Full Text Search[^]

Posted 10-Dec-11 3:01am

RaviRanjanKr

Comments

Abhinav S 10-Dec-11 9:17am

Another good suggestion. 5.

RaviRanjanKr 10-Dec-11 9:23am

Thanks Abhinav :)

Eduard Lu · Answer 5 · 2011-12-10T05:13:00

You could merge it into 1 query. Use stored procedure instead. Here is the code.

SQL

IF EXISTS(SELECT 1 FROM [TableName] WHERE ID = @ID)
BEGIN
   -- You can do update here, or simply prompt a message saying that item already exists
END
ELSE
BEGIN
   -- Item is not yet existing so you can do INSERT query here
END

Regards,
Eduard