Hi,

1. execute block is quite same as stored procedure. If you know how to write stored procedure then you know how to write execute block
2...
3. then you now have now this?
Where MBH.HEADER_BOOK_CODE = '127518010109038'
If yest then only a liittle improvement can be composite index on 3 fields..

4. One more option can be “merge” statement.

regards,
Karol Bieniaszewski




From: mailto:firebird-support@yahoogroups.com
Sent: Wednesday, October 14, 2015 4:12 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How To Speed Up Update SQL

>>1. EXECUTE BLOCK and there for select and delete

I am completely unaware about execute block. Would you offer any help, so I could learn from it.

>>2. Temp Table
>>3. speed up subselect with expression index on Upper(Trim(MBH.HEADER_BOOK_CODE))

I have taken off Upper(Trim()) functions. All fields in condition which needs paramerter value, I have crated every single index for individual column.

On Wednesday, 14 October 2015 6:23 PM, "liviuslivius liviuslivius@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:





Hi,

you have few ways
1. EXECUTE BLOCK and there for select and delete
2. Temp Table
3. speed up subselect with expression index on Upper(Trim(MBH.HEADER_BOOK_CODE))

regards,
Karol Bieniaszewski


W dniu 2015-10-14 11:21:44 użytkownik Vishal Tiwari vishualsoft@... [firebird-support] <firebird-support@yahoogroups.com> napisał:

Hi All,

I have 480 Update SQL statements which I am executing using ADScript component in Delphi 2010.

Update SQL updates data in only one table called MY_BOOK, but there is a join with MY_BOOK_HEADER table. Below is the Update SQL, which is taking 81 seconds to execute all 480 Update SQL statements. Any idea how could I improve the speed of these 480 Update SQL statements ?

Update SQL:

Update MY_BOOK MB SET BOOK_NAME = 'Book Name1' , BOOK_DESCRIPTION = 'Book Desfcription1'
Where MB.BOOK_TYPE = 4 And
Exists (Select 1 from MY_BOOK_HEADER MBH
Where
Upper(Trim(MBH.HEADER_BOOK_CODE)) = Upper(Trim('127518010109038'))
And MBH.FK_BOOK_GROUP = '{79B79C33-CE56-4084-912B-6DD9F70B3DC4}'
And MBH.PK_BOOK_HEADER = MB.SK_BOOK_HEADER
&n bsp; );


Thanks In Advance.

With Best Regards.

Vishal








[Non-text portions of this message have been removed]