Help us tune a query

Question

Help us tune a query

Viewing 15 posts - 76 through 90 (of 103 total)

You must be logged in to reply to this topic. Login to reply

Mel Harbour SSC Eights! Points: 857 More actions · Answer 1

Well, at least it's saved me time in posting execution plans! 🙂

As I said, the nice thing would be to write an online execution plan display. That would be a little bit time consuming though, I guess.

Mel HarbourProject ManagerRed Gate Software

Mel Harbour SSC Eights! Points: 857 More actions · Answer 2

Here's the DDL for the Users table:

CREATE TABLE [dbo].[Users]

(

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

[SingleSignonMemberID] [int] NOT NULL,

[EmailAddress] [nvarchar] (250) COLLATE Latin1_General_CI_AS NOT NULL,

[DisplayName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NOT NULL,

[CompanyID] [int] NULL,

[Biography] [text] COLLATE Latin1_General_CI_AS NULL,

[Address] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL,

[PostCode] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL,

[Country] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL,

[AuthorFirstName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL,

[AuthorLastName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL,

[AuthorFullName] [varchar] (200) COLLATE Latin1_General_CI_AS NULL,

[AuthorForumID] [int] NULL,

[IsRegularAuthor] [bit] NOT NULL CONSTRAINT [DF_Users_IsRegularAuthor] DEFAULT ((0)),

[LastLoginDate] [datetime] NOT NULL,

[PaymentDetails] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL

)

GO

ALTER TABLE [dbo].[Users] ADD CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserID])

GO

CREATE NONCLUSTERED INDEX [IX_AuthorNames] ON [dbo].[Users] ([AuthorFullName])

GO

CREATE NONCLUSTERED INDEX [IX_UsersDisplayName] ON [dbo].[Users] ([DisplayName])

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_UsersEmailAddress] ON [dbo].[Users] ([EmailAddress])

GO

CREATE NONCLUSTERED INDEX [IX_UsersSSOID] ON [dbo].[Users] ([SingleSignonMemberID])

GO

Mel HarbourProject ManagerRed Gate Software

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 3

GilaMonster (7/15/2009)
Jeff Moden (7/14/2009)
Something else is going on as well. Look at the difference in durations on your machine compared to mine.
Look at the difference in reads as well.
I'm running exactly the code I posted (comments and all) just with GOs added between the three tests and the names of the temp tables changed. Weird.

What kind of box are you running it on, Gail? Mine is a 7 year old p4 running at 1.8 Ghz on 1GB Ram, Windows XP sp2, SQL Server 2k5 Dev Ed sp 2 (haven't done the sp3 upgrade yet because I still have a lot of support work on sp2 to do).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mel Harbour SSC Eights! Points: 857 More actions · Answer 4

I don't understand the logic behind its selection of IX_UsersDisplayName when it does the join into the Users table. Perhaps I'm just totally misunderstanding how indexes work, but I'm really surprised that it attempts to use that index, given that we're not asking it to filter on DisplayName.

Mel HarbourProject ManagerRed Gate Software

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 5

Mel Harbour (7/15/2009)
I don't understand the logic behind its selection of IX_UsersDisplayName when it does the join into the Users table. Perhaps I'm just totally misunderstanding how indexes work, but I'm really surprised that it attempts to use that index, given that we're not asking it to filter on DisplayName.

Me too... perhaps I'm just missing it on this thread, but I don't see where you identified what the indexes were that you have on the Users table. Any chance of you popping that out with the table creation statement?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mel Harbour SSC Eights! Points: 857 More actions · Answer 6

It's just below the table definition. Scroll down the code block a couple of posts above and you should see them.

Mel HarbourProject ManagerRed Gate Software

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 7

Mel Harbour (7/15/2009)
It's just below the table definition. Scroll down the code block a couple of posts above and you should see them.

Heh... thanks Mel... you're reading my mind and posting before me.

You shouldn't need a covering index for this display name problem but it sure does seem like SQL Server want's it to run right. You can try one of two things... create a covering index for UserID/DisplayName or add an index hint (I hate to do that) to force the PK to be used. Um... maybe SQL Server is just making a bad choice based on stats... when's the last time you did a rebuild on the indexes on the Users table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jeff Moden (7/15/2009)
GilaMonster (7/15/2009)
Jeff Moden (7/14/2009)
Something else is going on as well. Look at the difference in durations on your machine compared to mine.
Look at the difference in reads as well.
I'm running exactly the code I posted (comments and all) just with GOs added between the three tests and the names of the temp tables changed. Weird.
What kind of box are you running it on, Gail? Mine is a 7 year old p4 running at 1.8 Ghz on 1GB Ram, Windows XP sp2, SQL Server 2k5 Dev Ed sp 2 (haven't done the sp3 upgrade yet because I still have a lot of support work on sp2 to do).

A 1 year old Intel Core-2 Quad, 4 GB memory, Vista Ultimate, SQL 2008 developer SP1. SQL's allowed to use 2 of those cores and 2 GB memory

There's definitely some parallelism going on, if you look at the results I posted, duration's lower than CPU time, which is only possible with parallelism or time travel. It's the reads that have me confused. Durations I can understand differing, but the difference in reads is really strange.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mel Harbour SSC Eights! Points: 857 More actions · Answer 9

Statistics are being updated daily at the moment, so I wouldn't have thought they'd be too far out of date. I can certainly try creating a covering index for those fields, and see how it reacts.

Mel HarbourProject ManagerRed Gate Software

Mel Harbour SSC Eights! Points: 857 More actions · Answer 10

And here's the results. Query ran faster with the index added. Still doing an Index Scan of the users table, but the join is now quicker.

Mel HarbourProject ManagerRed Gate Software

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 11

Personally i think i would be better to have a seperate code path for where @AdditionalUser is and is not null.

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 12

Just a test... please ignore this post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 13

Heh... well, that little test pretty much cinches things up. The addition of the post above didn't change my counts on the home page what-so-ever.... not even in a new instance of IE with a Ctrl-f5 reload. The home page also loads very quickly when compared to the return to a thread after adding a post.

Survey says, do the calc once a day and store it in a table (which I think is being done now, anyway). Use a synonym to flop between two tables so the change is instant to the users which also might be being done now.

Save the brain cells to make adding a post faster. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 14

Heh... correction! It looks like the updates may be on a timer... both new posts suddenly registered on the home page. Still, I wouldn't spend much more time on this particular problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mel Harbour SSC Eights! Points: 857 More actions · Answer 15

@jeff,

The points stored in UserPoints are always bang up to date. What's happening is that the ASP.NET control that displays the output has some caching built in. So if you look at the homepage twice in relatively quick succession, it doesn't bother rebuilding the control, it just uses what was output last time round.

Mel HarbourProject ManagerRed Gate Software