|
Home > Archive > MS SQL Data Warehousing > November 2006 > sp_executesql
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Erencan SAŠIROŠLU 2006-11-01, 7:12 pm |
| Hi To All,
I have a question. I execute two query in seperate times with sp_executesql.
The first query executes with parameters, the second quert executes without
parameters.
In normally the first query (With parameter) has to execute too fast than
second query. But the first query's performance very poor. Microsoft says
that sp_executesql with parameter performance is good because sql server
optimize query with excution plan.
I say again. These querys are same. But just one of them with parameters and
other query not with parameters.
Do you have a suggest for me pls?
| |
| John Bell 2006-11-01, 7:12 pm |
| Hi
THis will depend on what and how you are executing, posting example calls
and the code for the procdures that you are running may be useful, see
http://www.aspfaq.com/etiquette.asp?id=5006 It could be that the first query
is being compiled where as the second query is using the already created
execution plan. What happens if you subsequently run the query with
parameters? Another alternative is that you have a parameter sniffing
problem, this may mean that the query with parameters will usually run slowly.
John
"Erencan SAĆIROĆLU" wrote:
> Hi To All,
> I have a question. I execute two query in seperate times with sp_executesql.
> The first query executes with parameters, the second quert executes without
> parameters.
> In normally the first query (With parameter) has to execute too fast than
> second query. But the first query's performance very poor. Microsoft says
> that sp_executesql with parameter performance is good because sql server
> optimize query with excution plan.
> I say again. These querys are same. But just one of them with parameters and
> other query not with parameters.
>
> Do you have a suggest for me pls?
>
>
>
>
| |
| Mike C# 2006-11-02, 12:12 am |
|
"Erencan SAŠIROŠLU" <erencans@hotmail.com> wrote in message
news:%239TS7gd$GHA.3396@TK2MSFTNGP02.phx.gbl...
> Hi To All,
> I have a question. I execute two query in seperate times with
> sp_executesql.
> The first query executes with parameters, the second quert executes
> without parameters.
> In normally the first query (With parameter) has to execute too fast than
> second query. But the first query's performance very poor. Microsoft says
> that sp_executesql with parameter performance is good because sql server
> optimize query with excution plan.
> I say again. These querys are same. But just one of them with parameters
> and other query not with parameters.
>
> Do you have a suggest for me pls?
I suggest posting examples.
Is the query without parameters being executed with exactly the same query
string every time? The optimization you're talking about with
parameterization comes from cached query execution plan re-use. If you
execute a concatenated string as a dynamic SQL query you won't get query
execution plan re-use unless the query strings are exactly the same each
time, which is highly unlikely (otherwise you wouldn't need dynamic SQL).
| |
| Erencan SAŠIROŠLU 2006-11-02, 5:12 am |
| Hi Mike And John,
Thanks for your answers.
I've found what's my problem. The problem was that when i execute dynamic
sql parameters is nchar type.
But parameter's field type in my table varchar. And my table has 2,5 million
row.
When i execute like this query so sql server try to convert nvarchar to
varchar for every row. So this is very poor
performance.
I happy now. I've fixed the problem.
Thanks again.
"Mike C#" <xyz@xyz.com> wrote in message
news:3Le2h.87$FD2.30@newsfe11.lga...
>
> "Erencan SAŠIROŠLU" <erencans@hotmail.com> wrote in message
> news:%239TS7gd$GHA.3396@TK2MSFTNGP02.phx.gbl...
>
> I suggest posting examples.
>
> Is the query without parameters being executed with exactly the same query
> string every time? The optimization you're talking about with
> parameterization comes from cached query execution plan re-use. If you
> execute a concatenated string as a dynamic SQL query you won't get query
> execution plan re-use unless the query strings are exactly the same each
> time, which is highly unlikely (otherwise you wouldn't need dynamic SQL).
>
| |
| Arnie Rowland 2006-11-02, 7:12 pm |
| Thanks for following up your own post. It prevents others from wasting their
time trying to help you after you have solved the problem, and it helps
others when you share your solution.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Erencan SAŠIROŠLU" <erencans@hotmail.com> wrote in message
news:%23XfQ97l$GHA.996@TK2MSFTNGP02.phx.gbl...
> Hi Mike And John,
> Thanks for your answers.
> I've found what's my problem. The problem was that when i execute dynamic
> sql parameters is nchar type.
> But parameter's field type in my table varchar. And my table has 2,5
> million row.
> When i execute like this query so sql server try to convert nvarchar to
> varchar for every row. So this is very poor
> performance.
> I happy now. I've fixed the problem.
> Thanks again.
>
> "Mike C#" <xyz@xyz.com> wrote in message
> news:3Le2h.87$FD2.30@newsfe11.lga...
>
>
|
|
|
|
|