Why LEFT OUTER JOINs slowing query down

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)

Why LEFT OUTER JOINs slowing query down

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-28 : 15:16:41

When I comment out the two LEFT OUTER JOINs, this query runs very quickly, however those two joins slow it way down. Any thoughts?
I checked and all of the variabes being joined are indexed.


SELECT 		A.GSID 			AS MQAID
	,	YEAR(C.PERDATE) 	AS PERIOD_YEAR
	,	MONTH(C.PERDATE)	AS PERIOD_MONTH
	,	C.BROKER 		AS BROKER_CODE
	,	C.ANALYST 		AS ANALYST_CODE
	,	C.ESTDATE 		AS ESTIMATE_DATE
	,	C.PERIOD 		AS PERIOD
	,	C.PDFLAG 		AS P_D_FLAG
	,	C.VALUE_ 		AS VAL
	,	E.EXCLUDEDATE	
	,	F.STOPDATE 
	
FROM 		@MJD_TMPW13138		 	D 
	INNER JOIN 
		GSECMSTR 			A 
		ON 		D.ID 		 = A.GSID 
	INNER JOIN 
		GSECMAP 			B 
		ON 		A.SECCODE 	 = B.SECCODE 
			AND 	B.VENTYPE 	 = 2
	INNER JOIN 
		IBGDESTL1 			C 
		ON 		B.VENCODE 	 = C.CODE 
			AND 	C.PERTYPE 	 = 1 
			AND 	YEAR(C.PERDATE) >= 2005
	LEFT OUTER JOIN 
		IBGDEXCL1 			E 
		ON 		C.CODE 		 = E.CODE 
			AND 	C.BROKER	 = E.BROKER 
			AND 	C.ESTDATE 	 = E.ESTDATE 
			AND 	C.PERDATE 	 = E.PERDATE
	LEFT OUTER JOIN 
		IBGDSTPL1 			F 
		ON 		C.CODE 		 = F.CODE 
			AND 	C.BROKER	 = F.BROKER 
			AND 	C.PERTYPE 	 = F.PERTYPE 
			AND 	C.PERDATE 	 = F.PERDATE 
			AND 	F.STOPDATE 	>= C.ESTDATE

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-28 : 15:20:08

If they were inner joins instead, is the query slow? How slow are we talking about? Can you post the DDL including indexes and constraints for all of the tables involved? Are statistics updated? Have you recently run DBCC DBREINDEX or DBCC INDEXDEFRAG? How many rows in each of those tables?

Tara Kizer
aka tduggan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-28 : 16:02:57

You have an inequality (>=) on your second LEFT OUTER JOIN. This will cause one row in your inner table to be joined with potentially many rows in the outer table, resulting in slower execution time and duplicates. Are you sure this is what you want?

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-28 : 16:34:39

I'll do my best to answer, some of your questions are going to be learning opportunities for me.

Sorry, but what syntax would result in a LEFT INNER JOIN? Or are you saying just do a in INNER JOIN? If so that would exclude rows that I want to see (NULL valued for the left joins).

With those LEFT JOINs flipped to JOINs, the query runs slower.

I'd say it takes 10 times as long with the LEFT JOINS present.

I don't have the Data Definition Language available to me (third party creates the tables), but here are the details for each table involved:

Oh I do have the DDL for the temp table:


CREATE	TABLE #MJD_TMPW13138 
	(ID		VARCHAR(12)
	PRIMARY KEY 	(ID)
	)
TABLE_NAME                                                                                                                       COLUMN_NAME                                                                                                                      DATA_TYPE                                                                                                                             CONSTRAINTS 
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------- 
GSecMstr                                                                                                                         Country                                                                                                                          varchar(3)                                                                                                                            NULL
GSecMstr                                                                                                                         GsId                                                                                                                             varchar(12)                                                                                                                           NULL
GSecMstr                                                                                                                         IdBase                                                                                                                           varchar(5)                                                                                                                            NULL
GSecMstr                                                                                                                         IdSeq                                                                                                                            int                                                                                                                                   NULL
GSecMstr                                                                                                                         Isin                                                                                                                             varchar(12)                                                                                                                           NULL
GSecMstr                                                                                                                         Name                                                                                                                             varchar(60)                                                                                                                           NULL
GSecMstr                                                                                                                         PrevSedol                                                                                                                        varchar(6)                                                                                                                            NULL
GSecMstr                                                                                                                         SecCode                                                                                                                          int                                                                                                                                   PRIMARY KEY
GSecMstr                                                                                                                         Sedol                                                                                                                            varchar(6)                                                                                                                            NULL
GSecMstr                                                                                                                         Vendors                                                                                                                          varchar(20)                                                                                                                           NULL

(10 row(s) affected)

TABLE_NAME                                                                                                                       COLUMN_NAME                                                                                                                      DATA_TYPE                                                                                                                             CONSTRAINTS 
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------- 
GSecMap                                                                                                                          SecCode                                                                                                                          int                                                                                                                                   PRIMARY KEY
GSecMap                                                                                                                          VenCode                                                                                                                          int                                                                                                                                   NULL
GSecMap                                                                                                                          VenType                                                                                                                          smallint                                                                                                                              PRIMARY KEY

(3 row(s) affected)

TABLE_NAME                                                                                                                       COLUMN_NAME                                                                                                                      DATA_TYPE                                                                                                                             CONSTRAINTS 
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------- 
IbgdEstL1                                                                                                                        Analyst                                                                                                                          int                                                                                                                                   PRIMARY KEY
IbgdEstL1                                                                                                                        Broker                                                                                                                           int                                                                                                                                   PRIMARY KEY
IbgdEstL1                                                                                                                        Code                                                                                                                             int                                                                                                                                   PRIMARY KEY
IbgdEstL1                                                                                                                        Currency_                                                                                                                        varchar(1)                                                                                                                            NULL
IbgdEstL1                                                                                                                        EstDate                                                                                                                          smalldatetime                                                                                                                         PRIMARY KEY
IbgdEstL1                                                                                                                        IsoCurrency                                                                                                                      varchar(3)                                                                                                                            NULL
IbgdEstL1                                                                                                                        Measure                                                                                                                          int                                                                                                                                   PRIMARY KEY
IbgdEstL1                                                                                                                        PDFlag                                                                                                                           varchar(1)                                                                                                                            NULL
IbgdEstL1                                                                                                                        PerDate                                                                                                                          smalldatetime                                                                                                                         NULL
IbgdEstL1                                                                                                                        Period                                                                                                                           tinyint                                                                                                                               PRIMARY KEY
IbgdEstL1                                                                                                                        PerType                                                                                                                          tinyint                                                                                                                               PRIMARY KEY
IbgdEstL1                                                                                                                        RevDate                                                                                                                          smalldatetime                                                                                                                         NULL
IbgdEstL1                                                                                                                        Value_                                                                                                                           float                                                                                                                                 NULL

(13 row(s) affected)

TABLE_NAME                                                                                                                       COLUMN_NAME                                                                                                                      DATA_TYPE                                                                                                                             CONSTRAINTS 
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------- 
IbgdExcL1                                                                                                                        Analyst                                                                                                                          int                                                                                                                                   PRIMARY KEY
IbgdExcL1                                                                                                                        Broker                                                                                                                           int                                                                                                                                   PRIMARY KEY
IbgdExcL1                                                                                                                        Code                                                                                                                             int                                                                                                                                   PRIMARY KEY
IbgdExcL1                                                                                                                        EndExcludeDate                                                                                                                   smalldatetime                                                                                                                         NULL
IbgdExcL1                                                                                                                        EstDate                                                                                                                          smalldatetime                                                                                                                         PRIMARY KEY
IbgdExcL1                                                                                                                        ExcludeDate                                                                                                                      smalldatetime                                                                                                                         NULL
IbgdExcL1                                                                                                                        Measure                                                                                                                          smallint                                                                                                                              PRIMARY KEY
IbgdExcL1                                                                                                                        PerDate                                                                                                                          smalldatetime                                                                                                                         PRIMARY KEY
IbgdExcL1                                                                                                                        Period                                                                                                                           tinyint                                                                                                                               NULL
IbgdExcL1                                                                                                                        PerType                                                                                                                          tinyint                                                                                                                               PRIMARY KEY
IbgdExcL1                                                                                                                        Value_                                                                                                                           float                                                                                                                                 NULL

(11 row(s) affected)

TABLE_NAME                                                                                                                       COLUMN_NAME                                                                                                                      DATA_TYPE                                                                                                                             CONSTRAINTS 
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------- 
IbgdStpL1                                                                                                                        Broker                                                                                                                           int                                                                                                                                   PRIMARY KEY
IbgdStpL1                                                                                                                        Code                                                                                                                             int                                                                                                                                   PRIMARY KEY
IbgdStpL1                                                                                                                        Measure                                                                                                                          smallint                                                                                                                              PRIMARY KEY
IbgdStpL1                                                                                                                        PerDate                                                                                                                          smalldatetime                                                                                                                         PRIMARY KEY
IbgdStpL1                                                                                                                        PerType                                                                                                                          tinyint                                                                                                                               PRIMARY KEY
IbgdStpL1                                                                                                                        StopDate                                                                                                                         smalldatetime                                                                                                                         PRIMARY KEY

(6 row(s) affected)

I'm not sure what you mean by 'statistics updated', can you help me understand?

No I haven't run the DBCC DBREINDEX or DBCC INDEXDEFRAG commands.
To be honest I didn't know they existed, but from a quick peek at the Books OnLine maybe this is something I should look into.
I'm not particularly experienced with the degredation of index integrity...
(I'm guessing from the doc that this is what is being addressed). Feel free to enlighten me.

Thanks so much for your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-28 : 16:39:48

There is no such thing as LEFT INNER JOIN and I also didn't mention trying that. Check Jeff's post for a possible problem in your query. Your last past is too wide to read.

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-03-01 : 10:38:12

Thanks again for your help. I managed to cut the runtime to about 1/20th by creating a temp table and splitting out the left joins into separate queries:


UPDATE 	#NEW
	SET 		EXCLUDEDATE	= B.EXCLUDEDATE
	FROM 			#NEW 		A
		LEFT JOIN 	IBGDEXCL1 	B
			ON		A.CODE		 = B.CODE 
				AND 	A.BROKER_CODE	 = B.BROKER 
				AND 	A.ESTIMATE_DATE	 = B.ESTDATE 
				AND 	A.PERDATE 	 = B.PERDATE

UPDATE 	#NEW
	SET 		STOPDATE	= B.STOPDATE
	FROM 			#NEW 		A
		LEFT JOIN 	IBGDSTPL1 	B
			ON 		A.CODE 		 = B.CODE 
				AND 	A.BROKER_CODE	 = B.BROKER 
				AND 	A.PERTYPE 	 = B.PERTYPE 
				AND 	A.PERDATE 	 = B.PERDATE 
				AND 	B.STOPDATE 	>= A.ESTIMATE_DATE
				AND	A.EXCLUDEDATE IS NOT NULL

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-03-03 : 11:28:55

Posting the execution plan for the original is the main way to track the poor peformanance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources