Force execution plan in a view

Question

Force execution plan in a view

Viewing 7 posts - 16 through 21 (of 21 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Jeff Moden - Tuesday, November 21, 2017 9:10 PM
Hmmm... I haven't tried it but perhaps a BIGINT Sequence would work here.

This might be the path I'll take. The identiy column is almost meaningless, but it's reaching the bigint limit. A looping sequence might be the best bet.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

Luis Cazares - Wednesday, November 22, 2017 7:41 AM
Jeff Moden - Tuesday, November 21, 2017 9:10 PM
Hmmm... I haven't tried it but perhaps a BIGINT Sequence would work here.
This might be the path I'll take. The identiy column is almost meaningless, but it's reaching the bigint limit. A looping sequence might be the best bet.

Did you try it by naming the columns in the SELECT list instead of using (*)? My (probably over simplified) testing shows that using (*) produces a CI scan (causing the whole view to materialize) whereas using the named columns (even if it's ALL the columns) produces the desired CI Seek followed by an effective range scan.

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Jeff Moden - Wednesday, November 22, 2017 8:15 AM
Did you try it by naming the columns in the SELECT list instead of using (*)? My (probably over simplified) testing shows that using (*) produces a CI scan (causing the whole view to materialize) whereas using the named columns (even if it's ALL the columns) produces the desired CI Seek followed by an effective range scan.

I did. In fact, that was how I was testing because I originally assigned the values to variables to eliminate the display of the full view (before testing the filter).
Here are the execution plans (anonymized but might give you an idea of what's happening).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sergiy SSC Guru Points: 110206 More actions · Answer 4

If returned sets are always filtered by PVID you may use this trick:

CustomData = ROW_NUMBER() OVER (PARTITION BY PVID ORDER BY (SELECT NULL))

_____________
Code for TallyGenerator

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

Sergiy - Thursday, November 30, 2017 2:17 AM
If returned sets are always filtered by PVID you may use this trick:
CustomData = ROW_NUMBER() OVER (PARTITION BY PVID ORDER BY (SELECT NULL))

Thank you, but that was slower because it stills calculates number for the whole table before filtering. And, with this option, it has to sort the data.

They approved using the SEQUENCE, but I just hope I won't encounter a wild 2008 version during deployment.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

Luis Cazares - Thursday, November 30, 2017 9:32 AM
Sergiy - Thursday, November 30, 2017 2:17 AM
If returned sets are always filtered by PVID you may use this trick:
CustomData = ROW_NUMBER() OVER (PARTITION BY PVID ORDER BY (SELECT NULL))
Thank you, but that was slower because it stills calculates number for the whole table before filtering. And, with this option, it has to sort the data.
They approved using the SEQUENCE, but I just hope I won't encounter a wild 2008 version during deployment.

They should just put the IDENTITY column back and call it a day.

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

Jeff Moden - Thursday, November 30, 2017 8:01 PM
They should just put the IDENTITY column back and call it a day.

They don't want to keep reseeding the identity.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2