last name is...

Chris-475469 (4/19/2016)

---

Hi Kevin,

I'm not new to being a DBA, just new to my current role and have been given this process to look at.

The INSERT statements are the biggest offenders...

Simple EAV structure will be way more effective than dynamic schema or XML.

With appropriate indexing searching for a value in EAV structure will be much faster than in XML.

And more...

Jeff Moden (4/18/2016)

---

I looked for the 68% cost node in the execution plan and I'm not finding it. What I am finding is that there are a number of...

INSERT INTO #temp1

SELECT S.samplepartnercode, S.samplePartnerIncId, S.samplePartnerSqlId,

S.clientSampleCode, S.internalSampleCode,PST.sampleStatusName,

ST.studyCode, A.activityCode,

SAFV.txtValue, SMT.sampleTypeName,S.sampleDescription, C.clientName

from SamplesPartners as S

....

INTO #temp2

FROM SamplesPartners S

INNER JOIN #temp1 ActTemp

???

Just to keep the system...

You must create a clustered index with definitions matching your purging criteria.

If during purging you delete all the records with DateInserted less than 90 days ago, then that must be...

That's it.

This condition can allow you, or me, or somebody with the same level of...

Sean Lange (4/15/2016)

---

Only if you say so.

You forgot to mention scalar functions and multi-statement table...

Well, performance of the query seems to be irrelevant, as it's built incorrectly.

declare @Assignee as varchar(25); set @Assignee = 'Smith, John'

select ...

from ...

left join v_R_User vru on vru.Distinguished_Name0 =...

First thing to start with - look at the execution plans taken in both environments.

It will show you what's so different there.

nhansen pcc (4/11/2016)

---

Both our internal SAN team, and the vendor have looked at it, and...

And you can safely remove left join

dbo.Table3 t3 on t1.Code3 = t3.Code3 and

t3.Code4 = 1

left-joined table is not gonna affect COUNT(*) in any way.

You need to have a look into your indexing and how it correlates with the query.

It seems like the indexes on the tables involved are so irrelevant to the parameters...

Amen.

TheSQLGuru (4/14/2016)

---

I have a Guruism I preach often: "Anything that allows developers to slap code together more quickly is inversely proportional to the performance (and likely concurrency) you will get...