WHAT were they thinking? - 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

Site Related Forums

The Yak Corral

WHAT were they thinking?

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 14:57:42

Yup..just inherited a new database...they gave us their "Scripts" to build the database..unfortunatley..I compile the sprocs...columns not found...ambiguous column names...what have you...no error handling...but what rocket scientist came up with this?


CREATE PROCEDURE [dbo].[GetCaseCurrentOwner]
(
	@Id INT
)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT ctl.AllocatedTo AS curOwnerId,appsec.FirstName + ' ' + appsec.LastName AS CurOwner
	FROM CaseTaskList ctl
	INNER JOIN CaseHeader ch On ctl.CaseId=ch.CaseId
	INNER JOIN AppSecurity appsec ON ctl.AllocatedTo=appsec.UserId
	INNER JOIN Status ON ch.StatusId=ctl.StatusId
	WHERE ctl.CaseId=@Id
	AND ctl.Id = (SELECT MAX(Id) FROM CaseTaskList Where CaseId=@Id)
	
END

GO

Cartesian Product?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

edit: moved to proper forum

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-10 : 15:28:52

I'm not seeing why you think this is SO BAD.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 15:44:14

The Join to Status and no Join Predicate to it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-10 : 15:50:26

quote:
Originally posted by X002548

The Join to Status and no Join Predicate to it?

Just because you are joining to a table, doesn't mean you need to filter on that table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 16:07:22

it's not a filter..it establishes the relationship to the data..if there is no relationship, then EVERY row is applied to all others in the result set...

What am I missing?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-10 : 16:17:17

Oh I finally see what you are saying. There is a join condition listed after the join to the Status table, but it isn't referencing the Status table. See, you have to point these things out to us in bold.

It's probably just an oversight.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 16:26:00

[sheepishly] Oh [/sheepishly]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-01-10 : 18:20:27

I think its cool. An INNER JOIN that works even if the table is empty

What? That was NOT the reason they did it?

This is crap too:


AND ctl.Id = (SELECT MAX(Id) FROM CaseTaskList Where CaseId=@Id)

surely


SELECT TOP 1 ...
...
WHERE ctl.CaseId=@Id
...
ORDER BY ctl.Id DESC

would be faster? And, as a nice side effect, would fix the Cartesian join too !!

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 09:45:56

I guess that's why they thought it was working

get it up and running..then time top rewrite this beast

There 120 tables, 60 of which are not referenced by any sproc...my guess is code in the app

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources