I don't know the number of rows it will return, but your non-ansi query translates to this:

select Count(*)

from dbo.it_1_Revenue__Costs rev

left outer join dbo.et_Summary_Income_Statement inc

...

COldCoffee (4/16/2010)

---

Thanks Gianluca.. what if i dont have a order by clause? still the top operator is processed only after getting the result set?

Do u think SET ROWCOUNT will...

Looking at the exec plan, you will see that the TOP operator is applied as last.

Looking at the estimated row counts, you'll see that it can actually be applied as...

I don't know what would Jeff do and it probably would be better than whatever I my suggest...

That said, here's my two cents:

-- Hope Jeff doesn't mind I borrowed his...

I forgot to add one line of code to the function... sorry.

ALTER FUNCTION dbo.FirstToken(@Parameter VARCHAR(8000))

RETURNS varchar(500)

AS

BEGIN

SET @Parameter = ',' + @Parameter + ','

...

Take a look at that article, it's an eye-opener. I'm sure you'll find it enlightening.

I have simply created...

This should do the trick:

CREATE FUNCTION dbo.FirstToken(@Parameter VARCHAR(8000))

RETURNS varchar(500)

AS

BEGIN

RETURN (

SELECT TOP 1 SUBSTRING(@Parameter,N+2,CHARINDEX(',',@Parameter,N+1)-N-2)

FROM dbo.Tally

WHERE N < LEN(@Parameter)

AND SUBSTRING(@Parameter,N,1) = ','

ORDER BY N

)

END

CREATE TABLE Test (

valueList varchar(8000),

firstValue as dbo.FirstToken(valueList)

)

INSERT INTO...

Creating indexes will slow down writes, but will improve read performance.

What exactly is unclear to you of the above?

I could say at a first glance that you are using a #temp table that could be avoided using a CTE, but there's much more that could be involved in...

I could say at a first glance that you are using a #temp table that could be avoided using a CTE, but there's much more that could be involved in...

With the information you provided it's impossible to give an answer.

You could start by reading this[/url].

Index fragmentation or statistics not up to date cannot be the cause of such an issue, if the destination table(s) is only populated by the job. Index fragmentation an outdated...

Paul White NZ (4/14/2010)

---

Thanks very much, Stefan. ...

Stefan_G (4/13/2010)

---

I dont know why your numbers are so strange. Perhaps your system was heavily loaded ?

I ran the same test on my unloaded 2-CPU laptop and I got the...

First of all, thank you for you reply, Paul.

Very informative and precise, as usual.

I forgot to mention that I ran these tests on my dev server (8 dual core...