SQL fine tune - 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)

SQL fine tune

Author

Topic

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 16:08:33

Is there any best way to find tune this:
I am comparing some value with around five servers, but takes long time.

I need to show the exception if the values are not matching

SELECT customerid, s1.status s1, s2.status s2, s3.status s3, s4.status s4, s5.status s5
FROM (select customerid, name, status from server1) s1
JOIN (select customerid, name, status from server2) s2 on s1.customerid = s2.customerid
JOIN (select customerid, name, status from server3) s3 on s1.customerid = s3.customerid
LEFT JOIN (select customerid, name, status from server4) s4 on s1.customerid = s4.customerid
LEFT (select customerid, name, status from server5) s5 on s1.customerid = s5.customerid
where s1.status <> s2.status
and s1.status <> s3.status
and s1.status <> s4.status
and s1.status <> s5.status

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 16:22:07

What is yor objective?

With your query as of now, you will get customers with same status from server3 and server4, becuase they are both inequal to customer from server1.

Example: Customer #100 from Server1 has status 'a', customer #100 from server3 has status 'b' and customer #100 from server4 has status 'b', is a valid combination.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 16:24:09

this might do the same thing

select customerid from (
SELECT customerid, status from server1 union all
SELECT customerid, status from server2 union all
SELECT customerid, status from server3 union all
SELECT customerid, status from server4 union all
SELECT customerid, status from server5) as d
group by customerid having min(status) <> max(status)

unless you provide better explanation, or some sample data and expected output, your problem may be unsolved.

Peter Larsson
Helsingborg, Sweden

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 16:25:18

yes its a valid combination. i need to display the customer #100 only.
thanks Peter

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 16:35:24

What is performance with suggestion posted 07/02/2007 : 16:24:09 ?

Peter Larsson
Helsingborg, Sweden

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 16:40:45

I have given below the sample date.
select 'c1' customerid, 'a' status into #server1
union all
select 'c2' customerid, 'b' status
union all
select 'c3' customerid, 'z' status
union all
select 'c4' customerid, 'a' status

select 'c1' customerid, 'a' status into #server2
union all
select 'c2' customerid, 'b' status
union all
select 'c3' customerid, 'z' status
union all
select 'c4' customerid, 'a' status

select 'c1' customerid, 'x' status into #server3
union all
select 'c2' customerid, 'b' status
union all
select 'c3' customerid, 'c' status
union all
select 'c4' customerid, 'a' status

select 'c1' customerid, 'x' status into #server4
union all
select 'c2' customerid, 'b' status
union all
select 'c3' customerid, 'c' status
union all
select 'c4' customerid, 'a' status
union all
select 'c5' customerid, 'a' status
union all
select 'c6' customerid, 'a' status

select 'c1' customerid, 'x' status into #server5
union all
select 'c2' customerid, 'j' status
union all
select 'c3' customerid, 'c' status
union all
select 'c4' customerid, 'a' status
union all
select 'c5' customerid, 'a' status

SELECT s1.customerid, s1.status s1, s2.status s2, s3.status s3, s4.status s4, s5.status s5
FROM (select customerid, status from #server1) s1
JOIN (select customerid, status from #server2) s2 on s1.customerid = s2.customerid
JOIN (select customerid, status from #server3) s3 on s1.customerid = s3.customerid
LEFT JOIN (select customerid, status from #server4) s4 on s1.customerid = s4.customerid
LEFT JOIn (select customerid, status from #server5) s5 on s1.customerid = s5.customerid
where s1.status <> s2.status
or s1.status <> s3.status
or s1.status <> s4.status
or s1.status <> s5.status

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 16:44:49

Is server1 some kind of "master" server?
Because this sample data is also valid

insert #server1 select 'c0', 'd'
insert #server2 select 'c0', 'g'
insert #server3 select 'c0', 'g'
insert #server4 select 'c0', 'g'
insert #server5 select 'c0', 'g'

Peter Larsson
Helsingborg, Sweden

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 16:46:00

yes Peter

hey

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 16:52:12

then the out put i need as follows:
c0 d g g g g
c1 a a x x x
c2 b b b b j
c3 z z c c c

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 16:52:19

Are you only interested in the CustomerID's? Where the status on Server2 to Server5 is different from the status on Server1?
Try this

SELECT DISTINCT	s1.CustomerID
FROM		#Server1 AS s1
LEFT JOIN	(
			SELECT	CustomerID, Status FROM #Server2 UNION ALL
			SELECT	CustomerID, Status FROM #Server3 UNION ALL
			SELECT	CustomerID, Status FROM #Server4 UNION ALL
			SELECT	CustomerID, Status FROM #Server5
		) AS s ON s.CustomerID = s1.CustomerID
			AND s.Status <> s1.Status

Peter Larsson
Helsingborg, Sweden

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 16:53:31

nope, we need which server the status is wrong as well.

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 16:55:24

Your "JOIN" should be edited to "LEFT JOIN", as the other servers.

Peter Larsson
Helsingborg, Sweden

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 16:59:28

thanks much, i am working on it now.

hey

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 17:00:44

only server 4 and 5 is left join

hey

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 17:10:08

I got some idea based on you SQL. Cheers

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 17:14:33

[code]SELECT s1.customerid,
s1.status AS s1,
s2.status AS s2,
s3.status AS s3,
s4.status AS s4,
s5.status AS s5
FROM #server1 AS s1
INNER JOIN #server2 AS s2 on s2.customerid = s1.customerid
INNER JOIN #server3 AS s3 on s3.customerid = s1.customerid
LEFT JOIN #server4 AS s4 on s4.customerid = s1.customerid
LEFT JOIN #server5 AS s5 on s5.customerid = s1.customerid
where s1.status <> s2.status
or s1.status <> s3.status
or s1.status <> s4.status
or s1.status <> s5.status[/code]

Peter Larsson
Helsingborg, Sweden

hey001us
Posting Yak Master

185 Posts

Posted - 2007-07-02 : 17:19:55

Thanks much.

hey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 17:25:01

Here is a query that runs twice as fast (20 reads instead of 44).

select		customerid,
		max(s1) as s1,
		max(s2) as s2,
		max(s3) as s3,
		max(s4) as s4,
		max(s5) as s5
from		(
			select	customerid, status as s1, '' as s2, '' as s3, '' as s4, '' as s5 from #server1 union all
			select	customerid, null, status, '', '', '' from #server2 union all
			select	customerid, null, '', status, '', '' from #server3 union all
			select	customerid, null, '', '', status, '' from #server4 union all
			select	customerid, null, '', '', '', status from #server5
		) as d
group by	customerid
having		max(s1) <> max(s2)
		or max(s1) <> max(s3)
		or max(s1) <> max(s4)
		or max(s1) <> max(s5)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources