Free text very slow first time

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 2008 Forums

Transact-SQL (2008)

Free text very slow first time

Author

Topic

sandeep.kumar
Starting Member

7 Posts

Posted - 2011-09-06 : 02:22:26

Hi All,

I am executing the following query in SQL Server 2008 R2.

Select * from table1 where contains(*, 'text1') -> it takes 31 seconds

I am running the same query again and it takes 2 secs

Select * from table1 where contains(*, 'text1') -> it takes 2 seconds

Again I am changing the parameters

Select * from table1 where contains(*, 'text2') -> it also takes 2 seconds

And it takes time only when I am running query for the first time.

Could any body tell me why it is happening for firs time and not second time.

Thanks

Sandy

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-06 : 02:33:17

Data will still be in cache as a result of the
previous execution.

SQL Server will re-use old execution plans if they are
still in the buffer

Karthik
http://karthik4identity.blogspot.com/

sandeep.kumar
Starting Member

7 Posts

Posted - 2011-09-06 : 02:38:40

If the data is stored in cache then it should take same time for
searching another text.

quote:
Originally posted by karthik_padbanaban

Data will still be in cache as a result of the
previous execution.

SQL Server will re-use old execution plans if they are
still in the buffer

Karthik
http://karthik4identity.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-06 : 02:43:22

quote:
Originally posted by sandeep.kumar

If the data is stored in cache then it should take same time for
searching another text.

quote:
Originally posted by karthik_padbanaban

Data will still be in cache as a result of the
previous execution.

SQL Server will re-use old execution plans if they are
still in the buffer

Karthik
http://karthik4identity.blogspot.com/

NO once you change the search text. SQL server will create a New execution plan for that text.
so in that case it cant use the old execution plan which has been created for old text..

Karthik
http://karthik4identity.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-06 : 02:49:02

This link would be very useful to see the how to reuse the execution plan.

http://msdn.microsoft.com/en-us/library/ms175580.aspx

Karthik
http://karthik4identity.blogspot.com/

sandeep.kumar
Starting Member

7 Posts

Posted - 2011-09-06 : 02:59:50

OK, then how do I save the execution plan so it takes less than 2-3 seconds for every search.

quote:
Originally posted by karthik_padbanaban

This link would be very useful to see the how to reuse the execution plan.

http://msdn.microsoft.com/en-us/library/ms175580.aspx

Karthik
http://karthik4identity.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-06 : 03:06:04

there is nothing like you can store your Exceution plan for using it next time.,

but you can use
sp_executesql
SQLBindParameter

for reuse of exceution plan under no schema or data change.

please go throug the link.
http://msdn.microsoft.com/en-us/library/ms175580.aspx

and google more on Execution Plans.

Karthik
http://karthik4identity.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources