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. I
f
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,
RichUnless 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" <Rich@.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?
"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 accessin
g
> the base tables...
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@.microsoft.com...
>
>|||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?
"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" <Rich@.discussions.microsoft.com> wrote in message
> news:8500A93B-2C02-452B-A8F2-E45BA68B6F82@.microsoft.com...
>|||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. 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 quer
y
the table with as little lag as possible, but I also what to be able to writ
e
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:
>
> 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.
>
> 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 m
odifications have less
lag. The opposite. Then you modify the tables, the index on the view also ne
ed to be maintained,
which can be more costly compared to a similar index on the base table (depe
nding 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/e...l/statquery.asp for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.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 qu
ery
> the table with as little lag as possible, but I also what to be able to wr
ite
> 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 yiel
d
> more performance? to have this option checked on or off?
> Thanks,
> Rich
> "Hugo Kornelis" wrote:
>|||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 thousan
d
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.
"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
>
