May 2, 2003 at 5:05 am
hello
I have a procdure that runs the below code. previous to this it creates all the tables used in the query below and populates them. If I run all the SQL statements individually then it all runs ok in about a minute. Also if I strip out the creation and population of the tabs into a seperate proc and put the below in a new proc and run it after it runs ok.
However if I run it as one procedure or together in one continuos query analyzer window it hangs when it gets to the below.
It appears to be creating a huge no of exclusive EXT type locks on the tempdb database.
Any ideas why this might be happening?
thanks
INSERT INTO test_tmp(
sdept_key,
fin_form_key,
day_key,
fin_week_key,
fin_week_comm,
fin_month_key,
fin_month_no,
fin_year_key,
store_key,
retail_value,
local_value)
SELECT
h.sdept_key,
h.fin_form_key,
w.day_key,
w.fin_week_key,
w.fin_week_comm,
w.fin_month_key,
w.fin_month_no,
w.fin_year_key,
st.store_key,
sum(isnull(sw.retail_value,0)),
sum(isnull(sw.local_value,0))
FROM
tmp1 sw
right outer join day_store_keys as st on st.store_key = sw.store_key
right outer join day_keys as w on w.fin_week_key = sw.fin_week_key
AND w.day_key = sw.day_key
right outer join day_hier_keys as h on h.fin_form_key = sw.fin_form_key
right outer join server.db.owner.sclass_struct as sc on sc.sclass_key = sw.sclass_key
AND h.sdept_key = sc.sdept_key
GROUP BY
h.sdept_key,
h.fin_form_key,
w.day_key,
w.fin_week_key,
w.fin_week_comm,
w.fin_month_key,
w.fin_month_no,
w.fin_year_key,
st.store_key
May 5, 2003 at 8:00 am
This was removed by the editor as SPAM
May 5, 2003 at 3:13 pm
My only thought is that the server is picking the wrong execution plan due to the fact that the tables are being created AND populated in the same proc. By populating the tables with a large amount of data, the execution plan may be picking a table scan at first, when an index seek/scan would be a better idea. I would recommend putting the code that populates the tables into its own proc, and running an UPDATE STATISTICS in the same proc on the tables after they are filled with data. Then, you may need to use the "WITH RECOMPILE" clause on the 2nd proc that you listed that inserts into the test_tmp table.
-Dan
-Dan
May 6, 2003 at 9:59 am
I did run update stats after populating all the tables and also created the proc with recompile, but still no go!
I split out the creation of tables and population and it now works ok. If I monitor what's going on in Ent Manager it doesn't create the locks on tempdb.
thanks for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply