Stored Proc: Difference running via a Job or Query Analyzer

[rzirpolo]

I have come across something very strange and is not helping with my investigation of an issue.

I have a stored procedure and when I run it via an exec statement from Query Analyzer it takes 18 mins to complete.

I restored the database to how it was before this procedure was run but this time I put the exec statement in a job and set it to run every 5 mins. It only took 9 mins to run in here when looking at the Job History.

This is really confusing me now ? Which is correct and what would be the reason for the difference in time ?

 

[donutman]

The job runs in 9min but you run it every 5 min?
-Karl

[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLBill]

Besides the obvious that donutman pointed out...

How did you 'restore the database to how it was before'?

Did you really RESTORE it, or did you just make changes that put the database back the way it was?
-Reason: could be an indexing issue. Maybe restoring the database changed the index (defragged it, shrank it...) which would make a script run faster.

Maybe the original query remained in memory (SQL Server will do this) which makes subsequent executes of the same query run faster.

-SQLBill

 

[rzirpolo]

Ok I see your point, I'm investigating a job which normally takes 2 mins to run (hence the schedule is every 5 mins). Why it has taken 9 mins on my local environment I do not know... don't worry about this as it's not the point of the issue. (I shouldn't have mentioned it as I can see your concern).

I just can't see why a job which runs an exec statement is quicker than manually running the same statement from Query Anaylzer ?

In regards to restoring I should have been more descriptive. I deleted the database and re-created using the BAK file.

When you state the original query remained in memory... can I query this anywhere ?

 

[hmckillop]

There are different reasons as to why the exec in the SQL job and Query analyser may produce different results, some of these may the connection options for each.
When you connect via Query Analyser certain options are set-up behind the scenes, things like lock timeout, ansi options (SET ANSI_PADDING ON) etc.
Some of these options we have noticed can affect the query plans and performance.

Going back to SQLBills post, if you restore the database using RESTORE (or enterprise manager), it will restore the database exactly as it was stored including the defragmentation of the indices/tables.
You need to have a look at the performance on your database, check the contig information for the tables in question - e.g. DBCC SHOWCONTIG(Tablename) WITH ALL_INDEXES
Then look at the scan_density for the indices.

A few things to note, unless your query is doing extensive work to millions of rows I wouldnt expect it to take any longer than seconds rather than minutes. Can you give some data sizes and maybe sample code.


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[SQLSister]

hmckillop has a good point, I would first look at how to improve the overall efficiency of the query. Even two minutes is extremely long for a query.

If a query gets longer over time, more than likely the culprit for this behavior is indexes which need updating (or need to be created) or a significant increase in the number of rows processed. Sometimes a process which was acceptable when there were few rows, becomes ridiculously slow, once the item is in production and the number of records increases. This is particularly true if you used a cursor or did not set up appropriate indexes to begin with.