nick.mcdermaid (2/6/2013)

---

I took an uneducated guess and

-Added a unique index to RowNum_Start

-Removed the SRC_System join (I know I know, changing the requirements is cheating )

and query time went...

No, I wouldn't use OR just because that makes a view updateable -- UPDATEs aren't typically done using views like that anyway.

AND

CASE WHEN @InfoKey = 5 THEN

CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030)

ELSE

CLIENT_IDENTIFIER_TYPE.Code = @Code

END

The "THEN" and "ELSE" parts of a CASE statement must evaluate to a single value. The value can...

Is the "MS Dynamics Navision ERP" application running on the same box as SQL Server in prod?

If so, I'd lower the fixed memory for SQL considerably.

Even if not, I'd still...

Hmm, you could create a db with the new name, and create synonyms pointing back to the old db.

But synonyms can only be used for these types of objects:

Assembly (CLR)...

nick.mcdermaid (2/5/2013)

---

OK, I have reposted an unsanitised query, the sqlplan file, the DDL, and a picture of part of the query plan.

I can try clustering SE on RowNum_Start and RowNum_End...

If possible, you need to get rid of the massive GROUP BY (and also reduce the number of expensive joins required); that means using a subquery on CDBF.

I've taken my...

CAST(CAST(CAST(ISNULL(char_value, 0) AS decimal(5, 2)) * 100 AS int) AS varchar(5))

SELECT

CAST(CAST(CAST(ISNULL(char_value, 0) AS decimal(5, 2)) * 100 AS int) AS varchar(5))

FROM (

SELECT...

Code below should take care of the 'C' type.

The 'B' type is attempting to concatenate chars and numerics, which will always cause a conversion error, so it definitely needs changed...

SELECT

Studentid,

MIN(dateid) AS First_Absence_Date,

MAX(dateid) AS Last_Absence_Date,

COUNT(*) AS Total_Consecutive_Absences

FROM (

...

wnarretto (2/5/2013)

---

Thank you Mr. Pletcher....

yes the original code is doing a select into. Your answer is what i was suggesting but a coworker mentioned that their was a filter...

Are you doing a SELECT ... INTO?

If so, that may hold schema locks for the entire duration.

Instead, do an initial "dummy" SELECT ... INTO ... WHERE 1 = 0 to...

Want a cool sig (2/5/2013)

---

The link you are referring to is the first one in my signature. Unfortunately it doesn't do much good here because the OP is " in...

You...

If Dec 31, 1967 is the "border" date, and the date and time values really are separate integers::

For a date only:

SELECT

DATEADD(DAY, UniDataDays, CAST('19671231' AS date))

For a...