Re: Urgent pls: Poor performance on update/insert

• Previous message: Aykut Canturk: "Urgent pls: Poor performance on update/insert"
• In reply to: Aykut Canturk: "Urgent pls: Poor performance on update/insert"
• Messages sorted by: [ date ] [ thread ]

Date: Thu, 27 Jan 2005 16:46:45 +0100

Hello,

Personally, I would create a stored procedure like that and use the
procedure to make the insert / Update

Create procedure .....
as
SET NOCOUNT ON
Update Table Set ...
Where ...
IF @@RowCount = 0
  Insert into Table.....
  Values (....)

Marc Allard
Allcomp

Aykut Canturk wrote:
> I need to update a set of records in SQL server table.
> But due to neccessities, the number of records on the screen can be
> different from the number of records in database.
>
> If a record exits on database, I need to update it. if it doesn't I need to
> insert a new one
>
> the sample code is below. but the performance is very poor. I works a little
> slow on development computer. I mean application and SQL server are both
> installed on same PC. but it you separete them and take SQL server to
> another PC on network (which is full dublex 100mbps) performace becomes very
> boring stages. even for 20 records (600 total records on table) this
> procedure waits very very long times like 10 to 20 seconds.
>
> Any idea about how can I make this routine faster ? for example with single
> sql statement or someting.
>
>
> PS: guidid field is the key field on this table.
>
>
> cn.Open ConnectionString
>
> cn.BeginTrans
>
> sqlstr = "SELECT * FROM rackrates"
>
> rst.Open sqlstr, cn, adOpenDynamic, adLockOptimistic
>
> For a = 1 To RoomCount
>
> CurrentRecordGuidid = myarray(a) ' get record guidid to be updated or
> added from an array
>
> rst.Filter = "guidid='" & CurrentRecordGuidid & "' "
>
> If rst.BOF And rst.EOF Then ' means no record, let's add one
> rst.AddNew
> End If
>
> rst("guidid") = CurrentRecordGuidid
>
> rst("name") = "something"
>
> ' .... update other fields
>
> rst.Update
> Next
> cn.CommitTrans
>
>
>

• Previous message: Aykut Canturk: "Urgent pls: Poor performance on update/insert"
• In reply to: Aykut Canturk: "Urgent pls: Poor performance on update/insert"
• Messages sorted by: [ date ] [ thread ]