SQL query slow down on first attempt.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL query slow down on first attempt.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-24 : 08:40:17

Ethan writes "Hi,

I have a question regarding to the SQL server performance, and desperated need some help from the team. Here is what I have:

The SQL statements take 1 second to return on SQL Analizer, even if I flush the cache. But on our application, the first time we run the query took about 5 mins. And if we rerun the query, it took 1 second. Until we flush the cache, rerun the query will slow again.

It doesn't seem like a network issue, like I said, rerun the query on AP only took 1 second to get result.

Please let me know if you have any thought.
Thanks

Ethan"

Kristen
Test

22859 Posts

Posted - 2006-01-24 : 08:48:18

"SQL statements"

Are there lots of statements?

Are you sending them one-by-one from your application, or as a single batch?

Are you using Stored Procedures?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-24 : 08:53:15

>>the first time we run the query took about 5 mins. And if we rerun the query, it took 1 second.

If you used Stored Procedure, then first time it takes time to cache the execution plan so that next time it will be faster

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-01-24 : 10:34:43

not 5 minutes.

Kristen
Test

22859 Posts

Posted - 2006-01-24 : 13:42:56

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-01-25 : 08:58:11

I mean caching execution plan doesn't make 5 minutes difference.

Data caching usually makes more difference. To be sure all data are flashed out, you have to execute checkpoint to have dirty pages written to the disk. Here is the script:


checkpoint
go
dbcc dropCleanBuffers
go
DBCC FREEPROCCACHE
go

(don't run that on the production server)

Another possilbe reason may be that there are different connection settings between QA and application that produce different execution plans.

Kristen
Test

22859 Posts

Posted - 2006-01-25 : 09:00:56

"I mean caching execution plan doesn't make 5 minutes difference."

Ah ... I see what you mean now. Was just being thick! Sorry.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-01-25 : 09:10:39

no problem.

Jothikannan
Starting Member

36 Posts

Posted - 2006-01-25 : 10:33:27

r u Using any complex views in that SP?