Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
raniait
Starting Member
14 Posts |
 Posted - 2002-11-27 : 01:25:26
|
| HiI am trying to use stored procedure during my insertion (Not bulk one) and updating but it works very slowly it takes may be 10 times the normal Sql,ADO executionKnowing that i am using text field in my tablei was searching for this problem i found this articlehttp://support.microsoft.com/default.aspx?scid=KB;en-us;304365&Can u help me because i am confusedThe following is my procedureCREATE PROC sp_Insert (@nParentID Int, @serialID nvarChar(200),@Title nvarChar(100),@Author nvarChar(100),@Link nvarChar(400),@Keyword nvarChar(100),@IsLeaf bit ,@Date1 datetime,@strFileText text) AS IF EXISTS (SELECT ID FROM Article WHERE Link = @Link)Update Article Set SerialID=@serialID,Title=@Title,Author=@Author,Link=@Link,Keywords=@Keyword,Doc_Text=@strFileText,Doc_Date=@Date1,IsLeaf=@IsLeaf where Link=@LinkelseINSERT Article (ParentID,SerialID,Title,Author,Link,keywords,IsLeaf,Doc_Date,Doc_Text) VALUES (@nParentID, @serialID ,@Title,@Author,@Link,@Keyword,@IsLeaf,@Date1 ,@strFileText)GO |
|
|
sherrer
64 Posts |
Posted - 2002-11-27 : 16:39:59
|
| First it is probably more efficient to not check for the existance of the row, and just delete it instead of updating. Then you can insert your data. Of course you would want to wrap this in a transaction and check for errors after your delete and insert, so that you won't lose any data.Are there any clustered indexes in your table?Are you doing single transactions or is this procedure being called in a loop? |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-28 : 15:21:48
|
| Try using WRITETEXT and UPDATETEXT instead.Sarah Berger MCSD |
|
|
|
raniait
Starting Member
14 Posts |
Posted - 2002-12-11 : 08:54:22
|
| yes i am using the procedure in a loopas i am inserting the content of articles so i am forced to insert one by one not batch insert as the content is so large |
|
|
|
|
|
|
|
|
very slowly insertion procedure