Why is this in a loop? Each UPDATE does a full table scan or at least updates all matching records if optimized with indexes.
>Can anyone spead this up! This takes 10 minutes to run on looping through 2000 records several times.
>
>
>DO WHILE !EOF()
>
> UPDATE data;
> SET data.trailer1=aud_trls.trailer;
> WHERE ALLTRIM(data.ACCOUNT)=ALLTRIM(aud_trls.ACCOUNT) AND aud_trls.max_trail=1;
> and alltrim(data.screennum)=alltrim(aud_trls.screennum);
> and alltrim(data.showtime)=alltrim(aud_trls.showtime)
>
> UPDATE data;
> SET data.trailer2=aud_trls.trailer;
> WHERE ALLTRIM(data.ACCOUNT)=ALLTRIM(aud_trls.ACCOUNT) AND aud_trls.max_trail=2;
> and alltrim(data.screennum)=alltrim(aud_trls.screennum);
> and alltrim(data.showtime)=alltrim(aud_trls.showtime)
>
> UPDATE data;
> SET data.trailer3=aud_trls.trailer;
> WHERE ALLTRIM(data.ACCOUNT)=ALLTRIM(aud_trls.ACCOUNT) AND aud_trls.max_trail=3;
> and alltrim(data.screennum)=alltrim(aud_trls.screennum);
> and alltrim(data.showtime)=alltrim(aud_trls.showtime)
>
> UPDATE data;
> SET data.trailer4=aud_trls.trailer;
> WHERE ALLTRIM(data.ACCOUNT)=ALLTRIM(aud_trls.ACCOUNT) AND aud_trls.max_trail=4;
> and alltrim(data.screennum)=alltrim(aud_trls.screennum);
> and alltrim(data.showtime)=alltrim(aud_trls.showtime)
>
> UPDATE data;
> SET data.trailer5=aud_trls.trailer;
> WHERE ALLTRIM(data.ACCOUNT)=ALLTRIM(aud_trls.ACCOUNT) AND aud_trls.max_trail=5;
> and alltrim(data.screennum)=alltrim(aud_trls.screennum);
> and alltrim(data.showtime)=alltrim(aud_trls.showtime)
>
> UPDATE data;
> SET data.trailer6=aud_trls.trailer;
> WHERE ALLTRIM(data.ACCOUNT)=ALLTRIM(aud_trls.ACCOUNT) AND aud_trls.max_trail=6;
> and alltrim(data.screennum)=alltrim(aud_trls.screennum);
> and alltrim(data.showtime)=alltrim(aud_trls.showtime)
>
> UPDATE data;
> SET data.trailer7=aud_trls.trailer;
> WHERE ALLTRIM(data.ACCOUNT)=ALLTRIM(aud_trls.ACCOUNT) AND aud_trls.max_trail=7;
> and alltrim(data.screennum)=alltrim(aud_trls.screennum);
> and alltrim(data.showtime)=alltrim(aud_trls.showtime)
>
> SKIP
>
>ENDDO
>
>
>Thanks,
>Inadvance
Mark McCasland
Midlothian, TX USA