Compare two query - 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

General SQL Server Forums

New to SQL Server Administration

Compare two query

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-11 : 05:17:18

Hi,

Two following query retrieve employees that have serviced more than one customer.
I just want to know what index can be useful for performance from analysis execution plan, and how can I compare two solution with their plans?







USE Northwind;
GO

SELECT E.EmployeeID
  FROM Employees E
  JOIN Orders O
    ON E.EmployeeID = O.EmployeeID
 GROUP BY E.EmployeeID
HAVING COUNT(DISTINCT O.CustomerID) > 1


SELECT E.EmployeeID
  FROM Employees E
  JOIN Orders O
    ON E.EmployeeID = O.EmployeeID
 GROUP BY E.EmployeeID
HAVING CAST(CHECKSUM(MIN(CustomerID)) AS BIGINT) <>
       CAST(CHECKSUM(MAX(CustomerID)) AS BIGINT)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-11 : 05:31:25

First of all, using CHECKSUM is a way to disaster since it produces "false positives".
This will be enough

SELECT		e.EmployeeID
FROM		dbo.Employees AS e
INNER JOIN	dbo.Orders AS o ON o.EmployeeID = e.EmployeeID
GROUP BY	e.EmployeeID
HAVING		MIN(o.CustomerID) < MAX(o.CustomerID)

N 56°04'39.26"
E 12°55'05.63"

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-11 : 06:34:37

I forgot something!
I want use CHECKSUM for this:

-- X-Y = 0 --> X = Y
SELECT E.EmployeeID
  FROM Employees E
  JOIN Orders O
    ON E.EmployeeID = O.EmployeeID
 GROUP BY E.EmployeeID
HAVING CAST(CHECKSUM(MIN(CustomerID)) AS BIGINT) -
       CAST(CHECKSUM(MAX(CustomerID)) AS BIGINT) <> 0
      

SELECT E.EmployeeID
  FROM Employees E
  JOIN Orders O
    ON E.EmployeeID = O.EmployeeID
 GROUP BY E.EmployeeID
HAVING MIN(CustomerID) - MAX(CustomerID) <> 0
-- Error:
-- Operand data type nchar is invalid for subtract operator.

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 06:35:17

Test it?

EXISTS might be faster if you have a suitable covering index.


SELECT EmployeeID
FROM Orders AS O1
WHERE EXISTS
(
    SELECT * 
    FROM Orders AS O2 
    WHERE O2.EmployeeID = O1.EmployeeID 
      AND O2.CustomerID <> O1.CustomerID
)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-11 : 06:44:33

quote:
Originally posted by Kristen

Test it?

EXISTS might be faster if you have a suitable covering index.

SELECT DISTINCT EmployeeID
FROM Orders AS O1
WHERE EXISTS
(
SELECT *
FROM Orders AS O2
WHERE O2.EmployeeID = O1.EmployeeID
AND O2.CustomerID <> O1.CustomerID
)

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 06:57:23

Yeah, that would be important!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-12 : 03:20:49

Don't. Use. Checksum.

SELECT	CHECKSUM('a'),
	CHECKSUM('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources