Oh, and I forgot to mention what @cutoffdate is doing. That's actually only ever set to one value, namely 30 days ago. It happens to calculate this value in the...

Ok, lots of information to post after various different tests. Attached are the execution plans of the insert statement. There are separate runs for it with and without points category,...

Well, that got a reaction out of it!

Once we've got a version of the SP that there's general agreement for, I'm happy to put it live so that we can pick up some real data on how...

More plans, with the different indexes suggested by Gail.

@GSquared, yes, indeed we can!

And here's the plans for the temporary table with the index added.

Out of interest, Gail, when you say that it looks better already, what part are you referring to? When...

While I can try splitting the query and look at the execution plans, making the code changes will require that that part of it passes through our test and release...

Ok, I've run the query, simply using a temporary table rather than a table variable. I haven't put any indexes on the temporary table. The execution plans are attached.

Yes, sorry, done really quickly!

Gail,

The PointsCategory null bit has three possible values that it's ever called with:

NULL = Get me all the points (used on the homepage, and top left on the main scores...

Ok, perhaps some further explanation of how the table works!

I've attached a sample of the data in it, namely the data for me. As you'll see, I've answered a few...

UserPoints:

CREATE TABLE [dbo].[UserPoints]

(

[UserPointsRecordID] [int] NOT NULL IDENTITY(1, 1),

[UserID] [int] NOT NULL,

[Date] [smalldatetime] NOT NULL,

[PointsScored] [int] NOT NULL,

[PointsCategory] [int] NOT NULL

)

GO

ALTER TABLE [dbo].[UserPoints] ADD CONSTRAINT [PK_UserPoints] PRIMARY KEY CLUSTERED ([UserPointsRecordID])

GO

CREATE...

It's just been pointed out to me that I missed a 'wide open' opportunity to plug SQL Data Generator! 😀

Oh, and it's 2005 Standard.

We've put the wheels in motion to get some new hardware, and when we do that we'll almost certainly move to 2008 at the same time.

Mel

Sure thing!

CREATE TABLE [dbo].[UserPoints](

[UserPointsRecordID] [int] IDENTITY(1,1) NOT NULL,

[UserID] [int] NOT NULL,

[Date] [smalldatetime] NOT NULL,

[PointsScored] [int] NOT NULL,

[PointsCategory] [int] NOT NULL,

CONSTRAINT [PK_UserPoints] PRIMARY KEY CLUSTERED

(

[UserPointsRecordID] ASC

)WITH (PAD_INDEX =...