?
Solved

Job schedule Very slow

Posted on 2005-05-05
9
Medium Priority
?
217 Views
Last Modified: 2012-05-05
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_Open
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_Short_In_12Weeks

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
0
Comment
Question by:mingfattt
  • 5
  • 4
9 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13942039
Its very strange that it takes different times between running it from the (windows?) scheduler, and running it interactively.

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
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13942371
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
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13942511
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:mingfattt
ID: 13942832
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
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 1000 total points
ID: 13942848
I can only suggest you try with OSQL as you have a;ready mentioned.

I've never had this problem before.


BTW have you run it all thourhg the index wizrd to ensure there are no indexing issues?
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13942913
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
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13942940
another thing is can you explain further on index wizard, fyi i am just into SQL server for two months, thanx
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13952918
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.

0
 
LVL 2

Author Comment

by:mingfattt
ID: 14015429
Well, Thanx for your assist... and i make it through the OSQL... thanx
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question