First Transaction Slow

Hi!
I am working on a Delphi 5, SQL Server project. I am running a
process, which processes information in batches and then commits the
information in a database.
The process performs multiple commits on the database in a single run.
My problem is that before the first commit, the process is very slow
but just as the first commit is performed, the process speeds up. The
same block of code, which interacts with the database, processes
information and takes around 2500 ms to complete suddenly starts taking
200 ms. There is no difference in the flow of code before or after the
commit.
I have figured out that this is the problem with the transaction
restart. As soon as the transaction is restarted (committed and then
restored) the process speeds up. But I can't find out the reason why
it is like this, why is the first transaction slow. It even speeds up
if I just abort the first transaction manually and then start a new
one.
Can anybody help me find a reason behind this that why is the
transaction exhibiting such behavior? I need to know why the things
are slow before the first commit. It seems that the transaction
restart has created a drastic effect on the process. But what and why?
Thanks in advance.
P.S: I have debugged it like hell and have made sure that it's not a
code or data specific problem.Could it be physical I/O in the first run, and data cached in subsequent runs? Can be tested doing
CHECKPOINT
DBCC DROPCLEANBUFFERS
Between the runs
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JDee" <jawwad.ali@.gmail.com> wrote in message
news:1119512928.157958.250760@.z14g2000cwz.googlegroups.com...
> Hi!
> I am working on a Delphi 5, SQL Server project. I am running a
> process, which processes information in batches and then commits the
> information in a database.
> The process performs multiple commits on the database in a single run.
> My problem is that before the first commit, the process is very slow
> but just as the first commit is performed, the process speeds up. The
> same block of code, which interacts with the database, processes
> information and takes around 2500 ms to complete suddenly starts taking
> 200 ms. There is no difference in the flow of code before or after the
> commit.
> I have figured out that this is the problem with the transaction
> restart. As soon as the transaction is restarted (committed and then
> restored) the process speeds up. But I can't find out the reason why
> it is like this, why is the first transaction slow. It even speeds up
> if I just abort the first transaction manually and then start a new
> one.
> Can anybody help me find a reason behind this that why is the
> transaction exhibiting such behavior? I need to know why the things
> are slow before the first commit. It seems that the transaction
> restart has created a drastic effect on the process. But what and why?
> Thanks in advance.
> P.S: I have debugged it like hell and have made sure that it's not a
> code or data specific problem.
>