Viewing 15 posts - 61 through 75 (of 78 total)
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...
July 14, 2009 at 2:50 am
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,...
July 14, 2009 at 2:38 am
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...
July 13, 2009 at 9:56 am
More plans, with the different indexes suggested by Gail.
@GSquared, yes, indeed we can!
July 13, 2009 at 9:55 am
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...
July 13, 2009 at 9:40 am
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...
July 13, 2009 at 9:36 am
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.
July 13, 2009 at 9:28 am
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...
July 13, 2009 at 9:04 am
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...
July 13, 2009 at 9:00 am
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...
July 13, 2009 at 8:52 am
It's just been pointed out to me that I missed a 'wide open' opportunity to plug SQL Data Generator! 😀
July 13, 2009 at 8:38 am
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
July 13, 2009 at 8:29 am
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 =...
July 13, 2009 at 8:26 am
Viewing 15 posts - 61 through 75 (of 78 total)