tmoleary (8/27/2012)

---

Also, I am running these tests using data sets that have about 1,000 patients and 130,000 main documents because if I run them with larger data sets in SQL...

Ok, try this query:

SELECT

pat1.TIUDocumentSID,

pat1.ScrSSN,

pat1.Sta3n,

doc.ReferenceDate,

doc.ReportText,

...

tmoleary (8/27/2012)

---

Here are two more query execution plans. SingleQueryExecutionPlanWithoutHints.sqlplan is the same query as before, but with the NoLock and FAST 1 hints removed. SingleQueryExecutionPlanWithDiagTempTable.sqlplan is for the requested query...

What facility is the "Server only Alias"? Can you point me to the doc for it?

The only way that I've heard of anything like this was through the SPN's,...

Use the profiler to pull up the actual query plan for your user. Compare that to your own query plan when you try to execute it.

What do you mean "changing back to an earlier database"?

pcasey (8/24/2012)

---

Dozens of our tables are set up where the table names are identical except for an ID number at the end. I am often called upon to write...

ChrisM@Work (8/24/2012)

---

SQL Kiwi (8/24/2012)

---

ChrisM@Work (8/24/2012)

---

It looks to me that APPLY evaluates the row where Stringy = NULL and returns NULL, which is subsequently retained by OUTER APPLY and discarded by...

GilaMonster (8/26/2012)

---

It's FASTFIRSTROW that no longer works, the replacement is FAST 1

It's worth noting, the FAST 1 hint will result in the optimiser estimating and optimising for 1 row only....

Can you show us what the query/execution plan from this looks like:

SELECT pat2.TIUDocumentSID, icd.ICDCode, icd.DiagnosisText

into #d

FROM [VHA19\VHASLCOlearT].PatientMap_1K_patients AS pat2

INNER JOIN Src.Visit_IDs AS vis ON pat2.Sta3n = vis.Sta3n...

I see you're also using OPTION (FAST 1), I thought that that didn't work anymore?

Hmm, the estimates in the NO-OrderBy version are really bad too. I wonder why that is?

Could you supply the DDL for the tables with keys and indexes please? ...

Hmm, the query plan looks odd to me. The relationship between estimated and actual suggests that things could be improved, but what's really suprising to me is that the...

Well, you're trying to sort 1.6 million rows. To do that "quickly" (several seconds) requires a substantial amount of memory, depending on how big these rows are. If...

Resetting the password is the proper procedure for this.