mingfattt
asked on
Job schedule Very slow
Hai expert,
Good afternoon, Currently i am encounter with the job schedule performance problem which,
i have the code that run me about 1 hour plus to complete (In Query Analyzer), anyhow when i put it into the job scheduling, i found out that it takes about 7 hours to run, what is going on FYI i already put on the setting like SET NOCOUNT ON, but i am not sure whether it is the right way... Please Help
My Code is some sort of like this
SET NOCOUNT ON
execute U_Shortage_10100_Temp_WIP_
execute U_Shortage_10201_Temp_PO
execute U_Shortage_10301_Temp_IR
execute U_Shortage_10400_Temp_OH
execute U_Shortage_10500_Down_Date
execute U_Shortage_10110_WIP_Sum
execute U_Shortage_10600_All_Parts
delete from Temp_POD
delete from Temp_IRD
i need this badly, since i already struggle and cratching my head for few weeks because of this... and this is tough to test where i need to wait all the time... any idea?
Me,
Yee
Good afternoon, Currently i am encounter with the job schedule performance problem which,
i have the code that run me about 1 hour plus to complete (In Query Analyzer), anyhow when i put it into the job scheduling, i found out that it takes about 7 hours to run, what is going on FYI i already put on the setting like SET NOCOUNT ON, but i am not sure whether it is the right way... Please Help
My Code is some sort of like this
SET NOCOUNT ON
execute U_Shortage_10100_Temp_WIP_
execute U_Shortage_10201_Temp_PO
execute U_Shortage_10301_Temp_IR
execute U_Shortage_10400_Temp_OH
execute U_Shortage_10500_Down_Date
execute U_Shortage_10110_WIP_Sum
execute U_Shortage_10600_All_Parts
delete from Temp_POD
delete from Temp_IRD
i need this badly, since i already struggle and cratching my head for few weeks because of this... and this is tough to test where i need to wait all the time... any idea?
Me,
Yee
ASKER
Well, actually this is just a prior trial on the automation of the system itself... which the job i run it manually and schedule in to run one time, of course running under without any other jobs runing circumstances, but anyhow... this thing 7 hours... its damn long.. just couldn't find out how it is going, well i am just thinking on another way maybe cmdexec or osql? but i never try it before.... please help
I'm not sure which scheduler you're using, but I would certainly recommend running it using SQL Job (Under SQL Agent in Enterprise Manager) as opposed to the windows scheduler.
You can run SQL directly that way.
That doesn't explain why there is a difference when yuo shcedule as opposed to when you run it interactively.
SET NOCOUNT won't make any difference to its speed.
Are you absolutely certiain that the scheduled one takes longer? Is it possible it just had more to do?
You can run SQL directly that way.
That doesn't explain why there is a difference when yuo shcedule as opposed to when you run it interactively.
SET NOCOUNT won't make any difference to its speed.
Are you absolutely certiain that the scheduled one takes longer? Is it possible it just had more to do?
ASKER
Certainly i m using the SQL agent in enterprise manager, well, i do the observation it is really giving me 7 hours with the code above meanwhile i only ran it for 1 hour plus inside the query analyzer... well the nocount function i am actually refering to the next link
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q249/7/30.asp&NoWebContent=1
please helpm, i need this urgently
Thanx for the reply anyway
Me, Yee
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q249/7/30.asp&NoWebContent=1
please helpm, i need this urgently
Thanx for the reply anyway
Me, Yee
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will try it out and let you know later on, Well, any others idea? I am also looking forward to use the CmdExec, but i am not sure whether it can be done in that way... Anyway i truely appreciate your quick reply....
Thanx
Me,Yee
Thanx
Me,Yee
ASKER
another thing is can you explain further on index wizard, fyi i am just into SQL server for two months, thanx
Depending on your queries you can add indexes to speed up selects from your tables.
Indexes take up space but they speed up select queries.
This doesn't explain why its running slower as a job, but its good practice anyway.
If you look up 'indextuning wizard in SQL Books Online, you'll find a lot of information ,including the following description of how to start it.
On the Tools menu, click Wizards.
Expand Management.
Double-click Index Tuning Wizard.
Complete the steps in the wizard.
Indexes take up space but they speed up select queries.
This doesn't explain why its running slower as a job, but its good practice anyway.
If you look up 'indextuning wizard in SQL Books Online, you'll find a lot of information ,including the following description of how to start it.
On the Tools menu, click Wizards.
Expand Management.
Double-click Index Tuning Wizard.
Complete the steps in the wizard.
ASKER
Well, Thanx for your assist... and i make it through the OSQL... thanx
What are the other different things? Is it scheduled to run in the middle of the night (when all the other jobs are running?)
Yo sould probably write to a log in the middle to fnid out if its just one step or if it is spread over all of the steps