Dynamic Sql Intermittenetly Slow - Losing Hair Fast

Hi guys,
I've been staring at this stored proc for hours and I just can't see
what wrong with it. It will run fine 40+ times in a row and then one
time it it will take up-to 15 seconds to complete. Am baffled.
Anyway here's the query. It basically says, select all the hotel rates
where the option is in the OptionList parameter and the agentid = x and
the AgentPassword is equal to x. There is an added complication in that
I have to filter out any HotelRate that is zero for the selected room
types (single, double etc)
Any help appreciated!
Cheers, Pete (lad4bear)
CREATE PROCEDURE [dbo].[SelectMatchingHotelRates]
@.AgentId nvarchar(50),
@.AgentPassword nvarchar(50),
@.OptionList nvarchar(3900),
@.Single int,
@.Double int,
@.Twin int,
@.Triple int
AS
BEGIN
DECLARE @.dynamicSql nvarchar(4000)
SET @.dynamicSql =
'SELECT *
FROM
HotelsOptionRates
WHERE
[HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
AND [HotelOptionRates_AgentId] = ''' + @.AgentId + '''
AND [HotelOptionRates_AgentPassword] = ''' + @.AgentPassword + ''''
IF (@.Single > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_SingleRate]
<> 0'
IF (@.Double > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_DoubleRate]
<> 0'
IF (@.Twin > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TwinRate]
<> 0'
IF (@.Triple > 0)
SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TripleRate]
<> 0'
EXECUTE(@.dynamicSql)
END
GO<lad4bear@.gmail.com> wrote in message
news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I've been staring at this stored proc for hours and I just can't see
> what wrong with it. It will run fine 40+ times in a row and then one
> time it it will take up-to 15 seconds to complete. Am baffled.
>
What is the execution plan when it takes 15 seconds to complete?
David|||I'm pretty sure that you can write the query without using dynamic SQL.
A lot of information at this site:
http://www.sommarskog.se/
Read "Dynamic Seach Conditions" first.
"Arrays and Lists in SQL server" will help you with this part:
> [HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
<lad4bear@.gmail.com> wrote in message
news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> Hi guys,
> I've been staring at this stored proc for hours and I just can't see
> what wrong with it. It will run fine 40+ times in a row and then one
> time it it will take up-to 15 seconds to complete. Am baffled.
> Anyway here's the query. It basically says, select all the hotel rates
> where the option is in the OptionList parameter and the agentid = x and
> the AgentPassword is equal to x. There is an added complication in that
> I have to filter out any HotelRate that is zero for the selected room
> types (single, double etc)
> Any help appreciated!
> Cheers, Pete (lad4bear)
>
> CREATE PROCEDURE [dbo].[SelectMatchingHotelRates]
> @.AgentId nvarchar(50),
> @.AgentPassword nvarchar(50),
> @.OptionList nvarchar(3900),
> @.Single int,
> @.Double int,
> @.Twin int,
> @.Triple int
> AS
> BEGIN
> DECLARE @.dynamicSql nvarchar(4000)
> SET @.dynamicSql =
> 'SELECT *
> FROM
> HotelsOptionRates
> WHERE
> [HotelOptionRates_OptionCode] IN ( ' + @.OptionList + ')
> AND [HotelOptionRates_AgentId] = ''' + @.AgentId + '''
> AND [HotelOptionRates_AgentPassword] = ''' + @.AgentPassword + ''''
> IF (@.Single > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_SingleRate]
> <> 0'
> IF (@.Double > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_DoubleRate]
> <> 0'
> IF (@.Twin > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TwinRate]
> <> 0'
> IF (@.Triple > 0)
> SET @.DynamicSql = @.DynamicSql + ' AND [HotelOptionRates_TripleRate]
> <> 0'
> EXECUTE(@.dynamicSql)
> END
> GO
>|||To test a hypothesis I replaced the dynamic sql with similar (although not
the same) non-dynamic sql. Every forty or so attempts it takes 15 seconds to
complete.
Now currently I don't know if the stored proc itself is taking 15 seconds or
if the line of code I use to call the stored proc is taking 15 seconds. It's
one line of thoroughly tested code so I see no reason to that it should be
causing this problem.
As the database is remote I cannot use sql profiler to see how long the
stored proc is actually taking. Is there another way to get timing
information?
Thanks for your help
Pete (aka lad4bear)
"David Browne" wrote:

> <lad4bear@.gmail.com> wrote in message
> news:1132944029.175652.67830@.f14g2000cwb.googlegroups.com...
> What is the execution plan when it takes 15 seconds to complete?
> David
>
>|||Figured it out. I forgot I had scheduled batch update set to run every 3
minutes which was updating the table. Looks like I was running into some
locking issues. Thanks for the advice and the links
Cheers Pete (aka lad4bear)
"lad4bear" wrote:
> To test a hypothesis I replaced the dynamic sql with similar (although not
> the same) non-dynamic sql. Every forty or so attempts it takes 15 seconds
to
> complete.
> Now currently I don't know if the stored proc itself is taking 15 seconds
or
> if the line of code I use to call the stored proc is taking 15 seconds. It
's
> one line of thoroughly tested code so I see no reason to that it should be
> causing this problem.
> As the database is remote I cannot use sql profiler to see how long the
> stored proc is actually taking. Is there another way to get timing
> information?
> Thanks for your help
> Pete (aka lad4bear)
>
> "David Browne" wrote:
>