Help us tune a query

Question

Help us tune a query

Viewing 14 posts - 91 through 103 (of 103 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

GilaMonster (7/15/2009)
Mel Harbour (7/15/2009)
Note that I've adjusted the forum setup to avoid having to zip up these sqlplans.
Excellent. Thank you for that.

Really. That alone was worth the price of admission. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

manish.singh-1128425 SSC-Addicted Points: 402 More actions · Answer 2

Hi,

Can you change the where clause from

WHERE PointsCategory = ISNULL(@PointsCategory, PointsCategory)

to

WHERE (PointsCategory = @PointsCategory OR @PointsCategory IS NULL)

This would definetly give some benifits.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Jeff Moden (7/15/2009)
...
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.
...

If we're talking about updating the User Points, a once-a-day recalc would not be nearly sufficient. That would undoubtedly lead to a lot of confused and frustrated users. The 5-10 minute lag that we have now is already confusing enough...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

@manish,

There's almost no difference between the two versions, performance-wise. As it happens, we sidestepped the issue by having a whole separate query for the two different cases (null and not null).

Mel HarbourProject ManagerRed Gate Software

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

RBarryYoung (7/16/2009)
Jeff Moden (7/15/2009)
...
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.
...
If we're talking about updating the User Points, a once-a-day recalc would not be nearly sufficient. That would undoubtedly lead to a lot of confused and frustrated users. The 5-10 minute lag that we have now is already confusing enough...

Whatever... what's really important is that it doesn't appear to be instant right now and probably doesn't need to be. The 5-10 minute lag really isn't confusing if you simply understand that lags are a possibility just like a lot of online checking accounts use to have.

--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: 1004406 More actions · Answer 6

Hey Mel,

I don't know if anyone else is having the same problem but all I can see of Avatar's today is a little red x. And, yeah, I'm using IE.

--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)

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 7

Jeff Moden (7/16/2009)
Hey Mel,
I don't know if anyone else is having the same problem but all I can see of Avatar's today is a little red x. And, yeah, I'm using IE.

If you go to edit your avatar, you see:

Invalid Avatar Directory!

----------------------

The avatar upload directory settings is pointing to a directory that does not exist.

Uploads\Avatars

If your the administrator you can update this setting within the admin area to point to a valid upload directory

p.s. *cough* grammar check *cough*

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

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

Avatars now fixed again. We rolled out a couple of changes to the site and the forums this morning, and the directory containing the avatars needed to be copied across.

Mel HarbourProject ManagerRed Gate Software

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 9

Thanks Mel, added a custom avatar just for Jeff!

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

One other thing you might want to take a gander at.... the wrong type of quotes have been used on the IFCode Shortcuts that have them. For example, ”sql” should be "sql" and ”xml” should be "xml" for the code shortcuts.

--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 11

@jcrawf02,

You had me worried for a minute there... 🙂

Mel HarbourProject ManagerRed Gate Software

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

Jeff Moden (7/16/2009)
One other thing you might want to take a gander at.... the wrong type of quotes have been used on the IFCode Shortcuts that have them. For example, ”sql” should be "sql" and ”xml” should be "xml" for the code shortcuts.

Ach... hard to see there. In the IFCode Shortcuts window, and in the edit window, they show up as italic quotes and they don't work as they are. Once they're manually changed to straight quotes, they produce a code window.

--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 13

@jeff,

Ok, think I've fixed that now!

Mel HarbourProject ManagerRed Gate Software

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

jcrawf02 (7/16/2009)
Thanks Mel, added a custom avatar just for Jeff!

Oh, that's wicked! You got me on that one... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]