Cursor required for update?

Hello All,

I am trying to determine the most efficient way of performing an update and am new to SQL Server. Here's what I am trying to do:

1. Get max of numerical address_ID field in Table 1.
2. Update records in Table 2 where homeadd_id is null with results from #1, plus 1 to increment.
3. Back to Table 1 to append a new record with newest homeadd_id number, plus fill in a couple of fields in that row.
4. Continue until all records from 2 are filled in.

I hope that this is clear enough - sorry, it's been a long time since I've coded anything and am just getting my feet wet.

Thanks for any advice.
CarmenYour approach doesnt seem correct.
As soon as you update everything to a new number that is null in table 2 it would be nothing more to update so it can be done only once.
The correct way would be insert record into table 1 with max(address_id) + 1 after record inserted go for inserting records into table 2 with new address_id.

Hope I understood your question correctly.

Good Luck.sql

Cube Design - Number Max of Dimensions

Hi all,

Some cubes into my project have been designed containing 11 dimensions ? Is there a number max of dimensions? The performances look fine so far but I was wondering if it will be a good idea to split these cubes to have a limited number of dimension per cube.

Thanks a lot for your support

Juan

I think you're a long way off hitting the maximum:
http://msdn2.microsoft.com/en-us/library/ms365363.

The only thing you need to look out for when you start adding lots of dimensions to a cube is that aggregation design will take longer and the you're less likely to get good results using the Storage Design Wizard alone - you'll probably need to do Usage Based Optimisation to get the aggregation design you really need. It's also gradually emerging that putting all your data in one cube with multiple measure groups might not be the most efficient way of designing your cubes - see
http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx

HTH,

Chris

|||

Chris,

Many Thanks for your feedback.

The tests of performance (processing of cubes) I have made meet our requirements.

Do you think that there will be an impact in the restitution of these cubes (with a lot of dimensions) via Excel (add-in) ?

Does the cube design with a lot of dimensions have an impact in the restitution performances ?

Thanks again.

Juan

|||

What do you mean by 'restitution', sorry? Do you mean query performance? If so, no there's no reason why a cube with many dimensions should perform worse than a cube with few dimensions, although as I said you need to be more careful with your aggregation design when you have many dimensions in order to get good performance.

Chris

|||

Sorry for my english...

I meant 'browse' the cube thanks to Excel (Add_in).

My tests of performance have been done to measure :

- performance to process cubes

- performance to access cubes with Excel

If I understood well, the design has a direct impact in the processing of the cube but not in the browsing of the cube.

Hope to be clearer...

Cheers

Juan

|||

Well, what I was trying to say was that adding dimensions to the cube doesn't necessarily cause worse query performance so long as you pay attention to your aggregation design. But it would not be true to say that cube design in general has no effect on query performance - it does.

Chris