从VB.NET在SQL Server中快速搜索 [英] Fast Searching in SQL Server from VB.NET

问题描述

大家好...

关于从包含至少45,00,000到50,00,000条记录的巨大表中搜索记录，我有一个独特的问题.

桌面应用程序.
开发工具:SQL Server 2005，Framework 2.0
语言:VB.NET

现在的问题是，当我假设要在表中插入新记录时，有1,00,000条记录需要检查重复性.为此，我创建了一个函数:

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

函数返回true或false&根据该记录，将其添加到数据表中.
此函数为每个记录调用in循环，这非常耗时.
有什么方法可以节省检查表中ID的时间.
如果有的话，请让我知道...
感谢Adv ...

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

推荐答案

您好，Harshad，

您可以创建一个StoredProcedure并在该StoredProcedure中进行整个重复检查和插入，然后在您的代码中调用该过程.

顺便说一下，让SQL Server处理ID.为什么要打扰自己?

祝你好运

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

我根本看不出检查唯一性的原因.如果ID字段本质上是唯一的，则应在数据库中将其定义为唯一.请参阅:唯一约束 [

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.

您可以在SQL数据库中使用SqlDataReader进行快速搜索.
有关更多帮助，请参见: http://www.startvbdotnet.com/ado/sqlserver.aspx [ ^ ]
http://www.dotnetheaven.com/UploadFile/skrishnasamy/SQLPerformanceTunning03112005044423AM/SQLPerformanceTunning.aspx [ ^ ]

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[^]

这篇关于从VB.NET在SQL Server中快速搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！