Infinite Loop while cube processing in SSAS 2005 SP2

Hi,

We build the cube, quite large one processing 4 million records of data daily. It was working fine in SSAS 2005 Service Pack 1. But recently Service Pack 2 for SSAS came out, in the releases notes it said SP2 solves some of the issues we had on the drillthrough. So, after applying SP2 on the production server, running on 32 bit Windows 2003 SP2, the ETL process is stuck. It stuck while processing the cube, on large dimension.

I was wondering whether microsoft released hotfix for this issue or anyone else had this problem.

I note again, that whole thing was working fine under SSAS 2005 SP1 environment, but i believe in SP2 there is some flaw that leads to infinite loop while processing the cube.

Regards,

Bakhodir Makhamadov

Hello,

Short after releasing sp2 they came out again with an update of SP2 !?

Recreate all your partitions and redesign de storage designs, maybe this will help too !?sql

Infinite Loop while cube processing in SSAS 2005 SP2

Hi,

We build the cube, quite large one processing 4 million records of data daily. It was working fine in SSAS 2005 Service Pack 1. But recently Service Pack 2 for SSAS came out, in the releases notes it said SP2 solves some of the issues we had on the drillthrough. So, after applying SP2 on the production server, running on 32 bit Windows 2003 SP2, the ETL process is stuck. It stuck while processing the cube, on large dimension.

I was wondering whether microsoft released hotfix for this issue or anyone else had this problem.

I note again, that whole thing was working fine under SSAS 2005 SP1 environment, but i believe in SP2 there is some flaw that leads to infinite loop while processing the cube.

Regards,

Bakhodir Makhamadov

Hello,

Short after releasing sp2 they came out again with an update of SP2 !? Recreate all your partitions and redesign de storage designs, maybe this will help too !?

indexing suggestions

I'm looking for some help on how i should index this table.

current table has about 500k records in it.
the fields in the table are:
member_num (varchar(12), not null)
first_name (varchar(20), null)
last_name (varchar(20), null)
ssn (varchar(50), null)
address1 (nvarchar(200), null)
address2 (nvarchar(200), null)
city (nvarchar(200), null)
state (nvarchar(200), null)
zip (nvarchar(100), null)
phone1 (nvarchar(50), null)

all of the fields are searchable through an asp.net webform.

my first stab at this consisted of creating a clustered index on member_num and then creating a separate index for each of the remaining fields.I generally take a look at how the actual usage pans out. Do you expect the same amount of queries on address2 as last_name? Then there is the question of how often is last_name queried with or without first_name? Once you know how the searches usually shape up, then you can index smarter.|||If I know that first_name and last_name will always be used should I group them both into one index?|||Yes. I would bet that Last_Name will be more commonly used on its own, than first_name alone, so make Last_Name first in the index.

Almost suffered a zen moment in there. Some thing like last shall be first...Must need more coffee.|||I guess the better question would be : "How do I redesign my table"

State 200, zip 100, ssn 50?

Indexing Question

I have a SQL 2005 database with 2 main tables both of which are very big and
a bunch of smaller tables.
These tables are getting 100s of records inserts a minute, they have a
forigen key relationship with one another and are also referenced with
forigen keys in other tables.
In order to do the inserts and in order to run reports I need to create
indexes on the tables.
Is it better to create a number of multi column indecis that have some
parallelism or I should just create a single colum index for every column
that I was considering to include in more than one multi colum index?
Thanks
SagiHi, Sagi,
I understand that you would like to create indexes on the tables which are
getting hundreds of records inserts per minute; but you would like to know
which way is better, creating a number of multi column indexes or creating
a single column index for every column.
If I have misunderstood, please let me know.
I do not recommend that you directly create indexes on your operating table
since it has a heavy inserts on it.
Once an index is created, the insert operation will spend more time to
finish. I recommend that you timely synchronize your database to another
SQL Server which is used for report. You can create indexes on the database
for report. For creating single column index or multi-column index, this
depends on your requirements. A multi-column index cannot be utilized if
you query your database on only one of the columns; but if your query is
based on the multiple columns which are in the multi-column index, you can
get better performance. Generally I recommend that you create single column
index for critical columns (such as datetime, name columns etc) and create
multiple column index for the non-critical columns.
Also, for getting better performance, you may also consider to create
partition views or partition tables for the database.
For more information about index in SQL Server, please refer to:
CREATE INDEX (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||This is a bad response which might mislead the person who posted the
question.
Multi-column indexes CAN be used by queries that only access a single
column, as long as that column is the left-most column in the index.
Creating many single column indexes can actually degrade query performance
by an order of magnitude as the query processor might need to seek between
multiple indexes to resolve some queries. It is usually far better to design
indexes that cover the requirements of performance sensitive queries by
including at least the columns that provide filtering (those used in the
WHERE + JOIN clauses) & possibly including columns only named in the SELECT
list if many rows are being returned.
Regards,
Greg Linwood
SQL Server MVP
http://www.SQLBenchmarkPro.com
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ODFE3IVhHHA.5272@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Sagi,
> I understand that you would like to create indexes on the tables which are
> getting hundreds of records inserts per minute; but you would like to know
> which way is better, creating a number of multi column indexes or creating
> a single column index for every column.
> If I have misunderstood, please let me know.
> I do not recommend that you directly create indexes on your operating
> table
> since it has a heavy inserts on it.
> Once an index is created, the insert operation will spend more time to
> finish. I recommend that you timely synchronize your database to another
> SQL Server which is used for report. You can create indexes on the
> database
> for report. For creating single column index or multi-column index, this
> depends on your requirements. A multi-column index cannot be utilized if
> you query your database on only one of the columns; but if your query is
> based on the multiple columns which are in the multi-column index, you can
> get better performance. Generally I recommend that you create single
> column
> index for critical columns (such as datetime, name columns etc) and create
> multiple column index for the non-critical columns.
> Also, for getting better performance, you may also consider to create
> partition views or partition tables for the database.
> For more information about index in SQL Server, please refer to:
> CREATE INDEX (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188783.aspx
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ODFE3IVhHHA.5272@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Sagi,
> I understand that you would like to create indexes on the tables which are
> getting hundreds of records inserts per minute; but you would like to know
> which way is better, creating a number of multi column indexes or creating
> a single column index for every column.
> If I have misunderstood, please let me know.
> I do not recommend that you directly create indexes on your operating
> table
> since it has a heavy inserts on it.
> Once an index is created, the insert operation will spend more time to
> finish. I recommend that you timely synchronize your database to another
> SQL Server which is used for report.
This is probably the best idea.

> You can create indexes on the database
> for report. For creating single column index or multi-column index, this
> depends on your requirements. A multi-column index cannot be utilized if
> you query your database on only one of the columns;
Umm, I have to disagree with this Charles. A multi-column index can
certainly be used if the first column is part of the query. And in certain
cases additional fields can be used if they appear in the query.

> but if your query is
> based on the multiple columns which are in the multi-column index, you can
> get better performance. Generally I recommend that you create single
> column
> index for critical columns (such as datetime, name columns etc) and create
> multiple column index for the non-critical columns.
I'm not entirely sure I'd agree with this. I think it really depends on
what the queries are.
I owuld agree however, that he probably wants to keep the total number of
indices to a bare minimum.

> Also, for getting better performance, you may also consider to create
> partition views or partition tables for the database.
Another option that I've used is to put the indexes in their own file group.
(Obviously don't put the clustered index in the same one, won't gain you
anything. :-)

> For more information about index in SQL Server, please refer to:
> CREATE INDEX (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188783.aspx
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, Greg,
Thanks for your pointing out. I am awfully sorry for that.
It seemed that I misuderstood the multi-column index before. Yes, you are
right. The multi-column index was useful for the left-most column.
Actually, there is a phase of description in SQL Server BOL:
========================================
==
Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (> ), less than (< ), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should
be ordered based on their level of distinctness, that is, from the most
distinct to the least distinct.
For example, if the index is defined as LastName, FirstName the index will
be useful when the search criterion is WHERE LastName = 'Smith' or WHERE
LastName = Smith AND FirstName LIKE 'J%'. However, the query optimizer
would not use the index for a query that searched only on FirstName (WHERE
FirstName = 'Jane').
========================================
====
(ref: General Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms191195.aspx )
I also do not recommend creating many single column indexes. I mean that it
is better to create single indexes for those critical columns which are
mostly common used as conditions for queries. Actually sometimes even if
you create an index on some column (which has many duplicated values), the
index may not be used when you perform a query on that column. SQL Server
optimizer will make a decision to choose a less cost solution between a
table full scan and the index query.
Thanks again!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thank you Greg,
It is my fault that I misunderstood it before.
Please refer to my reply to Greg Linwood.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Indexing Question

I have a SQL 2005 database with 2 main tables both of which are very big and
a bunch of smaller tables.
These tables are getting 100s of records inserts a minute, they have a
forigen key relationship with one another and are also referenced with
forigen keys in other tables.
In order to do the inserts and in order to run reports I need to create
indexes on the tables.
Is it better to create a number of multi column indecis that have some
parallelism or I should just create a single colum index for every column
that I was considering to include in more than one multi colum index?
Thanks
Sagi
Hi, Sagi,
I understand that you would like to create indexes on the tables which are
getting hundreds of records inserts per minute; but you would like to know
which way is better, creating a number of multi column indexes or creating
a single column index for every column.
If I have misunderstood, please let me know.
I do not recommend that you directly create indexes on your operating table
since it has a heavy inserts on it.
Once an index is created, the insert operation will spend more time to
finish. I recommend that you timely synchronize your database to another
SQL Server which is used for report. You can create indexes on the database
for report. For creating single column index or multi-column index, this
depends on your requirements. A multi-column index cannot be utilized if
you query your database on only one of the columns; but if your query is
based on the multiple columns which are in the multi-column index, you can
get better performance. Generally I recommend that you create single column
index for critical columns (such as datetime, name columns etc) and create
multiple column index for the non-critical columns.
Also, for getting better performance, you may also consider to create
partition views or partition tables for the database.
For more information about index in SQL Server, please refer to:
CREATE INDEX (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ODFE3IVhHHA.5272@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Sagi,
> I understand that you would like to create indexes on the tables which are
> getting hundreds of records inserts per minute; but you would like to know
> which way is better, creating a number of multi column indexes or creating
> a single column index for every column.
> If I have misunderstood, please let me know.
> I do not recommend that you directly create indexes on your operating
> table
> since it has a heavy inserts on it.
> Once an index is created, the insert operation will spend more time to
> finish. I recommend that you timely synchronize your database to another
> SQL Server which is used for report.
This is probably the best idea.

> You can create indexes on the database
> for report. For creating single column index or multi-column index, this
> depends on your requirements. A multi-column index cannot be utilized if
> you query your database on only one of the columns;
Umm, I have to disagree with this Charles. A multi-column index can
certainly be used if the first column is part of the query. And in certain
cases additional fields can be used if they appear in the query.

> but if your query is
> based on the multiple columns which are in the multi-column index, you can
> get better performance. Generally I recommend that you create single
> column
> index for critical columns (such as datetime, name columns etc) and create
> multiple column index for the non-critical columns.
I'm not entirely sure I'd agree with this. I think it really depends on
what the queries are.
I owuld agree however, that he probably wants to keep the total number of
indices to a bare minimum.

> Also, for getting better performance, you may also consider to create
> partition views or partition tables for the database.
Another option that I've used is to put the indexes in their own file group.
(Obviously don't put the clustered index in the same one, won't gain you
anything. :-)

> For more information about index in SQL Server, please refer to:
> CREATE INDEX (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188783.aspx
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi, Greg,
Thanks for your pointing out. I am awfully sorry for that.
It seemed that I misuderstood the multi-column index before. Yes, you are
right. The multi-column index was useful for the left-most column.
Actually, there is a phase of description in SQL Server BOL:
==========================================
Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should
be ordered based on their level of distinctness, that is, from the most
distinct to the least distinct.
For example, if the index is defined as LastName, FirstName the index will
be useful when the search criterion is WHERE LastName = 'Smith' or WHERE
LastName = Smith AND FirstName LIKE 'J%'. However, the query optimizer
would not use the index for a query that searched only on FirstName (WHERE
FirstName = 'Jane').
============================================
(ref: General Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms191195.aspx )
I also do not recommend creating many single column indexes. I mean that it
is better to create single indexes for those critical columns which are
mostly common used as conditions for queries. Actually sometimes even if
you create an index on some column (which has many duplicated values), the
index may not be used when you perform a query on that column. SQL Server
optimizer will make a decision to choose a less cost solution between a
table full scan and the index query.
Thanks again!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Thank you Greg,
It is my fault that I misunderstood it before.
Please refer to my reply to Greg Linwood.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
sql

Indexing Question

I have a SQL 2005 database with 2 main tables both of which are very big and
a bunch of smaller tables.
These tables are getting 100s of records inserts a minute, they have a
forigen key relationship with one another and are also referenced with
forigen keys in other tables.
In order to do the inserts and in order to run reports I need to create
indexes on the tables.
Is it better to create a number of multi column indecis that have some
parallelism or I should just create a single colum index for every column
that I was considering to include in more than one multi colum index?
Thanks
SagiHi, Sagi,
I understand that you would like to create indexes on the tables which are
getting hundreds of records inserts per minute; but you would like to know
which way is better, creating a number of multi column indexes or creating
a single column index for every column.
If I have misunderstood, please let me know.
I do not recommend that you directly create indexes on your operating table
since it has a heavy inserts on it.
Once an index is created, the insert operation will spend more time to
finish. I recommend that you timely synchronize your database to another
SQL Server which is used for report. You can create indexes on the database
for report. For creating single column index or multi-column index, this
depends on your requirements. A multi-column index cannot be utilized if
you query your database on only one of the columns; but if your query is
based on the multiple columns which are in the multi-column index, you can
get better performance. Generally I recommend that you create single column
index for critical columns (such as datetime, name columns etc) and create
multiple column index for the non-critical columns.
Also, for getting better performance, you may also consider to create
partition views or partition tables for the database.
For more information about index in SQL Server, please refer to:
CREATE INDEX (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||This is a bad response which might mislead the person who posted the
question.
Multi-column indexes CAN be used by queries that only access a single
column, as long as that column is the left-most column in the index.
Creating many single column indexes can actually degrade query performance
by an order of magnitude as the query processor might need to seek between
multiple indexes to resolve some queries. It is usually far better to design
indexes that cover the requirements of performance sensitive queries by
including at least the columns that provide filtering (those used in the
WHERE + JOIN clauses) & possibly including columns only named in the SELECT
list if many rows are being returned.
Regards,
Greg Linwood
SQL Server MVP
http://www.SQLBenchmarkPro.com
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ODFE3IVhHHA.5272@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Sagi,
> I understand that you would like to create indexes on the tables which are
> getting hundreds of records inserts per minute; but you would like to know
> which way is better, creating a number of multi column indexes or creating
> a single column index for every column.
> If I have misunderstood, please let me know.
> I do not recommend that you directly create indexes on your operating
> table
> since it has a heavy inserts on it.
> Once an index is created, the insert operation will spend more time to
> finish. I recommend that you timely synchronize your database to another
> SQL Server which is used for report. You can create indexes on the
> database
> for report. For creating single column index or multi-column index, this
> depends on your requirements. A multi-column index cannot be utilized if
> you query your database on only one of the columns; but if your query is
> based on the multiple columns which are in the multi-column index, you can
> get better performance. Generally I recommend that you create single
> column
> index for critical columns (such as datetime, name columns etc) and create
> multiple column index for the non-critical columns.
> Also, for getting better performance, you may also consider to create
> partition views or partition tables for the database.
> For more information about index in SQL Server, please refer to:
> CREATE INDEX (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188783.aspx
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:ODFE3IVhHHA.5272@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Sagi,
> I understand that you would like to create indexes on the tables which are
> getting hundreds of records inserts per minute; but you would like to know
> which way is better, creating a number of multi column indexes or creating
> a single column index for every column.
> If I have misunderstood, please let me know.
> I do not recommend that you directly create indexes on your operating
> table
> since it has a heavy inserts on it.
> Once an index is created, the insert operation will spend more time to
> finish. I recommend that you timely synchronize your database to another
> SQL Server which is used for report.
This is probably the best idea.
> You can create indexes on the database
> for report. For creating single column index or multi-column index, this
> depends on your requirements. A multi-column index cannot be utilized if
> you query your database on only one of the columns;
Umm, I have to disagree with this Charles. A multi-column index can
certainly be used if the first column is part of the query. And in certain
cases additional fields can be used if they appear in the query.
> but if your query is
> based on the multiple columns which are in the multi-column index, you can
> get better performance. Generally I recommend that you create single
> column
> index for critical columns (such as datetime, name columns etc) and create
> multiple column index for the non-critical columns.
I'm not entirely sure I'd agree with this. I think it really depends on
what the queries are.
I owuld agree however, that he probably wants to keep the total number of
indices to a bare minimum.
> Also, for getting better performance, you may also consider to create
> partition views or partition tables for the database.
Another option that I've used is to put the indexes in their own file group.
(Obviously don't put the clustered index in the same one, won't gain you
anything. :-)
> For more information about index in SQL Server, please refer to:
> CREATE INDEX (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188783.aspx
> If you have any other questions or concerns, please feel free to let me
> know. It is my pleasure to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, Greg,
Thanks for your pointing out. I am awfully sorry for that.
It seemed that I misuderstood the multi-column index before. Yes, you are
right. The multi-column index was useful for the left-most column.
Actually, there is a phase of description in SQL Server BOL:
==========================================Consider the order of the columns if the index will contain multiple
columns. The column that is used in the WHERE clause in an equal to (=),
greater than (>), less than (<), or BETWEEN search condition, or
participates in a join, should be placed first. Additional columns should
be ordered based on their level of distinctness, that is, from the most
distinct to the least distinct.
For example, if the index is defined as LastName, FirstName the index will
be useful when the search criterion is WHERE LastName = 'Smith' or WHERE
LastName = Smith AND FirstName LIKE 'J%'. However, the query optimizer
would not use the index for a query that searched only on FirstName (WHERE
FirstName = 'Jane').
============================================(ref: General Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms191195.aspx )
I also do not recommend creating many single column indexes. I mean that it
is better to create single indexes for those critical columns which are
mostly common used as conditions for queries. Actually sometimes even if
you create an index on some column (which has many duplicated values), the
index may not be used when you perform a query on that column. SQL Server
optimizer will make a decision to choose a less cost solution between a
table full scan and the index query.
Thanks again!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thank you Greg,
It is my fault that I misunderstood it before.
Please refer to my reply to Greg Linwood.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Indexing on calculated fields

I would like to index on a calculated field. I need to index records by
w and want to store the W Number. Below is a test schema that
should work but the index will not create.
drop table calcdate;
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,[basedate],0)
);
create index calcdate_idx ON calcdate(calcsun);
I get the error " 37000(1933)[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot create index because the key column 'calcsun' is
non-deterministic or imprecise."
The page
http://msdn.microsoft.com/library/d...>
_08_95v7.asp
says that DATEDIFF is deterministic so I cannot see why the above index
will not create.
All help much appreciated.
GJHello, GJ
I was somehow surprised of this behaviour, too. It turns out that
DATEDIFF is indeed deterministic, but of it's parameters was not: 0 as
a datetime is non-deterministic! The page you quoted says that CONVERT
is deterministic with a datetime only when the style parameter is
specified (and it's not 0, 9, 100 or 109). Therefore, this works (and I
think that you will get the same results):
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,basedate,convert(datetime,'1
9000101',112))
);
create index calcdate_idx ON calcdate(calcsun);
Razvan

Indexing Huge Table

Well i have a database on my production server which is containing a table
having 10 million records and daily 10 - 20 K records are being inserted int
o
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please helpAmit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
You don't need to reindex for new rows. They are automatically added to
indexes on the table when the row is added/updated/deleted.
I'm guessing this table is used in a DSS scenario and that's the reason
for all the indexing. Without knowing more about your environment and
how the table is used, it's hard to recommend options other than to say
you may want the clustered index on the table to be on a key(s) that
won't cause too much page splitting. But, then again, for DSS it may be
better to have the clustered index on a set of keys that benefits
queries.
If you need to reindex then you should do this off-hours. Rebuilding the
clustered index will cause a non-clustered rebuild as well. You can also
defrag tables/indexes using DBCC INDEXDEFRAG.
David Gugick
Imceda Software
www.imceda.com|||Amit,
Have a look into the below article. This is really great.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"Amit Patel" wrote:

> Well i have a database on my production server which is containing a table
> having 10 million records and daily 10 - 20 K records are being inserted i
nto
> the same. The Table is also containing 25 indexes on different columns so
> which is best time and way to reindex my table for newly inserted records
.
> Please help

Indexing Huge Table

Well i have a database on my production server which is containing a table
having 10 million records and daily 10 - 20 K records are being inserted into
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please helpAmit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
You don't need to reindex for new rows. They are automatically added to
indexes on the table when the row is added/updated/deleted.
I'm guessing this table is used in a DSS scenario and that's the reason
for all the indexing. Without knowing more about your environment and
how the table is used, it's hard to recommend options other than to say
you may want the clustered index on the table to be on a key(s) that
won't cause too much page splitting. But, then again, for DSS it may be
better to have the clustered index on a set of keys that benefits
queries.
If you need to reindex then you should do this off-hours. Rebuilding the
clustered index will cause a non-clustered rebuild as well. You can also
defrag tables/indexes using DBCC INDEXDEFRAG.
David Gugick
Imceda Software
www.imceda.com|||Amit,
Have a look into the below article. This is really great.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"Amit Patel" wrote:
> Well i have a database on my production server which is containing a table
> having 10 million records and daily 10 - 20 K records are being inserted into
> the same. The Table is also containing 25 indexes on different columns so
> which is best time and way to reindex my table for newly inserted records.
> Please help

Indexing Huge Table

Well i have a database on my production server which is containing a table
having 10 million records and daily 10 - 20 K records are being inserted into
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please help
Amit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
You don't need to reindex for new rows. They are automatically added to
indexes on the table when the row is added/updated/deleted.
I'm guessing this table is used in a DSS scenario and that's the reason
for all the indexing. Without knowing more about your environment and
how the table is used, it's hard to recommend options other than to say
you may want the clustered index on the table to be on a key(s) that
won't cause too much page splitting. But, then again, for DSS it may be
better to have the clustered index on a set of keys that benefits
queries.
If you need to reindex then you should do this off-hours. Rebuilding the
clustered index will cause a non-clustered rebuild as well. You can also
defrag tables/indexes using DBCC INDEXDEFRAG.
David Gugick
Imceda Software
www.imceda.com
|||Amit,
Have a look into the below article. This is really great.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"Amit Patel" wrote:

> Well i have a database on my production server which is containing a table
> having 10 million records and daily 10 - 20 K records are being inserted into
> the same. The Table is also containing 25 indexes on different columns so
> which is best time and way to reindex my table for newly inserted records.
> Please help

Indexing Huge Table

Well i have a database on my production server which is containing a table
having 10 million records and daily 10 - 20 K records are being inserted int
o
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please helpAmit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
multi-post. see response in other ng.
David Gugick
Imceda Software
www.imceda.com

Indexing Floats

I was looking for a few opinions from some DBA's with experience in this.
I'm setting up a table with over a million records in it. Two of the columns
are latitude and longitude. I'm looking for fast, read-only lookup against
queries based on those columns.
I have been told not to index on floats because it's not efficient, but I
have existing tables that store latitude and longitude as floats and they
work well, although the number of records involved is significantly lower.
A typical lat/long coordinate uses 8 to 9 places, up to three before the
decimal, up to six after, and are signed.
If I need fast lookups, should I be using decimals instead?
Regards,
Fred Chateau
http://hotelmotelnow.comOne other thing...
I don't know if it makes any difference, but I'm not querying for one
record, but rather a range of values. In other words, all records between
one set of coordinates and a second set of coordinates.
Regards,
Fred Chateau
http://hotelmotelnow.com
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u5sM3fWrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the
> columns are latitude and longitude. I'm looking for fast, read-only lookup
> against queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>|||On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
wrote:
> I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the colum
ns
> are latitude and longitude. I'm looking for fast, read-only lookup against
> queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateauhttp://hotelmotelnow.com
Floats are fine and I use them all the time instead of decimal,
numeric etc although not for indexing just to store values.|||I don't think there is any problem with indexing float data types. Perhaps
you are confusing this with using a float(s) as a primary key. The PK issue
is that real and float are approximate data types so not all decimal values
can be stored.
For example:
DECLARE @.MyFloat float
SET @.MyFloat = 1.15
SELECT @.MyFloat
returns 1.1499999999999999 instead of 1.15. However, some GUIs like SQL
Server Management Studio will round the value for display and show the value
as 1.15.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u5sM3fWrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the
> columns are latitude and longitude. I'm looking for fast, read-only lookup
> against queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>|||> Floats are fine and I use them all the time instead of decimal,
> numeric etc although not for indexing just to store values.
Float is an approximate data type and cannot accurately store all decimal
values. I suggest you reconsider this approach except in cases where
accuracy is not needed. See the Books Online and my response to Fred for
more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1181706330.040993.149140@.d30g2000prg.googlegroups.com...
> On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
> wrote:
> Floats are fine and I use them all the time instead of decimal,
> numeric etc although not for indexing just to store values.
>|||On Tue, 12 Jun 2007 22:07:23 -0500, Fred Chateau wrote:

>I'm setting up a table with over a million records in it. Two of the column
s
>are latitude and longitude. I'm looking for fast, read-only lookup against
>queries based on those columns.
Hi Fred,
Though your question has been answered, you might be interested in
reading "Expert SQL Server 2005 Development", written by Adam Machanic
with small contributions from Lara Rubbelke and myself. The chapter I
contributed is entirely devoted to working with spatial data, and
finding locations in a table that uses latitude and longitude to store
the locations is a major part of that chapter.
After reading that chapter, check my blog (see signature for the URL)
for a correction to an annoying error I was alerted to just AFTER the
deadline for corrections.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jun 13, 6:41 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> Float is an approximate data type and cannot accurately store all decimal
> values. I suggest you reconsider this approach except in cases where
> accuracy is not needed. See the Books Online and my response to Fred for
> more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1181706330.040993.149140@.d30g2000prg.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
When I sum hundreds of thousands I can sacrifice precision of ONE
penny although I do not understand why SQL Server can not maintain
exact precision like numbers with larger precision. The original
poster said that he would search within a range of co-ordinates and
therefore exact match to the 1/100th of decimal may not be important.
Thanks.|||> When I sum hundreds of thousands I can sacrifice precision of ONE
> penny although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision. The original
> poster said that he would search within a range of co-ordinates and
> therefore exact match to the 1/100th of decimal may not be important.
I see no problem with using float as long as you are aware of the
implications. I just wanted to caution that using floats "all the time" is
definitely not a Best Practice. It should be used only as a conscious
decision when accuracy is not important. In most cases, one should never
use float for currency values. Auditors can get particularly annoyed when
those pennies drop on the floor.

> although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision.
This is not a SQL Server issue but one inherent with the IEEE standard. See
http://en.wikipedia.org/wiki/IEEE_754.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1181822618.110812.193080@.x35g2000prf.googlegroups.com...
> On Jun 13, 6:41 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
> When I sum hundreds of thousands I can sacrifice precision of ONE
> penny although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision. The original
> poster said that he would search within a range of co-ordinates and
> therefore exact match to the 1/100th of decimal may not be important.
> Thanks.
>

Indexing Floats

I was looking for a few opinions from some DBA's with experience in this.
I'm setting up a table with over a million records in it. Two of the columns
are latitude and longitude. I'm looking for fast, read-only lookup against
queries based on those columns.
I have been told not to index on floats because it's not efficient, but I
have existing tables that store latitude and longitude as floats and they
work well, although the number of records involved is significantly lower.
A typical lat/long coordinate uses 8 to 9 places, up to three before the
decimal, up to six after, and are signed.
If I need fast lookups, should I be using decimals instead?
--
Regards,
Fred Chateau
http://hotelmotelnow.comOne other thing...
I don't know if it makes any difference, but I'm not querying for one
record, but rather a range of values. In other words, all records between
one set of coordinates and a second set of coordinates.
--
Regards,
Fred Chateau
http://hotelmotelnow.com
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u5sM3fWrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the
> columns are latitude and longitude. I'm looking for fast, read-only lookup
> against queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>|||On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
wrote:
> I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the columns
> are latitude and longitude. I'm looking for fast, read-only lookup against
> queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateauhttp://hotelmotelnow.com
Floats are fine and I use them all the time instead of decimal,
numeric etc although not for indexing just to store values.|||I don't think there is any problem with indexing float data types. Perhaps
you are confusing this with using a float(s) as a primary key. The PK issue
is that real and float are approximate data types so not all decimal values
can be stored.
For example:
DECLARE @.MyFloat float
SET @.MyFloat = 1.15
SELECT @.MyFloat
returns 1.1499999999999999 instead of 1.15. However, some GUIs like SQL
Server Management Studio will round the value for display and show the value
as 1.15.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u5sM3fWrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the
> columns are latitude and longitude. I'm looking for fast, read-only lookup
> against queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>|||> Floats are fine and I use them all the time instead of decimal,
> numeric etc although not for indexing just to store values.
Float is an approximate data type and cannot accurately store all decimal
values. I suggest you reconsider this approach except in cases where
accuracy is not needed. See the Books Online and my response to Fred for
more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1181706330.040993.149140@.d30g2000prg.googlegroups.com...
> On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
> wrote:
>> I was looking for a few opinions from some DBA's with experience in this.
>> I'm setting up a table with over a million records in it. Two of the
>> columns
>> are latitude and longitude. I'm looking for fast, read-only lookup
>> against
>> queries based on those columns.
>> I have been told not to index on floats because it's not efficient, but I
>> have existing tables that store latitude and longitude as floats and they
>> work well, although the number of records involved is significantly
>> lower.
>> A typical lat/long coordinate uses 8 to 9 places, up to three before the
>> decimal, up to six after, and are signed.
>> If I need fast lookups, should I be using decimals instead?
>> --
>> Regards,
>> Fred Chateauhttp://hotelmotelnow.com
> Floats are fine and I use them all the time instead of decimal,
> numeric etc although not for indexing just to store values.
>|||On Tue, 12 Jun 2007 22:07:23 -0500, Fred Chateau wrote:
>I'm setting up a table with over a million records in it. Two of the columns
>are latitude and longitude. I'm looking for fast, read-only lookup against
>queries based on those columns.
Hi Fred,
Though your question has been answered, you might be interested in
reading "Expert SQL Server 2005 Development", written by Adam Machanic
with small contributions from Lara Rubbelke and myself. The chapter I
contributed is entirely devoted to working with spatial data, and
finding locations in a table that uses latitude and longitude to store
the locations is a major part of that chapter.
After reading that chapter, check my blog (see signature for the URL)
for a correction to an annoying error I was alerted to just AFTER the
deadline for corrections.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jun 13, 6:41 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > Floats are fine and I use them all the time instead of decimal,
> > numeric etc although not for indexing just to store values.
> Float is an approximate data type and cannot accurately store all decimal
> values. I suggest you reconsider this approach except in cases where
> accuracy is not needed. See the Books Online and my response to Fred for
> more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1181706330.040993.149140@.d30g2000prg.googlegroups.com...
>
> > On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
> > wrote:
> >> I was looking for a few opinions from some DBA's with experience in this.
> >> I'm setting up a table with over a million records in it. Two of the
> >> columns
> >> are latitude and longitude. I'm looking for fast, read-only lookup
> >> against
> >> queries based on those columns.
> >> I have been told not to index on floats because it's not efficient, but I
> >> have existing tables that store latitude and longitude as floats and they
> >> work well, although the number of records involved is significantly
> >> lower.
> >> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> >> decimal, up to six after, and are signed.
> >> If I need fast lookups, should I be using decimals instead?
> >> --
> >> Regards,
> >> Fred Chateauhttp://hotelmotelnow.com
> > Floats are fine and I use them all the time instead of decimal,
> > numeric etc although not for indexing just to store values.- Hide quoted text -
> - Show quoted text -
When I sum hundreds of thousands I can sacrifice precision of ONE
penny although I do not understand why SQL Server can not maintain
exact precision like numbers with larger precision. The original
poster said that he would search within a range of co-ordinates and
therefore exact match to the 1/100th of decimal may not be important.
Thanks.|||> When I sum hundreds of thousands I can sacrifice precision of ONE
> penny although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision. The original
> poster said that he would search within a range of co-ordinates and
> therefore exact match to the 1/100th of decimal may not be important.
I see no problem with using float as long as you are aware of the
implications. I just wanted to caution that using floats "all the time" is
definitely not a Best Practice. It should be used only as a conscious
decision when accuracy is not important. In most cases, one should never
use float for currency values. Auditors can get particularly annoyed when
those pennies drop on the floor.
> although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision.
This is not a SQL Server issue but one inherent with the IEEE standard. See
http://en.wikipedia.org/wiki/IEEE_754.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1181822618.110812.193080@.x35g2000prf.googlegroups.com...
> On Jun 13, 6:41 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
>> > Floats are fine and I use them all the time instead of decimal,
>> > numeric etc although not for indexing just to store values.
>> Float is an approximate data type and cannot accurately store all decimal
>> values. I suggest you reconsider this approach except in cases where
>> accuracy is not needed. See the Books Online and my response to Fred for
>> more information.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1181706330.040993.149140@.d30g2000prg.googlegroups.com...
>>
>> > On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
>> > wrote:
>> >> I was looking for a few opinions from some DBA's with experience in
>> >> this.
>> >> I'm setting up a table with over a million records in it. Two of the
>> >> columns
>> >> are latitude and longitude. I'm looking for fast, read-only lookup
>> >> against
>> >> queries based on those columns.
>> >> I have been told not to index on floats because it's not efficient,
>> >> but I
>> >> have existing tables that store latitude and longitude as floats and
>> >> they
>> >> work well, although the number of records involved is significantly
>> >> lower.
>> >> A typical lat/long coordinate uses 8 to 9 places, up to three before
>> >> the
>> >> decimal, up to six after, and are signed.
>> >> If I need fast lookups, should I be using decimals instead?
>> >> --
>> >> Regards,
>> >> Fred Chateauhttp://hotelmotelnow.com
>> > Floats are fine and I use them all the time instead of decimal,
>> > numeric etc although not for indexing just to store values.- Hide
>> > quoted text -
>> - Show quoted text -
> When I sum hundreds of thousands I can sacrifice precision of ONE
> penny although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision. The original
> poster said that he would search within a range of co-ordinates and
> therefore exact match to the 1/100th of decimal may not be important.
> Thanks.
>

Indexing Floats

I was looking for a few opinions from some DBA's with experience in this.
I'm setting up a table with over a million records in it. Two of the columns
are latitude and longitude. I'm looking for fast, read-only lookup against
queries based on those columns.
I have been told not to index on floats because it's not efficient, but I
have existing tables that store latitude and longitude as floats and they
work well, although the number of records involved is significantly lower.
A typical lat/long coordinate uses 8 to 9 places, up to three before the
decimal, up to six after, and are signed.
If I need fast lookups, should I be using decimals instead?
Regards,
Fred Chateau
http://hotelmotelnow.com
One other thing...
I don't know if it makes any difference, but I'm not querying for one
record, but rather a range of values. In other words, all records between
one set of coordinates and a second set of coordinates.
Regards,
Fred Chateau
http://hotelmotelnow.com
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u5sM3fWrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the
> columns are latitude and longitude. I'm looking for fast, read-only lookup
> against queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>
|||On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
wrote:
> I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the columns
> are latitude and longitude. I'm looking for fast, read-only lookup against
> queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateauhttp://hotelmotelnow.com
Floats are fine and I use them all the time instead of decimal,
numeric etc although not for indexing just to store values.
|||I don't think there is any problem with indexing float data types. Perhaps
you are confusing this with using a float(s) as a primary key. The PK issue
is that real and float are approximate data types so not all decimal values
can be stored.
For example:
DECLARE @.MyFloat float
SET @.MyFloat = 1.15
SELECT @.MyFloat
returns 1.1499999999999999 instead of 1.15. However, some GUIs like SQL
Server Management Studio will round the value for display and show the value
as 1.15.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fred Chateau" <webmaster@.hotelmotelnow.com> wrote in message
news:u5sM3fWrHHA.3636@.TK2MSFTNGP06.phx.gbl...
>I was looking for a few opinions from some DBA's with experience in this.
> I'm setting up a table with over a million records in it. Two of the
> columns are latitude and longitude. I'm looking for fast, read-only lookup
> against queries based on those columns.
> I have been told not to index on floats because it's not efficient, but I
> have existing tables that store latitude and longitude as floats and they
> work well, although the number of records involved is significantly lower.
> A typical lat/long coordinate uses 8 to 9 places, up to three before the
> decimal, up to six after, and are signed.
> If I need fast lookups, should I be using decimals instead?
> --
> Regards,
> Fred Chateau
> http://hotelmotelnow.com
>
|||> Floats are fine and I use them all the time instead of decimal,
> numeric etc although not for indexing just to store values.
Float is an approximate data type and cannot accurately store all decimal
values. I suggest you reconsider this approach except in cases where
accuracy is not needed. See the Books Online and my response to Fred for
more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1181706330.040993.149140@.d30g2000prg.googlegr oups.com...
> On Jun 13, 9:07 am, "Fred Chateau" <webmas...@.hotelmotelnow.com>
> wrote:
> Floats are fine and I use them all the time instead of decimal,
> numeric etc although not for indexing just to store values.
>
|||On Tue, 12 Jun 2007 22:07:23 -0500, Fred Chateau wrote:

>I'm setting up a table with over a million records in it. Two of the columns
>are latitude and longitude. I'm looking for fast, read-only lookup against
>queries based on those columns.
Hi Fred,
Though your question has been answered, you might be interested in
reading "Expert SQL Server 2005 Development", written by Adam Machanic
with small contributions from Lara Rubbelke and myself. The chapter I
contributed is entirely devoted to working with spatial data, and
finding locations in a table that uses latitude and longitude to store
the locations is a major part of that chapter.
After reading that chapter, check my blog (see signature for the URL)
for a correction to an annoying error I was alerted to just AFTER the
deadline for corrections.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jun 13, 6:41 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> Float is an approximate data type and cannot accurately store all decimal
> values. I suggest you reconsider this approach except in cases where
> accuracy is not needed. See the Books Online and my response to Fred for
> more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1181706330.040993.149140@.d30g2000prg.googlegr oups.com...
>
>
>
>
>
> - Show quoted text -
When I sum hundreds of thousands I can sacrifice precision of ONE
penny although I do not understand why SQL Server can not maintain
exact precision like numbers with larger precision. The original
poster said that he would search within a range of co-ordinates and
therefore exact match to the 1/100th of decimal may not be important.
Thanks.
|||> When I sum hundreds of thousands I can sacrifice precision of ONE
> penny although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision. The original
> poster said that he would search within a range of co-ordinates and
> therefore exact match to the 1/100th of decimal may not be important.
I see no problem with using float as long as you are aware of the
implications. I just wanted to caution that using floats "all the time" is
definitely not a Best Practice. It should be used only as a conscious
decision when accuracy is not important. In most cases, one should never
use float for currency values. Auditors can get particularly annoyed when
those pennies drop on the floor.

> although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision.
This is not a SQL Server issue but one inherent with the IEEE standard. See
http://en.wikipedia.org/wiki/IEEE_754.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1181822618.110812.193080@.x35g2000prf.googlegr oups.com...
> On Jun 13, 6:41 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
> When I sum hundreds of thousands I can sacrifice precision of ONE
> penny although I do not understand why SQL Server can not maintain
> exact precision like numbers with larger precision. The original
> poster said that he would search within a range of co-ordinates and
> therefore exact match to the 1/100th of decimal may not be important.
> Thanks.
>

indexing distinct queries ...

Hi,
I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.
Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:
select distinct CATEGORY from table TEST
Obviously the server has to scan all rows in order to get all unique columns
which takes quite a while, especially since that web page contains several
of these types of queries. We also have a MAX(DATE) and MIN(DATE) query that
also add to the load.
I already created indexes on the CATEGORY (actually on all categories)
column which might help a little but I'm pretty sure that there has got to
be a better way.
I also create a view (select distinct CATEGORY from table TEST) and tried to
index it, but it won't let me index a query that contains a DISTINCT
statement.
Isn't there a way to create an index that contains only the distinct values?
Is there another way to speed this up?
Thanks for any hints!try creating a view with schema binding and use group by (which will return
the distinct values), then you should be able to add a unique index on the v
iew.|||Thanks, that actually seemed to work. I had tried this before but
discouraged by (yet another) error message mentioning count_big(*) missing.
Now I realized that I can just add the count_big(*) and it saved the index.
Initial Tests look promising - thanks.
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:BD3A168C-90FD-4FDC-AE1C-46618C6FF5A6@.microsoft.com...
> try creating a view with schema binding and use group by (which will
return the distinct values), then you should be able to add a unique index
on the view.

indexing distinct queries ...

Hi,
I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.
Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:
select distinct CATEGORY from table TEST
Obviously the server has to scan all rows in order to get all unique columns
which takes quite a while, especially since that web page contains several
of these types of queries. We also have a MAX(DATE) and MIN(DATE) query that
also add to the load.
I already created indexes on the CATEGORY (actually on all categories)
column which might help a little but I'm pretty sure that there has got to
be a better way.
I also create a view (select distinct CATEGORY from table TEST) and tried to
index it, but it won't let me index a query that contains a DISTINCT
statement.
Isn't there a way to create an index that contains only the distinct values?
Is there another way to speed this up?
Thanks for any hints!try creating a view with schema binding and use group by (which will return the distinct values), then you should be able to add a unique index on the view.|||Thanks, that actually seemed to work. I had tried this before but
discouraged by (yet another) error message mentioning count_big(*) missing.
Now I realized that I can just add the count_big(*) and it saved the index.
Initial Tests look promising - thanks.
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:BD3A168C-90FD-4FDC-AE1C-46618C6FF5A6@.microsoft.com...
> try creating a view with schema binding and use group by (which will
return the distinct values), then you should be able to add a unique index
on the view.

Indexing columns question?

I have 2 tables that both contain around 500,000 records a piece. There are
about 180 column in each table and each row averages about 20k to 50k of
data. After adding some indexes to the tables my sp's are running much
faster now. In the selections for the Indexes I have unique, clustered, do
not automactically recompute statistics"
I have been selecting "do not automaticallyrecompute statistics". My goal
is to achieve max performance with the indexing (I have only added 2 indexes
per table). I am still not clear on the use of Clustered indexes. So do I
want to not recompute statistics automatically? When would I want to use a
clustered index?
Thanks,
RichSee if this helps:
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Rich" wrote:

> I have 2 tables that both contain around 500,000 records a piece. There a
re
> about 180 column in each table and each row averages about 20k to 50k of
> data. After adding some indexes to the tables my sp's are running much
> faster now. In the selections for the Indexes I have unique, clustered, d
o
> not automactically recompute statistics"
> I have been selecting "do not automaticallyrecompute statistics". My goal
> is to achieve max performance with the indexing (I have only added 2 index
es
> per table). I am still not clear on the use of Clustered indexes. So do
I
> want to not recompute statistics automatically? When would I want to use
a
> clustered index?
> Thanks,
> Rich|||Thanks for this tip.
Rich
"Alejandro Mesa" wrote:
> See if this helps:
> Tips on Optimizing SQL Server Clustered Indexes
> http://www.sql-server-performance.c...red_indexes.asp
>
> AMB
>
> "Rich" wrote:
>

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karan
karanlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert
|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegr oups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>
|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert
|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>
|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karankaranlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegroups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert