Re: Performance enhancement
From: jpatton (jppatton_at_vt.edu)
Date: 05/12/04
- Next message: Dolphin: "Transaction problem"
- Previous message: Vojta Nadvornik: "row edited by more than one user at same time"
- In reply to: Mike Walsh: "Re: Performance enhancement"
- Next in thread: Mike Walsh: "Re: Performance enhancement"
- Reply: Mike Walsh: "Re: Performance enhancement"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Wed, 12 May 2004 08:46:03 -0400
Ok I am trying to follow you here so I hope I make sense... I am new at this
and the two senior developers I go to with questions are out this week so
you all have been a great help!
I am actually doing a select as you said (by relating the two different data
sources)... here is the actual code:
adoAwrdsQty.SQL.Add( 'SELECT EL_Id, Quantity FROM
EL_Item_Characteristics' );
adoAwrdsQty.SQL.Add( ' WHERE Serial_No =
'+#39+adoGetEndItem.FieldByName( 'Serial_No' ).AsString+#39 );
adoAwrdsQty.SQL.Add( ' AND NIIN = '+#39+adoGetEndItem.FieldByName(
'EI_NIIN' ).AsString+#39 );
adoAwrdsQty.SQL.Add( ' AND EL_Id IN ' );
adoAwrdsQty.SQL.Add( ' ( SELECT EL_Id FROM Equipment_List WHERE
Plan_Id = '+sCurrPlanId+' )' );
So the parameters are being set as the loop is executed.
What if I saved all the information selected in my query and somehow just
copied it over to the dataset in a big update? Is this possible and would it
speed up execution?
"Mike Walsh" <techs.msllib.com@verizon.net> wrote in message
news:40a129c0$1@newsgroups.borland.com...
>
> "jpatton" <jppatton@vt.edu> wrote in message
> news:40a11b8b$1@newsgroups.borland.com...
> > The second from last line was supposed to be adoQuery1.Next; sorry about
> > that.
> >
> > What I have here is a grid that displays items. For each item there are
> two
> > quantity fields... one from the existing database and one from another
> > source. The purpose of the tool is to allow the user to choose which
> > quantity is correct and then update their existing database. I guess the
> > adoDataSet is a sort of temporary storage that updates a temporary table
> > however, this looping structure is what is taking so long.
> > I am not sure about the BatchOptimistic... etc. I am not familiar with
> > those. I was just asked to speed this up and identified this as the
> > bottleneck.
> >
>
> Like Vitali said originally, the looping is inefficient. You're retrieving
> information just to discard it and update with new values. I don't use
> sybase, but it should be similar to other DBMS for this to work. Since you
> say that the adoDataSet is opening a temp table, why not do something like
> (I'm using your component names for table names since I don't know them)
>
> insert into adoDataSet (Field1)
> select Something from adoTable1, adoTable where (somehow they relate)
>
> If there isn't a sybase temp table that you're updating, but instead some
> other thing that sybase can't access, then update your temp table with a
> parameterized insert statement.
>
> insert into adoDataSet (Field1) values (:Field1Value)
>
> As you loop thru your other datasets, simply set the parameter value, and
> execute the sql instead of opening it. You also might want to rethink the
> queries used in adoQuery1 and adoQuery2. You should be able to come up
with
> a single sql statement that will retrieve the records you want. Without
> knowing more information, I can't help with that
>
> Mike Walsh
>
>
- Next message: Dolphin: "Transaction problem"
- Previous message: Vojta Nadvornik: "row edited by more than one user at same time"
- In reply to: Mike Walsh: "Re: Performance enhancement"
- Next in thread: Mike Walsh: "Re: Performance enhancement"
- Reply: Mike Walsh: "Re: Performance enhancement"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
