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

<lad4b***@gmail.com> wrote in message
What is the execution plan when it takes 15 seconds to complete?

David

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)



Show quoteHide quote

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)

Show quoteHide quote

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:
<lad4b***@gmail.com> wrote in message
Show quoteHide quote

Trasform columns to rows
Still struggling w/ LAST transaction date!!!
DISTINCT MonthName for a lot of dates....
Dynamic Stored Procedure
selecting from a 6 month date range