Indianrock (12/16/2016)

---

Regarding the modifications Lynn Pettis made to the OP query. I attached the plan.

------LYNN PETTIS CHANGE

-- 92 rows in QA in 03:29 first run 10...

Lynn Pettis (12/14/2016)

---

TheSQLGuru (12/14/2016)

---

You may have missed it Lynn, but this is an ORM app. I doubt very much it is going to be reworking that query much at all,...

You may have missed it Lynn, but this is an ORM app. I doubt very much it is going to be reworking that query much at all, almost certainly not...

Lynn Pettis (12/14/2016)

---

junk.mail291276 (12/14/2016)

---

Hello,

I have a stored procedure that takes a project ID as a parameter and returns a list of results on that project. Now what I'd like to...

If you go henriksen's route, a potentially nice improvement would be to substring the field used in this JOIN as you populate table #b:

ON a.dim_1 = SUBSTRING( b.dim_value, 1, 2...

Easy way to get back a bunch of GBs of free space, eh??

Now, you did NOT shrink the database, right?!?! :hehe:

Indeed there will need to be something (which is definitely not shown) that provides guaranteed order of IDs 1, 2 and 3 as you have it.

Also, you REALLY should...

Oh, there is WAY more wrong with this query (from an optimization standpoint) than just a lot of substrings. At least for those you can replace them with LIKE for...

HAH!! :hehe:

You could add something like this to your WHERE clause:

AND EXISTS (SELECT * FROM ... WHERE ... "they shopped in Men's")

AND EXISTS (SELECT * FROM ... WHERE ... "they shopped...

patrickmcginnis59 10839 (12/13/2016)

---

TheSQLGuru (12/13/2016)

---

A) Regarding index reorg, I don't think you get "random crap in your database" because of it while running NOLOCK SELECTs. You may get committed bad data...

patrickmcginnis59 10839 (12/13/2016)

---

Alan.B (12/9/2016)

---

Indianrock (12/9/2016)

---

I've heard this advice everywhere. Don't do things to change the way the sql optimizer produces plans etc. "it's smart so don't use plan...

Carolyn Richardson (12/13/2016)

---

We are fortunate enough to have a well over-specked server in a non 24/7 operation so I don't have to worry about running jobs like this out of...

PLEASE understand that ORDER BY is a HEAVY TEMP OPERATION and should ABSOLUTELY be avoided unless there is a defined requirement for the particular order (AND it isn't small set...

Yep, but it...