INSERT: WHERE NOT EXISTS issue - timeout error

[Animartis]

Hello all,

I need a tad bit of assistance and am a newb so please take that into
consideration.

I am using ASP to query the folder contents on our Information Server
and want to write the filenames to a table, but only if they do not
exist. Here is the ASP page.

Set MyDirectory=Server.CreateObject("Scripting.FileSystemObject")
Set MyFiles=MyDirectory.GetFolder(Server.MapPath("\flashWork\instVids
\vids"))
Set oSQLConn = Server.CreateObject("ADODB.Connection")
oSQLConn.Open("Driver={SQL
Server};Server=xxxx;Database=xxx;Uid=xxx;Pwd=xxx")

For each filefound in MyFiles.files

sSQL = "sp_addVid '"
sSQL = sSQL + filefound.Name & "'"
'response.write sSQL & "<br>"
oSQLConn.Execute(sSQL)

Next
oSQLConn.close
Set oSQLConn = Nothing

The Stored Procedure is as follows:

CREATE PROCEDURE sp_addVid
(
@vidName varchar(50)
)
AS

BEGIN TRAN
INSERT tbl_vidNames(vidName)
select (@vidName)
where
not exists (select * from tbl_vidNames where vidName = @vidName)
COMMIT TRAN
GO

So here is the problem. This Stored Procedure works fine when i run
it singularly in Query Analyzer, but gives me a timeout error on the
asp page.

Any ideas would be greatly appreciated.

Thanks!

[Jason Lepack]

Why not just create a unique index on tbl_vidNames.vidName and then
just run the insert? If it already exists it will bounce it with a PK
Viloation error which you can then trap on the applciation side.

Cheers,
Jason Lepack

Post by Animartis
Hello all,
I need a tad bit of assistance and am a newb so please take that into
consideration.
I am using ASP to query the folder contents on our Information Server
and want to write the filenames to a table, but only if they do not
exist. Here is the ASP page.
Set MyDirectory=Server.CreateObject("Scripting.FileSystemObject")
Set MyFiles=MyDirectory.GetFolder(Server.MapPath("\flashWork\instVids
\vids"))
Set oSQLConn = Server.CreateObject("ADODB.Connection")
oSQLConn.Open("Driver={SQL
Server};Server=xxxx;Database=xxx;Uid=xxx;Pwd=xxx")
For each filefound in MyFiles.files
sSQL = "sp_addVid '"
sSQL = sSQL + filefound.Name & "'"
'response.write sSQL & "<br>"
oSQLConn.Execute(sSQL)
Next
oSQLConn.close
Set oSQLConn = Nothing
CREATE PROCEDURE sp_addVid
(
@vidName varchar(50)
)
AS
BEGIN TRAN
INSERT tbl_vidNames(vidName)
where
COMMIT TRAN
GO
So here is the problem. This Stored Procedure works fine when i run
it singularly in Query Analyzer, but gives me a timeout error on the
asp page.
Any ideas would be greatly appreciated.
Thanks!

[rpresser]

Post by Animartis
Hello all,
I need a tad bit of assistance and am a newb so please take that into
consideration.
I am using ASP to query the folder contents on our Information Server
and want to write the filenames to a table, but only if they do not
exist.  Here is the ASP page.
So here is the problem.  This Stored Procedure works fine when i run
it singularly in Query Analyzer, but gives me a timeout error on the
asp page.
Any ideas would be greatly appreciated.
Thanks!

As Jason said, make sure you have an index on tbl_vidNames.vidName --
unique would help, since it is what you really want. Next, either try
to insert it and trap a failure, or use this stored procedure:

CREATE PROCEDURE sp_addVid
(
@vidName varchar(50)
)
AS

BEGIN TRAN
IF (SELECT COUNT(*) FROM tbl_vidNames where vidName = @vidName) = 0
THEN
INSERT tbl_vidNames(vidName)
select (@vidName)
COMMIT TRAN
GO

Having the index is essential to speedy execution, no matter what
method you use. NOT EXISTS really slows things down; it would probably
be sped up by the index, but since it isn't really an essential part
of the design, why not dump it?

[Animartis]

Post by rpresser

Post by Animartis
Hello all,
I need a tad bit of assistance and am a newb so please take that into
consideration.
I am using ASP to query the folder contents on our Information Server
and want to write the filenames to a table, but only if they do not
exist. Here is the ASP page.
So here is the problem. This Stored Procedure works fine when i run
it singularly in Query Analyzer, but gives me a timeout error on the
asp page.
Any ideas would be greatly appreciated.
Thanks!

As Jason said, make sure you have an index on tbl_vidNames.vidName --
unique would help, since it is what you really want. Next, either try
CREATE PROCEDURE sp_addVid
(
@vidName varchar(50)
)
AS
BEGIN TRAN
THEN
INSERT tbl_vidNames(vidName)
COMMIT TRAN
GO
Having the index is essential to speedy execution, no matter what
method you use. NOT EXISTS really slows things down; it would probably
be sped up by the index, but since it isn't really an essential part
of the design, why not dump it?

Thank you Gentlemen! This was exactly what I needed. I was sure
there was a more efficient way and just needed a little direction.

Cheers!