April 12, 2016 at 5:52 pm
Hello SQLServerCentral! Its been a very long time.
This may be an over-analyzed issue and if there are any threads dealing with this, please feel free to forward me to them...
I have a series of queries written using the "Inline" View/Join style and I am running SQL tuning on these queries using a tuning tool. Problem is, that the tool doesn't like multiple selects in the same query.The execution plan for the "Inline" join says its lower cost and has most of the cost on table scans but is using a "Nested Loops" step, while a re-written version of the query using standard join syntax shows a higher overall cost but the majority of the cost is in "Hash Match" step at a significant amount.
Which is better?:
a) Cost for larger amounts of data (1M rows per table)
b) Faster for larger amounts of data (1M rows per table)
c) Preferred overall
d) Pros and Cons
Because I have to rewrite the query to standard for the tuning tool I want to be fair and give the inline syntax a shot at being a better query/style/approach to use moving forward.
Example follows:
CREATE TABLE [dbo].[TestTable99](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[FullName] [varchar](100) NULL
)
GO
CREATE TABLE [dbo].[TestTable100](
[ID] [Bigint] NULL,
[Address] [varchar](100) NULL
)
GO
INSERT INTO [dbo].[TestTable99]
([FullName])
SELECT 'Bob Bee'
UNION
SELECT 'Sara See'
UNION
SELECT 'Joe Gee'
GO
INSERT TestTable100
SELECT '1', '123 Main St. AnyTown, AnyState, 10004'
UNION ALL
SELECT '2', '124 Main St. AnyTown, AnyState, 10005'
UNION ALL
SELECT '3', '125 Main St. AnyTown, AnyState, 10006'
-- First Query - Inline View/Join
SELECT COUNT(1) FROM TestTable100 Hun
WHERE Hun.ID IN (
SELECT Nine.ID
FROM TestTable99 Nine
WHERE Nine.ID = Hun.ID)
-- Second Query - Standard Join Syntax
SELECT COUNT(1)
FROM TestTable99 Nine JOIN TestTable100 Hun ON Nine.ID = Hun.ID
Thank You All In Advance!
JT
April 13, 2016 at 2:03 am
Jason Tontz (4/12/2016)
-- First Query - Inline View/JoinSELECT COUNT(1) FROM TestTable100 Hun
WHERE Hun.ID IN (
SELECT Nine.ID
FROM TestTable99 Nine
WHERE Nine.ID = Hun.ID)
-- Second Query - Standard Join Syntax
SELECT COUNT(1)
FROM TestTable99 Nine JOIN TestTable100 Hun ON Nine.ID = Hun.ID
Those queries are not the same. If there are duplicates in TestTable99, the second form will produce more rows than the first. This observation makes any performance comparison between the two meaningless.
Another observation: if you use a tool for query tuning that doesn't understand query syntax, return the tool and demand a refund.
April 13, 2016 at 3:16 am
Jason Tontz (4/12/2016)
Which is better?:
The one that returns the correct results.
IN, along with EXISTS are used when you want to see whether there's a matching row/matching values in the subquery. It won't always use nested loop, as with all queries, the optimiser will chose the optimal join type based on the row estimates. If there are multiple matching rows in the subquery for one in the outer table, it will return the row from the outer table once
JOIN is used when you want to fetch the rows from the second table/subquery and use the columns. It won't always use hash join, as with all queries the optimiser will chose the optimal join type based on the row count estimates. If there are multiple matching rows in the subquery for one in the outer table, it will return the row from the outer table multiple times.
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 13, 2016 at 10:07 am
Thanks for the reply Hugo. This tool does do a really good job at tuning so I wont be returning it. A tool is only as good as the person using it, hence my question.
Thank you for clarifying the duplicates context. That IS actually the use of the query so its good to know that is the preferred.
Could we get duplicates by GROUPING the columns (where we expect the duplicates) in the standard syntax'd query and would this be any better?
Thanks,
JT
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply