Different query times

I have a procedure which pulls the data by batchID. What I
found is batch of 1000 records take 4 minutes while batch
of 6000 records take 1 minute. Same Stored Proc is run on
the same machine.
Anyone can shed light on what can be causing this?
Thanks
Likely, you are seeing data caching - which is a good thing. Try re-running
the first batch and see how long it now takes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Atul" <anonymous@.discussions.microsoft.com> wrote in message
news:1cf6401c422f0$471db080$a401280a@.phx.gbl...
I have a procedure which pulls the data by batchID. What I
found is batch of 1000 records take 4 minutes while batch
of 6000 records take 1 minute. Same Stored Proc is run on
the same machine.
Anyone can shed light on what can be causing this?
Thanks
|||Atul
You mean that stored procedure accepts BatchID as a parameter. Am I right?
One of the options is to create stored procedure WITH RECOMPILE option. Each
time that stored procedure to be run a new execution plan will be created
by query optimizer.
There are lots of postes in this newsgroup about such kind of problems.
"Atul" <anonymous@.discussions.microsoft.com> wrote in message
news:1cf6401c422f0$471db080$a401280a@.phx.gbl...
> I have a procedure which pulls the data by batchID. What I
> found is batch of 1000 records take 4 minutes while batch
> of 6000 records take 1 minute. Same Stored Proc is run on
> the same machine.
> Anyone can shed light on what can be causing this?
> Thanks