Performance Tuning for Large Table 1

[mcconnellj]

I have a large table with 24 million rows. I have one complex update statement that is updating about 15 columns, but it is taking 4 hours to update the entire table.

I noticed when evaluating the table structure, that I have datatypes that are larger than necessary. For example, I have a dec(14,2), but a dec(9,2) will suffice. This would reduce the storage requirement down by 4 bytes.

If I make this change and others I have identified, I estimate that I will decrease the size of the table by 1 - 2 GB.

Should I expect this change will translate into a faster execution of the above mentioned 4 hour update query?

Thanks for your help!

 

[vongrunt]

It depends. While decreased storage space is always welcomed, you may benefit more from "chunked" updates (faq183-3141) and some other tricks. How large is this table initially? And how many rows affects this UPDATE statement? All 24M?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[mcconnellj]

vongrunt,

The table is about 10GB. Yes, the update affects all 24M rows. And you will probably cringe - the update uses about 10 UDFs. However, the UDFs are in the various SET statements and not in the where clause. Also, each one of the UDFs mentioned in the SET statement have case statements that may call 1 - 3 other UDFs.

 

[mcconnellj]

vongrunt,

BTW, the FAQ you referenced appears like it may be relevant. I plan to test it out.

Thanks

 

[vongrunt]

Another note: if some of these UDFs select data from other tables (I hope not), query logic becomes row-based instead of preferred set-based - and execution time gets waaay longer.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[donutman]

Maybe if a cursor was used... Just kidding, I would suggest 1,000 rows at a clip. I think it will make a dramatic difference.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]