Fastest way to update a viewcount from results in SQL query-CodePudding

I've got a problem and a working solution to update viewvounts on some posts on a website I'm building but I'm looking for the fastest possible way and it feels very long way around

I want to be able to get posts based on lots of different criteria, such as who posted it, last 20 posts, top 20 containing some search text etc but with 2 lots of extra information INNER JOINED which is all easy enough but as I have no interaction other than selecting by some criteria I cant see a way to update the viewcounts for each.

Hopefully if the site goes well this will be heavily used so I am looking to do it as quickly as possible.

The best working solution I have so far is to;

Open the connection
Get the posts I want
Process them onto the web page making a string of all the ID numbers
run another stored procedure to update the viewcounts of those numbers
Close the connection

Am I missing something stupidly simple here?

the [Posts] table is simple;

[PostID] BIGINT
[PostUserID] INT
[PostGroupID] INT
[PostType] INT
[PostText] NVARCHAR(500)
[PostLinkText] NVARCHAR(250)
[Vewcount] INT
[Likes] INT
[comments] INT

The code I'm using to update the views is like this:

DECLARE @ViewedPosts NVARCHAR(MAX) = '1000|22|13|1000|400101011|22|13|1000|22|13|400101011' 

SET NOCOUNT ON
DECLARE @Temp TABLE
(
[PostID] BIGINT
)
INSERT INTO @Temp
SELECT [Value] AS [PostID] FROM dbo.SPLIT(@ViewedPosts, '|')

DECLARE @PID BIGINT
WHILE EXISTS(SELECT * FROM @Temp)
BEGIN
    SELECT TOP 1 @PID = [PostID] FROM @Temp
    -- UPDATE [Posts] SET [Viewcount]=[Viewcount] 1 WHERE [PostID]=@PID --
    SELECT [PostID] FROM @Temp WHERE [PostID]=@PID
    DELETE @Temp WHERE [PostID]= @PID
END
SET NOCOUNT OFF

the dbo.SPLIT just makes a single column table of all the postID's from the viewed posts string.

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos 1, CHARINDEX(@Delimiter,@String,endpos 1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'value' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String) 1)-stpos)
    FROM Split
)
GO

I've done the usual scouring google and stackoverflow but all I am finding is different solutions that work out more or less what I've already got.

UPDATE: Working solution:

DECLARE @Temp TABLE ( [PostIDt] BIGINT )
INSERT INTO @Temp SELECT [PostID] AS [PostIDt] FROM [Posts] WITH(NOLOCK) ORDER BY [POSTID] DESC;
UPDATE [Posts] SET [ViewCount]=[ViewCount] 1 WHERE [PostId] IN (SELECT [PostIDt] AS [PostId] FROM @Temp);
SELECT * FROM @Temp INNER JOIN [Posts] ON [PostIDt] = [Posts].[PostID];

CodePudding user response：

And now to updating part, the split string contains the same Id multiple times, thus I assume you want to increase by how many times an Id occurs there, not just 1:

DECLARE @ViewedPosts NVARCHAR(MAX) =N'1000|22|13|1000|400101011|22|13|1000|22|13|400101011';

SET NOCOUNT ON;
UPDATE [Posts]
SET    [ViewCount]=[ViewCount] tmp.Views
FROM
       (
         SELECT
                  s, COUNT(*) AS Views
         FROM     dbo.ufn_SplitString(@ViewedPosts, '|')
         GROUP BY s
       ) tmp
WHERE  [postId]=tmp.s;
SET NOCOUNT OFF;

EDIT: Getting the posts from SQL server and update all in one call:

UPDATE [Posts]
    SET    [ViewCount]=[ViewCount] 1
    WHERE  [postId] in (SELECT TOP(20) PostId 
                        FROM [Posts] 
                        ORDER BY [POSTID] DESC);

CodePudding user response：

OK you didn't tag the database but from the code it is SQL server (not sure of the version, I would assume not the latest and even may be an older version). Let's start with splitting the string. It is still not clear to me how you are generating that string. You're saying you construct it using reader("PostId") in webpage. That reader might be the result of an ExecuteReader, meaning you are getting it from the database? If so, getting PostIds from database and then sending back might be unnecessary trip. For the moment, the source of it is not clear, so I would assume it is needed to be constructed as a string as you did and then sent to database to be split. Splitting the string is the first performance point. For small strings as in your example, it may not be important taking a fraction of time with the Split() function you have. However, for large strings, that code is slow and may even time out and fail if the string is large enough. I would suggest changing that to a CLR function.

You can use this code to do that (you may try this on a new test database if you will before applying to yours):

First if CLR is not enabled, it needs to be enabled:

EXEC sp_configure 'clr enabled', '1';
RECONFIGURE;

And execute this code to add the function (SQL 2017 and later, for older versions, dbo.sp_add_trusted_assembly doesn't exists, instead SET TRUSTWORTHY ON is a way to do it - not sure if that would be a recommended thing to do):

IF EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[ufn_SplitString]')
          AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )
)
    DROP FUNCTION [dbo].[ufn_SplitString];
GO
IF EXISTS
(
    SELECT *
    FROM sys.[assemblies] AS [a]
    WHERE [a].[name] = 'SQLUtils'
          AND [a].[is_user_defined] = 1
)
    DROP ASSEMBLY SqlUtils;
GO
DECLARE @ta VARBINARY(64);
SELECT @ta = hash
FROM sys.trusted_assemblies AS ta
WHERE description = N'SQL CLR Utils';
IF @ta IS NOT NULL
BEGIN
    EXEC dbo.sp_drop_trusted_assembly @hash = @ta;
END;
GO

EXEC dbo.sp_add_trusted_assembly @hash = 0xCEB076E6BB8C51E08743269F6BA1AC0BB3C6E2E25B78085918DBAEE60BFC88ED45EEE861CC571DBDEDF0AD7252AEF9D1DD51DB94E6E46C69F24D0C518A8E2D98,
                                 @description = N'SQL CLR Utils';

CREATE ASSEMBLY SQLUtils AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000\
800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A\
2400000000000000504500004C01030061F3D4630000000000000000E00022200B013000001600000006000000000000BA3400000020000000400000\
0000001000200000000200000400000000000000060000000000000000800000000200003AD200000300608500001000001000000000100000100000\
00000000100000000000000000000000683400004F000000004000007803000000000000000000000000000000000000006000000C00000030330000\
1C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000\
082000004800000000000000000000002E74657874000000C0140000002000000016000000020000000000000000000000000000200000602E727372\
6300000078030000004000000004000000180000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001C0000\
00000000000000000000000040000042000000000000000000000000000000009C340000000000004800000002000500982200001810000009000000\
000000000000000000000000B03200008000000000000000000000000000000000000000000000000000000000000000000000003602036F1B00000A\
28020000062A5A1FFE730800000625027D0700000425037D090000042A4E0302741F000001281C00000A731D00000A512A1E02281E00000A2A2E7306\
00000680010000042A8203281F00000A2D16036F2000000A16310D03166F2100000A282200000A2A162A6602281E00000A02037D0300000402282300\
000A7D050000042A1B3002001B00000001000011027B030000040A061FFD2E040617330A00DE0702280B000006DC2A00011000000200110002130007\
000000001B300300E800000002000011027B030000040B072C0E07173BA8000000160ADDCE00000002157D03000004027B06000004282400000A3AAC\
000000027B080000048E2D36027B060000047201000070282500000A7E02000004252D17267E01000004FE0607000006732600000A25800200000428\
0100002B2B14027B06000004027B08000004176F2800000A0D090C02086F1800000A7D0A000004021FFD7D030000042B28027B0A0000046F1500000A\
13040211047D0400000402177D03000004170ADE2D021FFD7D03000004027B0A0000046F1400000A2DCB02280B00000602147D0A000004160ADE0702\
2809000006DC062A0110000004000000DFDF0007000000006E02157D03000004027B0A0000042C0B027B0A0000046F1300000A2A1E027B040000042A\
1A732900000A7A00133002004300000003000011027B030000041FFE3318027B05000004282300000A330B02167D03000004020A2B07167308000006\
0A06027B070000047D0600000406027B090000047D08000004062A1E02280F0000062A0042534A4201000100000000000C00000076342E302E333033\
31390000000005006C00000044050000237E0000B0050000D006000023537472696E677300000000800C00000800000023555300880C000010000000\
2347554944000000980C00008003000023426C6F6200000000000000020000015717A20B090A000000FA013300160000010000002600000004000000\
0A000000100000000800000005000000290000001A000000030000000100000002000000020000000700000003000000010000000400000002000000\
010000000000800301000000000006009E02B60406001F03B6040600870184040F00D60400000600AF01940306008102940306004D02940306000603\
94030600BE0294030600D70294030600E401940306009B0197040600790197040600300294030600FF013D0306006B058D030A006C02D8030A00D700\
D803060009012205060021018D030600C601B60406002A008C000600F2028D030A005205E50406005E01B60406005F008D03060015018D030600F303\
2205060038008C00060018028404060066038D030A005703E5040600CA038D030600B9058D030E00AD06030512000A01BE03060035058D030600A603\
8D03000000006A000000000001000100010010007803FA044100010001000321100088000000410001000500030110001B0000004100030008003600\
66006E01160001007201010053017A01010097067D010100C2007A01010069057D0106007D047D0101006005800106005B0580010100460084015020\
00000000960061038B0101005E20000000009100CF03920103007520000000009600F9009D0105008920000000008618700406000700912000000000\
91187604A501070089200000000086187004060007009D200000000083000A00A9010700BE20000000008618700401000800D82000000000E1013801\
06000900102100000000E101A406200009001422000000008100510006000900302200000000E1092A06AE010900382200000000E101720506000900\
302200000000E1096C062F000900402200000000E101FF03B20109008F2200000000E101430442000900000001006905000002006005000001006905\
00000200600500000100BC0302000200A500000001007B0400000100530104000A0004004D000400060004006D000400710009007004010011007004\
0600190070040A0029007004100031007004100039007004100041007004100049007004100051007004100059007004100061007004150069007004\
1000710070041000790070041000890070040600A90070041A00B90070040600C90070040600D9004B010600E100A40620000C008B062A00E1009105\
0600E1008B062F00140062043900990062044200F10070040600F900B3064700010197054C00C10070045300810070040600F900E6005A00F9006D03\
5F00F900480563000901A30568001101A7006D00F900BF065A001901B30585001C0070049300210126019900F900B305B40031017004060020007B00\
A0022E000B00C2012E001300CB012E001B00EA012E002300F3012E002B0001022E00330001022E003B0001022E004300F3012E004B0007022E005300\
01022E005B0001022E0063001F022E006B0049022E0073005602400083005503630093007A03830093007A0384008B007A030001D3007A032001D300\
7A038001D3007A03A001D3007A03C001D3007A03E001D3007A030002D3007A0371007500BE00040001000000C505BA0100000306BE0102000C000300\
02000E000500040012002700040014002900040018002B0004001A002D0004001C002F0004001E003100040020003300240033008C00048000000100\
00000000000001000000CC00FA040000040000000000000000000000C3007F0000000000040000000000000000000000C30073000000000004000000\
0000000000000000C3008D0300000000040000000000000000000000C3002C010000000003000200040002004F00B00000000000003C3E395F5F315F\
30003C53706C69747465723E625F5F315F30003C53706C69747465723E645F5F310049456E756D657261626C6560310049456E756D657261746F7260\
31003C3E375F5F7772617031003C3E6D5F5F46696E616C6C79310046756E636032003C3E39003C4D6F64756C653E0053797374656D2E44617461006D\
73636F726C6962003C3E630053797374656D2E436F6C6C656374696F6E732E47656E65726963006765745F43757272656E744D616E61676564546872\
6561644964003C3E6C5F5F696E697469616C546872656164496400446174614163636573734B696E640049734E756C6C4F7257686974655370616365\
0046696C6C537472696E675461626C650049456E756D657261626C650049446973706F7361626C6500547970650057686572650053797374656D2E43\
6F72650053797374656D2E49446973706F7361626C652E446973706F7365003C3E315F5F737461746500436F6D70696C657247656E65726174656441\
747472696275746500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275\
746500417373656D626C795469746C65417474726962757465004974657261746F7253746174654D616368696E654174747269627574650041737365\
6D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500446562756767657248696464\
656E41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E6669677572617469\
6F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E4174747269627574\
6500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F647563744174747269627574650041\
7373656D626C79436F7079726967687441747472696275746500506172616D417272617941747472696275746500417373656D626C79436F6D70616E\
794174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E56657273\
696F6E696E670053716C537472696E670053706C6974537472696E67006765745F4C656E6774680053716C5574696C0053514C5574696C732E646C6C\
0053797374656D0053797374656D2E5265666C656374696F6E004E6F74537570706F72746564457863657074696F6E006F0053797374656D2E4C696E\
7100436861720053706C6974746572004D6963726F736F66742E53716C5365727665722E5365727665720049456E756D657261746F72005379737465\
6D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261626C653C53797374656D2E537472696E673E2E476574456E756D65726174\
6F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261626C652E476574456E756D657261746F72002E63746F72002E6363746F72\
003C3E335F5F730053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F705365727669636573005379\
7374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C\
54797065730053514C5574696C730053797374656D2E546578742E526567756C617245787072657373696F6E730053797374656D2E436F6C6C656374\
696F6E7300537472696E6753706C69744F7074696F6E73006765745F43686172730053716C4368617273003C3E335F5F736570617261746F7273004F\
626A6563740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E5265736574006F705F496D706C696369740049734C6574\
7465724F7244696769740053706C697400456E7669726F6E6D656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E75\
6D657261746F723C53797374656D2E537472696E673E2E43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F\
722E43757272656E740053797374656D2E436F6C6C656374696F6E732E47656E657269632E49456E756D657261746F723C53797374656D2E53747269\
6E673E2E6765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74003C\
3E325F5F63757272656E74004D6F76654E65787400526567657800546F4368617241727261790049734E756C6C4F72456D7074790000000000055C00\
62000000233D59D645D99849B7DE049542552E5000042001010803200001052001011111042001010E04200101020520010112510320000205151275\
010E04200013000320001C05151259010E08200015127501130004200012710420001D030600011180810E06200101118081040001020E0320000804\
20010308040001020303000008030701080F07050208151259010E151259010E0E0600021D0E0E0E06151269020E02052002011C1816100102151259\
011E00151259011E00151269021E0002030A010E0920021D0E1D03118095040701121008B77A5C561934E08980A00024000004800000940000000602\
000000240000525341310004000001000100858173EEBF9F0EA6C8BFB7F1F3E5D6238024C53672D0B039376C1BB97CED6DE9A5691A1E0A66156709DC\
7DBDB2B05D5100920CA9760A6C2564ED6B0FD3902607D1D5D77423550EAE3E32F8B33F6DCDB48237AB6A03168459A775F7BFEDD50E8FF32E97B29C47\
AB96874597204608BA5C13F538F0900EAD2412D1CE105904CDB50306120C0706151269020E0202060802060E03061D030606151275010E060002124D\
0E0E0A0002151259010E0E1D03070002011C10126103000001042001020E0320000E072000151275010E0328000E0328001C0801000800000000001E\
01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000000D01000853514C5574696C73000005010000000017\
010012436F7079726967687420C2A920203230323200002901002436643732343736392D346666632D343966652D386561612D666136313330386138\
62313800000C010007312E302E302E3000004901001A2E4E45544672616D65776F726B2C56657273696F6E3D76342E380100540E144672616D65776F\
726B446973706C61794E616D65122E4E4554204672616D65776F726B20342E3880B3010002005455794D6963726F736F66742E53716C536572766572\
2E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572\
653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737300000000540E11\
46696C6C526F774D6574686F644E616D650F46696C6C537472696E675461626C652401001F53514C5574696C732E53716C5574696C2B3C53706C6974\
7465723E645F5F310000040100000000B6ECAB35C4CC0AC896D3D3404EB66253CEF0E979635EF6ABBD1F00C96573B53D179A64C90C920112116D5900\
6D514E9A2B8F93C3866239B484803DC130276DAED8404492A11ACEB072A4DDF8C297A913673ED2417AE3FECFBFB5B2A7D0343A05EF2D3DEDFEC82553\
9BBA85A0C2AD2956F73B8DE88E4F5C558BB7065C9BCA81A50000000061F3D46300000000020000001C0100004C3300004C15000052534453849790AE\
7336D34085CC0E2F186C71EC01000000443A5C53514C53706C6974434C525C53514C5574696C735C6F626A5C52656C656173655C53514C5574696C73\
2E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000903400000000000000000000AA3400000020000000000000\
000000000000000000000000000000009C340000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25\
002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000\
000001000100000030000080000000000000000000000000000001000000000048000000584000001C03000000000000000000001C03340000005600\
53005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000\
000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F000000000024000400\
00005400720061006E0073006C006100740069006F006E00000000000000B0047C020000010053007400720069006E006700460069006C0065004900\
6E0066006F0000005802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E0074007300000000000000\
22000100010043006F006D00700061006E0079004E0061006D00650000000000000000003A0009000100460069006C00650044006500730063007200\
69007000740069006F006E0000000000530051004C005500740069006C00730000000000300008000100460069006C00650056006500720073006900\
6F006E000000000031002E0030002E0030002E00300000003A000D00010049006E007400650072006E0061006C004E0061006D006500000053005100\
4C005500740069006C0073002E0064006C006C00000000004800120001004C006500670061006C0043006F0070007900720069006700680074000000\
43006F0070007900720069006700680074002000A90020002000320030003200320000002A00010001004C006500670061006C005400720061006400\
65006D00610072006B007300000000000000000042000D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000\
530051004C005500740069006C0073002E0064006C006C0000000000320009000100500072006F0064007500630074004E0061006D00650000000000\
530051004C005500740069006C00730000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E00\
30002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E00\
30002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000003000000C000000BC3400000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION ufn_SplitString
(
    @s NVARCHAR(MAX),
    @separators NVARCHAR(1000) = ''
)
RETURNS TABLE
(
    s NVARCHAR(1000)
)
AS
EXTERNAL NAME SQLUtils.[SQLUtils.SqlUtil].SplitString;
GO

Finally you can test the added function:

SELECT s FROM dbo.ufn_SplitString('a|b|x','|');
SELECT s FROM dbo.ufn_SplitString('a,b,x',',');
SELECT s FROM dbo.ufn_SplitString('a,b|x','|,');

(I will continue on a new reply just to keep this thing not to long splitting is itself a different part of the question)