Execution order in a SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Execution order in a SP

Author

Topic

raphaelrivest
Starting Member

14 Posts

Posted - 2009-05-28 : 13:13:33

Hello,

I have a huge SP which loads our DB each morning. It goes like that :

INSERT INTO table1
SELECT table1_row1,
table1_row2,
table1_row3
FROM staging_table1;

INSERT INTO table2
SELECT table2_row1,
table2_row2,
table2_row3
FROM staging_table2;

INSERT INTO table_merge
SELECT t1.table1_row1
t1.table1_row2
t2.table2_row3
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON table1_row2 = table2_row2

As you can see table1 and table2 HAVE TO be loaded BEFORE the insert into table_merge.

Sometimes it happens that table_merge miss a few fields that comes from table2. As if table_merge was loaded before the entire load from table2 has finished.

Is there a way to force a specific order with the INSERT into the same SP? I thought that the order chosen in the code of a SP was the same executed on the server. But it doesn't seem to be that way.

Thanks,

Raph.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 13:20:50

sorry didnt understand that. do you mean by the time it runs third insert, the insert into table2 is not complete?

raphaelrivest
Starting Member

14 Posts

Posted - 2009-05-28 : 14:25:21

Hello,

No, everything is completed at the end of it. But I see some fields missing in the third insert. And they come from the second insert. So that's why it looks like the execution order of the inserts is not the same I written in the code.

I hope it's clear ;)

Raph.

quote:
Originally posted by visakh16

sorry didnt understand that. do you mean by the time it runs third insert, the insert into table2 is not complete?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 14:34:01

what do you mean by missing fields? fields with NULL values? If thats case, that might be fields from table2. since you're taking left join the last select returns records which are not in table 2 also. there records will have null values for table2 fields

raphaelrivest
Starting Member

14 Posts

Posted - 2009-05-28 : 15:49:12

Exactly : fields in table_merge with NULL values. And these fields are from table2. The problem is the rows ARE in table2! If I do the same left join AFTER the SP, I'll find the fields with the right values.

raphaelrivest
Starting Member

14 Posts

Posted - 2009-05-29 : 11:03:55

Would it be a good idea to enclose the inserts with BEGIN TRANSACTION t1 / COMMIT TRANSACTION t1?

Raph.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-29 : 14:59:08

quote:
Originally posted by raphaelrivest

Exactly : fields in table_merge with NULL values. And these fields are from table2. The problem is the rows ARE in table2! If I do the same left join AFTER the SP, I'll find the fields with the right values.

can you try after putting WITH (NOLOCK) near table names?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-06-01 : 08:28:19

NOLOCK would make things worse! Sorry but I cannot ever recommend use of NOLOCK and I can't see how that would help.
You are right to think SP statements are executed in order. However if you are loading it non-transactional and asynchronous then you might see that the staging tables are being populated while your SP is being run, which is all I can think of. Either that or your queries to check are using different conditions to the load (chars/varchars?)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources