SELECT
					distinct a.deviceId,
					sum(b.pointsUPS) as upspoints,
					sum(b.pointsIovations) as iovationspoints,
					case 
					when z.deviceid is null then 'Good'
					else 'Bad' end as devicestatus,
					isnull(acct.cus_id_nr,'') as cus_id_nr,
					isnull(acct.uuid,'') as uuid
					from dbo.fpsdeviceId_iovationelements as a
					join dbo.fpsiovationselements as b
					on(a.elementcode=b.code)
					LEFT outer join dbo.fpsbaddevice as z
					on(a.deviceId=z.deviceId)
					left outer join dbo.tfpstxmldtl as acct
					on(a.deviceId=acct.deviceId)
					where a.deviceid is not null
				and convert(varchar,a.updatedDate,101) = '07/13/2009'
					group by a.deviceID,acct.uuid,acct.cus_id_nr,ac_nr,z.deviceid
					order by deviceId

SELECT
			distinct 
			a.deviceId,
			sum(b.pointsUPS) as upspoints,
			sum(b.pointsIovations) as iovationspoints,
			case 
			 when z.deviceid is null then 'Good'
			 else 'Bad' 
			end as devicestatus,
			isnull(acct.cus_id_nr,'') as cus_id_nr,
			isnull(acct.uuid,'') as uuid
			from dbo.fpsdeviceId_iovationelements as a
			join dbo.fpsiovationselements as b
			on(a.elementcode=b.code)
			LEFT outer join dbo.fpsbaddevice as z
			on(a.deviceId=z.deviceId)
			left outer join dbo.tfpstxmldtl as acct
			on(a.deviceId=acct.deviceId)
where a.deviceid is not null
and a.updatedDate >= '20090713'                                     -- = '07/13/2009'
and a.updatedDate < '20090714'
			group by a.deviceID,acct.uuid,acct.cus_id_nr,ac_nr,z.deviceid
			order by deviceId

quote:

---

Originally posted by tee11732

Thanks for the help. It sped it up by 3 seconds. Table tfpstxmldtl is the largest with 1021248 records but it has 10 indices. The other tables are small...30-40 rows max and are not indexed.

---

    SELECT	  a.deviceId
			, SUM (b.pointsUPS)				AS SUM_upspoints
			, SUM(b.pointsIovations)		AS SUM_pointsIovations
		,	 CASE WHEN z.deviceid IS NULL   THEN 'Good'
											ELSE 'Bad' 
			   END AS devicestatus
			, COALESCE(acct.cus_id_nr, '')	AS cus_id_nr
			, COALESCE(acct.uuid, '')		AS uuid
      FROM	  fpsdeviceId_iovationelements  AS a
INNER JOIN fpsiovationselements				AS b
		ON a.elementcode					= b.code
 LEFT JOIN fpsbaddevice AS z
		ON a.deviceId=z.deviceId
 LEFT JOIN tfpstxmldtl AS acct
		ON a.deviceId=acct.deviceId
     WHERE a.deviceid is not null
--     AND CONVERT(varchar, a.updatedDate101) = '07/13/2009'
	   AND a.updateDate101 > = '07/13/2009'
       AND a.updateDate101 < = '0713/2009 12:59:59:999'
  GROUP BY a.deviceIDacct.uuidacct.cus_id_nrac_nrz.deviceid
  ORDER BY deviceId