November 15, 2012 at 5:54 pm
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!
November 15, 2012 at 8:50 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 15, 2012 at 9:13 pm
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
November 15, 2012 at 9:23 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 15, 2012 at 9:31 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 15, 2012 at 9:56 pm
Thank you very much for your help Dwain. Yes, I do need stuff out of those other JOINS. I'll have to consider things..
November 15, 2012 at 10:14 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 16, 2012 at 2:55 am
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 19, 2012 at 1:59 pm
Thank you very much Dwain! You are certainly the MAN with respect to SQL. I will consider all your helpful advice.
Matt
November 19, 2012 at 3:06 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
November 25, 2012 at 5:30 pm
Thank you very much Scott! - Matt
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply