Return most recent result or a default

Question

Post reply

Return most recent result or a default

JChrisCompton

SSCommitted

Points: 1609
More actions
January 26, 2011 at 5:48 pm

#233413

I need to return the most recent blog post in a particular category.
Similar question: http://www.sqlservercentral.com/Forums/FindPost648858.aspx "T-SQL (SS2K5) » Return a default value on Select"
Difference: I actually need to return the most recent row (top 1 ordered desc) really fast, and I think this approach is would be faster.
I'm told that there will always be a match for @WhichCat... trying to be professional, I will provide a default result.
This is for a public website that gets a peak of about 28k hits per hour (7.6/second) so I want to execute as fast as I can.
The general algorithm is like this:
PROCEDURE [dbo].[uspRecentPost] @WhichCat varchar(99) AS
IF EXISTS (
SELECT * FROM {3 table join}
WHERE IsPublished = 1 AND IsNull(c.CategoryName,'') = @WhichCat
)
BEGIN
SELECT
TOP 1
{actual field list}
FROM {same as EXISTS statement}
WHERE {same as EXISTS statement}
ORDER BY DateCreatedSort DESC
END
ELSE
BEGIN
SELECT {hardcoded default values}
END
The db and data already exist - I can't change the structure (so category must remain a string, joins are required, etc.)
There are 16 categories and 500 posts growing about 1 post/day. (in case that helps)
My thought is that the "IF EXISTS" portion will determine if there is a value very quickly
if there is one, then the db can retrieve the records, sort them, and return the one that I need. (Or maybe I'm completely wrong?)
I'm hoping that someone can offer me advice along the lines of
'do not use SELECT * in an EXISTS statement'
or
'if you put the actual statement you want in the exists it won't have to redo the execution plan'
or
anything you can say that would make it possible to cache the execution plan or results (I can't see how this could happen with a string in the where clause)
I have stared at the estimated execution plan for a while; apparently I'm very out of practice because I can't read anything useful from it.
If you'd like to help me but won't/can't because I have posted pseudo code, feel free to use the PM button below to say, "Interesting question I'd like to play with it, but won't unless you post a working example."
Thanks for reading,
-Chris C.

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

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

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

I would be inclined to look at:

1. Just using union all for the default result set:

SELECT TOP (1)

{actual field list}

FROM {same as EXISTS statement}

WHERE {same as EXISTS statement}

UNION ALL

SELECT CAST('19000101' as datetime) AS DateCreatedSort

,{hardcoded default values}

ORDER BY DateCreatedSort DESC

2. Returning the results as OUTPUT parameters, not a result set.

3. Using ASP.Net caching.

JChrisCompton SSCommitted Points: 1609 More actions · Answer 2

Ken McKelvey (1/27/2011)
I would be inclined to look at:
1. Just using union all for the default result set...
2. Returning the results as OUTPUT parameters, not a result set.
3. Using ASP.Net caching.

:blush: I actually thought about UNION and was worried that the default might appear at the top instead... but I'm already sorting by date!

I need an emoticon that says, "Doh!" Maybe X-( where the "X" represents 'just smacked himself in the face with the palm of his hand'.

It's been a loooong week so far, demo Friday and release Monday [sigh]

I appreciate the .net caching idea too. I hope to do that in the next release... the one that actually has a CMS instead of html pages that make it look like it has a cms. :Whistling:

Thanks again,

-Chris C.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

Chris,

Since this is a procedure rather than a view or in-line query you could also do this:

SELECT TOP (1)

{actual field list}

INTO #T

FROM {same as EXISTS statement}

WHERE {same as EXISTS statement}

IF @@rowcount = 0

SELECT Default List

ELSE

SELECT * FROM #T

Todd Fifield

JChrisCompton SSCommitted Points: 1609 More actions · Answer 4

Yeah. So if I follow, that would create an in-memory temp table, but because of the very small footprint (Max 2 rows) I shouldn't have to worry much about memory.

(It was changed from "latest" to "last 2" late yesterday)

OR is the table #T is actually written to disk?

A glance at the 2005 docs seem to indicate that it could be written to disk, but it is unclear.

Thanks for the idea!

-Chris C.

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

chris.compton-977504 (1/29/2011)
OR is the table #T is actually written to disk?
A glance at the 2005 docs seem to indicate that it could be written to disk, but it is unclear.

It could be. I wouldn't worry too much about that, with 2 rows it's unlikely.

There's no such thing as a memory-only table.

That said, if the goal is 'as fast as possible' the overhead of a temp table may be more than you want. Test and see.

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