General comments: unless you're doing a group by you don't need "Top 100
perent"  Instead of doing a correlated sub-query I used a join to an
aggregate query
(i.e. replaced CEMW_EffectiveDate = (SELECT MAX(CEMW_EffectiveDate) FROM 
dbo.CEEmpMinWage WHERE dbo.CEEmpMinWage.CEMW_EmpNo =  
dbo.CEEmpPermData.CEEP_EmpNo)

with

inner join
	(select CEMW_EmpNo,max(CEMW_EffectiveDate) as MaxEffDate
	from dbo.CEEmpMinWage Wage1
	group by Wage1.CEMW_EmpNo) as qry_Wage
 on CEEmpMinWage.CEMW_EmpNo=qry_Wage.CEMW_EmpNo 
	and a.CEMW_EffectiveDate=qry_Wage.MaxEffDate

this allows the optimizer to run the aggregate query once and place in
temporary table to join to other tables.  make sure that all tables have an
index (possibly clustered) on employee number.  To speed lookups/joins.
Look at the execution plan in query analyzer and see where your bottlenect
is see if you can restructure or change the database to improve that
bottlenect. 

If you need additional help post the textual query execution plan and the
percent total cost for each and we might be able to make further
suggestions.  

I hope this helps.

Brian Freeman


Possible improved query:
SELECT  dbo.CEEmpPermData.CEEP_EmpNo AS [Emp #], 
	dbo.CEEmpPermData.CEEP_Prefix AS Sal, 
      	dbo.CEEmpPermData.CEEP_FirstName AS [First Name], 
	dbo.CEEmpPermData.CEEP_MiddleName AS [MI/Name], 
      	dbo.CEEmpPermData.CEEP_LastName AS [Last Name], 
	dbo.CEEmpPermData.CEEP_Suffix AS Suffix, 
	c.CEER_RegionID AS Region, 
      	dbo.CEEmpPermData.CEEP_EmployeeEmail AS [E-Mail], 
	CONVERT(varchar, a.CEEH_OrigHireDate, 101) AS [Hire Date], 
	CONVERT(varchar, a.CEEH_TermDate, 101) AS [Term Date], 
	CONVERT(varchar, a.CEEH_RehireDate, 101) AS [Rehire Date], 
	CONVERT(varchar, a.CEEH_EffectiveDate, 101) AS [Effective Date], 
	CONVERT(varchar, dbo.CEEmpPermData.CEEP_DateOfBirth, 101) AS
CEEP_DateofBirth, 
	dbo.CEEmpPermData.CEEP_SSN, 
	dbo.CEEmpPermData.CEEP_Gender, 
	dbo.CEEmpPermData.CEEP_LevelOfEducation, 
	dbo.CEEmpPermData.CEEP_PreviousExperience, 
      	REPLACE(REPLACE(CONVERT(varchar(20), a.CEEH_OrigHireDate, 120), "-",
""), " ", "") AS [Hire Sort], 
	REPLACE(REPLACE(CONVERT(varchar(20), a.CEEH_TermDate, 120), "-",
""), " ", "") AS [Term Sort], 
	REPLACE(REPLACE(CONVERT(varchar(20), a.CEEH_RehireDate, 120), "-",
""), " ", "") AS [Rehire Sort], 
      	REPLACE(REPLACE(CONVERT(varchar(20), a.CEEH_EffectiveDate, 120),
"-", ""), " ", "") AS [Effective Sort],
	dbo.CEEmpMinWage.CEMW_EmpMinWage as [Min Wage], 
      	dbo.CEEmpMinWage.CEMW_EffectiveDate as [Min Wage EffecDate]
FROM   dbo.CEEmpPermData INNER JOIN dbo.CEEmpHistData a 
	ON dbo.CEEmpPermData.CEEP_EmpNo = a.CEEH_EmpNo 
INNER JOIN dbo.CEEmpMinWage 
	ON dbo.CEEmpPermData.CEEP_EmpNo = dbo.CEEmpMinWage.CEMW_EmpNo 
LEFT OUTER JOIN 
(select RA1.CEER_RegionID, RA1.CEER_EffectiveDate,RA1.CEER_EmpNo
	from dbo.CEEmpRegionAssignments RA1
	inner join 
	(SELECT     RA2.ceer_empno,MAX(RA2.CEER_EffectiveDate) as MaxEffDate
            FROM          dbo.CEEmpRegionAssignments RA2
		group by RA2.cer_empno
	) as qry_RA1
	ON RA1.CEER_EffectiveDate=qry_RA1.MaxEffDate
		and RA1.ceer_EmpNo=qry_RA1.ceer_EmpNo
) as c
	ON dbo.CEEmpPermData.CEEP_EmpNo = c.CEER_EmpNo 
inner join
	(select CEEH_EmpNo,max(CEEH_EffectiveDate) as MaxEffDate
	from dbo.CEEmpHistData Hist1
	group by Hist1.CEH_EmpNo) as qry_Hist
 on a.CEEH_EmpNo=qry_Hist.EmpNo 
	and a.CEEH_EffectiveDate=qry_Hist.MaxEffDate
inner join
	(select CEMW_EmpNo,max(CEMW_EffectiveDate) as MaxEffDate
	from dbo.CEEmpMinWage Wage1
	group by Wage1.CEMW_EmpNo) as qry_Wage
 on CEEmpMinWage.CEMW_EmpNo=qry_Wage.CEMW_EmpNo 
	and a.CEMW_EffectiveDate=qry_Wage.MaxEffDate



WHERE     
(a.CEEH_RehireDate < a.CEEH_TermDate) 
AND 
(a.CEEH_TermDate IS NOT NULL) 
OR
(a.CEEH_RehireDate is null)
ORDER BY CAST(c.CEER_RegionID AS numeric), 
dbo.CEEmpPermData.CEEP_LastName, dbo.CEEmpPermData.CEEP_FirstName, 
c.CEER_EffectiveDate DESC

-----Original Message-----
From: Carolyn Friedberg [mailto:cfriedberg@c...]
Sent: Thursday, January 23, 2003 11:10 AM
To: sql language
Subject: [sql_language] Select stmt needing better performance


I know the code below is lengthy but I need to know just how to get better 
performance.  It will draw up 3341 records and takes approximately 22 
seconds.  My data is date sensitive so I need to use the date criteria in 
some fashion.

Thank you,  Carolyn


SELECT     TOP 100 PERCENT dbo.CEEmpPermData.CEEP_EmpNo AS [Emp #], 
dbo.CEEmpPermData.CEEP_Prefix AS Sal, 
      dbo.CEEmpPermData.CEEP_FirstName AS [First Name], 
dbo.CEEmpPermData.CEEP_MiddleName AS [MI/Name], 
      dbo.CEEmpPermData.CEEP_LastName AS [Last Name], 
dbo.CEEmpPermData.CEEP_Suffix AS Suffix, c.CEER_RegionID AS Region, 
      dbo.CEEmpPermData.CEEP_EmployeeEmail AS [E-Mail], CONVERT(varchar, 
a.CEEH_OrigHireDate, 101) AS [Hire Date], CONVERT(varchar, 
      a.CEEH_TermDate, 101) AS [Term Date], CONVERT(varchar, 
a.CEEH_RehireDate, 101) AS [Rehire Date], CONVERT(varchar, 
a.CEEH_EffectiveDate, 101) 
      AS [Effective Date], CONVERT(varchar, 
dbo.CEEmpPermData.CEEP_DateOfBirth, 101) AS CEEP_DateofBirth, 
dbo.CEEmpPermData.CEEP_SSN, 
      dbo.CEEmpPermData.CEEP_Gender, 
dbo.CEEmpPermData.CEEP_LevelOfEducation, 
dbo.CEEmpPermData.CEEP_PreviousExperience, 
      REPLACE(REPLACE(CONVERT(varchar, a.CEEH_OrigHireDate, 120), "-
", ""), " ", "") AS [Hire Sort], REPLACE(REPLACE(CONVERT(varchar, 
      a.CEEH_TermDate, 120), "-", ""), " ", "") AS [Term Sort], REPLACE
(REPLACE(CONVERT(varchar, a.CEEH_RehireDate, 120), "-", ""), " ", "") AS 
[Rehire Sort], 
      REPLACE(REPLACE(CONVERT(varchar, a.CEEH_EffectiveDate, 120), "-
", ""), " ", "") AS [Effective Sort],
	  dbo.CEEmpMinWage.CEMW_EmpMinWage as [Min Wage], 
      dbo.CEEmpMinWage.CEMW_EffectiveDate as [Min Wage EffecDate]
FROM         dbo.CEEmpPermData INNER JOIN
      dbo.CEEmpHistData a ON dbo.CEEmpPermData.CEEP_EmpNo = a.CEEH_EmpNo 
INNER JOIN
      dbo.CEEmpMinWage ON dbo.CEEmpPermData.CEEP_EmpNo = 
dbo.CEEmpMinWage.CEMW_EmpNo LEFT OUTER JOIN
      dbo.CEEmpRegionAssignments c ON dbo.CEEmpPermData.CEEP_EmpNo = 
c.CEER_EmpNo AND c.CEER_EffectiveDate 
          (SELECT     MAX(CEER_EffectiveDate)
            FROM          CEEmpRegionAssignments d
            WHERE      c.ceer_empno = d.ceer_empno)
WHERE     (a.CEEH_RehireDate IS NOT NULL) AND (a.CEEH_RehireDate < 
a.CEEH_TermDate) AND (a.CEEH_EffectiveDate 
      (SELECT MAX(CEEH_EffectiveDate)
        FROM CEEmpHistData b
        WHERE a.ceeh_empno = b.ceeh_empno)) OR
  (a.CEEH_RehireDate IS NULL) AND (a.CEEH_EffectiveDate 
      (SELECT MAX(CEEH_EffectiveDate)
      FROM CEEmpHistData b
       WHERE a.ceeh_empno = b.ceeh_empno)) AND (a.CEEH_TermDate IS NOT 
NULL) and   
(CEMW_EffectiveDate = (SELECT MAX(CEMW_EffectiveDate) FROM 
dbo.CEEmpMinWage WHERE dbo.CEEmpMinWage.CEMW_EmpNo =  
dbo.CEEmpPermData.CEEP_EmpNo))
ORDER BY CAST(c.CEER_RegionID AS numeric), 
dbo.CEEmpPermData.CEEP_LastName, dbo.CEEmpPermData.CEEP_FirstName, 
c.CEER_EffectiveDate DESC