Archives of the TeradataForum

Message Posted: Mon, 04 Nov 2002 @ 13:40:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I doubt that it's procedure caching, per se, that's causing the performance improvement in SQL Server on the second run of the query. It seems more likely to me that the performance improvement would be due to data caching effects.

The performance results you cite are typical of situations I have encountered on SQL Server where the data necessary to execute the query is brought into cache and is available for subsequent runs of the query.

The first time the query is processed, it runs slowly, the second run goes like lightning. Having the data in memory makes a tremendous difference to SQL Server. You see the most marked difference when you process a query that involves a scan of one or more of the tables in question.

In fact, you could do something like this:

select getdate()
go
select sum() from
go
select getdate()
go
select sum() from
go
select getdate()
go

on a table that fits into memory and compare the first processing time of the query to the second. If there's no other use of the server, you should see a dramatic improvement from the first to the second runs.

	<Prev	Next>		<<First	<Prev	Next>	Last>>