ADO Find Method... Is there a faster way?

[Kinl]

HI all,

I'm trying to search through a database that has over 700 records in it. I need to search through it, and look for a duplicate entry in one of the columns. I have tried looping through the database, and even using the recordset.Find method. But both methods seem to clock very badly!!! Sometimes it will take over 2 -3 minutes for it to process. I need this to process (the find, and then if i dont find anything tha tmatches, update the recordset) in about 3-10 seconds.
I plan to have this database grow to a few thousand entries.
Is there a faster way to to this?

Thanx,

Donn

 

[foxbox]

is this one-time only? What database do you use? You accept duplicate records in your table but do not want them? Why not define a (combination of) field(s) as Primary Key (a database like MS-SQL will not allow duplicate records)? br
Gerard

 

[Kinl]

This is will be for multiple time use. If some one enters in information, it will see if they have tried to enter it in before. A primary key is composed of a bunch of different fields. The DB is SQL-SERVER 7.

d

 

[vasah20]

if you're using SQL Server 7, i would suggest writing a stored proc to handle the insertion of data, and have the SP return a value signifying whether or not the data was inserted.

here's an example:
CREATE PROCEDURE usp_insertData
(
@name varchar(64),
@add varchar(64)
) AS
SET NOCOUNT ON

IF NOT EXISTS (
SELECT name FROM tblMyTable
WHERE name = @name AND address = @add
)
BEGIN
INSERT tblMyTable (name, address) VALUES (@name, @add)
RETURN 1
END
ELSE
RETURN 0
SET NOCOUNT OFF

Then, in your ASP do something like

Dim cmd, retVal
set cmd = server.createobject("adodb.command&quot
with cmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "usp_insertData&quot

.Parameters.Append .CreateParameter("retval",adInteger,adParamReturnValue)
.Parameters.Append .CreateParameter("@name",adVarChar,adParamInput,64,Request.Form("name&quot
.Parameters.Append .CreateParameter
("@add",adVarChar,adParamInput,64,Request.Form("address&quot
.Execute
retval = int(.Parameters("retval&quot)
end with

if retval = 1 then
Response.write "data added!"
else
Response.write "data was not added..."
end if

by doing it this way, you can better isolate the logic, and you can also optimize runtime (since a SP is compiled, and creates an execution plan, the majority of the work sits wiht the DBMS).

hth
leo leo

 

[Kinl]

Thanx alot!