Using Temp Tables to Improve Performance

[glennfishwick]

Is this a good idea:

In an order processing type system we will currently create and order & order_lines as follows:

INSERT INTO orders...
INSERT INTO order_lines...
INSERT INTO order_lines...
INSERT INTO order_lines... <repeated for number of order lines>
< then various other repeated statements>

This obviously gives big performance degradation as the number of order_lines increases.

I was considering using a #Temp table to hold the values that I want to insert into the order_lines table. These values would be inserted as the user adds them on the screen. This will be invisible to the user as performance will be almost instant. Then when i come to insert into order_lines it is possible with one bulk insert statement run from within a stored procedure.

I like the look of this approach but can anyone see any problems with it?

On an order with 50+ lines this gives a performance improvement of several seconds.

Thanks.