Query Optimization - 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 2005 Forums

Transact-SQL (2005)

Query Optimization

Author

Topic

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-23 : 05:22:58

Hi Guys,

Can anyone please help me optimize the below query:

SELECT IsNull(case
when (IsNull((select top(1) usagemeter
from gpsCountervalues
where lastUpdated >= '04/21/2009 00:00:00'
and LastUpdated <= '05/05/2009 23:59:59'
and usagemeter = 0
and counteruid = 1004
and GpsCounterValues.assetuid = assetInfo.assetuid
order by GpsCounterValuesUid desc),
'-1')) = '-1' then
(select top(1) usagemeter
from gpscountervalues
where lastUpdated >= '04/21/2009 00:00:00'
and counteruid = 1004
and GpsCounterValues.assetuid = assetInfo.assetuid
order by GpsCounterValuesUid asc)
else
(select top(1) usagemeter
from gpscountervalues
where lastUpdated >= '04/21/2009 00:00:00'
and LastUpdated <= '05/05/2009 23:59:59'
and counteruid = 1004
and usagemeter = 0
and GpsCounterValues.assetuid = assetInfo.assetuid)
end,
0) as beginUsage,
IsNull((select top(1) usagemeter
from gpsCountervalues
where lastUpdated >= '04/21/2009 00:00:00'
and LastUpdated <= '05/05/2009 23:59:59'
and counteruid = 1004
and GpsCounterValues.assetuid = assetInfo.assetuid
order by GpsCounterValuesUid desc),
0) as endUsage,
(IsNull((select top(1) usagemeter
from gpsCountervalues
where lastUpdated >= '04/21/2009 00:00:00'
and LastUpdated <= '05/05/2009 23:59:59'
and counteruid = 1004
and GpsCounterValues.assetuid = assetInfo.assetuid
order by GpsCounterValuesUid desc),
0)) - (IsNull(case
when (IsNull((select top(1) usagemeter
from gpsCountervalues
where lastUpdated >= '04/21/2009 00:00:00'
and LastUpdated <= '05/05/2009 23:59:59'
and usagemeter = 0
and counteruid = 1004
and GpsCounterValues.assetuid = assetInfo.assetuid
order by GpsCounterValuesUid desc),
'-1')) = '-1' then
(select top(1) usagemeter
from gpscountervalues
where lastUpdated >= '04/21/2009 00:00:00'
and counteruid = 1004
and GpsCounterValues.assetuid = assetInfo.assetuid
order by GpsCounterValuesUid asc)
else
(select top(1) usagemeter
from gpscountervalues
where lastUpdated >= '04/21/2009 00:00:00'
and LastUpdated <= '05/05/2009 23:59:59'
and counteruid = 1004
and usagemeter = 0
and GpsCounterValues.assetuid = assetInfo.assetuid)
end,
0)) as diff,
AssetInfo.Id,
AssetInfo.AssetUid,
(select errorstatus from asset where uid = assetinfo.assetuid) as errorStatus,
(select DeviceType from Asset where Asset.Uid = AssetInfo.AssetUid) as asset_DeviceType,
1004as counterUid,
(select MileageCounterName
from mileageCounter
where Counteruid = 1004) as mileageCounterName,
IsNull((select top 1 countervalue
from GpsCounterValues
where CounterUid in
(select counteruid
from mileagecounter
where companyuid = 150000126
and mileagecounterName = 'Total Mileage')
and lastUpdated >= '04/21/2009 00:00:00'
and LastUpdated <= '05/05/2009 23:59:59'
and GpsCounterValues.assetuid = assetInfo.assetuid
order by gpscountervaluesuid desc),
0) as TotalMiles,
IsNull((select top(1) CounterValue
from gpsCountervalues
where lastUpdated >= '04/21/2009 00:00:00'
and LastUpdated <= '05/05/2009 23:59:59'
and counteruid = 1004
and GpsCounterValues.assetuid = assetInfo.assetuid
order by GpsCounterValuesUid desc),
0) as CounterValue
FROM assetInfo
WHERE (AssetInfo.CompanyUid in (37))
AND id is not null

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-23 : 05:35:54

OP' Formatting (no changes)


SELECT IsNull(case
                when (IsNull((select top(1) usagemeter
                               from gpsCountervalues
                              where lastUpdated >= '04/21/2009 00:00:00'
                                and LastUpdated <= '05/05/2009 23:59:59'
                                and usagemeter = 0
                                and counteruid = 1004
                                and GpsCounterValues.assetuid = assetInfo.assetuid
                              order by GpsCounterValuesUid desc),
                             '-1')) = '-1' then
                 (select top(1) usagemeter
                    from gpscountervalues
                   where lastUpdated >= '04/21/2009 00:00:00'
                     and counteruid = 1004
                     and GpsCounterValues.assetuid = assetInfo.assetuid
                   order by GpsCounterValuesUid asc)
                else
                 (select top(1) usagemeter
                    from gpscountervalues
                   where lastUpdated >= '04/21/2009 00:00:00'
                     and LastUpdated <= '05/05/2009 23:59:59'
                     and counteruid = 1004
                     and usagemeter = 0
                     and GpsCounterValues.assetuid = assetInfo.assetuid)
              end,
              0) as beginUsage,
       IsNull((select top(1) usagemeter
                from gpsCountervalues
               where lastUpdated >= '04/21/2009 00:00:00'
                 and LastUpdated <= '05/05/2009 23:59:59'
                 and counteruid = 1004
                 and GpsCounterValues.assetuid = assetInfo.assetuid
               order by GpsCounterValuesUid desc),
              0) as endUsage,
       (IsNull((select top(1) usagemeter
                 from gpsCountervalues
                where lastUpdated >= '04/21/2009 00:00:00'
                  and LastUpdated <= '05/05/2009 23:59:59'
                  and counteruid = 1004
                  and GpsCounterValues.assetuid = assetInfo.assetuid
                order by GpsCounterValuesUid desc),
               0)) - (IsNull(case
                               when (IsNull((select top(1) usagemeter
                                              from gpsCountervalues
                                             where lastUpdated >= '04/21/2009 00:00:00'
                                               and LastUpdated <= '05/05/2009 23:59:59'
                                               and usagemeter = 0
                                               and counteruid = 1004
                                               and GpsCounterValues.assetuid = assetInfo.assetuid
                                             order by GpsCounterValuesUid desc),
                                            '-1')) = '-1' then
                                (select top(1) usagemeter
                                   from gpscountervalues
                                  where lastUpdated >= '04/21/2009 00:00:00'
                                    and counteruid = 1004
                                    and GpsCounterValues.assetuid = assetInfo.assetuid
                                  order by GpsCounterValuesUid asc)
                               else
                                (select top(1) usagemeter
                                   from gpscountervalues
                                  where lastUpdated >= '04/21/2009 00:00:00'
                                    and LastUpdated <= '05/05/2009 23:59:59'
                                    and counteruid = 1004
                                    and usagemeter = 0
                                    and GpsCounterValues.assetuid = assetInfo.assetuid)
                             end,
                             0)) as diff,
       AssetInfo.Id,
       AssetInfo.AssetUid,
       (select errorstatus from asset where uid = assetinfo.assetuid) as errorStatus,
       (select DeviceType from Asset where Asset.Uid = AssetInfo.AssetUid) as asset_DeviceType,
       1004as counterUid,
       (select MileageCounterName
          from mileageCounter
         where Counteruid = 1004) as mileageCounterName,
       IsNull((select top 1 countervalue
                from GpsCounterValues
               where CounterUid in
                     (select counteruid
                        from mileagecounter
                       where companyuid = 150000126
                         and mileagecounterName = 'Total Mileage')
                 and lastUpdated >= '04/21/2009 00:00:00'
                 and LastUpdated <= '05/05/2009 23:59:59'
                 and GpsCounterValues.assetuid = assetInfo.assetuid
               order by gpscountervaluesuid desc),
              0) as TotalMiles,
       IsNull((select top(1) CounterValue
                from gpsCountervalues
               where lastUpdated >= '04/21/2009 00:00:00'
                 and LastUpdated <= '05/05/2009 23:59:59'
                 and counteruid = 1004
                 and GpsCounterValues.assetuid = assetInfo.assetuid
               order by GpsCounterValuesUid desc),
              0) as CounterValue
  FROM assetInfo
 WHERE (AssetInfo.CompanyUid in (37))
   AND id is not null

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 06:48:23

Here is a shot for the ugly query above

SELECT		COALESCE(gcv13.UsageMeter, 0) AS BeginUsage
		COALESCE(gcv2.UsageMeter, 0) AS EndUsage,
		COALESCE(gcv2.UsageMeter, 0) - COALESCE(gcv13.UsageMeter, 0) AS Diff,
		ai.ID,
		ai.AssetUID,
		a.ErrorStatus,
		a.DeviceType AS asset_DeviceType,
		1004 AS CounterUID,
		mc.MileageCounterName,
		COALESCE(gcv4.UsageMeter, 0) AS TotalMiles
		COALESCE(w.peso1, 0) AS CounterValue
FROM		dbo.assetInfo AS ai
LEFT JOIN	(
			SELECT		gcv.AssetUID,
					MAX(CASE WHEN gcv.LastUpdated < '20090506' AND gcv.UsageMeter = 0 THEN gcv.GpsCounterValuesUID ELSE NULL END) AS peso1,
					MAX(CASE WHEN gcv.LastUpdated < '20090506' THEN gcv.GpsCounterValuesUID ELSE NULL END) AS peso2,
					MIN(gcv.GpsCounterValuesUID) AS peso3,
					MAX(CASE WHEN gcv.LastUpdated < '20090506' AND mc.CompanyUID IS NOT NULL THEN gcv.GpsCounterValuesUID ELSE NULL END) AS peso4
			FROM		dbo.GpsCounterValues AS gcv
			LEFT JOIN	dbo.MileageCounter AS mc ON mc.CounterUID = gcv.CounterUID
						AND mc.CompanyUID = 150000126
						AND mc.MileageCounterName = 'Total Mileage'
			WHERE		gcv.LastUpdated >= '20090421'
					AND gcv.CounterUID = 1004
			GROUP BY	gcv.AssetUID
		) AS w ON w.AssetUID = ai.AssetUID
LEFT JOIN	dbo.GpsCounterValues AS gcv13 ON gcv.GpsCounterValuesUID = COALESCE(w.peso1, w.peso3)
LEFT JOIN	dbo.GpsCounterValues AS gcv2 ON gcv.GpsCounterValuesUID = w.peso2
LEFT JOIN	dbo.GpsCounterValues AS gcv4 ON gcv.GpsCounterValuesUID = w.peso4
LEFT JOIN	dbo.Assett AS a ON a.UID = ai.AssetUID
LEFT JOIN	dbo.MileageCounter AS mc ON mc.CounterUID = 1004
WHERE		ai.CompanyUID = 37
		AND ai.ID IS NOT NULL

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

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-24 : 02:13:50

@Peso

Thanks for your help..
But this query throws me errors :

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "gcv.GpsCounterValuesUID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "gcv.GpsCounterValuesUID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "gcv.GpsCounterValuesUID" could not be bound.

Query:
SELECT COALESCE(gcv13.UsageMeter, 0) AS BeginUsage,
COALESCE(gcv2.UsageMeter, 0) AS EndUsage,
COALESCE(gcv2.UsageMeter, 0) - COALESCE(gcv13.UsageMeter, 0) AS Diff,
ai.ID,
ai.AssetUID,
a.ErrorStatus,
a.DeviceType AS asset_DeviceType,
1004 AS CounterUID,
mc.MileageCounterName,
COALESCE(gcv4.UsageMeter, 0) AS TotalMiles,
COALESCE(w.peso1, 0) AS CounterValue
FROM dbo.assetInfo AS ai
LEFT JOIN (
SELECT gcv.AssetUID,
MAX(CASE WHEN gcv.LastUpdated < '20090506' AND gcv.UsageMeter = 0 THEN gcv.GpsCounterValuesUID ELSE NULL END) AS peso1,
MAX(CASE WHEN gcv.LastUpdated < '20090506' THEN gcv.GpsCounterValuesUID ELSE NULL END) AS peso2,
MIN(gcv.GpsCounterValuesUID) AS peso3,
MAX(CASE WHEN gcv.LastUpdated < '20090506' AND mc.CompanyUID IS NOT NULL THEN gcv.GpsCounterValuesUID ELSE NULL END) AS peso4
FROM dbo.GpsCounterValues AS gcv
LEFT JOIN dbo.MileageCounter AS mc ON mc.CounterUID = gcv.CounterUID
AND mc.CompanyUID = 37
AND mc.MileageCounterName = 'Total Mileage'
WHERE gcv.LastUpdated >= '20090421'
AND gcv.CounterUID = 1004
GROUP BY gcv.AssetUID
) AS w ON w.AssetUID = ai.AssetUID
LEFT JOIN dbo.GpsCounterValues AS gcv13 ON gcv.GpsCounterValuesUID = COALESCE(w.peso1, w.peso3)
LEFT JOIN dbo.GpsCounterValues AS gcv2 ON gcv.GpsCounterValuesUID = w.peso2
LEFT JOIN dbo.GpsCounterValues AS gcv4 ON gcv.GpsCounterValuesUID = w.peso4
LEFT JOIN dbo.Assett AS a ON a.UID = ai.AssetUID
LEFT JOIN dbo.MileageCounter AS mc ON mc.CounterUID = 1004
WHERE ai.CompanyUID = 37
AND ai.ID IS NOT NULL

Thanks,
Sourav

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-24 : 02:22:18

[code]
SELECT COALESCE(gcv13.UsageMeter, 0) AS BeginUsage,
COALESCE(gcv2.UsageMeter, 0) AS EndUsage,
COALESCE(gcv2.UsageMeter, 0) - COALESCE(gcv13.UsageMeter, 0) AS Diff,
ai.ID,
ai.AssetUID,
a.ErrorStatus,
a.DeviceType AS asset_DeviceType,
1004 AS CounterUID,
mc.MileageCounterName,
COALESCE(gcv4.UsageMeter, 0) AS TotalMiles,
COALESCE(w.peso1, 0) AS CounterValue
FROM dbo.assetInfo AS ai
LEFT JOIN (
SELECT gcv.AssetUID,
MAX(CASE WHEN gcv.LastUpdated < '20090506' AND gcv.UsageMeter = 0 THEN gcv.GpsCounterValuesUID ELSE NULL END) AS peso1,
MAX(CASE WHEN gcv.LastUpdated < '20090506' THEN gcv.GpsCounterValuesUID ELSE NULL END) AS peso2,
MIN(gcv.GpsCounterValuesUID) AS peso3,
MAX(CASE WHEN gcv.LastUpdated < '20090506' AND mc.CompanyUID IS NOT NULL THEN gcv.GpsCounterValuesUID ELSE NULL END) AS peso4
FROM dbo.GpsCounterValues AS gcv
LEFT JOIN dbo.MileageCounter AS mc ON mc.CounterUID = gcv.CounterUID
AND mc.CompanyUID = 37
AND mc.MileageCounterName = 'Total Mileage'
WHERE gcv.LastUpdated >= '20090421'
AND gcv.CounterUID = 1004
GROUP BY gcv.AssetUID
) AS w ON w.AssetUID = ai.AssetUID
LEFT JOIN dbo.GpsCounterValues AS gcv13 ON gcv13.GpsCounterValuesUID = COALESCE(w.peso1, w.peso3)
LEFT JOIN dbo.GpsCounterValues AS gcv2 ON gcv2.GpsCounterValuesUID = w.peso2
LEFT JOIN dbo.GpsCounterValues AS gcv4 ON gcv4.GpsCounterValuesUID = w.peso4
LEFT JOIN dbo.Assett AS a ON a.UID = ai.AssetUID
LEFT JOIN dbo.MileageCounter AS mc ON mc.CounterUID = 1004
WHERE ai.CompanyUID = 37
AND ai.ID IS NOT NULL
[/code]

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-24 : 03:16:33

It worked exactly as expected...

Thanks Guys! You guys are genius..

Sourav

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 03:18:25

Is there a speed difference from your original query and the suggested query?

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

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-25 : 05:08:34