Forum Discussion

waddah07's avatar
waddah07
Copper Contributor
Apr 17, 2023

Need Help with speed up update query

Hello Everyone 

 

I have this update query to update Generaljournals table from DIMENSIONATTRIBUTELEVELVALUEVIEW 

Generaljournals table has more than 20M rows 

DIMENSIONATTRIBUTELEVELVALUEVIEW   7M rows 

 

when I run the query it takes around 1hour

 

how can I make the update be better and take less time ?

 

update GENERALJOURNALS set branches = a.DISPLAYVALUE
from DIMENSIONATTRIBUTELEVELVALUEVIEW a
where a.PARTITION = GENERALJOURNALS.PARTITION and
a.VALUECOMBINATIONRECID = GENERALJOURNALS.LEDGERDIMENSION
and a.DIMENSIONATTRIBUTE = '5637145326' and GENERALJOURNALS.branches = ''


update GENERALJOURNALS set mangment = a.DISPLAYVALUE
from DIMENSIONATTRIBUTELEVELVALUEVIEW a
where a.PARTITION = GENERALJOURNALS.PARTITION and
a.VALUECOMBINATIONRECID = GENERALJOURNALS.LEDGERDIMENSION
and a.DIMENSIONATTRIBUTE = '5637145333' and GENERALJOURNALS.mangment=''

 

update GENERALJOURNALS set MainSector = a.DISPLAYVALUE
from DIMENSIONATTRIBUTELEVELVALUEVIEW a
where a.PARTITION = GENERALJOURNALS.PARTITION and
a.VALUECOMBINATIONRECID = GENERALJOURNALS.LEDGERDIMENSION
and a.DIMENSIONATTRIBUTE = '5637145327' and GENERALJOURNALS.MainSector = ''

 

update GENERALJOURNALS set SecondSector = a.DISPLAYVALUE
from DIMENSIONATTRIBUTELEVELVALUEVIEW a
where a.PARTITION = GENERALJOURNALS.PARTITION and
a.VALUECOMBINATIONRECID = GENERALJOURNALS.LEDGERDIMENSION
and a.DIMENSIONATTRIBUTE = '5637145328' and GENERALJOURNALS.SecondSector = ''


update GENERALJOURNALS set ThirdSection = a.DISPLAYVALUE
from DIMENSIONATTRIBUTELEVELVALUEVIEW a
where a.PARTITION = GENERALJOURNALS.PARTITION and
a.VALUECOMBINATIONRECID = GENERALJOURNALS.LEDGERDIMENSION
and a.DIMENSIONATTRIBUTE = '5637145329' and GENERALJOURNALS.ThirdSection = ''

 

update GENERALJOURNALS set Vendor = a.DISPLAYVALUE
from DIMENSIONATTRIBUTELEVELVALUEVIEW a
where a.PARTITION = GENERALJOURNALS.PARTITION and
a.VALUECOMBINATIONRECID = GENERALJOURNALS.LEDGERDIMENSION
and a.DIMENSIONATTRIBUTE = '5637145334' and GENERALJOURNALS.Vendor = ''

 

Resources