|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Indexed view appears slower
Placing an indexed view into a query appears to slow the query down. There
is a cross database reference to a table which has its own indexes. Creating an indexed view similar to the table minus a field or two and substituting the view for the table seems to slow the query down. This may be logical and if so, an answer may help me understand how to better speed up a query. Example: Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a on a.x=b.x slower than Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.table2 a on a.x=b.x -- Regards, Jamie Hi
Which edition are you using? Enterprise Edition will use Indexed views without additional hints but other editions need the NOEXPAND hint to use them. If you don't want to use an Indexed View in Enterprise Edition use the EXPAND VIEWS hint. Check out your query execution plan to see what is being used. John Show quoteHide quote "thejamie" wrote: > Placing an indexed view into a query appears to slow the query down. There > is a cross database reference to a table which has its own indexes. Creating > an indexed view similar to the table minus a field or two and substituting > the view for the table seems to slow the query down. This may be logical and > if so, an answer may help me understand how to better speed up a query. > > Example: > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a > on a.x=b.x > > slower than > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.table2 a > on a.x=b.x > -- > Regards, > Jamie We are using the STANDARD edition.
-- Show quoteHide quoteRegards, Jamie "John Bell" wrote: > Hi > > Which edition are you using? Enterprise Edition will use Indexed views > without additional hints but other editions need the NOEXPAND hint to use > them. If you don't want to use an Indexed View in Enterprise Edition use the > EXPAND VIEWS hint. Check out your query execution plan to see what is being > used. > > John > > "thejamie" wrote: > > > Placing an indexed view into a query appears to slow the query down. There > > is a cross database reference to a table which has its own indexes. Creating > > an indexed view similar to the table minus a field or two and substituting > > the view for the table seems to slow the query down. This may be logical and > > if so, an answer may help me understand how to better speed up a query. > > > > Example: > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a > > on a.x=b.x > > > > slower than > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.table2 a > > on a.x=b.x > > -- > > Regards, > > Jamie Hi
You will need to use a hint so the index is not expanded. John Show quoteHide quote "thejamie" wrote: > We are using the STANDARD edition. > -- > Regards, > Jamie > > > "John Bell" wrote: > > > Hi > > > > Which edition are you using? Enterprise Edition will use Indexed views > > without additional hints but other editions need the NOEXPAND hint to use > > them. If you don't want to use an Indexed View in Enterprise Edition use the > > EXPAND VIEWS hint. Check out your query execution plan to see what is being > > used. > > > > John > > > > "thejamie" wrote: > > > > > Placing an indexed view into a query appears to slow the query down. There > > > is a cross database reference to a table which has its own indexes. Creating > > > an indexed view similar to the table minus a field or two and substituting > > > the view for the table seems to slow the query down. This may be logical and > > > if so, an answer may help me understand how to better speed up a query. > > > > > > Example: > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a > > > on a.x=b.x > > > > > > slower than > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.table2 a > > > on a.x=b.x > > > -- > > > Regards, > > > Jamie Hi
I am not sure what has happened to the other post... You will need to use the NOEXPAND hint to make sure the indexed view is used. John Show quoteHide quote "thejamie" wrote: > We are using the STANDARD edition. > -- > Regards, > Jamie > > > "John Bell" wrote: > > > Hi > > > > Which edition are you using? Enterprise Edition will use Indexed views > > without additional hints but other editions need the NOEXPAND hint to use > > them. If you don't want to use an Indexed View in Enterprise Edition use the > > EXPAND VIEWS hint. Check out your query execution plan to see what is being > > used. > > > > John > > > > "thejamie" wrote: > > > > > Placing an indexed view into a query appears to slow the query down. There > > > is a cross database reference to a table which has its own indexes. Creating > > > an indexed view similar to the table minus a field or two and substituting > > > the view for the table seems to slow the query down. This may be logical and > > > if so, an answer may help me understand how to better speed up a query. > > > > > > Example: > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a > > > on a.x=b.x > > > > > > slower than > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.table2 a > > > on a.x=b.x > > > -- > > > Regards, > > > Jamie Such as: (???)
Create View IndxVw WITH SCHEMABINDING as Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a on a.x=b.x where dattimestmp between '01-01-05' and '12-31-05' Assume INDXVW_MYNDX is the clustered Index Then to use the view SET NOEXPAND ON SELECT * FROM IndxVW (INDXVW_MYNDX) WHERE monthname like 'Feb%' SET NOEXPAND OFF Or do I have the concept wrong? -- Show quoteHide quoteRegards, Jamie "John Bell" wrote: > Hi > > I am not sure what has happened to the other post... > > You will need to use the NOEXPAND hint to make sure the indexed view is used. > > John > > "thejamie" wrote: > > > We are using the STANDARD edition. > > -- > > Regards, > > Jamie > > > > > > "John Bell" wrote: > > > > > Hi > > > > > > Which edition are you using? Enterprise Edition will use Indexed views > > > without additional hints but other editions need the NOEXPAND hint to use > > > them. If you don't want to use an Indexed View in Enterprise Edition use the > > > EXPAND VIEWS hint. Check out your query execution plan to see what is being > > > used. > > > > > > John > > > > > > "thejamie" wrote: > > > > > > > Placing an indexed view into a query appears to slow the query down. There > > > > is a cross database reference to a table which has its own indexes. Creating > > > > an indexed view similar to the table minus a field or two and substituting > > > > the view for the table seems to slow the query down. This may be logical and > > > > if so, an answer may help me understand how to better speed up a query. > > > > > > > > Example: > > > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a > > > > on a.x=b.x > > > > > > > > slower than > > > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.table2 a > > > > on a.x=b.x > > > > -- > > > > Regards, > > > > Jamie Hi
NOEXPAND is a hint SELECT * FROM IndxVW WITH (NOEXPAND) WHERE monthname like 'Feb%' or SELECT * FROM IndxVW WITH (NOEXPAND,INDEX(1)) WHERE monthname like 'Feb%' EXPAND VIEWS is an option SELECT * FROM IndxVW WHERE monthname like 'Feb%' OPTION (EXPAND VIEWS) See books online for more. John Show quoteHide quote "thejamie" wrote: > Such as: (???) > Create View IndxVw WITH SCHEMABINDING as > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a > on a.x=b.x where dattimestmp between '01-01-05' and '12-31-05' > > Assume INDXVW_MYNDX is the clustered Index > > Then to use the view > SET NOEXPAND ON > SELECT * FROM IndxVW (INDXVW_MYNDX) WHERE monthname like 'Feb%' > SET NOEXPAND OFF > > Or do I have the concept wrong? > > -- > Regards, > Jamie > > > "John Bell" wrote: > > > Hi > > > > I am not sure what has happened to the other post... > > > > You will need to use the NOEXPAND hint to make sure the indexed view is used. > > > > John > > > > "thejamie" wrote: > > > > > We are using the STANDARD edition. > > > -- > > > Regards, > > > Jamie > > > > > > > > > "John Bell" wrote: > > > > > > > Hi > > > > > > > > Which edition are you using? Enterprise Edition will use Indexed views > > > > without additional hints but other editions need the NOEXPAND hint to use > > > > them. If you don't want to use an Indexed View in Enterprise Edition use the > > > > EXPAND VIEWS hint. Check out your query execution plan to see what is being > > > > used. > > > > > > > > John > > > > > > > > "thejamie" wrote: > > > > > > > > > Placing an indexed view into a query appears to slow the query down. There > > > > > is a cross database reference to a table which has its own indexes. Creating > > > > > an indexed view similar to the table minus a field or two and substituting > > > > > the view for the table seems to slow the query down. This may be logical and > > > > > if so, an answer may help me understand how to better speed up a query. > > > > > > > > > > Example: > > > > > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a > > > > > on a.x=b.x > > > > > > > > > > slower than > > > > > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.table2 a > > > > > on a.x=b.x > > > > > -- > > > > > Regards, > > > > > Jamie John,
If I understand correctly, an indexed view can be used as a way of obtaining a subset of data from a very large table. When I create an indexed view against a table, it causes values that are written to this table to fail. When the inserted value affects the where condition - example: view contains values in table where flag = 0 - and a new value is inserted which contains the value with a flag of zero - thus forcing the index to try to rebuild itself. In the rebuild on such a large table, it prevents the value from being written to the table. All the attempts made by me to create an indexed view have resulted in a catastrophic failure on the database system. These failures make me doubt what value exists in an indexed view. So far, they do not seem to be faster, they cause the database optimization routines to fail, they cause production to go down... I'd truly like to see a solid example of when an indexed view makes sense. It would make sense that they work but..., under what circumstances? -- Show quoteHide quoteRegards, Jamie "John Bell" wrote: > Hi > > NOEXPAND is a hint > > SELECT * FROM IndxVW WITH (NOEXPAND) WHERE monthname like 'Feb%' > or > SELECT * FROM IndxVW WITH (NOEXPAND,INDEX(1)) WHERE monthname like 'Feb%' > > EXPAND VIEWS is an option > > SELECT * FROM IndxVW WHERE monthname like 'Feb%' OPTION (EXPAND VIEWS) > > See books online for more. > > John > > "thejamie" wrote: > > > Such as: (???) > > Create View IndxVw WITH SCHEMABINDING as > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a > > on a.x=b.x where dattimestmp between '01-01-05' and '12-31-05' > > > > Assume INDXVW_MYNDX is the clustered Index > > > > Then to use the view > > SET NOEXPAND ON > > SELECT * FROM IndxVW (INDXVW_MYNDX) WHERE monthname like 'Feb%' > > SET NOEXPAND OFF > > > > Or do I have the concept wrong? > > > > -- > > Regards, > > Jamie > > > > > > "John Bell" wrote: > > > > > Hi > > > > > > I am not sure what has happened to the other post... > > > > > > You will need to use the NOEXPAND hint to make sure the indexed view is used. > > > > > > John > > > > > > "thejamie" wrote: > > > > > > > We are using the STANDARD edition. > > > > -- > > > > Regards, > > > > Jamie > > > > > > > > > > > > "John Bell" wrote: > > > > > > > > > Hi > > > > > > > > > > Which edition are you using? Enterprise Edition will use Indexed views > > > > > without additional hints but other editions need the NOEXPAND hint to use > > > > > them. If you don't want to use an Indexed View in Enterprise Edition use the > > > > > EXPAND VIEWS hint. Check out your query execution plan to see what is being > > > > > used. > > > > > > > > > > John > > > > > > > > > > "thejamie" wrote: > > > > > > > > > > > Placing an indexed view into a query appears to slow the query down. There > > > > > > is a cross database reference to a table which has its own indexes. Creating > > > > > > an indexed view similar to the table minus a field or two and substituting > > > > > > the view for the table seems to slow the query down. This may be logical and > > > > > > if so, an answer may help me understand how to better speed up a query. > > > > > > > > > > > > Example: > > > > > > > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.ixview a > > > > > > on a.x=b.x > > > > > > > > > > > > slower than > > > > > > > > > > > > Select b.x,b.y, a.z from table1 b left outer join diffdb.dbo.table2 a > > > > > > on a.x=b.x > > > > > > -- > > > > > > Regards, > > > > > > Jamie  
Other interesting topics
Advice about database deployment
sp_indexoption Cannot change tran log to "unrestricted" growth Revert to checkpoint change precision in a table column Multiple files/filegroups Getting a syntax error involving Microsoft.SqlServer.SmoEnum SQL 2000 restore problem Should I be concerned about this? Problem with top 1. urgent help needed |
|||||||||||||||||||||||
