I think this is the (actual) logic you need, based on analyzing the original code:

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

GO

ALTER PROCEDURE [dbo].[usp_test_Arrears_BrokenArrangement]

@PolicyId int,

@AccountId int,

@PolicyNodeId int,

@output bit OUTPUT

AS

SET NOCOUNT...

ChrisM@Work (2/23/2016)

---

spaghettidba (2/23/2016)

---

There is no built-in CONVERT style that will handle the format DDMMYYYY. This means that you will have to code it yourself. This should do:

CONVERT(date, RIGHT(Date1,2) + SUBSTRING(Date1,...

SELECT

r.ID, r.FULL_NAME, r.FULL_ADDRESS,r.MEMBER_TYPE, r.BIRTH_DATE,

CONVERT (int,DATEDIFF(hour,r.BIRTH_DATE,GETDATE())/8766)as Age,r.MEETING_TYPE,r.MEETING,r.TITLE,

r.Parent1_FullName,r.Parent1_Cell,r.Parent1_Email,

r.Parent2_FullName,r.Parent2_Cell,r.Parent2_Email

FROM (

SELECT r2.ID

FROM IMIS.dbo.vw_csys_registrations r2

WHERE r2.MEETING_TYPE in ('PADA', 'TSM') AND r2.MEMBER_TYPE LIKE...

I think this will identity whether or not a row exist and will probably run much more quickly, since no massive join is involved, although a huge sort will be...

hlsc1983 (2/23/2016)

---

And one more thing. How terrible would it be if I used only one table with a many columns like I said earlier. ? Of course it won't...

Below is a more fully sketched out design. Hopefully this is not a homework or project assignment :-D.

CREATE TABLE dbo.subjects (

subject_id int IDENTITY(1, 1)

...

Worse, all this query tuning is likely effectively wasted anyway since the clus index is not being correct first. As soon as another column is added to this...

I think you should get check all NULLable columns for a given table in a single pass. It could hurt severely hurt performance to have to scan the same...

Hugo Kornelis (2/19/2016)

---

When it's an insert datetime, I think that this guess would...

Hugo Kornelis (2/19/2016)

---

How can you possibly determine that this should be a good clustering key based on seeing only one single query...

The "standard" performance problem when clustering on an identity column "by default". Trying to work around the wrong clustered index will generate lots of extra I/O.

You should cluster this...

Make sure the sub-procs have their own CATCH blocks. You can use empty/dummy blocks if you don't care about errors there:

CREATE PROCEDURE sub_proc

AS

...

BEGIN CATCH

--ignore error here

END CATCH

Try the RECOMPILE option. That time will be trivial compared to I/O time.

Also, do review the indexes on the table: getting the best clustered index on every table is...

In theory you could:

SET ANSI_WARNINGS OFF

But read up very, very carefully on that before you do it!

Or, you could create a stored proc and pass the values into the proc...

Yes.

The first query only considers rows where [LotJob] = '0000337495'.

The second first gets the max LotJob and then that max value must be = '0000337495' for the row...