Post reply

Need help

  • January 6, 2012 at 2:18 am

    #248600

    Hi Sirs,

    I have a query as below and takes almost a minute to execute. But if i replace IN clause with "=" it hardly takes one sec. Could you please help me why it takes such huge time, I will have to continue with IN because i may get more than one publisher.

    SELECT TOP (1) newsID, Header, CASE WHEN LEN(Header) > 60 THEN stuff(Header, 60, len(Header), '...') ELSE Header END AS ShortHeader,

    PublishedTime

    FROM News n

    JOIN NewsDiscriminator d on d.DiscriminatorID = n.DiscriminatorID

    AND n.DiscriminatorID = 2 --1 = SpUMMot

    AND n.PublisherID IN (SELECT [item] FROM dbo.fn_Split('33',','))

    AND Workflowstate = 4 --StatusCode 4 = published

    ORDER BY PublishedTime DESC

    SELECT TOP (1) newsID, Header, CASE WHEN LEN(Header) > 60 THEN stuff(Header, 60, len(Header), '...') ELSE Header END AS ShortHeader,

    PublishedTime

    FROM News n

    JOIN NewsDiscriminator d on d.DiscriminatorID = n.DiscriminatorID

    AND n.DiscriminatorID = 2 --1 = SpUMMot

    AND n.PublisherID = '33'

    AND Workflowstate = 4 --StatusCode 4 = published

    ORDER BY PublishedTime DESC

    Thanks in Advance,

    Ravi Krishna B.

  • January 6, 2012 at 2:22 am

    #1429483

    How long does this take to run:

    SELECT [item] FROM dbo.fn_Split('33',',')

    Can you post the CREATE statement for this function?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • January 6, 2012 at 2:24 am

    #1429485

    Sorry, I forgot to mention about it it takes no time to return results.

  • January 6, 2012 at 2:38 am

    #1429492

    Can you post the actual plan for both queries - and the code for the function? Looks like the function is being executed once per row and it only needs to be run once per query.

    Two possible quick fixes:

    Run the subquery separately, output the results to a #temp table then use EXISTS in your main query. Since you're using TOP, you could simply join it, but the intermediate working set would be larger.

    Introduce the function using CROSS APPLY.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply