Slow performance - Using top 1 Order by Desc

Question

Slow performance - Using top 1 Order by Desc

vineet_dubey1975

SSCrazy

Points: 2306
More actions
November 6, 2017 at 7:34 am

#341195

Hi I am using SQL Construct on table which contains Top 1 and order by values usage/ Reading table contain billions of rows.
This is causing a very slow performing query. Can some body help me to have a batter way to handle this.
e.g.
select top 1 prev.[Value] from Usage x with(nolock)
            inner join Reading prev with(nolock) on prev.TypeId = 'ABC'
            and prev.FK_UsageID = x.FK_UsageID where x.FK_MeterID = 22998
            and x.FK_MachineD = x.FK_MachineD
            and x.UsageID < 288150964
            and cast(x.EventDateTime as Date) = cast('2017-03-07 19:05:59.000' as date)
            and datepart(hh, x.EventDateTime) = datepart(hh, '2017-03-07 19:05:59.000' )
            order by x.UsageID desc
Regards,
VD

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Let's start with removing the unnecessary functions in your WHERE clause, and formatting your code better, along with removing a totally unnecessary line in your WHERE clause that requires a column to be equal to itself. Note that I also re-ordered the ON clause for your JOIN to make it clearer:
SELECT TOP (1) prev.[Value] FROM Usage AS x WITH (NOLOCK) INNER JOIN Reading AS prev WITH (NOLOCK) ON prev.FK_UsageID = x.FK_UsageID AND prev.TypeId = 'ABC' WHERE x.FK_MeterID = 22998 AND x.UsageID < 288150964 AND x.EventDateTime >= '2017-03-07 19:00:00.000' AND x.EventDateTime < '2017-03-07 20:00:00.000' ORDER BY x.UsageID DESC;

Next steps: Do you have an index on the Reading table? An ideal one would be on FK_UsageID and be a "filtered" index, using a WHERE clause of WHERE TypeId = 'ABC'. Given such an index, this query might go as fast as it can. Lacking it, you've likely got a very poorly performing query.

EDIT: You can probably also add UsageID < 288150964 to the WHERE clause for that index, given that it will also need to be true for a row to qualify for this query.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

vineet_dubey1975 SSCrazy Points: 2306 More actions · Answer 2

Hey Stev ,
I will check OupPut of the query with the result set and get back to you.
Thanks
VD

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

sgmunson - Monday, November 6, 2017 7:45 AM
An ideal one would be on FK_UsageID and be a "filtered" index, using a WHERE clause of WHERE TypeId = 'ABC'. Given such an index, this query might go as fast as it can. Lacking it, you've likely got a very poorly performing query.
EDIT: You can probably also add UsageID < 288150964 to the WHERE clause for that index, given that it will also need to be true for a row to qualify for this query.

Be careful with filtered indexes. Unless the query always, always filters for TypeID of ABC (and never of BCD or anything else) and always filters by usageid of 288150964, a normal index will likely be better (more stable at least)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sgmunson SSC Guru Points: 110640 More actions · Answer 4

GilaMonster - Monday, November 6, 2017 8:06 AM
sgmunson - Monday, November 6, 2017 7:45 AM
An ideal one would be on FK_UsageID and be a "filtered" index, using a WHERE clause of WHERE TypeId = 'ABC'. Given such an index, this query might go as fast as it can. Lacking it, you've likely got a very poorly performing query.
EDIT: You can probably also add UsageID < 288150964 to the WHERE clause for that index, given that it will also need to be true for a row to qualify for this query.
Be careful with filtered indexes. Unless the query always, always filters for TypeID of ABC (and never of BCD or anything else) and always filters by usageid of 288150964, a normal index will likely be better (more stable at least)

I agree, although I rarely encounter hard-coded values like this, and most of the time, when they exist, it's for a specific reason or purpose, and

GilaMonster - Monday, November 6, 2017 8:06 AM
sgmunson - Monday, November 6, 2017 7:45 AM
An ideal one would be on FK_UsageID and be a "filtered" index, using a WHERE clause of WHERE TypeId = 'ABC'. Given such an index, this query might go as fast as it can. Lacking it, you've likely got a very poorly performing query.
EDIT: You can probably also add UsageID < 288150964 to the WHERE clause for that index, given that it will also need to be true for a row to qualify for this query.
Be careful with filtered indexes. Unless the query always, always filters for TypeID of ABC (and never of BCD or anything else) and always filters by usageid of 288150964, a normal index will likely be better (more stable at least)

I agree, although in my experience, hard-coded values like that are often for a specific purpose that isn't deviated from or duplicated in similar form elsewhere, so that's where my head was. Good advice none the less.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)