In addition to the T-SQL techniques discussed in Aaron Bertrands article, "Best approaches for grouped running totals", you might want to look at a SQLCLR
procedure (if you are running less than SQL Server 2012).
The main advantages are that a SQLCLR
procedure requires only a single scan of the source records, and benefits from the increased execution speed of compiled code. The sample below is taken from Aaron's post:
DDL
CREATE TABLE dbo.SpeedingTickets
(
IncidentID INT IDENTITY(1,1) PRIMARY KEY,
LicenseNumber INT NOT NULL,
IncidentDate DATE NOT NULL,
TicketAmount DECIMAL(7,2) NOT NULL
);
CREATE UNIQUE INDEX x
ON dbo.SpeedingTickets(LicenseNumber, IncidentDate)
INCLUDE(TicketAmount);
Sample data
;WITH TicketAmounts(ID,Value) AS
(
-- 10 arbitrary ticket amounts
SELECT i,p FROM
(
VALUES(1,32.75),(2,75), (3,109),(4,175),(5,295),
(6,68.50),(7,125),(8,145),(9,199),(10,250)
) AS v(i,p)
),
LicenseNumbers(LicenseNumber,[newid]) AS
(
-- 1000 random license numbers
SELECT TOP (1000) 7000000 + number, n = NEWID()
FROM [master].dbo.spt_values
WHERE number BETWEEN 1 AND 999999
ORDER BY n
),
JanuaryDates([day]) AS
(
-- every day in January 2014
SELECT TOP (31) DATEADD(DAY, number, '20140101')
FROM [master].dbo.spt_values
WHERE [type] = N'P'
ORDER BY number
),
Tickets(LicenseNumber,[day],s) AS
(
-- match *some* licenses to days they got tickets
SELECT DISTINCT l.LicenseNumber, d.[day], s = RTRIM(l.LicenseNumber)
FROM LicenseNumbers AS l CROSS JOIN JanuaryDates AS d
WHERE CHECKSUM(NEWID()) % 100 = l.LicenseNumber % 100
AND (RTRIM(l.LicenseNumber) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
OR (RTRIM(l.LicenseNumber+1) LIKE '%' + RIGHT(CONVERT(CHAR(8), d.[day], 112),1) + '%')
)
INSERT dbo.SpeedingTickets(LicenseNumber,IncidentDate,TicketAmount)
SELECT t.LicenseNumber, t.[day], ta.Value
FROM Tickets AS t
INNER JOIN TicketAmounts AS ta
ON ta.ID = CONVERT(INT,RIGHT(t.s,1))-CONVERT(INT,LEFT(RIGHT(t.s,2),1))
ORDER BY t.[day], t.LicenseNumber;
SQL CLR procedure DDL
CREATE ASSEMBLY [Demo] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EC0861530000000000000000E00002210B010B00000E00000006000000000000DE2C0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000882C00005300000000400000A802000000000000000000000000000000000000006000000C000000502B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E40C000000200000000E000000020000000000000000000000000000200000602E72737263000000A8020000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000C02C0000000000004800000002000500E02100007009000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30060046010000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A0772350000706F0900000A1A8D0B000001130A110A1672E40200701E730A00000AA2110A1772000300701F1F730A00000AA2110A18721A0300701B1D18730B00000AA2110A1972340300701B1F0918730B00000AA2110A0C16730C00000A130516730C00000A1306280D00000A130707176F0E00000A130808730F00000A1309110711096F1000000A11086F1100000A2C761108166F1200000A13041108166F1200000A0D1108186F1300000A13050911042E0B16730C00000A1306091304110916096F1400000A1109171108176F1500000A6F1600000A11091811056F1700000A11091911061105281800000A2513066F1700000A110711096F1900000A11086F1100000A2D9411076F1A00000ADE0A072C06076F1B00000ADCDE0A062C06066F1B00000ADC2A0000413400000200000017000000180100002F0100000A00000000000000020000000B000000300100003B0100000A000000000000001E02281C00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000000020000237E00006C020000D002000023537472696E6773000000003C05000050030000235553008C0800001000000023475549440000009C080000D400000023426C6F620000000000000002000001471402000900000000FA253300160000010000001500000002000000020000001C0000000400000001000000010000000200000000000A0001000000000006003900320006006D005A000B00810000000600B00090000600D00090000A001F0104010A004B0135010A006C0159010A007E0135010A00980159010A00B20104010A00BE01F8000600C80132000A00D00104010A00DB0104010A00EC0135010A00FA01F8000A00180204010A00370259010600660232000600BC02320000000000010000000000010001000100100018000000050001000100502000000000960040000A000100D82100000000861854000E000100110054001200210054001800290054000E00310054000E00390054002200410079010E00490054000E004900890127005100A2012200590054002D005900540034006900540018007100E3013D0049000A024200910054004900790026025000990044025600990049025A00990052025F0091005D02650099006F026B0091007B027100910087027800690092027F0079009E0250007900AD020E00A900C8020E00090054000E00200023001D002E000B00A1002E001300AA002E001B00B3008800048000000000000000000000000000000000EE00000002000000000000000000000001002900000000000200000000000000000000000100F8000000000000000000003C4D6F64756C653E004461746162617365312E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A656374005469636B657452756E6E696E67546F74616C73002E63746F720053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004461746162617365310053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64007365745F436F6E6E656374696F6E004462436F6D6D616E64007365745F436F6D6D616E64546578740053716C4D657461446174610053716C44625479706500446563696D616C0053716C436F6E746578740053716C50697065006765745F506970650053716C4461746152656164657200436F6D6D616E644265686176696F7200457865637574655265616465720053716C446174615265636F72640053656E64526573756C7473537461727400446244617461526561646572005265616400476574496E74333200476574446563696D616C00536574496E743332004461746554696D65004765744461746554696D65005365744461746554696D6500536574446563696D616C006F705F4164646974696F6E0053656E64526573756C7473526F770053656E64526573756C7473456E640049446973706F7361626C6500446973706F736500003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D00200074007200750065000082AD0D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000530045004C004500430054000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E004C006900630065006E00730065004E0075006D006200650072002C000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E0049006E0063006900640065006E00740044006100740065002C000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E005400690063006B006500740041006D006F0075006E0074000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000460052004F004D002000640062006F002E005300700065006500640069006E0067005400690063006B006500740073002000410053002000530054000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020004F0052004400450052002000420059000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E004C006900630065006E00730065004E0075006D006200650072002C000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530054002E0049006E0063006900640065006E00740044006100740065000D000A0020002000200020002000200020002000200020002000200020002000200020002000200020002000001B4C006900630065006E00730065004E0075006D00620065007200001949006E0063006900640065006E007400440061007400650000195400690063006B006500740041006D006F0075006E0074000019520075006E006E0069006E00670054006F00740061006C000000008361CA6040029B4991925394772E0D1F0008B77A5C561934E089030000010320000105200101110D04200101080401000000042001010E05200101121D062002010E1131082004010E11310505040000123D06200112411145062001011D122D052001011249032000020420010808052001113508052002010808052001115108062002010811510620020108113508000211351135113518070B121D12251D122D080811351135123D124112491D122D0801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000EC08615300000000020000001C0100006C2B00006C0D000052534453A775B484FE7A394C846393C91D55D95C01000000633A5C55736572735C5061756C2057686974655C536B7944726976655C446F63756D656E74735C56697375616C2053747564696F20323031325C50726F6A656374735C4461746162617365315C4461746162617365315C6F626A5C52656C656173655C4461746162617365312E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000B02C00000000000000000000CE2C0000002000000000000000000000000000000000000000000000C02C000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D00650000004400610074006100620061007300650031002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006100620061007300650031002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E03C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE [dbo].[TicketRunningTotals]
AS EXTERNAL NAME [Demo].[StoredProcedures].[TicketRunningTotals];
Usage
EXECUTE dbo.TicketRunningTotals;
Output
Execution plan
SQL CLR source code
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void TicketRunningTotals()
{
using (var conn = new SqlConnection("context connection = true"))
{
conn.Open();
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText =
@"
SELECT
ST.LicenseNumber,
ST.IncidentDate,
ST.TicketAmount
FROM dbo.SpeedingTickets AS ST
ORDER BY
ST.LicenseNumber,
ST.IncidentDate
";
var columns = new SqlMetaData[4]
{
new SqlMetaData("LicenseNumber", SqlDbType.Int),
new SqlMetaData("IncidentDate", SqlDbType.Date),
new SqlMetaData("TicketAmount", SqlDbType.Decimal, 7, 2),
new SqlMetaData("RunningTotal", SqlDbType.Decimal, 9, 2)
};
int licenseNumber, lastLicenseNumber;
var ticketAmount = 0m;
var runningTotal = 0m;
var pipe = SqlContext.Pipe;
var reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
var record = new SqlDataRecord(columns);
pipe.SendResultsStart(record);
if (reader.Read())
{
lastLicenseNumber = reader.GetInt32(0);
do
{
licenseNumber = reader.GetInt32(0);
ticketAmount = reader.GetDecimal(2);
if (licenseNumber != lastLicenseNumber)
{
runningTotal = 0m;
lastLicenseNumber = licenseNumber;
}
record.SetInt32(0, licenseNumber);
record.SetDateTime(1, reader.GetDateTime(1));
record.SetDecimal(2, ticketAmount);
record.SetDecimal(3, runningTotal += ticketAmount);
pipe.SendResultsRow(record);
}
while (reader.Read());
}
pipe.SendResultsEnd();
}
}
}
}
This is your query:
Select vname.Value, vgender.value, vindustry.value
from ABC [vname] with (nolock) join
ABC [vgender] with (nolock)
on vname.id = vgender.id join
ABC [vindustry] with (nolock)
on vgender.id = vindustry.id
where vname.field = 'Name' and
vgender.field = 'Gender' and
vindustry.field = 'Industry';
You can try indexes on this query. I would try ABC(id, field, value)
and ABC(field, id, value)
.
However, you might also try the following:
select max(case when field = 'Name' then value end) as Name,
max(case when field = 'Gender' then value end) as Gender,
max(case when field = 'Industry' then value end) as Industry
where field in ('Name', 'Gender', 'Industry')
from ABC
group by id;
Best Answer
The first thing I would do is avoid using table variables in heavy SQL operations. You should use a temp table instead. Either initially or select your table variable @table1 into a temp table then use that temp table in your update statement.
Table variables are historically known to be bottlenecks because statistics aren't maintained on them, and even with Microsoft's improvements to them in SQL 2014 they can still perform poorly. Here's a good Brent Ozar article about them: https://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/
You might also benefit from using the force seek hint on "anotherbg table". But start with the above change first.
For reference: http://nisalbi.blogspot.com/2015/11/what-is-forceseek-and-forcescan-table.html?m=1
And MS Docs on Table Hints: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
Typically query hints should only be used in very situational cases by people with experience, but I have found that with larger tables, sometimes the SQL Engine attempts to do an index scan when an index seek truly is a better choice. (Updating the table statistics on "anotherbg table" might more naturally fix your issue too.)
So it's situational and you'll need to test but it's possible a force seek index hint will help.
This is what your update query would look like with the index hint: