Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Keeping track of the number of times a record appears in query results


Keeping track of the number of times a record appears in query results

Author
Message
matt6749
matt6749
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 216
Goal: I have a web site for people to search for doctors. For marketing purposes for doctors, in my stored procedure I want to track the number of times a doctor's record appears in query results. Doctors table has 5,000 records.

This is the best way I've come up with, but might be cludgy:

SELECT DoctorID, LastName, FirstName
FROM Doctors P
INTO #Temp
WHERE [some criteria]

UPDATE Doctors
SET NumImpressionsInSearches = NumImpressionsInSearches + 1
FROM Doctors
INNER JOIN #Temp
ON Doctors.DoctorID = #Temp.DoctorID

SELECT * FROM #Temp

DROP TABLE #Temp

This works, but may be too much overhead? I find that Google and Bing crawlers are hitting this query a lot, and have started receving "Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim". I am on a shared SQL host (NewTek/CrystalTech)

Any thoughts? Thank you!
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4241 Visits: 6431
You can try something like this:


SELECT DoctorID, LastName, FirstName
FROM (
UPDATE Doctors
SET NumImpressionsInSearches = NumImpressionsInSearches + 1
OUTPUT INSERTED.*
FROM Doctors
WHERE [some criteria]
) a




Google: "composable dml"


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
matt6749
matt6749
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 216
Wow, that OUTPUT keyword is new to me. Thanks.
Followup question though, if you don't mind. My SELECT statement is pretty complex (used for paging through results in web page). Not sure I can use an UPDATE instead:

SELECT *
FROM
(
SELECT
TotalRows = COUNT(*) OVER(),
RowNum =
CASE
   WHEN @sortColumn = 'OccupationName_ASC' THEN ROW_NUMBER()OVER (ORDER BY O.OccupationName)
   WHEN @sortColumn = 'City_ASC' THEN ROW_NUMBER()OVER (ORDER BY D.City)
   WHEN @sortColumn = 'OccupationName_DESC' THEN ROW_NUMBER()OVER (ORDER BY O.OccupationName DESC)
   WHEN @sortColumn = 'City_DESC' THEN ROW_NUMBER()OVER (ORDER BY D.City DESC)
   ELSE ROW_NUMBER()OVER (ORDER BY NewID()) -- note this returns random order, and each page's results may not be unique.
END,
D.DoctorID, D.OccupationID, D.LastName, D.FirstName, D.Designations, D.NumRecommendations, D.City, D.State, D.Zip, D.Phone, D.NumProfileViews, D.ApprovedStatus, O.OccupationName, D.MembershipStatusID
FROM dbo.Doctors P WITH (NOLOCK)
INNER JOIN dbo.Occupations O ON O.OccupationID = D.OccupationID
INNER JOIN dbo.DoctorsPracticeAreas DPA ON DPA.DoctorID = D.DoctorID
WHERE DPA.PracticeAreaID = @practiceAreaID
AND (@occupationID IS NULL OR D.OccupationID = @occupationID)
AND (@name IS NULL OR D.LastName LIKE @name + '%' OR D.PracticeName LIKE '%' + @name + '%')
)
AS XYZ -- you need this AS XYZ
WHERE RowNum BETWEEN @startRow AND (@startRow + @rowsPerPage) - 1
ORDER BY RowNum ASC


If I use the OUTPUT keyword as you suggest, can I put all this in the WHERE clause of an UPDATE statement? Yikes.

Thanks,
Matt
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4241 Visits: 6431
This is just a guess mind you but perhaps something like this would work:


SELECT DoctorID, LastName, FirstName
FROM (
UPDATE Doctors
SET NumImpressionsInSearches = NumImpressionsInSearches + 1
OUTPUT INSERTED.*
FROM Doctors
WHERE DoctorID IN (
SELECT DoctorID
FROM
(
SELECT
TotalRows = COUNT(*) OVER(),
RowNum =
CASE
WHEN @sortColumn = 'OccupationName_ASC' THEN ROW_NUMBER()OVER (ORDER BY O.OccupationName)
WHEN @sortColumn = 'City_ASC' THEN ROW_NUMBER()OVER (ORDER BY D.City)
WHEN @sortColumn = 'OccupationName_DESC' THEN ROW_NUMBER()OVER (ORDER BY O.OccupationName DESC)
WHEN @sortColumn = 'City_DESC' THEN ROW_NUMBER()OVER (ORDER BY D.City DESC)
ELSE ROW_NUMBER()OVER (ORDER BY NewID()) -- note this returns random order, and each page's results may not be unique.
END,
D.DoctorID, D.OccupationID, D.LastName, D.FirstName, D.Designations, D.NumRecommendations, D.City, D.State, D.Zip, D.Phone, D.NumProfileViews, D.ApprovedStatus, O.OccupationName, D.MembershipStatusID
FROM dbo.Doctors P WITH (NOLOCK)
INNER JOIN dbo.Occupations O ON O.OccupationID = D.OccupationID
INNER JOIN dbo.DoctorsPracticeAreas DPA ON DPA.DoctorID = D.DoctorID
WHERE DPA.PracticeAreaID = @practiceAreaID
AND (@occupationID IS NULL OR D.OccupationID = @occupationID)
AND (@name IS NULL OR D.LastName LIKE @name + '%' OR D.PracticeName LIKE '%' + @name + '%')
)
AS XYZ -- you need this AS XYZ
WHERE RowNum BETWEEN @startRow AND (@startRow + @rowsPerPage) - 1

)
) a






My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4241 Visits: 6431
Note that with the suggested approach, you'll be limited to returning columns that exist in the Doctors table. If you need stuff out of those other JOINs you'll be out of luck.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
matt6749
matt6749
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 216
Thank you very much for your help Dwain. Yes, I do need stuff out of those other JOINS. I'll have to consider things..
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4241 Visits: 6431
You might still be able to do it by JOINing the derived table a to whatever it is you need to get. It might not be as complex as the first one (where you're limiting the range of the retrieved doctors).

I'm not sure it will work. You'd need to play with it some. But I think it will.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4241 Visits: 6431
Here's an interesting bit of code you can try:


CREATE TABLE #Doctors
(ID INT IDENTITY, Name VARCHAR(100), Practice VARCHAR(100), [Views] INT)

CREATE TABLE #DoctorInfo
(Name VARCHAR(100), OfficeDays VARCHAR(10))

INSERT INTO #Doctors
SELECT 'Dr. Dwain', 'Gynecology', 0 UNION ALL SELECT 'Dr. Jeff', 'General Practitioner', 0

INSERT INTO #DoctorInfo
SELECT 'Dr. Dwain', 'Monday'
UNION ALL SELECT 'Dr. Jeff', 'Tuesday'
UNION ALL SELECT 'Dr. Dwain', 'Tuesday'
UNION ALL SELECT 'Dr. Jeff', 'Wednesday'
GO

UPDATE d
SET [Views] = [Views] + 1
OUTPUT INSERTED.*, a.OfficeDays
FROM #Doctors d
INNER JOIN #DoctorInfo a ON a.Name = d.Name
WHERE ID = 1+ ABS(CHECKSUM(NEWID())) % 2
GO 5

SELECT * FROM #Doctors

DROP TABLE #Doctors, #DoctorInfo




If you can't figure out what it is doing let me know and I'll explain. But strangely you can dump the information from JOINed tables in the OUTPUT clause.

You cannot however, add any JOINs after the composable DML solution I suggested.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
matt6749
matt6749
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 216
Thank you very much Dwain! You are certainly the MAN with respect to SQL. I will consider all your helpful advice.
Matt
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6663
Other than the final ORDER BY, I think the query below will do it.

Personally, I wouldn't rewrite your existing query any more than you have to to make it do what you need:



UPDATE doctors_update
SET NumImpressionsInSearches = NumImpressionsInSearches + 1
OUTPUT
derived.*
FROM dbo.Doctors doctors_update
INNER JOIN (
   SELECT TOP 100 PERCENT *
   FROM
   (
   SELECT
   TotalRows = COUNT(*) OVER(),
   RowNum =
   CASE
   WHEN @sortColumn = 'OccupationName_ASC' THEN ROW_NUMBER()OVER (ORDER BY O.OccupationName)
   WHEN @sortColumn = 'City_ASC' THEN ROW_NUMBER()OVER (ORDER BY D.City)
   WHEN @sortColumn = 'OccupationName_DESC' THEN ROW_NUMBER()OVER (ORDER BY O.OccupationName DESC)
   WHEN @sortColumn = 'City_DESC' THEN ROW_NUMBER()OVER (ORDER BY D.City DESC)
   ELSE ROW_NUMBER()OVER (ORDER BY NewID()) -- note this returns random order, and each page's results may not be unique.
   END,
   D.DoctorID, D.OccupationID, D.LastName, D.FirstName, D.Designations, D.NumRecommendations, D.City, D.State, D.Zip, D.Phone, D.NumProfileViews, D.ApprovedStatus, O.OccupationName, D.MembershipStatusID
   FROM dbo.Doctors P WITH (NOLOCK)
   INNER JOIN dbo.Occupations O ON O.OccupationID = D.OccupationID
   INNER JOIN dbo.DoctorsPracticeAreas DPA ON DPA.DoctorID = D.DoctorID
   WHERE DPA.PracticeAreaID = @practiceAreaID
   AND (@occupationID IS NULL OR D.OccupationID = @occupationID)
   AND (@name IS NULL OR D.LastName LIKE @name + '%' OR D.PracticeName LIKE '%' + @name + '%')
   Wink
   AS XYZ -- you need this AS XYZ
   WHERE RowNum BETWEEN @startRow AND (@startRow + @rowsPerPage) - 1
   ORDER BY RowNum
) AS derived ON
derived.DoctorID = doctors_update.DoctorID




Of course the "ORDER BY RowNum" is NOT guaranteed for the final result, so you can drop it if you prefer.

You could OUTPUT into a temp table and then SELECT from the temp table with ORDER BY ... but of course you'd have to create the temp table first, one way or another.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search