reducing table scans - 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

SQL Server 2000 Forums

Transact-SQL (2000)

reducing table scans

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 05:47:46

hi guys and gals...

i was wondering is it possible to reduce 2 table scans (in bold)
of MyTable2 and Adresses into just one scan?


SELECT 	OO.org_id, VP.plan_year, AO.device_id, OO.Name  
FROM 	MyTable1 OO
	-- this returns main addresses from Address table AO.main_device=1
	LEFT JOIN (MyTable2 OC
		INNER JOIN Adresses AO ON (OC.device_id = AO.device_id 	and  AO.main_device=1)
	) ON OO.org_id = OC.org_id 
	LEFT JOIN MyTable4 VP ON (OO.org_id = VP.org_id AND VP.plan_year = '2004')  
WHERE  	-- must return all data from upper joins where even one address is in germany: country_code = 'de'
	-- each org can have multiple addresses
	exists (SELECT 	*
		FROM 	MyTable2 t1 
			INNER JOIN Adresses t2 ON t1.device_id = t2.device_id
		WHERE 	t1.org_id = OO.org_id and country_code = 'de')

so basically i want to get all organizations which have any type of address in germany,
but show their main address which can be anywhere in the world.

if anyone need any more info, say so...

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-20 : 12:40:56

The only thing I could think of would be to do temp table, but that may not give you much benefit if any... how big are the tables?

How often does the address table change? Could you index it on country code? create a view?

Sorry not much help I don't think...

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 12:50:20

i've got indexes and all of that set up...
temp table does me no good... unfortunately.
i was just wondering if it's possible to do it in one scan... it does 2 index scans now. one in the from and one in the exist.
the thing is it looks for 2 different things.... so my guess is it can't be done... but hey i'm open to suggestions

Addresses = 26213 rows
MyTable2 = 70280 rows

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-20 : 13:49:22

I built some small tables to test with, and I couldn't figure out how to get it to only do one. I think you are correct about the two different objectives...

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 14:16:57

ok... thanx man... u just confirmed my thoughts.
2 is ok too.. before there were 6 scans so i believe i downsized it ok...

Go with the flow & have fun! Else fight the flow

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-20 : 16:26:23

If there is (at most) one row in Addresses with main_device = 1 for each org_id in MyTable1, then yes, it's possible:


SELECT 	OO.org_id, VP.plan_year, AO.device_id, OO.Name  
FROM MyTable1 AS OO
INNER JOIN (
    SELECT OC.org_id, MAX(CASE WHEN AO.main_device = 1 THEN AO.device_id END) AS device_id
    FROM MyTable2 AS OC
    INNER JOIN Adresses AS AO ON OC.device_id = AO.device_id
    GROUP BY OC.org_id
    HAVING MAX(CASE WHEN country_code = 'de' THEN 1 END) IS NOT NULL
  ) AS AO ON OO.org_id = AO.org_id
LEFT JOIN MyTable4 VP ON OO.org_id = VP.org_id AND VP.plan_year = '2004'

However, the performance is likely to be far less predicable.

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 01:38:13

Does this do the same thing then? Dunno how many scans it is though ...


SELECT 	OO.org_id, VP.plan_year, AO.device_id, OO.Name  
FROM 	MyTable1 OO
	-- this returns main addresses from Address table AO.main_device=1
	LEFT JOIN (MyTable2 OC
		INNER JOIN Adresses AO ON (OC.device_id = AO.device_id 	and  AO.main_device=1)
	) ON OO.org_id = OC.org_id 
	LEFT JOIN MyTable4 VP ON (OO.org_id = VP.org_id AND VP.plan_year = '2004')  
        -- Only if German address exists
	LEFT JOIN Adresses t2 ON t1.device_id = t2.device_id
		AND t2.country_code = 'de' -- Country code is in t2, right?
WHERE  	-- must return all data from upper joins where even one address is in germany: country_code = 'de'
	-- each org can have multiple addresses
	t2.device_id IS NOT NULL	-- DE country must exist
	exists (SELECT 	*
		FROM 	MyTable2 t1 
			INNER JOIN Adresses t2 ON t1.device_id = t2.device_id
		WHERE 	t1.org_id = OO.org_id and country_code = 'de')

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 05:39:59

general: thanx... i'll have to dig into this to see... but i suspect performace will suck....
EDIT:
i forgot to metion something important: device_id and org_id are GUIDs
ergo, no aggregates
kristen: country_code is in address....

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 06:00:32

"kristen: country_code is in address...."

OK, so hopefully my code will work, Let me know if it makes a difference, I'm curious to know ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 06:19:17

well my code returns 21165 rows
yours returns 21151 rows

but it's ok. if they need it any faster, i'll post more details...

i acctually took your approach but i didn't think 1 more day of debuging would be worth the minimum increase in speed.
maybe next week when the testing continues...

Go with the flow & have fun! Else fight the flow

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-21 : 07:50:51

quote:
but i suspect performace will suck....

Course it will! But you were asking to get rid of joins, not make the performance better.

Hmm... never realized that MIN and MAX don't work on GUIDs. That's quite silly really, given that they can be compared with <, >. What's even sillier is that you can cast them to binary(16), aggregate that and then cast them back.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 07:58:35

yeah you just be mean...

my goal is to make it as fast as possible...

i hope 2005 corrects this with GUIDs.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 08:19:47

"well my code returns 21165 rows
yours returns 21151 rows"

Scary ... is my assumption correct that Adresses.device_id is a [NOT NULL] primary key?

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-21 : 08:32:44

It surprises me too. I would have expected that you'd get more rows from Kristen's query where orgs have multiple addresses in Germany.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 09:09:14

well this is the data that is in my code and not in kristen's:


name	main_device 	country_code	org_id 					plan_year	device_id
name1	NULL		NULL		86E45E56-4696-4416-BBBA-A6DC17A00A5F	NULL		NULL
name2	NULL		NULL		A5344719-4D67-4B06-9911-CBB1D5BACEB7	NULL		NULL
name3	NULL		NULL		73C9382E-6132-4E44-87AB-77DF79979785	NULL		NULL
name4	NULL		NULL		9AB90640-FB00-469C-A277-E398BC6B77FF	NULL		NULL
name5	NULL		NULL		FBCE5225-491B-43D7-9765-20653BD0A447	NULL		NULL
name6	NULL		NULL		6BA01FF9-7E68-448A-B138-55319CFAE3F0	NULL		NULL
name7	NULL		NULL		C3EC1288-49A5-48B6-BA28-89154CA28B23	NULL		NULL
name8	NULL		NULL		36A2392D-E758-48CA-86A7-83B797025C36	2004		NULL
name9	NULL		NULL		D1A18A1F-6453-41F0-816D-C241618F59EB	NULL		NULL
name9	NULL		NULL		6B5D8C9D-E33C-4E84-9B06-C34FB148E604	NULL		NULL
name10	NULL		NULL		E84343FA-6ADD-472B-AD5C-3E607ABE820E	NULL		NULL
name11	NULL		NULL		B86FCD5A-12B2-4CE5-95A0-91195BE91711	NULL		NULL
name12	1		es		39A59C84-14E2-4693-9997-47C4975A1554	NULL		8024D6C0-4152-499F-A89F-B3F1B83B5E16
name12	1		us		04CE5046-D37B-4E1B-B191-F98E897D3FF4	NULL		B573B8F4-FAD4-4A44-AD3F-640F12F19452

two org's that are in 'es' and 'us' have their main address in Spain and USA, but have secondary addresses in Germany (main_device=0)
all others that have device_id = null have main_device = 0.
Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 10:47:34

How do the NULL deviceId have an address in Germany? Isn't that the joining column?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 11:22:05

no because there is


MyTable1 OO
-- this returns main addresses from Address table AO.main_device=1
	LEFT JOIN (MyTable2 OC
		INNER JOIN Adresses AO ON (OC.device_id = AO.device_id 	and  AO.main_device=1)
	) ON OO.org_id = OC.org_id

MyTable2 is an intermediate table which has org_id and device_id. basicaly many to many.
to those that have device_id null have main_device = 0. because of the left join with MyTable1 there is of course null.
they show because exsist returns true for them because they have an address in germany but its main_device = 0 not 1.

if i would need to get it any faster i'll post DDL and some DML... no worries...

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 12:02:21

Ah, so I cocked up my extra outer join - need to use both MyTable2 and Addresses ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 12:50:40

yeah... and there goes another table scan...

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 13:20:42

Did you ever study BitSlice? A means by which a CPU could do arithmetic 4 bits in two parallel processes. Only problem was that the process handling the upper 4 bits needed to know if there was "carry" from the lower 4 bits. So there was a means of estimating the "carry" from the lower 4 bits. If I ever knew how that bit of magic worked I ahve forgotten it!

So ... what you need is something that will estimate whether a MyTable1 has a German address, or not!

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 13:34:39

bitslice? no...

not estimate, just check

Go with the flow & have fun! Else fight the flow

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources