Performance using execute ('QUERY...')

Last Post 18 Dec 2007 07:45 AM by Chris NYC. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
DebianTUX
New Member
New Member

--
21 May 2007 11:37 AM
What are the reasons for
execute ('SOME QUERY')
have a poor performance then
SOME QUERY.

As it is a dynamic query, i supose that it do not have
execution plan cache....
Is there any other?
Some that could realy create a performance issue
in my application?

Thanks
DebianTUX
New Member
New Member

--
21 May 2007 01:20 PM
Hum, that is a big performance issue.

So, why cant it use properly the indexes?
Wouldnt it perform a brand new EP, and do whatever
it need to do so the query run as fast as it (probably)
can?
What if i use directives to force the use of a given join
and etc?

Thanks
SQLUSA
New Member
New Member

--
21 May 2007 04:02 PM
There are some problems with dynamic sql performance.

Can you give us your example script?

If SQL Server reuses the first compiled plan, it may not be optimal for successive calls.

If it keeps compiling, that is not ideal either.

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com
tom27
New Member
New Member

--
12 Oct 2007 02:53 AM
If you need to pass parameters to your query sp_executesql has options for that also.
Chris NYC
New Member
New Member

--
18 Dec 2007 07:45 AM
On a some what related topic. I want to select into a #temp table from a select involving some other #temp tables and pysical tables. Once I get into a larger number of records 100K or more I am running into serious performance issues. Is there a way to set a batch size and "create" this table in memory in pieces?
SQLUSA
New Member
New Member

--
18 Dec 2007 08:18 AM
Yes, but you are responsible for the batching.


Here is an example: http://www.sqlusa.com/bestpractices...ugeupdate/
You are not authorized to post a reply.

Acceptable Use Policy