hlsc1983 (2/24/2016)

---

i meant to write like this: exam_year int,

The reason why i am using exam_year is because the marksheet that the system must finally print reads something like this:...

You need to use the actual semester number to record the grade. Year alone is not enough, nor is "odd/even" (bizarre!).

It really seems like this is some type of...

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...