Hi,
Lets suppose a table with 300.000+ rows. This table is heavily Updated,
SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
most of cases, a row is between 7 and 10 times updated, in different columns
.
I have created 5 indexes in this table, corresponding to columns that I
frequently use in SELECTs or UPDATEs actions.
BOL tells me that a 100(0)% Fill Factor should not be used on heavily
updated table.
In this particular case, what Fill Factor percentage can be used in each
index?
Thanks in advance...
Marco.Marco
if you have an index page with 1,4,5, keys and try to insert a row=2 so
page splits may occured because there is no room for a new one and 4,5 key
need to be moved on another page. These keys are out of the logical order
and it causes to External fragemtation your database. But if your keys are
added as 6,7,8 there will no pages splits and a fill factor 90% is gonna be
ok. The same way take a look at Updating and Deletion.
http://www.sql-server-performance.c...agmentation.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||If your index is doing page splits you might benefit from using a lower
fillfactor... However this is true on the main table ONLY when new rows are
NOT added at the end of the table ( as would be the case where the clustered
index is the PK, or a strictly increaseing Date field.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
> columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Marco
Also read this one
http://www.sql-server-performance.c...ing_indexes.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Firstly have a look at
http://msdn.microsoft.com/library/d...>
_05_9ak5.asp
This gives a bit of background on what a fill factor does.
So a fill factor of 100% means that the total size of the data in the
datafile will be as small as possible, but your running the risk of Page
Splitting, which can affect performance.
A lower value say 0% means that there is less change of page splitting, but
your datafiles will be a bit bigger.
So with your example you need to ask yourself 'What is more important to me'
size or performance. BTW its not to say that a 0% will not cause page
splitting, it does, but that there is probably going to be less chance of it
happening.
Anyway the answer to your question is going to be a bit glib. Basically
there is no real way of finding for yourself what is going to be the best
alternative except to experement with the fill factors until you find out
yourself what is the best combination of fill factors.
NB you can run a nightly job to re-organise your database index using the
Database Maintenance plans, that will get rid of your split pages, but it ma
y
take a bit of time to do, see
http://msdn.microsoft.com/library/d...r />
_4iur.asp
"Marco Pais" wrote:
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different colum
ns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Thanks guys...
I will check these articles out...
Showing posts with label deleted. Show all posts
Showing posts with label deleted. Show all posts
Wednesday, March 21, 2012
index Option - Fill Factor
Hi,
Lets suppose a table with 300.000+ rows. This table is heavily Updated,
SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
most of cases, a row is between 7 and 10 times updated, in different columns.
I have created 5 indexes in this table, corresponding to columns that I
frequently use in SELECTs or UPDATEs actions.
BOL tells me that a 100(0)% Fill Factor should not be used on heavily
updated table.
In this particular case, what Fill Factor percentage can be used in each
index?
Thanks in advance...
Marco.
Marco
if you have an index page with 1,4,5, keys and try to insert a row=2 so
page splits may occured because there is no room for a new one and 4,5 key
need to be moved on another page. These keys are out of the logical order
and it causes to External fragemtation your database. But if your keys are
added as 6,7,8 there will no pages splits and a fill factor 90% is gonna be
ok. The same way take a look at Updating and Deletion.
http://www.sql-server-performance.co...gmentation.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.
|||If your index is doing page splits you might benefit from using a lower
fillfactor... However this is true on the main table ONLY when new rows are
NOT added at the end of the table ( as would be the case where the clustered
index is the PK, or a strictly increaseing Date field.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
> columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.
|||Marco
Also read this one
http://www.sql-server-performance.co...ng_indexes.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.
|||Firstly have a look at
http://msdn.microsoft.com/library/de...es_05_9ak5.asp
This gives a bit of background on what a fill factor does.
So a fill factor of 100% means that the total size of the data in the
datafile will be as small as possible, but your running the risk of Page
Splitting, which can affect performance.
A lower value say 0% means that there is less change of page splitting, but
your datafiles will be a bit bigger.
So with your example you need to ask yourself 'What is more important to me'
size or performance. BTW its not to say that a 0% will not cause page
splitting, it does, but that there is probably going to be less chance of it
happening.
Anyway the answer to your question is going to be a bit glib. Basically
there is no real way of finding for yourself what is going to be the best
alternative except to experement with the fill factors until you find out
yourself what is the best combination of fill factors.
NB you can run a nightly job to re-organise your database index using the
Database Maintenance plans, that will get rid of your split pages, but it may
take a bit of time to do, see
http://msdn.microsoft.com/library/de...ntwiz_4iur.asp
"Marco Pais" wrote:
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.
|||Thanks guys...
I will check these articles out...
Lets suppose a table with 300.000+ rows. This table is heavily Updated,
SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
most of cases, a row is between 7 and 10 times updated, in different columns.
I have created 5 indexes in this table, corresponding to columns that I
frequently use in SELECTs or UPDATEs actions.
BOL tells me that a 100(0)% Fill Factor should not be used on heavily
updated table.
In this particular case, what Fill Factor percentage can be used in each
index?
Thanks in advance...
Marco.
Marco
if you have an index page with 1,4,5, keys and try to insert a row=2 so
page splits may occured because there is no room for a new one and 4,5 key
need to be moved on another page. These keys are out of the logical order
and it causes to External fragemtation your database. But if your keys are
added as 6,7,8 there will no pages splits and a fill factor 90% is gonna be
ok. The same way take a look at Updating and Deletion.
http://www.sql-server-performance.co...gmentation.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.
|||If your index is doing page splits you might benefit from using a lower
fillfactor... However this is true on the main table ONLY when new rows are
NOT added at the end of the table ( as would be the case where the clustered
index is the PK, or a strictly increaseing Date field.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
> columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.
|||Marco
Also read this one
http://www.sql-server-performance.co...ng_indexes.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.
|||Firstly have a look at
http://msdn.microsoft.com/library/de...es_05_9ak5.asp
This gives a bit of background on what a fill factor does.
So a fill factor of 100% means that the total size of the data in the
datafile will be as small as possible, but your running the risk of Page
Splitting, which can affect performance.
A lower value say 0% means that there is less change of page splitting, but
your datafiles will be a bit bigger.
So with your example you need to ask yourself 'What is more important to me'
size or performance. BTW its not to say that a 0% will not cause page
splitting, it does, but that there is probably going to be less chance of it
happening.
Anyway the answer to your question is going to be a bit glib. Basically
there is no real way of finding for yourself what is going to be the best
alternative except to experement with the fill factors until you find out
yourself what is the best combination of fill factors.
NB you can run a nightly job to re-organise your database index using the
Database Maintenance plans, that will get rid of your split pages, but it may
take a bit of time to do, see
http://msdn.microsoft.com/library/de...ntwiz_4iur.asp
"Marco Pais" wrote:
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.
|||Thanks guys...
I will check these articles out...
index Option - Fill Factor
Hi,
Lets suppose a table with 300.000+ rows. This table is heavily Updated,
SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
most of cases, a row is between 7 and 10 times updated, in different columns.
I have created 5 indexes in this table, corresponding to columns that I
frequently use in SELECTs or UPDATEs actions.
BOL tells me that a 100(0)% Fill Factor should not be used on heavily
updated table.
In this particular case, what Fill Factor percentage can be used in each
index?
Thanks in advance...
Marco.If your index is doing page splits you might benefit from using a lower
fillfactor... However this is true on the main table ONLY when new rows are
NOT added at the end of the table ( as would be the case where the clustered
index is the PK, or a strictly increaseing Date field.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
> columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Marco
if you have an index page with 1,4,5, keys and try to insert a row=2 so
page splits may occured because there is no room for a new one and 4,5 key
need to be moved on another page. These keys are out of the logical order
and it causes to External fragemtation your database. But if your keys are
added as 6,7,8 there will no pages splits and a fill factor 90% is gonna be
ok. The same way take a look at Updating and Deletion.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Marco
Also read this one
http://www.sql-server-performance.com/rebuilding_indexes.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Firstly have a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_9ak5.asp
This gives a bit of background on what a fill factor does.
So a fill factor of 100% means that the total size of the data in the
datafile will be as small as possible, but your running the risk of Page
Splitting, which can affect performance.
A lower value say 0% means that there is less change of page splitting, but
your datafiles will be a bit bigger.
So with your example you need to ask yourself 'What is more important to me'
size or performance. BTW its not to say that a 0% will not cause page
splitting, it does, but that there is probably going to be less chance of it
happening.
Anyway the answer to your question is going to be a bit glib. Basically
there is no real way of finding for yourself what is going to be the best
alternative except to experement with the fill factors until you find out
yourself what is the best combination of fill factors.
NB you can run a nightly job to re-organise your database index using the
Database Maintenance plans, that will get rid of your split pages, but it may
take a bit of time to do, see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dbmntwiz/dbmntwiz_4iur.asp
"Marco Pais" wrote:
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Thanks guys...
I will check these articles out...
Lets suppose a table with 300.000+ rows. This table is heavily Updated,
SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
most of cases, a row is between 7 and 10 times updated, in different columns.
I have created 5 indexes in this table, corresponding to columns that I
frequently use in SELECTs or UPDATEs actions.
BOL tells me that a 100(0)% Fill Factor should not be used on heavily
updated table.
In this particular case, what Fill Factor percentage can be used in each
index?
Thanks in advance...
Marco.If your index is doing page splits you might benefit from using a lower
fillfactor... However this is true on the main table ONLY when new rows are
NOT added at the end of the table ( as would be the case where the clustered
index is the PK, or a strictly increaseing Date field.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
> columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Marco
if you have an index page with 1,4,5, keys and try to insert a row=2 so
page splits may occured because there is no room for a new one and 4,5 key
need to be moved on another page. These keys are out of the logical order
and it causes to External fragemtation your database. But if your keys are
added as 6,7,8 there will no pages splits and a fill factor 90% is gonna be
ok. The same way take a look at Updating and Deletion.
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Marco
Also read this one
http://www.sql-server-performance.com/rebuilding_indexes.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Firstly have a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_9ak5.asp
This gives a bit of background on what a fill factor does.
So a fill factor of 100% means that the total size of the data in the
datafile will be as small as possible, but your running the risk of Page
Splitting, which can affect performance.
A lower value say 0% means that there is less change of page splitting, but
your datafiles will be a bit bigger.
So with your example you need to ask yourself 'What is more important to me'
size or performance. BTW its not to say that a 0% will not cause page
splitting, it does, but that there is probably going to be less chance of it
happening.
Anyway the answer to your question is going to be a bit glib. Basically
there is no real way of finding for yourself what is going to be the best
alternative except to experement with the fill factors until you find out
yourself what is the best combination of fill factors.
NB you can run a nightly job to re-organise your database index using the
Database Maintenance plans, that will get rid of your split pages, but it may
take a bit of time to do, see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dbmntwiz/dbmntwiz_4iur.asp
"Marco Pais" wrote:
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Thanks guys...
I will check these articles out...
Sunday, February 19, 2012
Index a view or a table
I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)
The table is also involved in frequent non-simple select statements. It currently has about a million rows.
Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.
Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.
Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?
ThanksOriginally posted by mattkrevs
I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)
The table is also involved in frequent non-simple select statements. It currently has about a million rows.
Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.
Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.
Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?
Thanks
If you use the six in your join and where clauses but you still need all 15, then you should go with indexes on the table. Otherwise, look at an indexed view.|||http://www.sqlteam.com/item.asp?ItemID=1015 for more information.|||Originally posted by Satya
http://www.sqlteam.com/item.asp?ItemID=1015 for more information.
nice article.
thanks|||I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset.
Plus, an indexed view will slow down your inserts.
Just add indexes to your table. Composite indexes may help specific querys as well. Have you run a showplan against your queries to see where delays are occuring?|||Originally posted by blindman
I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset. you don't see, blindman, that's right (pun intended). do you even know the rules of creating indexed views? aggregation? you can't aggregate an indexed view with anything other than count_big(*)!!! do you even know what you are talking about before making such suggestions?
Originally posted by blindman
Plus, an indexed view will slow down your inserts.
man, you just spit out guessing after guessing. how do you figure? so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?
and at the same time you're giving suggestions to "just add indexes to the table"?
i love this forum, unsupported ambitions are all over the place :rolleyes:|||holy flame war, batman
look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did
please, keep your personal bitterness out of your posts
refute the facts but please keep the vitriol to yourself
when you insult someone out of the blue like that, it makes you look like an idiot|||I thought that was "an ASS out of "U"and "ME"" ?
:D :D :D :D :D|||Originally posted by r937
holy flame war, batman
look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did
please, keep your personal bitterness out of your posts
refute the facts but please keep the vitriol to yourself
when you insult someone out of the blue like that, it makes you look like an idiot before jumping on me you should read his posts|||Originally posted by ms_sql_dba
before jumping on me you should read his posts I can't speak for anyone else, but I already did.
I'm with r937, dispute the facts if you will, but I'm not interested in what appears to be a vicious response to a civil posting. I don't care whether the original posting was technically accurate or not, that isn't relevant to this observation. I don't think that your response was appropriate.
-PatP|||Originally posted by ms_sql_dba
before jumping on me you should read his posts
i did, there's only one post of his in this thread, and it was friendly
if you are talking about other threads, they do not matter to this one
this thread will be found independently by people unaware of your own personal vendetta
please, be more civil|||ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:
using indexed views is more efficient than creating indexes on the underlying tables.
does this look better to you all? ;)|||Originally posted by ms_sql_dba
ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:
using indexed views is more efficient than creating indexes on the underlying tables.
does this look better to you all? ;) Way more gooder, yet even!
Now, all that one of you two needs to do is to come up with something (an URL, a reference to readily available printed material, etc) to support your opinions, then we'll all have something to discuss! ;)
Pretty quick I need to prepare to pig out! You guys go on and debate stuff without me, I'll join in later if I can waddle to the tube!
-PatP|||Let' start at the top...
"you can't aggregate an indexed view with anything other than count_big(*)!!!"
Wrong. You can also aggregate with SUM, and while you can't directly use the AVG, STDEV, STDEVP, VAR, or VARP functions, you can reproduce them using combinations of SUM and COUNT_BIG. Look it up yourself; its easy to find in Books Online.
"do you even know what you are talking about before making such suggestions?"
Yes.
"so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?"
Yes, I am. Indexed views are stored in the database, and it stands to reason that since they reflect any updates on their underlying tables then the process of updating the indexed view will require processor time. From Books Online:
"You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries."
"and at the same time you're giving suggestions to "just add indexes to the table"?"
Yes. I have found, after long years of experience, experiment and investigation, that indexes tend to speed up query processing. What makes no sense is to create an indexed view of a single base table, thus creating a copy of it, and then index the copy.
"using indexed views is more efficient than creating indexes on the underlying tables."
Yes, it does look better. But it is still wrong if you are only dealing with a single table without aggregation, as a stated in my post. I hope you don't create indexed views on all of your base tables instead of simply indexing the base tables themselves.
'Nuff said.|||100 human (hu-man)
200 noun.
300 A carbon based device that allows the user to quickly and
400 efficiently repeat the same mistake 50,000 times.
500 goto 100|||Looks like spaghetti code to me!|||Originally posted by blindman
Let' start at the top...
"you can't aggregate an indexed view with anything other than count_big(*)!!!"
Wrong. You can also aggregate with SUM, and while you can't directly use the AVG, STDEV, STDEVP, VAR, or VARP functions, you can reproduce them using combinations of SUM and COUNT_BIG. Look it up yourself; its easy to find in Books Online.
"do you even know what you are talking about before making such suggestions?"
Yes.
"so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?"
Yes, I am. Indexed views are stored in the database, and it stands to reason that since they reflect any updates on their underlying tables then the process of updating the indexed view will require processor time. From Books Online:
"You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries."
"and at the same time you're giving suggestions to "just add indexes to the table"?"
Yes. I have found, after long years of experience, experiment and investigation, that indexes tend to speed up query processing. What makes no sense is to create an indexed view of a single base table, thus creating a copy of it, and then index the copy.
"using indexed views is more efficient than creating indexes on the underlying tables."
Yes, it does look better. But it is still wrong if you are only dealing with a single table without aggregation, as a stated in my post. I hope you don't create indexed views on all of your base tables instead of simply indexing the base tables themselves.
'Nuff said. indexed views have just been introduced. what years?|||can you guys smell smoke? faintly reminicsent of when i used to play with matches as a kid.
you know that feeling? when your doing something that you know you shouldnt. like fighting|||"indexed views have just been introduced. what years?"
My post was:
"I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing."
Please read my posts more carefully before criticising them.|||i wasn't. and your answer is very christomatic. of course indexes speed up queries (if properly built) do you read my posts carefully?|||Originally posted by Ruprect
can you guys smell smoke? faintly reminicsent of when i used to play with matches as a kid.
you know that feeling? when your doing something that you know you shouldnt. like fighting At least from what I've seen in this thread, blindman has been both civil and technically correct. I'm willing to wait for ms_sql_dba to take a shot at validating their claims, but so far I can't fault blindman.
Maybe I'm missing something (I've been prone to do that lately), but I see this as a pretty one sided screaming match, with blindman presenting the civilized side. Please let me know if you disagree.
-PatP|||But I do like the word "christomatic". It's not on dictionary.com, but it should be! Very appropriate for the holiday!|||Originally posted by Pat Phelan
At least from what I've seen in this thread, blindman has been both civil and technically correct. I'm willing to wait for ms_sql_dba to take a shot at validating their claims, but so far I can't fault blindman.
Maybe I'm missing something (I've been prone to do that lately), but I see this as a pretty one sided screaming match, with blindman presenting the civilized side. Please let me know if you disagree.
-PatP ok, mr. judge, so you choose to ignore the technical stuff coming from me, and acknowledge only blindman's answers like "I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing." wow, it only takes that long to figure this one out :D
but this is why indexes exist, to improve performance of queries. please read carefully what the topic is all about, - indexed views vs. indexes on underlying tables (at least that's what it bottled down to)
...and thanks for stopping by. your arrival means it's time to go back to real life :p|||Originally posted by blindman
Looks like spaghetti code to me!
I'm sorry. Is this better? :)
CREATE TABLE thread(
thread_id VARCHAR(55),
thread_certain_participant_description VARCHAR(55),
thread_certain_participant_part_of_speech VARCHAR(55),
thread_certain_participant_definition VARCHAR(255))
GO
INSERT thread(
thread_id,
thread_certain_participant_description,
thread_certain_participant_part_of_speech,
thread_certain_participant_definition)
SELECT
'index a view or a table',
'human (hu-man)',
'noun',
'A carbon based device that allows the user to quickly and efficiently repeat the same mistake 50,000 times.'
GO
DECLARE @.int_counter INT
SELECT @.int_counter = 1
WHILE @.int_counter <= 50000
BEGIN
SELECT
thread_id,
thread_certain_participant_description,
thread_certain_participant_part_of_speech,
thread_certain_participant_definition
FROM
thread
WHERE
thread_id = 'index a view or a table'
SELECT @.int_counter = @.int_counter + 1
END
GO|||Originally posted by ms_sql_dba
ok, mr. judge, so you choose to ignore the technical stuff coming from me, and acknowledge only blindman's answers like "I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing." wow, it only takes that long to figure this one out :D
but this is why indexes exist, to improve performance of queries. please read carefully what the topic is all about, - indexed views vs. indexes on underlying tables (at least that's what it bottled down to)
...and thanks for stopping by. your arrival means it's time to go back to real life :p I contribute here because I like to help others when I've got a minute or two to spare. I try to behave civilly and professionally.
While you've offered some good technical content in other threads, I haven't seen that you've offered anything I value in this thread. I'm not sure what's wrong, whether it's my percepcion or your behavior.
Blindman has done a good job of defending his position. Your behavior struck me as poor, and your technical support of your position was minimal at best. Do you have anything technically useful to contribute?
-PatP|||when someone refuses to be civil, just add them to your ignore list (http://www.dbforums.com/misc.php?action=faq&page=1#buddy)
"Ignore lists are used for those people whose messages you wish not to read. By adding someone to your ignore list, those messages posted by these individuals will be hidden when you read a thread."
sounds tailor-made for certain people, don't it|||Except that then you may end up posting something somebody already has posted. I don't have anybody on my ignore list because it would be like having a conversation with four other people and only be able to hear one of them.
Do you have anybody on your ignore list, Rudy?
Rudy?
Hello? Can you hear me?
.
.
.
Hmmm.... :(|||don't it?
gotta be the Queens English...:D
Actually I find it all amusing...
I know that the net provides a huge amount of anonymity...
Makes you wonder how people actually behave in the real personal lives...
And yes it's all about the technology (if you can call it that)
MOO, of course...|||Originally posted by blindman
Except that then you may end up posting something somebody already has posted.
yeah, but if you do, your post will be seen by everybody else as coming from a reputable source, whereas the post from the ignored person, well, you wouldn't have put her on the ignore list for no good reason...|||Originally posted by blindman
Except that then you may end up posting something somebody already has posted.
Happens anyway, doesn't it?
Oh...[sniped]|||Is it just me .. or tempers on dbForums seem to be on a short fuse ?|||Like I said...I find it amusing...
Like it really matters...
lot of hard cash on the line.....|||Thats why I usually never get into Flame war ... (http://www.dbforums.com/showthread.php?threadid=972040)|||Ah yes, good old Sundial and his "Bob Cratchet" theory of employee loyalty. What the Dickens was he thinking?|||Hey...any job in this market is a good job...
And I can't believe that thread is 6 months old already...
Damn Mondays...|||Originally posted by Brett Kaiser
Damn Mondays... I refuse to let the "Monday-ness" of a day interfere with my enjoyment of the day! Somebody (John Ruskin?) once said that: "Monday is one H#)) of a way to spend one seventh of your life." I think that he "missed the boat" on this one, and that Monday really IS one heck of a way to spend one seventh of your life!
-PatP|||Originally posted by Pat Phelan
I refuse to let the "Monday-ness" of a day interfere with my enjoyment of the day! Somebody (John Ruskin?) once said that: "Monday is one H#)) of a way to spend one seventh of your life." I think that he "missed the boat" on this one, and that Monday really IS one heck of a way to spend one seventh of your life!
-PatP
Well I guess it's better than have 1/7 less....|||or 2/7 less
which is me, usually friday afternoon to sunday afternoon is a huge fog|||That's cause the leafs split at home...
Off to Ottowa...
Damn devils....|||on the contrary, i don't even know who's playing, i am so not a hockey fan|||picking myself off the floor
I though it was part of being allowed to live in Canada
Are you a transplant?
Has it stopped snowing yet?|||transplant? yes
the kid in the lederhosen is me in germany
however, i have lived in and around toronto for over 50 years
i useta, as we say, watch hockey when it was hockey, back when there were 6 teams...
last snow here was a month or so ago
today it's 9C and sunny|||You should post a more recent photo of you in Lederhosen.|||the only pics i'm willing to share are on my personal site ;)
der lederhosen guy is cute, isn't he?
i couldn't think of changing it, it's like a brand
did you buy the new coke when it came out?
neither did i
The table is also involved in frequent non-simple select statements. It currently has about a million rows.
Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.
Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.
Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?
ThanksOriginally posted by mattkrevs
I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)
The table is also involved in frequent non-simple select statements. It currently has about a million rows.
Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.
Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.
Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?
Thanks
If you use the six in your join and where clauses but you still need all 15, then you should go with indexes on the table. Otherwise, look at an indexed view.|||http://www.sqlteam.com/item.asp?ItemID=1015 for more information.|||Originally posted by Satya
http://www.sqlteam.com/item.asp?ItemID=1015 for more information.
nice article.
thanks|||I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset.
Plus, an indexed view will slow down your inserts.
Just add indexes to your table. Composite indexes may help specific querys as well. Have you run a showplan against your queries to see where delays are occuring?|||Originally posted by blindman
I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset. you don't see, blindman, that's right (pun intended). do you even know the rules of creating indexed views? aggregation? you can't aggregate an indexed view with anything other than count_big(*)!!! do you even know what you are talking about before making such suggestions?
Originally posted by blindman
Plus, an indexed view will slow down your inserts.
man, you just spit out guessing after guessing. how do you figure? so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?
and at the same time you're giving suggestions to "just add indexes to the table"?
i love this forum, unsupported ambitions are all over the place :rolleyes:|||holy flame war, batman
look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did
please, keep your personal bitterness out of your posts
refute the facts but please keep the vitriol to yourself
when you insult someone out of the blue like that, it makes you look like an idiot|||I thought that was "an ASS out of "U"and "ME"" ?
:D :D :D :D :D|||Originally posted by r937
holy flame war, batman
look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did
please, keep your personal bitterness out of your posts
refute the facts but please keep the vitriol to yourself
when you insult someone out of the blue like that, it makes you look like an idiot before jumping on me you should read his posts|||Originally posted by ms_sql_dba
before jumping on me you should read his posts I can't speak for anyone else, but I already did.
I'm with r937, dispute the facts if you will, but I'm not interested in what appears to be a vicious response to a civil posting. I don't care whether the original posting was technically accurate or not, that isn't relevant to this observation. I don't think that your response was appropriate.
-PatP|||Originally posted by ms_sql_dba
before jumping on me you should read his posts
i did, there's only one post of his in this thread, and it was friendly
if you are talking about other threads, they do not matter to this one
this thread will be found independently by people unaware of your own personal vendetta
please, be more civil|||ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:
using indexed views is more efficient than creating indexes on the underlying tables.
does this look better to you all? ;)|||Originally posted by ms_sql_dba
ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:
using indexed views is more efficient than creating indexes on the underlying tables.
does this look better to you all? ;) Way more gooder, yet even!
Now, all that one of you two needs to do is to come up with something (an URL, a reference to readily available printed material, etc) to support your opinions, then we'll all have something to discuss! ;)
Pretty quick I need to prepare to pig out! You guys go on and debate stuff without me, I'll join in later if I can waddle to the tube!
-PatP|||Let' start at the top...
"you can't aggregate an indexed view with anything other than count_big(*)!!!"
Wrong. You can also aggregate with SUM, and while you can't directly use the AVG, STDEV, STDEVP, VAR, or VARP functions, you can reproduce them using combinations of SUM and COUNT_BIG. Look it up yourself; its easy to find in Books Online.
"do you even know what you are talking about before making such suggestions?"
Yes.
"so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?"
Yes, I am. Indexed views are stored in the database, and it stands to reason that since they reflect any updates on their underlying tables then the process of updating the indexed view will require processor time. From Books Online:
"You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries."
"and at the same time you're giving suggestions to "just add indexes to the table"?"
Yes. I have found, after long years of experience, experiment and investigation, that indexes tend to speed up query processing. What makes no sense is to create an indexed view of a single base table, thus creating a copy of it, and then index the copy.
"using indexed views is more efficient than creating indexes on the underlying tables."
Yes, it does look better. But it is still wrong if you are only dealing with a single table without aggregation, as a stated in my post. I hope you don't create indexed views on all of your base tables instead of simply indexing the base tables themselves.
'Nuff said.|||100 human (hu-man)
200 noun.
300 A carbon based device that allows the user to quickly and
400 efficiently repeat the same mistake 50,000 times.
500 goto 100|||Looks like spaghetti code to me!|||Originally posted by blindman
Let' start at the top...
"you can't aggregate an indexed view with anything other than count_big(*)!!!"
Wrong. You can also aggregate with SUM, and while you can't directly use the AVG, STDEV, STDEVP, VAR, or VARP functions, you can reproduce them using combinations of SUM and COUNT_BIG. Look it up yourself; its easy to find in Books Online.
"do you even know what you are talking about before making such suggestions?"
Yes.
"so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?"
Yes, I am. Indexed views are stored in the database, and it stands to reason that since they reflect any updates on their underlying tables then the process of updating the indexed view will require processor time. From Books Online:
"You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries."
"and at the same time you're giving suggestions to "just add indexes to the table"?"
Yes. I have found, after long years of experience, experiment and investigation, that indexes tend to speed up query processing. What makes no sense is to create an indexed view of a single base table, thus creating a copy of it, and then index the copy.
"using indexed views is more efficient than creating indexes on the underlying tables."
Yes, it does look better. But it is still wrong if you are only dealing with a single table without aggregation, as a stated in my post. I hope you don't create indexed views on all of your base tables instead of simply indexing the base tables themselves.
'Nuff said. indexed views have just been introduced. what years?|||can you guys smell smoke? faintly reminicsent of when i used to play with matches as a kid.
you know that feeling? when your doing something that you know you shouldnt. like fighting|||"indexed views have just been introduced. what years?"
My post was:
"I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing."
Please read my posts more carefully before criticising them.|||i wasn't. and your answer is very christomatic. of course indexes speed up queries (if properly built) do you read my posts carefully?|||Originally posted by Ruprect
can you guys smell smoke? faintly reminicsent of when i used to play with matches as a kid.
you know that feeling? when your doing something that you know you shouldnt. like fighting At least from what I've seen in this thread, blindman has been both civil and technically correct. I'm willing to wait for ms_sql_dba to take a shot at validating their claims, but so far I can't fault blindman.
Maybe I'm missing something (I've been prone to do that lately), but I see this as a pretty one sided screaming match, with blindman presenting the civilized side. Please let me know if you disagree.
-PatP|||But I do like the word "christomatic". It's not on dictionary.com, but it should be! Very appropriate for the holiday!|||Originally posted by Pat Phelan
At least from what I've seen in this thread, blindman has been both civil and technically correct. I'm willing to wait for ms_sql_dba to take a shot at validating their claims, but so far I can't fault blindman.
Maybe I'm missing something (I've been prone to do that lately), but I see this as a pretty one sided screaming match, with blindman presenting the civilized side. Please let me know if you disagree.
-PatP ok, mr. judge, so you choose to ignore the technical stuff coming from me, and acknowledge only blindman's answers like "I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing." wow, it only takes that long to figure this one out :D
but this is why indexes exist, to improve performance of queries. please read carefully what the topic is all about, - indexed views vs. indexes on underlying tables (at least that's what it bottled down to)
...and thanks for stopping by. your arrival means it's time to go back to real life :p|||Originally posted by blindman
Looks like spaghetti code to me!
I'm sorry. Is this better? :)
CREATE TABLE thread(
thread_id VARCHAR(55),
thread_certain_participant_description VARCHAR(55),
thread_certain_participant_part_of_speech VARCHAR(55),
thread_certain_participant_definition VARCHAR(255))
GO
INSERT thread(
thread_id,
thread_certain_participant_description,
thread_certain_participant_part_of_speech,
thread_certain_participant_definition)
SELECT
'index a view or a table',
'human (hu-man)',
'noun',
'A carbon based device that allows the user to quickly and efficiently repeat the same mistake 50,000 times.'
GO
DECLARE @.int_counter INT
SELECT @.int_counter = 1
WHILE @.int_counter <= 50000
BEGIN
SELECT
thread_id,
thread_certain_participant_description,
thread_certain_participant_part_of_speech,
thread_certain_participant_definition
FROM
thread
WHERE
thread_id = 'index a view or a table'
SELECT @.int_counter = @.int_counter + 1
END
GO|||Originally posted by ms_sql_dba
ok, mr. judge, so you choose to ignore the technical stuff coming from me, and acknowledge only blindman's answers like "I have found, after long years of experience, experiment and investigation, that INDEXES tend to speed up query processing." wow, it only takes that long to figure this one out :D
but this is why indexes exist, to improve performance of queries. please read carefully what the topic is all about, - indexed views vs. indexes on underlying tables (at least that's what it bottled down to)
...and thanks for stopping by. your arrival means it's time to go back to real life :p I contribute here because I like to help others when I've got a minute or two to spare. I try to behave civilly and professionally.
While you've offered some good technical content in other threads, I haven't seen that you've offered anything I value in this thread. I'm not sure what's wrong, whether it's my percepcion or your behavior.
Blindman has done a good job of defending his position. Your behavior struck me as poor, and your technical support of your position was minimal at best. Do you have anything technically useful to contribute?
-PatP|||when someone refuses to be civil, just add them to your ignore list (http://www.dbforums.com/misc.php?action=faq&page=1#buddy)
"Ignore lists are used for those people whose messages you wish not to read. By adding someone to your ignore list, those messages posted by these individuals will be hidden when you read a thread."
sounds tailor-made for certain people, don't it|||Except that then you may end up posting something somebody already has posted. I don't have anybody on my ignore list because it would be like having a conversation with four other people and only be able to hear one of them.
Do you have anybody on your ignore list, Rudy?
Rudy?
Hello? Can you hear me?
.
.
.
Hmmm.... :(|||don't it?
gotta be the Queens English...:D
Actually I find it all amusing...
I know that the net provides a huge amount of anonymity...
Makes you wonder how people actually behave in the real personal lives...
And yes it's all about the technology (if you can call it that)
MOO, of course...|||Originally posted by blindman
Except that then you may end up posting something somebody already has posted.
yeah, but if you do, your post will be seen by everybody else as coming from a reputable source, whereas the post from the ignored person, well, you wouldn't have put her on the ignore list for no good reason...|||Originally posted by blindman
Except that then you may end up posting something somebody already has posted.
Happens anyway, doesn't it?
Oh...[sniped]|||Is it just me .. or tempers on dbForums seem to be on a short fuse ?|||Like I said...I find it amusing...
Like it really matters...
lot of hard cash on the line.....|||Thats why I usually never get into Flame war ... (http://www.dbforums.com/showthread.php?threadid=972040)|||Ah yes, good old Sundial and his "Bob Cratchet" theory of employee loyalty. What the Dickens was he thinking?|||Hey...any job in this market is a good job...
And I can't believe that thread is 6 months old already...
Damn Mondays...|||Originally posted by Brett Kaiser
Damn Mondays... I refuse to let the "Monday-ness" of a day interfere with my enjoyment of the day! Somebody (John Ruskin?) once said that: "Monday is one H#)) of a way to spend one seventh of your life." I think that he "missed the boat" on this one, and that Monday really IS one heck of a way to spend one seventh of your life!
-PatP|||Originally posted by Pat Phelan
I refuse to let the "Monday-ness" of a day interfere with my enjoyment of the day! Somebody (John Ruskin?) once said that: "Monday is one H#)) of a way to spend one seventh of your life." I think that he "missed the boat" on this one, and that Monday really IS one heck of a way to spend one seventh of your life!
-PatP
Well I guess it's better than have 1/7 less....|||or 2/7 less
which is me, usually friday afternoon to sunday afternoon is a huge fog|||That's cause the leafs split at home...
Off to Ottowa...
Damn devils....|||on the contrary, i don't even know who's playing, i am so not a hockey fan|||picking myself off the floor
I though it was part of being allowed to live in Canada
Are you a transplant?
Has it stopped snowing yet?|||transplant? yes
the kid in the lederhosen is me in germany
however, i have lived in and around toronto for over 50 years
i useta, as we say, watch hockey when it was hockey, back when there were 6 teams...
last snow here was a month or so ago
today it's 9C and sunny|||You should post a more recent photo of you in Lederhosen.|||the only pics i'm willing to share are on my personal site ;)
der lederhosen guy is cute, isn't he?
i couldn't think of changing it, it's like a brand
did you buy the new coke when it came out?
neither did i
Subscribe to:
Posts (Atom)