Re: Urgent pls: Poor performance on update/insert
From: Victor Koch ("Victor)
Date: 01/27/05
- Next message: Victor Koch: "Re: JET CREATE TEMPORARY TABLE is Syntax Error"
- 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 09:55:24 -0300
Hi Aykut, try this
cn.Open ConnectionString
cn.BeginTrans
For a = 1 To RoomCount
CurrentRecordGuidid = myarray(a) ' get record guidid to be updated
or add
sqlstr = "SELECT * FROM rackrates WHERE guidid='" &
CurrentRecordGuidid & "'"
rst.Open sqlstr, cn, adOpenDynamic, adLockOptimistic
If rst.BOF And rst.EOF Then ' means no record, let's add one
rst.AddNew
rst("guidid") = CurrentRecordGuidid
End If
rst("name") = "something"
' .... update other fields
rst.Update
rst.close
Next a
cn.CommitTrans
--
Víctor Koch.
"Aykut Canturk" <aykut.canturk@karina-mira.com> escribió en el mensaje
news:uxJJxZGBFHA.1404@TK2MSFTNGP11.phx.gbl...
> 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
>
>
>
- Next message: Victor Koch: "Re: JET CREATE TEMPORARY TABLE is Syntax Error"
- 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 ]
Relevant Pages
|
|
