It's baaack

I've been chasing a performance issue on and off for several ws. A stored
procedure in a custom application will run in under 30 seconds or it may tak
e
as long as 6 hours to run. I've got a stack of execution plans on this
procedure and sometimes the procedure uses a good plan complete with
parallelism and other times the optimizer decides to take the long way
through the data and use a serial plan and just crunch hundreds of millions
of records.
I got some help from this board several ws ago and learned about
parameter sniffing and found that local variables and defaults helped the
optimizer choose the good plan and incorporate parallelism. We thought we ha
d
it solved until last w, when the optimizer began choosing the bad plan
again. So, we reindexed, updated stats on key tables with full scans and tha
t
solved it.
It's now morphed again and doesn' respond to previous remedies. For whatever
reason, the optimizer insists on taking the long way. I really need to
rewrite this procedure but need a quick fix AND I'd like to understand what'
s
going on. The procedure includes views joined to views which include views
(it's ugly) as well as joins to base tables and I know the confusion is the
order in which tables get joined and processed. So, I reordered the join
syntax in the SP and used a force order option. Now I get the good plan agai
n
complete with parallelism... for now until it morphs again.
Do views prevent the optimizer from making the best use of statiscs? Why
doesn't the optimizer always use parallelism..the cost setting is set to 5-
default and I set it down to 1 and it still didn't use parallelism?
Thanks for any insight
MardyWhether the actual query uses parallelism is determined at run time each and
every time the query is run. This is based on several factors such as how
busy the cpu's are, the amount of available memory etc. A complex query
will usually generate a serial and parallel plan but that is only the
initial plan. At run time the engine decides which to run. So running a
query with a single proc or more than 1 proc can be different each time it
is run. With that said a very complex query with lots of views, joins etc
can be tough to crunch. There are times when the optimizer simply gives up
and says this plan will have to do even though there may be a more optimal
plan that can be generated. When you have lots of objects (tables, views,
indexes) to deal with the cost of trying out each and every possibility can
get too prohibitive. The more objects you have the choices become somewhat
exponential. I have seen very complex sp's take a long time to compile and
not even have the most optimal plan. The solution is to rewrite your
queries. Probably breaking it down into multiple individual sp's. That
way each sp can be optimized rather quickly and is usually easier to tune.
Hope that helps.
Andrew J. Kelly SQL MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:37BD8EB7-2457-4742-B250-EE33CCE3DEA7@.microsoft.com...
> I've been chasing a performance issue on and off for several ws. A
> stored
> procedure in a custom application will run in under 30 seconds or it may
> take
> as long as 6 hours to run. I've got a stack of execution plans on this
> procedure and sometimes the procedure uses a good plan complete with
> parallelism and other times the optimizer decides to take the long way
> through the data and use a serial plan and just crunch hundreds of
> millions
> of records.
> I got some help from this board several ws ago and learned about
> parameter sniffing and found that local variables and defaults helped the
> optimizer choose the good plan and incorporate parallelism. We thought we
> had
> it solved until last w, when the optimizer began choosing the bad plan
> again. So, we reindexed, updated stats on key tables with full scans and
> that
> solved it.
> It's now morphed again and doesn' respond to previous remedies. For
> whatever
> reason, the optimizer insists on taking the long way. I really need to
> rewrite this procedure but need a quick fix AND I'd like to understand
> what's
> going on. The procedure includes views joined to views which include views
> (it's ugly) as well as joins to base tables and I know the confusion is
> the
> order in which tables get joined and processed. So, I reordered the join
> syntax in the SP and used a force order option. Now I get the good plan
> again
> complete with parallelism... for now until it morphs again.
> Do views prevent the optimizer from making the best use of statiscs? Why
> doesn't the optimizer always use parallelism..the cost setting is set to
> 5-
> default and I set it down to 1 and it still didn't use parallelism?
> Thanks for any insight
> Mardy|||If the query contains a lot of sub-queries, then consider inserting the
intermediate results into temporary tables.
Queries of the following style can trip up the optimizer at runtime:
select ... from ... where x in (select ... from ...)
Also, calling UDFs can impede the optimizer.
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:37BD8EB7-2457-4742-B250-EE33CCE3DEA7@.microsoft.com...
> I've been chasing a performance issue on and off for several ws. A
stored
> procedure in a custom application will run in under 30 seconds or it may
take
> as long as 6 hours to run. I've got a stack of execution plans on this
> procedure and sometimes the procedure uses a good plan complete with
> parallelism and other times the optimizer decides to take the long way
> through the data and use a serial plan and just crunch hundreds of
millions
> of records.
> I got some help from this board several ws ago and learned about
> parameter sniffing and found that local variables and defaults helped the
> optimizer choose the good plan and incorporate parallelism. We thought we
had
> it solved until last w, when the optimizer began choosing the bad plan
> again. So, we reindexed, updated stats on key tables with full scans and
that
> solved it.
> It's now morphed again and doesn' respond to previous remedies. For
whatever
> reason, the optimizer insists on taking the long way. I really need to
> rewrite this procedure but need a quick fix AND I'd like to understand
what's
> going on. The procedure includes views joined to views which include views
> (it's ugly) as well as joins to base tables and I know the confusion is
the
> order in which tables get joined and processed. So, I reordered the join
> syntax in the SP and used a force order option. Now I get the good plan
again
> complete with parallelism... for now until it morphs again.
> Do views prevent the optimizer from making the best use of statiscs? Why
> doesn't the optimizer always use parallelism..the cost setting is set to
5-
> default and I set it down to 1 and it still didn't use parallelism?
> Thanks for any insight
> Mardy

Issue with passing a parameter to Stored Procedure using IN keywor

Hello,
A program I've written creates a parameter to be passed to a stored
procedure based on a user's report selection.
A user can select one, two or three locations.
That parameter is used in an IN clause.
"ZMCC.WERKS IN (@.Locations) "
If a user requests a single location, it works just fine. However, when the
user makes multiple selection, the parameter fails.
I've built the parameter so it looks like " '0031', '0032', '0033' " and a
bunch of variations on this, but none of them work
How can I build the parameter and pass it to the stored procedure?
Thx.
Andy JacobsPlenty of suggestions here:
http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||http://www.aspfaq.com/2248
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||This one works for me
set @.StrType = ''''+ replace(@.StrType,',',''',''')+''''
Declare @.SQL varchar(5000)
Set @.SQL=
'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
ReqDate AS [Req Date],RptType AS [Rpt Type],OrderNo AS [Order No],Fee from
#Confirm WHERE RptType IN(' + @.StrType + ')'
--print @.sql
Exec(@.SQL)
END
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||SQL is treating the passed variable as a single string, instead of
interpreting the string as a set.
What you will need to do in your stored procedure is parse the string and
insert the values into a temporary table. Then reference that temporary tabl
e
in your select statement with the IN clause.
See http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" wrote:

> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when th
e
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>