Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Keeping track of the number of times a record appears in query results Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 5:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 9:58 PM
Points: 92, Visits: 196
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!
Post #1385406
Posted Thursday, November 15, 2012 8:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
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!
Post #1385439
Posted Thursday, November 15, 2012 9:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 9:58 PM
Points: 92, Visits: 196
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
Post #1385442
Posted Thursday, November 15, 2012 9:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
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!
Post #1385445
Posted Thursday, November 15, 2012 9:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
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!
Post #1385448
Posted Thursday, November 15, 2012 9:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 9:58 PM
Points: 92, Visits: 196
Thank you very much for your help Dwain. Yes, I do need stuff out of those other JOINS. I'll have to consider things..
Post #1385458
Posted Thursday, November 15, 2012 10:14 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
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!
Post #1385468
Posted Friday, November 16, 2012 2:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
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!
Post #1385566
Posted Monday, November 19, 2012 1:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 9:58 PM
Points: 92, Visits: 196
Thank you very much Dwain! You are certainly the MAN with respect to SQL. I will consider all your helpful advice.
Matt
Post #1386573
Posted Monday, November 19, 2012 3:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 2,095, Visits: 3,146
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 + '%')
)
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1386596
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse