July 18, 2008 at 8:18 am
Chris Morris (7/18/2008)
ON b.keyid LIKE a.keyid + '%'
Chris, that looks like a nifty little trick, what exactly is the '%' doing?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 18, 2008 at 8:30 am
Greg Snidow (7/18/2008)
Chris Morris (7/18/2008)
ON b.keyid LIKE a.keyid + '%'Chris, that looks like a nifty little trick, what exactly is the '%' doing?
Hi Greg
It's a wildcard character.
b.keyid LIKE a.keyid + '%'
means: if a.keyid matches to the leftmost part of b.keyid. You can use the % wildcard character to match within a string -
b.keyid LIKE '%' + a.keyid + '%'.
There are other wildcard characters, see LIKE in BOL for a pretty good explanation, but this one's very handy.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 18, 2008 at 8:41 am
Ok, I think I get it. Its just saying where b.id = any part of a.id, starting at the beginning of a.id. So, I am trying to figure out how to test for performance, and I have found the execution plan in QA, but I am not sure how to interpret the results. I ran your query,
SELECT a.*
FROM #TableA a
LEFT JOIN #TableB b
ON b.keyid LIKE a.keyid + '%'
WHERE b.keyid IS NULL
against mine,
SELECT
a.*
FROM #TableA a LEFT JOIN
#TableB b
ON SUBSTRING(a.keyid,1,CHARINDEX('.',a.keyid)-1) =
SUBSTRING(b.keyid,1,LEN(SUBSTRING(a.keyid,1,CHARINDEX('.',a.keyid)-1)))
WHERE b.keyid IS NULL
And I see two differences:
1) In the Nested loops/Left outer join box
Yours: Row count = 4, Estimated Row count = 5, Cost = .001
Mine; Row count = 6, Estimated Row count = 9, Cost = .000104
2) In the Filter box
Yours: CPU Cost = .000002, Cost = .000002
Mine: CPU Cost = .000004, Cost = .000004
So, it would seem that if smaller numbers a better, yours is clearly the better solution. Do you have any idea what the numbers mean, or an easier way to look at them other than hovering your mouse over the picture in the execurtion plan pane?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 18, 2008 at 9:21 am
Hi Greg
I'd test these for performance, if necessary, by scaling the rowcounts in the test tables up to a point where the time taken for each query to run is more significant, perhaps 10 or 20 seconds...
-- make some sample data
DROP TABLE #TableA
DROP TABLE #TableB
SELECT REPLACE(VENDOR_VNAME, ' ', '.') AS keyid
INTO #TableB
FROM customers (NOLOCK) -- (18,439 row(s) affected)
SELECT CAST(LEFT(keyid, 20) AS VARCHAR(30)) AS keyid INTO #TableA FROM #TableB -- (18,439 row(s) affected)
DELETE #TableB
FROM #TableB b
INNER JOIN #TableA a ON a.keyid = b.keyid --(13139 row(s) affected)
-- eyeball some data
select top 10 * from #TableA ORDER BY keyid
select top 10 * from #TableB ORDER BY keyid
-- Testing
SELECT a.*
FROM #TableA a
LEFT JOIN #TableB b
ON b.keyid LIKE a.keyid + '%'
WHERE b.keyid IS NULL -- (12982 row(s) affected) / 0:00:27
SELECT
a.*
FROM #TableA a LEFT JOIN
#TableB b
ON SUBSTRING(a.keyid,1,CHARINDEX('.',a.keyid)-1) =
SUBSTRING(b.keyid,1,LEN(SUBSTRING(a.keyid,1,CHARINDEX('.',a.keyid)-1)))
WHERE b.keyid IS NULL --
--Server: Msg 536, Level 16, State 3, Line 1
--Invalid length parameter passed to the substring function.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 21, 2008 at 12:30 am
Chris Morris (7/18/2008)
CREATE TABLE #TableA (KeyID varchar(20))CREATE TABLE #TableB (KeyID varchar(20))
INSERT INTO #TableA
SELECT '11111.001' UNION ALL
SELECT '11111.002' UNION ALL
SELECT '22222.001.01' UNION ALL
SELECT '120394.001' -- ADDED DATA
INSERT INTO #TableB
SELECT '11111.001.05.05' UNION ALL -- WOULD NOT PRINT
SELECT '11111.002.09.01.01' UNION ALL -- WOULD NOT PRINT
SELECT '22222.001.01' UNION ALL -- would NOT print
SELECT '102948.001' -- ADDED DATA
SELECT a.*
FROM #TableA a
LEFT JOIN #TableB b
ON b.keyid LIKE a.keyid + '%'
WHERE b.keyid IS NULL
Output:
keyid
120394.001
I think this hit the spot... anyways never heard from kipp to confirm if this is what he wants
"-=Still Learning=-"
Lester Policarpio
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply