|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
can a view of a table imrpove table input performance?I have a table (sql Server2k) which users connect to through ODBC - read
only. I have to input/write a lot of data to this table everyday. I observed that it takes a lot longer to populate this table than one which is not public. I am guessing that people have this table open through ODBC. If I had the users connect to a view of this table (Select * From tbl1) would this resolve the performance issue? I realize that people would have to refresh the view each day to get the most recent data, but I can send out a memo advising them of this. Any suggestions appreciated. Thanks, Rich Unless it is an indexed view, a view doesn't actually store data. So when
you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT .... view definition) which, long story short, means you're still accessing the base tables... Show quote "Rich" <R***@discussions.microsoft.com> wrote in message news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@microsoft.com... >I have a table (sql Server2k) which users connect to through ODBC - read > only. I have to input/write a lot of data to this table everyday. I > observed that it takes a lot longer to populate this table than one which > is > not public. I am guessing that people have this table open through ODBC. > If > I had the users connect to a view of this table (Select * From tbl1) would > this resolve the performance issue? > > I realize that people would have to refresh the view each day to get the > most recent data, but I can send out a memo advising them of this. > > Any suggestions appreciated. Thanks, > Rich I guess I was thinking about a snapshot view. Is there such a thing?
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Unless it is an indexed view, a view doesn't actually store data. So when > you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT > .... view definition) which, long story short, means you're still accessing > the base tables... > > > "Rich" <R***@discussions.microsoft.com> wrote in message > news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@microsoft.com... > >I have a table (sql Server2k) which users connect to through ODBC - read > > only. I have to input/write a lot of data to this table everyday. I > > observed that it takes a lot longer to populate this table than one which > > is > > not public. I am guessing that people have this table open through ODBC. > > If > > I had the users connect to a view of this table (Select * From tbl1) would > > this resolve the performance issue? > > > > I realize that people would have to refresh the view each day to get the > > most recent data, but I can send out a memo advising them of this. > > > > Any suggestions appreciated. Thanks, > > Rich > > > That would be creating an index on the view, an indexed view.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Rich" <R***@discussions.microsoft.com> wrote in message news:8500A93B-2C02-452B-A8F2-E45BA68B6F82@microsoft.com... >I guess I was thinking about a snapshot view. Is there such a thing? > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> Unless it is an indexed view, a view doesn't actually store data. So when >> you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT >> .... view definition) which, long story short, means you're still accessing >> the base tables... >> >> >> "Rich" <R***@discussions.microsoft.com> wrote in message >> news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@microsoft.com... >> >I have a table (sql Server2k) which users connect to through ODBC - read >> > only. I have to input/write a lot of data to this table everyday. I >> > observed that it takes a lot longer to populate this table than one which >> > is >> > not public. I am guessing that people have this table open through ODBC. >> > If >> > I had the users connect to a view of this table (Select * From tbl1) would >> > this resolve the performance issue? >> > >> > I realize that people would have to refresh the view each day to get the >> > most recent data, but I can send out a memo advising them of this. >> > >> > Any suggestions appreciated. Thanks, >> > Rich >> >> >> So to create an indexed view is it:
Create Index view1Ind On view1(columnID) While I am at it, is it possible to have on the most elemental index on a table and have the rest of the indexes on the view? where the view would contain all of the data in the table? Show quote "Tibor Karaszi" wrote: > That would be creating an index on the view, an indexed view. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Rich" <R***@discussions.microsoft.com> wrote in message > news:8500A93B-2C02-452B-A8F2-E45BA68B6F82@microsoft.com... > >I guess I was thinking about a snapshot view. Is there such a thing? > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > >> Unless it is an indexed view, a view doesn't actually store data. So when > >> you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT > >> .... view definition) which, long story short, means you're still accessing > >> the base tables... > >> > >> > >> "Rich" <R***@discussions.microsoft.com> wrote in message > >> news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@microsoft.com... > >> >I have a table (sql Server2k) which users connect to through ODBC - read > >> > only. I have to input/write a lot of data to this table everyday. I > >> > observed that it takes a lot longer to populate this table than one which > >> > is > >> > not public. I am guessing that people have this table open through ODBC. > >> > If > >> > I had the users connect to a view of this table (Select * From tbl1) would > >> > this resolve the performance issue? > >> > > >> > I realize that people would have to refresh the view each day to get the > >> > most recent data, but I can send out a memo advising them of this. > >> > > >> > Any suggestions appreciated. Thanks, > >> > Rich > >> > >> > >> > > On Fri, 26 Aug 2005 11:59:06 -0700, Rich wrote:
>So to create an indexed view is it: Hi Rich,> >Create Index view1Ind >On view1(columnID) To create an indexed view, you must use a unique clustered index: CREATE UNIQUE CLUSTERED INDEX view1Ind ON view1(columnID) (assuming that columnID is unique in the view, of course). Once you have this index in place, you can define additional views on the index, but these can't be clustered. >While I am at it, is it possible to have on the most elemental index on a I guess you can, but what's the point? If the view is an exact copy of>table and have the rest of the indexes on the view? where the view would >contain all of the data in the table? the table, you'll gain some performance by dropping the view and indexing the table instead. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Basically, I want to have my cake and eat it. I have about 8-9 indexes on
this one table. But data input (only I input the data programatically - batch operation) is becoming slow on this one table because people are opening the table through ODBC connection. I wasnt users to be able to query the table with as little lag as possible, but I also what to be able to write data to the table without the lag. By the way, when I create a new index using the GUI tool in table desing, it has a selection option of "Do not automatically recompute statistics". I have been checking this option on for each index I creat. What would yield more performance? to have this option checked on or off? Thanks, Rich Show quote "Hugo Kornelis" wrote: > On Fri, 26 Aug 2005 11:59:06 -0700, Rich wrote: > > >So to create an indexed view is it: > > > >Create Index view1Ind > >On view1(columnID) > > Hi Rich, > > To create an indexed view, you must use a unique clustered index: > > CREATE UNIQUE CLUSTERED INDEX view1Ind > ON view1(columnID) > > (assuming that columnID is unique in the view, of course). > > Once you have this index in place, you can define additional views on > the index, but these can't be clustered. > > > >While I am at it, is it possible to have on the most elemental index on a > >table and have the rest of the indexes on the view? where the view would > >contain all of the data in the table? > > I guess you can, but what's the point? If the view is an exact copy of > the table, you'll gain some performance by dropping the view and > indexing the table instead. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) > > Basically, I want to have my cake and eat it. That tend to be difficult in real life. Indexed views is not a way to make modifications have less lag. The opposite. Then you modify the tables, the index on the view also need to be maintained, which can be more costly compared to a similar index on the base table (depending on the circumstances). How real time does the reporting tables need to be. Why not create snapshots which you update regularly? In general, you do *not* want to turn off auto-update statistics. Statistics are not updated (in general) when you modify. They are updated when you SELECT, if they are out-of-date. This is so that the optimizer has good information to go on so it can pick a good plan. See http://msdn.microsoft.com/library/en-us/dnsql2k/html/statquery.asp for more information. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Rich" <R***@discussions.microsoft.com> wrote in message news:54F21B87-C722-45AD-A4E2-BB7DDD3A140F@microsoft.com... > Basically, I want to have my cake and eat it. I have about 8-9 indexes on > this one table. But data input (only I input the data programatically - > batch operation) is becoming slow on this one table because people are > opening the table through ODBC connection. I wasnt users to be able to query > the table with as little lag as possible, but I also what to be able to write > data to the table without the lag. > > By the way, when I create a new index using the GUI tool in table desing, it > has a selection option of "Do not automatically recompute statistics". I > have been checking this option on for each index I creat. What would yield > more performance? to have this option checked on or off? > > Thanks, > Rich > > "Hugo Kornelis" wrote: > >> On Fri, 26 Aug 2005 11:59:06 -0700, Rich wrote: >> >> >So to create an indexed view is it: >> > >> >Create Index view1Ind >> >On view1(columnID) >> >> Hi Rich, >> >> To create an indexed view, you must use a unique clustered index: >> >> CREATE UNIQUE CLUSTERED INDEX view1Ind >> ON view1(columnID) >> >> (assuming that columnID is unique in the view, of course). >> >> Once you have this index in place, you can define additional views on >> the index, but these can't be clustered. >> >> >> >While I am at it, is it possible to have on the most elemental index on a >> >table and have the rest of the indexes on the view? where the view would >> >contain all of the data in the table? >> >> I guess you can, but what's the point? If the view is an exact copy of >> the table, you'll gain some performance by dropping the view and >> indexing the table instead. >> >> Best, Hugo >> -- >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) >> you also can:
select * into new_table from old_table; load data into new_table drop table old_table; sp_rename new_table This is an interesting idea. Thanks. I could invoke DTS in code (large
table) Actually, well, thinking about it more, I am inserting a few thousand records a day, millions of records. Even with DTS that might be too slow. But it is an idea. When the table is large enough I may start using OLAP on it. Show quote "AK" wrote: > you also can: > > select * into new_table from old_table; > load data into new_table > drop table old_table; > sp_rename new_table > >  |
|||||||||||||||||||||||
Other interesting topics