Charles,

>That's the first time I've heard that (12 table join being 'not that excessive' that is).

SQL server is a different beast entirely, I don't advocate do everything in one SQL statement just because you can, from either a performance or maintenence perspective.

If things are clearer or faster to execute if they are broken into separate smaller statements then go for it. But you really do have to watch for contention issues in tempdb, it's a finite resource used by all the connections.

>I took your advice and there is one table scan in the execution plan and I can't get rid of it. All of the foreign key fields are indexed so I can't figure out what the deal is. Maybe because the table is so small right now (10 records) SQL Server makes the choice just to scan instead of seek, dunno, large table optimization is new to me. My concern is when this table starts collecting the hundreds of thousands of records that is in its future ... what happens then.

SQL will do scans on small tables and that's ok.. it's when table scans on million row tables start happening that you need to go in and fix the code. Unless you don't mind that SP bringing the server to it's knees *g*