Hi Peter, I'm running out of time today but here's some notes.

The entire cross apply bit can - and should - be replaced by a string splitter function. This is...

You're welcome, thanks for the feedback.

Dev @ +91 973 913 6683 (10/20/2011)

---

This is confusing. How can you guarantee the order of rows in a heap?...

Ninja's_RGR'us (10/20/2011)

---

Brandie Tarvin (10/20/2011)

---

Grrrr. Me be troll. "PANTS! PANTSPANTSPANTS! I dare you! GRRRRRR".

And on a more serious note, I need help badly, please. This one has me totally confused and...

This should give you some ideas, Peter.

SELECT

DrugNameString = c.DrugName,

DrugName = x.Item

FROM (

SELECT ClaimID = 123, DrugName = 'Oral Solution Citalopram HBR' UNION ALL

SELECT 124, 'CITALOPRAM TABLETS'...

Blimey! I've only ever found one bug in SQL Server, that was 5 years ago.

Ninja's_RGR'us (10/20/2011)

---

19K+ points. ±5 threads started so far. 3 of them still...

Ninja's_RGR'us (10/20/2011)

---

Ninja's_RGR'us (10/20/2011)

---

adlakha.22 (10/20/2011)

---

@ninja

I know in b/w 8 hours u r usually....see the posts read them...

its lot of work to do...its very time consuming to read all the posts..

I can...

Ninja's_RGR'us (10/20/2011)

---

Keep working with Chris... as I said I'm not doing 8+ hours of free tuning work here for a single issue. ...

Run the tests, post the results.

If there's no significant gain from my last suggestion, a gain which you're happy with, then Remi will take...

Quite possibly Remi, hence the test - but it could be worth it. I'd expect some improvement.

Well it's clear that a huge covering index would improve the performance of this query but the cost of maintaining it would be considerable. Here's a compromise.

CREATE NONCLUSTERED INDEX [IX_Testing]...

So each one of the three takes around 40 to 50 seconds?

Last test: add this index, then run each query again, noting the time each query takes to run:

CREATE...

;WITH First_record_set as (

select x.patientid,row_number() over (partition by x.patientid order by x.observationid) as rid,x.observationid,y.code

from xxx x

Inner Join yyy y on x.observationid = y.observationid

where y.code like 'glucose' and o.patientid =...

Not quite right - let's try again. Here are three...