The join in the recursive element was reversed

with AMASST

as

(

Select 'Asset1' as Astno,

'Masterasset1' as mstno

union all

Select 'Asset2','Masterasset1'

union all

select 'Asset3','Asset2'

union all

select 'Asset4','Asset2'

)

,Sites (mstno, astno,rootasst)

AS

(

...

I think your confusion comes from not defining your anchor element(s) correctly

USE [cap100]

go

WITH Sites (mstno, astno,rootasst)

AS

(

-- Anchor member definition

SELECT mstno, astno ,astno...

All this sort of syntax

JDAReceipts.PartnerInvoiceNo = ISNULL(@PartnerInvoiceNo,JDAReceipts.PartnerInvoiceNo)

Will , pretty much guarantee table scans ,

try Gails page here http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ for more info.

Edit--

Also you have a few scalar udfs...

Sorry , im a bit lost by your requirements can you post a simpler example ( I wont be offended if you feel its to simple and treat me like...

ankur003 (5/3/2010)

---

I just tested some SPs and found that after using NOLOCK the execution time of the queries reduced from 35 to 31 secs approx.... Now this is very tempting......

Here's one other side effect

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/04/23/increasing-deadlocks-with-nolock.aspx

You can always mark you share for my attention, ill happilly...

Well this would fall under the great caveat of "It depends".

The cross apply the kingston gave *could* perform better against a wider selection of rows.

What indexes do you have on...

Sorry , i lost track of this...

Can you post some code that demonstrates the problem you are having?

One method would be

with cteTracks

as(

select Trip.id, TrackPoint.lat, TrackPoint.lon,ROW_NUMBER() over ( partition by Trip.id order by trackpoint.id) as RowN

from TrackPoint

join Track on Track.id = TrackPoint.trackID

...

When i have to do a cursor i structure it like this , so this is not a issue

Declare ....

Open

While(0=0) begin

Fetch....

if(@@Fetch_status<>0) begin

...

Apologies for the size of this.....

Hmm , although i hear what you are saying comparing the Inline to the Scalar

Consider this

Drop FUNCTION dbo.pr_LunarPhaseITVF

go

CREATE FUNCTION dbo.pr_LunarPhaseITVF(@Year integer,@Month integer,@Day integer)

returns table

as

return(

...

Hmmm

Removing the Ip calculation and just setting it to zero , brings the run time crashing down to 600 - 700 ms..

As crazy as it sounds, Im seeing the same as Lynn at the moment.

This is running...

As i said, you are only accounting a single row...

Try this...

CREATE TRIGGER [dbo].[TriggerTestforDelete]

ON [dbo].[TriggerTest]

FOR DELETE

AS

INSERT INTO ArchivePrice(ID,Price)

SELECT d.ID, d.Price

FROM deleted d