July 6, 2017 at 4:58 am
hi, ( i need suggestion on coding practice)
i have a situation , i have one view , whoes structure is like
select x.*
from x
left join (
select x, y,z,poleid from y -- and pivot is done here
) as d
on x.poleid=d.poleid
where x.country =1
i have seen the plan it takes time in left join, becuase it gets whole data and then pivot it ,
if i could put the filter y.country =1 in side,
it would be faster . but that can not be done as view does not take parameter.
yours sincerley
July 6, 2017 at 5:12 am
rajemessage 14195 - Thursday, July 6, 2017 4:58 AMhi, ( i need suggestion on coding practice)
i have a situation , i have one view , whoes structure is like
select x.*
from x
left join (
select x, y,z,poleid from y -- and pivot is done here
) as d
on x.poleid=d.poleid
where x.country =1
i have seen the plan it takes time in left join, becuase it gets whole data and then pivot it ,
if i could put the filter y.country =1 in side,
it would be faster . but that can not be done as view does not take parameter.
yours sincerley
You have been around here long enough to know that we will need more information to answer this question!
😎
Post all related information, i.e. execution plan, DDL, sample data and what you have tried sofar.
July 6, 2017 at 5:25 am
Quick suggestion: convert it into an inline table-valued function. They work like a parameterised view.
Other than that, as Eirikur says, way too little information.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 6, 2017 at 6:13 am
Eirikur Eiriksson - Thursday, July 6, 2017 5:12 AMrajemessage 14195 - Thursday, July 6, 2017 4:58 AMhi, ( i need suggestion on coding practice)
i have a situation , i have one view , whoes structure is like
select x.*
from x
left join (
select x, y,z,poleid from y -- and pivot is done here
) as d
on x.poleid=d.poleid
where x.country =1
i have seen the plan it takes time in left join, becuase it gets whole data and then pivot it ,
if i could put the filter y.country =1 in side,
it would be faster . but that can not be done as view does not take parameter.
yours sincerley
You have been around here long enough to know that we will need more information to answer this question!
😎Post all related information, i.e. execution plan, DDL, sample data and what you have tried sofar.
first problem is i can not past those queries it is company polices.
second thing i am check DTA, AND PLANS,
third thing is , i want some general answers like making derived tables in views are good or not, because i am shure they are not going to be filltered
and the where clause is written on the basis of first table so first table will get filtered but what out derived tables?
ather than this, if i have stored procedure then it was quite easy for me to put the where clause in side derived tables. but it is a view?
so these scenario , i just need general thumb of rules.
July 6, 2017 at 6:36 am
rajemessage 14195 - Thursday, July 6, 2017 6:13 AMEirikur Eiriksson - Thursday, July 6, 2017 5:12 AMrajemessage 14195 - Thursday, July 6, 2017 4:58 AMhi, ( i need suggestion on coding practice)
i have a situation , i have one view , whoes structure is like
select x.*
from x
left join (
select x, y,z,poleid from y -- and pivot is done here
) as d
on x.poleid=d.poleid
where x.country =1
i have seen the plan it takes time in left join, becuase it gets whole data and then pivot it ,
if i could put the filter y.country =1 in side,
it would be faster . but that can not be done as view does not take parameter.
yours sincerley
You have been around here long enough to know that we will need more information to answer this question!
😎Post all related information, i.e. execution plan, DDL, sample data and what you have tried sofar.
first problem is i can not past those queries it is company polices.
second thing i am check DTA, AND PLANS,
third thing is , i want some general answers like making derived tables in views are good or not, because i am shure they are not going to be filltered
and the where clause is written on the basis of first table so first table will get filtered but what out derived tables?
ather than this, if i have stored procedure then it was quite easy for me to put the where clause in side derived tables. but it is a view?
so these scenario , i just need general thumb of rules.
My rule of thumb is not to give general advice when "it depends" on the underlying schema, data, indices, cardinality etc. etc. You can easily make up something with similar columns and data types, typical sample of fictitious data etc.
😎
July 6, 2017 at 6:55 am
In general, there's nothing wrong with derived tables, in views or elsewhere.
My general rule is to write any query in the simplest, most obvious way, and tweak it for performance reasons only after it's proven to have performance problems.
In your case, I'd echo Chris's advice. Convert your view to an in-line table-valued function, as they can be parameterised.
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
July 6, 2017 at 7:52 am
Eirikur Eiriksson - Thursday, July 6, 2017 6:36 AMrajemessage 14195 - Thursday, July 6, 2017 6:13 AMEirikur Eiriksson - Thursday, July 6, 2017 5:12 AMrajemessage 14195 - Thursday, July 6, 2017 4:58 AMhi, ( i need suggestion on coding practice)
i have a situation , i have one view , whoes structure is like
select x.*
from x
left join (
select x, y,z,poleid from y -- and pivot is done here
) as d
on x.poleid=d.poleid
where x.country =1
i have seen the plan it takes time in left join, becuase it gets whole data and then pivot it ,
if i could put the filter y.country =1 in side,
it would be faster . but that can not be done as view does not take parameter.
yours sincerley
You have been around here long enough to know that we will need more information to answer this question!
😎Post all related information, i.e. execution plan, DDL, sample data and what you have tried sofar.
first problem is i can not past those queries it is company polices.
second thing i am check DTA, AND PLANS,
third thing is , i want some general answers like making derived tables in views are good or not, because i am shure they are not going to be filltered
and the where clause is written on the basis of first table so first table will get filtered but what out derived tables?
ather than this, if i have stored procedure then it was quite easy for me to put the where clause in side derived tables. but it is a view?
so these scenario , i just need general thumb of rules.My rule of thumb is not to give general advice when "it depends" on the underlying schema, data, indices, cardinality etc. etc. You can easily make up something with similar columns and data types, typical sample of fictitious data etc.
😎
i have simplified the question.
i am calling a view (select * from view1 where country =1).
the problme is the fillter is applied to the first table in side the table becasue it is mandatory, but the other derived tables they are not mandatory
so the fillter is not getting applied to derived tables , and derived tables are slow.
q1) so if there is any way to apply the where clause in side derived tabels which is in left join, in side a view.( and view does not have parameter so i can not do it directly as my value of country param changes.
July 6, 2017 at 8:00 am
rajemessage 14195 - Thursday, July 6, 2017 7:52 AMEirikur Eiriksson - Thursday, July 6, 2017 6:36 AMrajemessage 14195 - Thursday, July 6, 2017 6:13 AMEirikur Eiriksson - Thursday, July 6, 2017 5:12 AMrajemessage 14195 - Thursday, July 6, 2017 4:58 AMhi, ( i need suggestion on coding practice)
i have a situation , i have one view , whoes structure is like
select x.*
from x
left join (
select x, y,z,poleid from y -- and pivot is done here
) as d
on x.poleid=d.poleid
where x.country =1
i have seen the plan it takes time in left join, becuase it gets whole data and then pivot it ,
if i could put the filter y.country =1 in side,
it would be faster . but that can not be done as view does not take parameter.
yours sincerley
You have been around here long enough to know that we will need more information to answer this question!
😎Post all related information, i.e. execution plan, DDL, sample data and what you have tried sofar.
first problem is i can not past those queries it is company polices.
second thing i am check DTA, AND PLANS,
third thing is , i want some general answers like making derived tables in views are good or not, because i am shure they are not going to be filltered
and the where clause is written on the basis of first table so first table will get filtered but what out derived tables?
ather than this, if i have stored procedure then it was quite easy for me to put the where clause in side derived tables. but it is a view?
so these scenario , i just need general thumb of rules.My rule of thumb is not to give general advice when "it depends" on the underlying schema, data, indices, cardinality etc. etc. You can easily make up something with similar columns and data types, typical sample of fictitious data etc.
😎i have simplified the question.
i am calling a view (select * from view1 where country =1).
the problme is the fillter is applied to the first table in side the table becasue it is mandatory, but the other derived tables they are not mandatory
so the fillter is not getting applied to derived tables , and derived tables are slow.
q1) so if there is any way to apply the where clause in side derived tabels which is in left join, in side a view.( and view does not have parameter so i can not do it directly as my value of country param changes.
An inline table-valued function could be used as a parameterised view.
Derived tables are not slow, that's silly - it's like saying cars are slow when they run out of petrol.
You could obfuscate your code - or use SQL Sentry Plan Explorer to obfuscate it for you.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply