September 22, 2009 at 8:14 am
I've been charged with redesigning a slow running query which uses multiple cursors and calls the same table-valued function many times (the function uses a cursor as well) and calls yet another function (which also uses a cursor) to use set-based methods. The query is used by multiple reports all of which run very slowly.
One little piece of this monster query is a subquery that uses a join which is very curious. I've seen this used once before but cannot remember the term used to describe this type of join (other than maybe "sloppy").
Curious? Any help is appreciated. Just help with the "term" not the join. The table join of tblHosts is the part in question (beginning with the comma).
SELECT cau.type
FROM dbo.tblAccessControl AS cac
INNER JOIN dbo.tblAccessUsers AS cau
ON cac.strRole = cau.strRole
, dbo.tblHosts AS h
Thanks.
September 22, 2009 at 8:36 am
I'm not 100% sure, but I believe that it's still just refered to as a join, the syntax of the statement is a little different that what would be considered a normal join statement; however, I believe it's still called a join... Sorry I could answer your question, and I wish you the best when it comes to rewriting all those cursors. 😛
September 22, 2009 at 8:44 am
That's a mixture of a new style JOIN and an old style JOIN
I could do
SELECT * FROM table1, table2 WHERE table1.col = table2.col
That's an old-style join
What has been done there is to mix the two styles.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 8:48 am
The name I'm thinking of (a slang term) could actually be more related to the From clause than the join. Another example would be like this:
Select c.strReceiptNo, d.strName
from dbo.tblControlLog c, dbo.tblDepartments d
Thanks.
September 22, 2009 at 8:53 am
rjv_rnjn (9/22/2009)
@Matt, As the where clause is not mentioned in the query I don't think it implies even a old style join.
That's assuming there is no where clause after the part which was posted... 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 9:01 am
There is a Where clause. It is indeed an "old-style" join. What I'm looking for is the term used to describe the old-style join. I've seen it used here and on other forums. ?? Somebody out there knows it! Thanks for the comments guys.
September 22, 2009 at 9:04 am
The name I'm thinking of (a slang term) could actually be more related to the From clause than the join. Another example would be like this:
That's poorly written. But I assume that's just your observation and there's no question in there.
Matt Whitfield (9/22/2009)
rjv_rnjn (9/22/2009)
@Matt, As the where clause is not mentioned in the query I don't think it implies even a old style join.That's assuming there is no where clause after the part which was posted... 🙂
(Wrong) Assumption is the root cause of all evil. 😛 And I still did that in my first sentence of this post. :w00t:
September 22, 2009 at 9:19 am
To be honest, most people call it 'old style join'... lame huh?
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 9:19 am
rjv_rnjn (9/22/2009)
(Wrong) Assumption is the root cause of all evil. 😛 And I still did that in my first sentence of this post. :w00t:
Errr - yeah - that's why I didn't make that assumption?
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 9:25 am
Because there is no column "joined" on, its a cross join. All records in table A join to all records in table B, resulting in a cartesian product. Is that what you are after?
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c4f98374-e8a7-4f73-8d0e-309bd94ad1ae.htm
Earlier syntax: From table A, table B
2005 syntax: From table A CROSS JOIN table B
[font="Arial Narrow"]bc[/font]
September 22, 2009 at 9:44 am
Is that what you are after?
No. Thanks. There is a Where clause. The syntax is not the question. I can fix the join. A while back I saw this term used (it was very clever) when referring to the old syntax of the From clause.
September 22, 2009 at 9:45 am
I second what bc has posted, its a cross join.
Basically, in your original query if you are getting 10 rows from the inner join and tblHosts has 10 rows then the query return 100 rows with the same 10 cau.type repeated.SELECT cau.type
FROM dbo.tblAccessControl AS cac
INNER JOIN dbo.tblAccessUsers AS cau
ON cac.strRole = cau.strRole
, dbo.tblHosts AS h
Hope that helps in re-writing your stored proc.
-Supriya
September 22, 2009 at 9:59 am
SSSolice (9/22/2009)
Is that what you are after?
No. Thanks. There is a Where clause. The syntax is not the question. I can fix the join. A while back I saw this term used (it was very clever) when referring to the old syntax of the From clause.
Was it 'ANSI SQL-89 syntax' you were thinking of?
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 10:19 am
SSSolice (9/22/2009)
...One little piece of this monster query is a subquery that uses a join which is very curious. I've seen this used once before but cannot remember the term used to describe this type of join (other than maybe "sloppy").Curious? Any help is appreciated. Just help with the "term" not the join. The table join of tblHosts is the part in question (beginning with the comma).
SELECT cau.type
FROM dbo.tblAccessControl AS cac
INNER JOIN dbo.tblAccessUsers AS cau
ON cac.strRole = cau.strRole
, dbo.tblHosts AS h
The only term that I have ever heard used for these (commas + Where clause) is "SQL-89 Joins". This is to distinguish them from the newer "SQL-92 Joins" that us the explicit "JOIN" syntax:
table_expression [{LEFT|RIGHT|FULL} [OUTER]] JOIN table_expression ON match_condition
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply