Optimisation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optimisation

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 08:51:05

I need to optimise this blighter.
I'm basically trying to find the PK of the Parent table and the Maximum Create Date
for each of 3 [optional] child tables, but only for "Valid, Active, Current" rows.


SELECT	usr_reg_Code, 
	[MAX_mem_em_zCrDt] = MAX(mem_em_zCrDt), 
	[MAX_mem_mb_zCrDt] = MAX(mem_mb_zCrDt), 
	[MAX_sm_msi_zCrDt] = MAX(sm_msi_zCrDt)
FROM	dbo.NMF_USR_REG_Registration
	LEFT OUTER JOIN dbo.NMF_MEM_EM_EMail
		 ON mem_em_Recipient = usr_reg_Code
		AND mem_em_DelRecpnt = 0
		AND mem_em_NewRecpnt = 1
	LEFT OUTER JOIN dbo.NMF_MEM_MB_MatchBox
		 ON mem_mb_usr_reg_Code1 = usr_reg_Code
		AND mem_mb_Zap = 0
		AND mem_mb_Hide = 0
		AND mem_mb_IsDormant = 0
	LEFT OUTER JOIN dbo.NMF_SM_MSI_MailShotItem
		 ON sm_msi_usr_reg_Code = usr_reg_Code
		AND sm_msi_sm_msh_ID = 6
WHERE	    usr_reg_Status = 0
	AND usr_reg_Valid = 1
	AND usr_reg_LogonDt < DATEADD(Day, -2, GetDate())
GROUP BY usr_reg_Code
HAVING
(
	    MAX(sm_msi_zCrDt) IS NULL 
	AND 
	(
		MAX(mem_em_zCrDt) > MAX(usr_reg_LogonDt) 
	     OR MAX(mem_mb_zCrDt) > MAX(usr_reg_LogonDt)
	)
)
OR MAX(mem_mb_zCrDt) > MAX(sm_msi_zCrDt)
OR MAX(mem_em_zCrDt) > MAX(sm_msi_zCrDt)
GO

Row counts are:
NMF_USR_REG_Registration 30,000
NMF_MEM_EM_EMail 7,000
NMF_MEM_MB_MatchBox 1,750,000
NMF_SM_MSI_MailShotItem 1,000,000

The ShowPlan says


  |--Parallelism(Gather Streams)
       |--Filter(WHERE:((([Expr1008]=NULL AND ([Expr1009]>[Expr1010] OR [Expr1011]>[Expr1010])) OR [Expr1011]>[Expr1008]) OR [Expr1009]>[Expr1008]))
            |--Stream Aggregate(GROUP BY:([NMF_USR_REG_Registration].[usr_reg_Code]) DEFINE:([Expr1008]=MAX([NMF_SM_MSI_MailShotItem].[sm_msi_zCrDt]), [Expr1009]=MAX([NMF_MEM_EM_EMail].[mem_em_zCrDt]), [Expr1010]=MAX([NMF_USR_REG_Registration].[usr_reg_LogonDt]), [Expr1011]=MAX([NMF_MEM_MB_MatchBox].[mem_mb_zCrDt])))
                 |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([NMF_USR_REG_Registration].[usr_reg_Code])=([NMF_MEM_MB_MatchBox].[mem_mb_usr_reg_Code1]), RESIDUAL:([NMF_USR_REG_Registration].[usr_reg_Code]=[NMF_MEM_MB_MatchBox].[mem_mb_usr_reg_Code1]))
                      |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([NMF_USR_REG_Registration].[usr_reg_Code])=([NMF_MEM_EM_EMail].[mem_em_Recipient]), RESIDUAL:([NMF_USR_REG_Registration].[usr_reg_Code]=[NMF_MEM_EM_EMail].[mem_em_Recipient]))
                      |    |--Merge Join(Left Outer Join, MERGE:([NMF_USR_REG_Registration].[usr_reg_Code])=([NMF_SM_MSI_MailShotItem].[sm_msi_usr_reg_Code]), RESIDUAL:([NMF_USR_REG_Registration].[usr_reg_Code]=[NMF_SM_MSI_MailShotItem].[sm_msi_usr_reg_Code]))
                      |    |    |--Sort(ORDER BY:([NMF_USR_REG_Registration].[usr_reg_Code] ASC))
                      |    |    |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NMF_USR_REG_Registration].[usr_reg_Code]))
                      |    |    |         |--Table Scan(OBJECT:([NMF_LIVE].[dbo].[NMF_USR_REG_Registration]), WHERE:(([NMF_USR_REG_Registration].[usr_reg_Status]=0 AND [NMF_USR_REG_Registration].[usr_reg_LogonDt]<dateadd(day, -2, getdate())) AND Convert([NMF_USR_REG_Registration].[usr_reg_Valid])=1))
                      |    |    |--Sort(ORDER BY:([NMF_SM_MSI_MailShotItem].[sm_msi_usr_reg_Code] ASC))
                      |    |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NMF_SM_MSI_MailShotItem].[sm_msi_usr_reg_Code]))
                      |    |              |--Table Scan(OBJECT:([NMF_LIVE].[dbo].[NMF_SM_MSI_MailShotItem]), WHERE:([NMF_SM_MSI_MailShotItem].[sm_msi_sm_msh_ID]=6))
                      |    |--Sort(ORDER BY:([NMF_MEM_EM_EMail].[mem_em_Recipient] ASC))
                      |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NMF_MEM_EM_EMail].[mem_em_Recipient]))
                      |              |--Table Scan(OBJECT:([NMF_LIVE].[dbo].[NMF_MEM_EM_EMail]), WHERE:(Convert([NMF_MEM_EM_EMail].[mem_em_DelRecpnt])=0 AND Convert([NMF_MEM_EM_EMail].[mem_em_NewRecpnt])=1))
                      |--Sort(ORDER BY:([NMF_MEM_MB_MatchBox].[mem_mb_usr_reg_Code1] ASC))
                           |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NMF_MEM_MB_MatchBox].[mem_mb_usr_reg_Code1]))
                                |--Table Scan(OBJECT:([NMF_LIVE].[dbo].[NMF_MEM_MB_MatchBox]), WHERE:((Convert([NMF_MEM_MB_MatchBox].[mem_mb_Zap])=0 AND Convert([NMF_MEM_MB_MatchBox].[mem_mb_Hide])=0) AND Convert([NMF_MEM_MB_MatchBox].[mem_mb_IsDormant])=0))

and of the things that are catching my attention:

Do I need to worry about all the CONVERT?
They are BIT columns, is CONVERT some extra-effort, or normal?

There doesn't seem to be much in the way of an index I could use here

The things [marked in red] that are slow (in terms of Estimated IO) are

1.942 OBJECT:([NMF_LIVE].[dbo].[NMF_USR_REG_Registration])...

6.01 OBJECT:([NMF_LIVE].[dbo].[NMF_SM_MSI_MailShotItem])...

11.71 OBJECT:([NMF_LIVE].[dbo].[NMF_MEM_MB_MatchBox])...

which are all doing table scans to get data which passes the Bit Field tests, which will "pull" about 95% of the data.

I wonder about making a TEMP table based on only one join, and then processing that against the other tables to "top up" the info, as it should be possible to use one of the tables, initially, to cut the job down to only 10% of the total data.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-14 : 10:45:49

How does this look?



SELECT	usr_reg_Code, 
	[MAX_mem_em_zCrDt] = MAX(mem_em_zCrDt), 
	[MAX_mem_mb_zCrDt] = MAX(mem_mb_zCrDt), 
	[MAX_sm_msi_zCrDt] = MAX(sm_msi_zCrDt)

  FROM (
SELECT	usr_reg_Code, 
	[mem_em_zCrDt]
	[mem_mb_zCrDt]
	[sm_msi_zCrDt]
FROM	dbo.NMF_USR_REG_Registration
	LEFT JOIN dbo.NMF_MEM_EM_EMail
		 ON mem_em_Recipient = usr_reg_Code
	LEFT JOIN dbo.NMF_MEM_MB_MatchBox
		 ON mem_mb_usr_reg_Code1 = usr_reg_Code
	LEFT JOIN dbo.NMF_SM_MSI_MailShotItem
		 ON sm_msi_usr_reg_Code = usr_reg_Code
) AS XXX
WHERE	    usr_reg_Status = 0
	AND usr_reg_Valid = 1
	AND usr_reg_LogonDt < DATEADD(Day, -2, GetDate())
	AND mem_em_DelRecpnt = 0
	AND mem_em_NewRecpnt = 1
	AND mem_mb_Zap = 0
	AND mem_mb_Hide = 0
	AND mem_mb_IsDormant = 0
	AND sm_msi_sm_msh_ID = 6
GROUP BY usr_reg_Code
HAVING
(
	    MAX(sm_msi_zCrDt) IS NULL 
	AND 
	(
		MAX(mem_em_zCrDt) > MAX(usr_reg_LogonDt) 
	     OR MAX(mem_mb_zCrDt) > MAX(usr_reg_LogonDt)
	)
)
OR MAX(mem_mb_zCrDt) > MAX(sm_msi_zCrDt)
OR MAX(mem_em_zCrDt) > MAX(sm_msi_zCrDt)
GO

I think the bits are causing the scan....

Brett

8-)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-07-14 : 10:46:29

Indices on the columns used in the join + where statements should be of benefit to you...even indiced that cover only some of the columns...post the ddl for all the tables to get an indepth answer.

re the convert....what do you get if you replace the 0 with true/false?...given they are bit fields...maybe the constants are being converted to such like
also the dateadd bit...can this be replaced with a check against a pre-determined variable...

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-14 : 11:33:06

You do a convert in a predicate and you'll be guaranteed a scan...

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 13:18:50

Brett>You do a convert in a predicate and you'll be guaranteed a scan...

Yeah, I knew that, but I didn't think that
WHERE MyBitColumn = 0
was going to cause that effect.

Seems like if I do
MyBitColumn = 0
if becomes
CONVERT(MyBitColumn, 0) = 0
which is definitely about-face performance-wise!

true/false is not valid ...

But I've fixed it by doing
WHERE MyBitColumn = CONVERT(bit, 0)
so that's another global find&replace of all code coming up ...

I've tried putting indexes on the JOIN columns, including "covering" the BIT columns, but its still doing full table scans (in fact Total Tree Cost has gone up from 44.88 to 53.61 - thats a relevant figure, right?)

I'll bung up some DDL in a moment


  |--Parallelism(Gather Streams)
       |--Filter(WHERE:((([Expr1008]=NULL AND ([Expr1009]>[Expr1010] OR [Expr1011]>[Expr1010])) OR [Expr1011]>[Expr1008]) OR [Expr1009]>[Expr1008]))
            |--Hash Match(Aggregate, HASH:([NMF_USR_REG_Registration].[usr_reg_Code]), RESIDUAL:([NMF_USR_REG_Registration].[usr_reg_Code]=[NMF_USR_REG_Registration].[usr_reg_Code]) DEFINE:([Expr1008]=MAX([NMF_SM_MSI_MailShotItem].[sm_msi_zCrDt]), [Expr1009]=MAX([NMF_MEM_EM_EMail].[mem_em_zCrDt]), [Expr1010]=MAX([NMF_USR_REG_Registration].[usr_reg_LogonDt]), [Expr1011]=MAX([NMF_MEM_MB_MatchBox].[mem_mb_zCrDt])))
                 |--Hash Match(Left Outer Join, HASH:([NMF_USR_REG_Registration].[usr_reg_Code])=([NMF_MEM_MB_MatchBox].[mem_mb_usr_reg_Code1]), RESIDUAL:([NMF_USR_REG_Registration].[usr_reg_Code]=[NMF_MEM_MB_MatchBox].[mem_mb_usr_reg_Code1]))
                      |--Hash Match(Left Outer Join, HASH:([NMF_USR_REG_Registration].[usr_reg_Code])=([NMF_SM_MSI_MailShotItem].[sm_msi_usr_reg_Code]), RESIDUAL:([NMF_USR_REG_Registration].[usr_reg_Code]=[NMF_SM_MSI_MailShotItem].[sm_msi_usr_reg_Code]))
                      |    |--Bitmap(HASH:([NMF_USR_REG_Registration].[usr_reg_Code]), DEFINE:([Bitmap1018]))
                      |    |    |--Hash Match(Right Outer Join, HASH:([NMF_MEM_EM_EMail].[mem_em_Recipient])=([NMF_USR_REG_Registration].[usr_reg_Code]), RESIDUAL:([NMF_USR_REG_Registration].[usr_reg_Code]=[NMF_MEM_EM_EMail].[mem_em_Recipient]))
                      |    |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NMF_MEM_EM_EMail].[mem_em_Recipient]))
                      |    |         |    |--Table Scan(OBJECT:([NMF_DEV].[dbo].[NMF_MEM_EM_EMail]), WHERE:([NMF_MEM_EM_EMail].[mem_em_DelRecpnt]=0 AND [NMF_MEM_EM_EMail].[mem_em_NewRecpnt]=1))
                      |    |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NMF_USR_REG_Registration].[usr_reg_Code]))
                      |    |              |--Table Scan(OBJECT:([NMF_DEV].[dbo].[NMF_USR_REG_Registration]), WHERE:(([NMF_USR_REG_Registration].[usr_reg_Status]=0 AND [NMF_USR_REG_Registration].[usr_reg_LogonDt]<dateadd(day, -2, getdate())) AND Convert([NMF_USR_REG_Registration].[usr_reg_Valid])=1))
                      |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NMF_SM_MSI_MailShotItem].[sm_msi_usr_reg_Code]), WHERE:(PROBE([Bitmap1018])=TRUE))
                      |         |--Table Scan(OBJECT:([NMF_DEV].[dbo].[NMF_SM_MSI_MailShotItem]), WHERE:([NMF_SM_MSI_MailShotItem].[sm_msi_sm_msh_ID]=6))
                      |--Parallelism(Repartition Streams, PARTITION COLUMNS:([NMF_MEM_MB_MatchBox].[mem_mb_usr_reg_Code1]))
                           |--Table Scan(OBJECT:([NMF_DEV].[dbo].[NMF_MEM_MB_MatchBox]), WHERE:(([NMF_MEM_MB_MatchBox].[mem_mb_Zap]=0 AND [NMF_MEM_MB_MatchBox].[mem_mb_Hide]=0) AND [NMF_MEM_MB_MatchBox].[mem_mb_IsDormant]=0))

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-14 : 13:27:41

I think an index on a bit column is a waste of space...

did you try my code?

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 13:32:28

Abridged DDL


CREATE TABLE [dbo].[NMF_MEM_EM_EMail] 
(
	[mem_em_zCrDt] [datetime] NULL ,
	[mem_em_ID] [int] IDENTITY (10000, 1) NOT NULL ,
	[mem_em_Recipient] [varchar] (13) NULL ,
	[mem_em_DelRecpnt] [bit] NOT NULL ,
	[mem_em_NewRecpnt] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NMF_MEM_MB_MatchBox]
(
	[mem_mb_zCrDt] [datetime] NOT NULL ,
	[mem_mb_usr_reg_Code1] [varchar] (13) NOT NULL ,
	[mem_mb_usr_reg_Code2] [varchar] (13) NOT NULL ,
	[mem_mb_Zap] [bit] NOT NULL ,
	[mem_mb_Hide] [bit] NOT NULL ,
	[mem_mb_IsDormant] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NMF_USR_REG_Registration]
(
	[usr_reg_Code] [varchar] (13) NOT NULL ,
	[usr_reg_Status] [smallint] NULL ,
	[usr_reg_LogonDt] [datetime] NULL ,
	[usr_reg_Valid] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NMF_SM_MSI_MailShotItem] (
	[sm_msi_zCrDt] [datetime] NULL ,
	[sm_msi_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[sm_msi_usr_reg_Code] [varchar] (13) NULL ,
	[sm_msi_sm_msh_ID] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NMF_MEM_EM_EMail] ADD 
	CONSTRAINT [DF_NMF_mem_em_zCrDt] DEFAULT (getdate()) FOR [mem_em_zCrDt],
	CONSTRAINT [DF_NMF_mem_em_DelRecpnt] DEFAULT (0) FOR [mem_em_DelRecpnt],
	CONSTRAINT [DF_NMF_mem_em_NewRecpnt] DEFAULT (1) FOR [mem_em_NewRecpnt],
	CONSTRAINT [PK_NMF_MEM_EM_EMail] PRIMARY KEY  NONCLUSTERED 
	(
		[mem_em_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NMF_MEM_MB_MatchBox] ADD 
	CONSTRAINT [DF_NMF_mem_mb_zCrDt] DEFAULT (getdate()) FOR [mem_mb_zCrDt],
	CONSTRAINT [DF_NMF_mem_mb_Zap] DEFAULT (0) FOR [mem_mb_Zap],
	CONSTRAINT [DF_NMF_mem_mb_Hide] DEFAULT (0) FOR [mem_mb_Hide],
	CONSTRAINT [DF_NMF_MEM_MB_IsDormant] DEFAULT (0) FOR [mem_mb_IsDormant],
	CONSTRAINT [PK_NMF_MEM_MB_MatchBox] PRIMARY KEY  NONCLUSTERED 
	(
		[mem_mb_usr_reg_Code1],
		[mem_mb_usr_reg_Code2]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NMF_USR_REG_Registration] ADD 
	CONSTRAINT [DF_NMF_usr_reg_Status] DEFAULT (4) FOR [usr_reg_Status],
	CONSTRAINT [DF_NMF_usr_reg_Valid] DEFAULT (0) FOR [usr_reg_Valid],
	CONSTRAINT [PK_NMF_USR_DET_UserDetail] PRIMARY KEY  NONCLUSTERED 
	(
		[usr_reg_Code]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NMF_SM_MSI_MailShotItem] ADD 
	CONSTRAINT [DF_NMF_sm_msi_zCrDt] DEFAULT (getdate()) FOR [sm_msi_zCrDt],
	CONSTRAINT [DF_NMF_sm_msi_sm_msh_ID] DEFAULT (0) FOR [sm_msi_sm_msh_ID],
	CONSTRAINT [PK_NMF_SM_MSI_MailShotItem] PRIMARY KEY  NONCLUSTERED 
	(
		[sm_msi_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NMF_SM_MSI_MailShotItem] ADD 
	CONSTRAINT [FK_NMF_SM_MSI_MailShotItem_NMF_USR_REG_Registration] FOREIGN KEY 
	(
		[sm_msi_usr_reg_Code]
	) REFERENCES [dbo].[NMF_USR_REG_Registration] 
	(
		[usr_reg_Code]
	)
GO

To this I added the following temporary indexes to "cover" the JOINs


CREATE INDEX KBM_01 ON dbo.NMF_USR_REG_Registration 
(
	usr_reg_Code
)

CREATE INDEX KBM_02 ON dbo.NMF_MEM_EM_EMail 
(
	mem_em_Recipient, 
	mem_em_DelRecpnt, 
	mem_em_NewRecpnt
)

CREATE INDEX KBM_03 ON dbo.NMF_MEM_MB_MatchBox 
(
	mem_mb_usr_reg_Code1, 
	mem_mb_Zap, 
	mem_mb_Hide, 
	mem_mb_IsDormant
)

CREATE INDEX KBM_04 ON dbo.NMF_SM_MSI_MailShotItem 
(
	sm_msi_usr_reg_Code, 
	sm_msi_sm_msh_ID
)

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 13:39:23

>I think an index on a bit column is a waste of space...

Yup, agree with that, I was just trying to see what would happen if the indexes covered the query.

>did you try my code?

No ...
there's a fair bit you've got in the WHERE outside the nested-select that needs to be inside it. I've been "in meetings" all day trying to work out what we are going to do with our XML data scrubbing, so I've only just got around to having a pop at this, so expect some input shortly. Or would that be OUTPUT?!

Kristen

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 13:40:57

By the by, I tried SHOWPLAN on the DDL I just posted (i.e. on completely empty tables) and it reckons it will use INDEX SCAN where I'm getting TABLE SCAN on the real data. Anything I should investigate? Like the granularity of the indexes, or whether the STATS are out of date?

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-14 : 14:57:51

I can't get matchbox not to scan...

I don't understand...

everything else is a seek...even if you restore the query to its "simpler" form like above...




SELECT	usr_reg_Code, 
	[MAX_mem_em_zCrDt] = MAX(mem_em_zCrDt), 
	[MAX_mem_mb_zCrDt] = MAX(mem_mb_zCrDt), 
	[MAX_sm_msi_zCrDt] = MAX(sm_msi_zCrDt)
  FROM (
SELECT	  A.usr_reg_Code 
	, [mem_em_zCrDt]
	, [mem_mb_zCrDt]
	, [sm_msi_zCrDt]
	, A.usr_reg_Status
	, A.usr_reg_Valid
	, A.usr_reg_LogonDt
	, mem_em_DelRecpnt
	, mem_em_NewRecpnt
	, mem_mb_Zap
	, mem_mb_Hide
	, mem_mb_IsDormant
	, sm_msi_sm_msh_ID 
FROM	(SELECT * FROM dbo.NMF_USR_REG_Registration
	LEFT JOIN dbo.NMF_MEM_EM_EMail
		 ON usr_reg_Code = mem_em_Recipient) AS A
LEFT JOIN
	(SELECT * FROM dbo.NMF_USR_REG_Registration
	LEFT JOIN dbo.NMF_MEM_MB_MatchBox
		 ON  usr_reg_Code = mem_mb_usr_reg_Code1) AS B
ON A.usr_reg_Code = B.usr_reg_Code
LEFT JOIN
	(SELECT * FROM dbo.NMF_USR_REG_Registration
	LEFT JOIN dbo.NMF_SM_MSI_MailShotItem
		 ON  usr_reg_Code = sm_msi_usr_reg_Code) AS C
ON A.usr_reg_Code = C.usr_reg_Code
) AS XXX
WHERE	    usr_reg_Status = 0
	AND usr_reg_Valid = 1
	AND usr_reg_LogonDt < DATEADD(Day, -2, GetDate())
	AND mem_em_DelRecpnt = 0
	AND mem_em_NewRecpnt = 1
	AND mem_mb_Zap = 0
	AND mem_mb_Hide = 0
	AND mem_mb_IsDormant = 0
	AND sm_msi_sm_msh_ID = 6
GROUP BY usr_reg_Code
HAVING
(
	    MAX(sm_msi_zCrDt) IS NULL 
	AND 
	(
		MAX(mem_em_zCrDt) > MAX(usr_reg_LogonDt) 
	     OR MAX(mem_mb_zCrDt) > MAX(usr_reg_LogonDt)
	)
)
OR MAX(mem_mb_zCrDt) > MAX(sm_msi_zCrDt)
OR MAX(mem_em_zCrDt) > MAX(sm_msi_zCrDt)
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-14 : 14:59:01

This gets the seek on matchbox like I want


SELECT usr_reg_Code
FROM	dbo.NMF_USR_REG_Registration
	LEFT JOIN dbo.NMF_MEM_MB_MatchBox
	ON  usr_reg_Code = mem_mb_usr_reg_Code1

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 15:24:40

So to describe my dilema in English:

we have Registered Users
who have Matches (this is a dating site - with reciprocal matching)

We want to mail them users who have:

New matches (but only if valid)
or New Email (but only if undeleted & unread)

and they were created

SINCE the last MailShotItem
AND since the user's last login

So, unfortunately:


SELECT usr_reg_Code
FROM	dbo.NMF_USR_REG_Registration
	LEFT JOIN dbo.NMF_MEM_MB_MatchBox
	ON  usr_reg_Code = mem_mb_usr_reg_Code1

tells me all users who have Matches, but it is important that the Matches are "Valid", which needs


	AND mem_mb_Zap = 0	-- Not "zapped"
	AND mem_mb_Hide = 0	-- Not "Hidden"
	AND mem_mb_IsDormant = 0 -- Not with Dormant users

I'm gonna have a go at a multi-stage #TEMP table approach ...

Kristen

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 21:47:30

I've got it sorted, thanks for your help chaps.

The CONVERT on the BIT fields was a useful find along the way ...

If you are interested I wound up doing


CREATE TABLE #KBM01
(
	[T_usr_reg_Code] varchar(14) NOT NULL PRIMARY KEY,
	[T_usr_reg_LogonDt] datetime, 
	[MAX_mem_em_zCrDt] datetime, 
	[MAX_mem_mb_zCrDt] datetime, 
	[MAX_sm_msi_zCrDt] datetime
)

INSERT INTO #KBM01 (T_usr_reg_Code, T_usr_reg_LogonDt)
SELECT	[T_usr_reg_Code] = usr_reg_Code, 
	[T_usr_reg_LogonDt] = usr_reg_LogonDt
FROM	dbo.NMF_USR_REG_Registration
WHERE	    usr_reg_Status = 0
	AND usr_reg_Valid = CONVERT(bit, 1)
	AND usr_reg_LogonDt < DATEADD(Day, -2, GetDate())

and then three UPDATES to get values into the MAX_mem_em_zCrDt, MAX_mem_mb_zCrDt, MAX_sm_msi_zCrDt columns.

Takes less than 3 seconds compared to 1.5 minutes before. HoHum!

Kristen

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 21:52:10

Blast! Why did I turn SHOWPLAN on ...

Can some bright spark explain this please:


INSERT INTO #KBM01 (T_usr_reg_Code, T_usr_reg_LogonDt)
SELECT	[T_usr_reg_Code] = usr_reg_Code, 
	[T_usr_reg_LogonDt] = usr_reg_LogonDt
FROM	dbo.NMF_USR_REG_Registration
WHERE	    usr_reg_Status = 0
	AND usr_reg_Valid = CONVERT(bit, 1)
	AND usr_reg_LogonDt < DATEADD(Day, -2, GetDate())

  |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#KBM01_..._00000037B8C0].[PK__#KBM01__0BA14934]), SET:([#KBM01].[MAX_sm_msi_zCrDt]=NULL, [#KBM01].[MAX_mem_mb_zCrDt]=NULL, [#KBM01].[MAX_mem_em_zCrDt]=NULL, [#KBM01].[T_usr_reg_LogonDt]=[NMF_USR_REG_Registration].[usr_reg_LogonDt], [#KBM01].[T_usr_reg_Code]=[NMF_USR_REG_Registration].[usr_reg_Code]))
       |--Sort(ORDER BY:([NMF_USR_REG_Registration].[usr_reg_Code] ASC))
            |--Top(ROWCOUNT est 0)
                 |--Table Scan(OBJECT:([NMF_LIVE].[dbo].[NMF_USR_REG_Registration]), WHERE:(([NMF_USR_REG_Registration].[usr_reg_Status]=0 AND [NMF_USR_REG_Registration].[usr_reg_Valid]=1) AND [NMF_USR_REG_Registration].[usr_reg_LogonDt]<dateadd(day, -2, getdate())))

Where's that ORDER BY and TOP coming from?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources