Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35

    Unanswered: AFTER INSERT trigger takes forever on a large table (20 million rows)

    I have a row that is being used log track plays on our website.

    Here's the table:

    Code:
    CREATE TABLE [dbo].[Music_BandTrackPlays](
    	[ListenDate] [datetime] NOT NULL DEFAULT (getdate()),
    	[TrackId] [int] NOT NULL,
    	[IPAddress] [varchar](20)
    ) ON [PRIMARY]
    There's a CLUSTERED INDEX on ListenDate ASC and a NON CLUSTERED INDEX on the TrackId.

    I have a TRIGGER on the Music_BandTrackPlays table that looks like the following:

    Code:
    CREATE TRIGGER [trig_Increment_Music_BandTrackPlays_PlayCount]
    ON [dbo].[Music_BandTrackPlays] AFTER INSERT
    AS
    UPDATE 
    	Music_BandTracks
    SET 
    	Music_BandTracks.PlayCount = Music_BandTracks.PlayCount + TP.PlayCount
    FROM
    	(SELECT TrackId, COUNT(*) AS PlayCount
    	FROM inserted
    	GROUP BY TrackId) AS TP
    WHERE 
    	Music_BandTracks.TrackId = TP.TrackId
    When a simple INSERT statement is done on the Music_BandTrackPlays table, it can take quite a long time. When I remove the TRIGGER the INSERTs are immediate. The Execution plan for the TRIGGER shows that a 'Inserted Scan' is taking up most of the resources.

    How exactly is the pseudo 'inserted' table formed?

    For now, I think the easiest thing to do is update my logging page so it performs 2 queries. One to UPDATE the Music_BandTracks table and increment the counter, and perform the INSERT into the Music_BandTrackPlays table seperately.

    I'm ok with that solution but I would really like to understand why the TRIGGER is taking so long. The 'inserted' pseudo table will be 1 row 99% of the time. Does SQL Server perform a table scan on all 20 million rows in order to determine what's new and put it in the inserted pseudo table?

    Thanks!
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, lets start with the fact that your logic appears faulty.

    You are updating Music_BandTracks with the count of TrackIDs INSERTED into Music_BandTrackPlays, but you are ignoring TrackID records that may have already existed in the table.
    For instance, you have a MusicTrack with 10 plays in your TrackPlays table. You add one more play, and this trigger updates BandTracks to show a count of 1 (inserted record), even though there are now 11 instances of that TrackID in the TrackPlays table.
    Is that what you really want to happen?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Isn't it cumulative blindman?

    Anyhoo - what's the DDL for music_BandTracks?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    Not sure if the Music_BandTracks DDL will really help but here it is:

    Code:
    CREATE TABLE [dbo].[Music_BandTracks](
    	[TrackId] [int] IDENTITY(1,1) NOT NULL,
    	[BandId] [int] NOT NULL,
    	[GenreId] [int],
    	[ReleaseId] [int],
    	[Name] [nvarchar](128) NOT NULL,
    	[NameUrl] [nvarchar](128),
    	[Length] [int] NULL,
    	[RecordingDate] [datetime],
    	[AudioFile] [nvarchar](256),
    	[IsExplicit] [bit] NOT NULL DEFAULT (0),
    	[IsLive] [bit] NOT NULL DEFAULT (1),
    	[BeatsPerMinute] [int] NULL,
    	[CreateDate] [datetime] NOT NULL DEFAULT (getdate()),
    	[PlayCount] [int] NOT NULL DEFAULT (0),
    blindman, the logic is fine. You can see that the trigger is updating the previous count.
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I was thinking more the indexes. Is the TrackID indexed? Is playCount indexed?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    There's a CLUSTERED INDEX on BandId
    A NON CLUSTERED INDEX on TrackId
    and a NON CLUSTERED INDEX on NameUrl
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

  7. #7
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    I think it's important to note that it's an 'Insert Scan' that's taking all the time according to the execution plan. Why would that take so long and could the fact that the Music_BandTrackPlays table is 20 million rows be part of the problem?

    The Music_BandTracks table is only 50,000 rows so I doubt any interaction with that table is the problem (no JOINs are taking place).
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •