This is my first post here, hopefully you folks can help me.
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=890048Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=892551|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Wareho...9
0.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=898374
Showing posts with label invoice. Show all posts
Showing posts with label invoice. Show all posts
Wednesday, March 21, 2012
Indexing/Unique Key
This is my first post here, hopefully you folks can help me.
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=890048
Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter
|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=892551
|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick
|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter
|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=898374
sql
I have the typical Invoice Header and Invoice Detail Fact Tables.
Ive read that you should declare a clustered index on all of the
foreign dimension keys in a fact table (for SQL Server).
However, that wouldnt be unique as a customer can be invoiced for the
same part on the same day, etc.. right?
So what do people typically do - declare a unique clustered index on
the Degenerate dimensions of Invoice Number and Invoice Line Number?
What is the best index to put on these fact tables?
Thanks,
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=890048
Nile,
there is no rule that says detail level fact tables must have the
combination of keys be unique. Though on a summary level fact table the
rule is they must be unique.
I believe you can make a non-unique clustered index on 2000 with no ill
effects.
Personally, nowadays I always create a single integer key at the front
of txn level fact tables and this is the primary key and then I just
create indexes on the columns in the fact tables as required......I
should note that I have not built a decent sized DW on 2000....I've
been doing this on other databases with bit mapped indexes...I did this
on 7 and it all worked ok after some coaxing.....I hope to get back
into the world of building the odd reasonably sized DW on sql server in
the not too distant future...
Peter
|||"" wrote:
> Nile,
> there is no rule that says detail level fact tables must have
> the
> combination of keys be unique. Though on a summary level fact
> table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000
> with no ill
> effects.
> Personally, nowadays I always create a single integer key at
> the front
> of txn level fact tables and this is the primary key and then
> I just
> create indexes on the columns in the fact tables as
> required......I
> should note that I have not built a decent sized DW on
> 2000....I've
> been doing this on other databases with bit mapped indexes...I
> did this
> on 7 and it all worked ok after some coaxing.....I hope to
> get back
> into the world of building the odd reasonably sized DW on sql
> server in
> the not too distant future...
> Peter
Thanks for your reply. Ill probably create a composite primary key
on that unique identity column plus a date key (smallint) so that the
index can be used. That approach was documented on msdn - what do you
think?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=892551
|||Peter Nolan wrote:
> Nile,
> there is no rule that says detail level fact tables must have the
> combination of keys be unique. Though on a summary level fact table the
> rule is they must be unique.
> I believe you can make a non-unique clustered index on 2000 with no ill
> effects.
> Personally, nowadays I always create a single integer key at the front
> of txn level fact tables and this is the primary key and then I just
> create indexes on the columns in the fact tables as required......I
> should note that I have not built a decent sized DW on 2000....I've
> been doing this on other databases with bit mapped indexes...I did this
> on 7 and it all worked ok after some coaxing.....I hope to get back
> into the world of building the odd reasonably sized DW on sql server in
> the not too distant future...
> Peter
>
Peter, why bother to create a primary key on a fact table at all? Its
never used for lookup, so its a wasted index.
My $0.02
- rick
|||Hi Rick,
this was discussed at some length on dwlist
(datawarehousing.com)...many people say they see no use for a unique
key on a fact table...
However, over the last few years at some of the sites I have worked on
we have found some great uses for putting a single integer key on the
front of a fact table. We do this and use it often. (But we don't
publish what we use it for.)
Best Regards
Peter
|||"" wrote:
> Hi Rick,
> this was discussed at some length on dwlist
> (datawarehousing.com)...many people say they see no use for a
> unique
> key on a fact table...
> However, over the last few years at some of the sites I have
> worked on
> we have found some great uses for putting a single integer key
> on the
> front of a fact table. We do this and use it often. (But we
> don't
> publish what we use it for.)
> Best Regards
> Peter
Can you please let me know what you use it for?
Thanks
Nile
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Data-Warehou...ict258090.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=898374
sql
Monday, March 12, 2012
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
You should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application
>
>.
>
|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application
|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
You should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application
>
>.
>
|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application
|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
Hi
Posting your DDL (Create table statements) is better than pseudo code!
I indexes can be created through enterprise manager using the buttons on the
table designer dialog. Hopefully you are maintaining your code in a source
code control system, therefore writing SQL directly.
For Create Index statements see Books Online or at
http://msdn.microsoft.com/library/de...asp?frame=true
If you wish to use SQL to do this.
If you have not created primary keys then look at the ALTER table statement
http://msdn.microsoft.com/library/de...sp?frame=true.
This statement can also be used to create a foreign key from the custno
column in the invoices table to the custno column in customers.
HTH
John
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e35501c45506$83d510c0$a401280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skc
Hi
Posting your DDL (Create table statements) is better than pseudo code!
I indexes can be created through enterprise manager using the buttons on the
table designer dialog. Hopefully you are maintaining your code in a source
code control system, therefore writing SQL directly.
For Create Index statements see Books Online or at
http://msdn.microsoft.com/library/de...asp?frame=true
If you wish to use SQL to do this.
If you have not created primary keys then look at the ALTER table statement
http://msdn.microsoft.com/library/de...sp?frame=true.
This statement can also be used to create a foreign key from the custno
column in the invoices table to the custno column in customers.
HTH
John
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e35501c45506$83d510c0$a401280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
Friday, March 9, 2012
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods
for doing this. You
create indexes to support the queries you submit. So the first part is to ge
t to the SQL queries
(using profiler, for instance) and then try different indexing schemes to su
pport those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application[vbcol=seagreen]
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and
how you use profiler to
catch the information you need to troubleshoot and tweak these situations. I
f you don't feel like
doing that, you can of course just catch the query in profiler, copy the tex
t to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx
.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
>
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods
for doing this. You
create indexes to support the queries you submit. So the first part is to ge
t to the SQL queries
(using profiler, for instance) and then try different indexing schemes to su
pport those queries.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
are[vbcol=seagreen]
then[vbcol=seagreen]
should[vbcol=seagreen]
application[vbcol=seagreen]
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and
how you use profiler to
catch the information you need to troubleshoot and tweak these situations. I
f you don't feel like
doing that, you can of course just catch the query in profiler, copy the tex
t to Query Analyzer and
go from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx
.gbl...[vbcol=seagreen]
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
>
> to learn the methods for doing this. You
> first part is to get to the SQL queries
> indexing schemes to support those queries.
> message
> are
> then
> should
> application|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx
.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
indexing
I am running SQL 2000 and Crystal Reports ASP.
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
>> I am running SQL 2000 and Crystal Reports ASP.
>> I have two tables called customer and invoice. There
are
>> 100000 customers and 7200000 invoices.
>> Here are the fields:
>> customer
>> - id ...1 to 100000
>> - custno ...1 to 100000
>> - name
>> - address1
>> - address2
>> - postcode
>> invoices
>> - id ...1 to 7200000
>> - custno ...random numbers below 100000
>> - inv_type
>> - amount
>> - invdate
>> The emphasis here is, if I type in a customer number
then
>> based on the customer numbers that match in the invoices
>> table invoices will appear.
>> I need to index both tables for quickness. I am new to
>> indexing, please help me!!! There are options for
>> clustered etc... and I do not know what to do. If I try
>> entering a customer number in Crystal Reports is takes
>> around 10 seconds to list around 20 invoices - this
should
>> be instantaneous as it is causing my .asp web
application
>> to time out.
>> Thanks,
>> skc
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> >--Original Message--
> >You should work with someone who has dome some indexing
> to learn the methods for doing this. You
> >create indexes to support the queries you submit. So the
> first part is to get to the SQL queries
> >(using profiler, for instance) and then try different
> indexing schemes to support those queries.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"skc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> >> I am running SQL 2000 and Crystal Reports ASP.
> >>
> >> I have two tables called customer and invoice. There
> are
> >> 100000 customers and 7200000 invoices.
> >>
> >> Here are the fields:
> >>
> >> customer
> >> - id ...1 to 100000
> >> - custno ...1 to 100000
> >> - name
> >> - address1
> >> - address2
> >> - postcode
> >>
> >> invoices
> >> - id ...1 to 7200000
> >> - custno ...random numbers below 100000
> >> - inv_type
> >> - amount
> >> - invdate
> >>
> >> The emphasis here is, if I type in a customer number
> then
> >> based on the customer numbers that match in the invoices
> >> table invoices will appear.
> >>
> >> I need to index both tables for quickness. I am new to
> >> indexing, please help me!!! There are options for
> >> clustered etc... and I do not know what to do. If I try
> >> entering a customer number in Crystal Reports is takes
> >> around 10 seconds to list around 20 invoices - this
> should
> >> be instantaneous as it is causing my .asp web
> application
> >> to time out.
> >>
> >> Thanks,
> >>
> >> skc
> >
> >
> >.
> >|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
I have two tables called customer and invoice. There are
100000 customers and 7200000 invoices.
Here are the fields:
customer
- id ...1 to 100000
- custno ...1 to 100000
- name
- address1
- address2
- postcode
invoices
- id ...1 to 7200000
- custno ...random numbers below 100000
- inv_type
- amount
- invdate
The emphasis here is, if I type in a customer number then
based on the customer numbers that match in the invoices
table invoices will appear.
I need to index both tables for quickness. I am new to
indexing, please help me!!! There are options for
clustered etc... and I do not know what to do. If I try
entering a customer number in Crystal Reports is takes
around 10 seconds to list around 20 invoices - this should
be instantaneous as it is causing my .asp web application
to time out.
Thanks,
skcYou should work with someone who has dome some indexing to learn the methods for doing this. You
create indexes to support the queries you submit. So the first part is to get to the SQL queries
(using profiler, for instance) and then try different indexing schemes to support those queries.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc|||where do I start then?
Also, I created a concatenated index (Manage indexes for
the tables) for cust_no and id. This has not speeded up
things.
skc
>--Original Message--
>You should work with someone who has dome some indexing
to learn the methods for doing this. You
>create indexes to support the queries you submit. So the
first part is to get to the SQL queries
>(using profiler, for instance) and then try different
indexing schemes to support those queries.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"skc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
>> I am running SQL 2000 and Crystal Reports ASP.
>> I have two tables called customer and invoice. There
are
>> 100000 customers and 7200000 invoices.
>> Here are the fields:
>> customer
>> - id ...1 to 100000
>> - custno ...1 to 100000
>> - name
>> - address1
>> - address2
>> - postcode
>> invoices
>> - id ...1 to 7200000
>> - custno ...random numbers below 100000
>> - inv_type
>> - amount
>> - invdate
>> The emphasis here is, if I type in a customer number
then
>> based on the customer numbers that match in the invoices
>> table invoices will appear.
>> I need to index both tables for quickness. I am new to
>> indexing, please help me!!! There are options for
>> clustered etc... and I do not know what to do. If I try
>> entering a customer number in Crystal Reports is takes
>> around 10 seconds to list around 20 invoices - this
should
>> be instantaneous as it is causing my .asp web
application
>> to time out.
>> Thanks,
>> skc
>
>.
>|||I would pick up a book that explain what indexes are, how they are used and how you use profiler to
catch the information you need to troubleshoot and tweak these situations. If you don't feel like
doing that, you can of course just catch the query in profiler, copy the text to Query Analyzer and
go from there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1dde801c45511$fbd10100$a601280a@.phx.gbl...
> where do I start then?
> Also, I created a concatenated index (Manage indexes for
> the tables) for cust_no and id. This has not speeded up
> things.
> skc
> >--Original Message--
> >You should work with someone who has dome some indexing
> to learn the methods for doing this. You
> >create indexes to support the queries you submit. So the
> first part is to get to the SQL queries
> >(using profiler, for instance) and then try different
> indexing schemes to support those queries.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"skc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> >> I am running SQL 2000 and Crystal Reports ASP.
> >>
> >> I have two tables called customer and invoice. There
> are
> >> 100000 customers and 7200000 invoices.
> >>
> >> Here are the fields:
> >>
> >> customer
> >> - id ...1 to 100000
> >> - custno ...1 to 100000
> >> - name
> >> - address1
> >> - address2
> >> - postcode
> >>
> >> invoices
> >> - id ...1 to 7200000
> >> - custno ...random numbers below 100000
> >> - inv_type
> >> - amount
> >> - invdate
> >>
> >> The emphasis here is, if I type in a customer number
> then
> >> based on the customer numbers that match in the invoices
> >> table invoices will appear.
> >>
> >> I need to index both tables for quickness. I am new to
> >> indexing, please help me!!! There are options for
> >> clustered etc... and I do not know what to do. If I try
> >> entering a customer number in Crystal Reports is takes
> >> around 10 seconds to list around 20 invoices - this
> should
> >> be instantaneous as it is causing my .asp web
> application
> >> to time out.
> >>
> >> Thanks,
> >>
> >> skc
> >
> >
> >.
> >|||Like Tibor, I hesistate giving much specific advice with such a narrow view
of your world, But
Based on your info IF most customers have < 5% of the invoices in the
invoices table, and this is the only query you are interested in,,,
I might try cluster customer on ID, cluster invoices on custno...
Again, it might not be smart excluding the consideration of all of the other
possible queries which might exist on these tables.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"skc" <anonymous@.discussions.microsoft.com> wrote in message
news:1e20b01c45506$91f0ab10$a101280a@.phx.gbl...
> I am running SQL 2000 and Crystal Reports ASP.
> I have two tables called customer and invoice. There are
> 100000 customers and 7200000 invoices.
> Here are the fields:
> customer
> - id ...1 to 100000
> - custno ...1 to 100000
> - name
> - address1
> - address2
> - postcode
> invoices
> - id ...1 to 7200000
> - custno ...random numbers below 100000
> - inv_type
> - amount
> - invdate
> The emphasis here is, if I type in a customer number then
> based on the customer numbers that match in the invoices
> table invoices will appear.
> I need to index both tables for quickness. I am new to
> indexing, please help me!!! There are options for
> clustered etc... and I do not know what to do. If I try
> entering a customer number in Crystal Reports is takes
> around 10 seconds to list around 20 invoices - this should
> be instantaneous as it is causing my .asp web application
> to time out.
> Thanks,
> skc
INDEXES: Is this correct
I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?> All I want to know is would this code work?
Well, did you try it? What happened?
--
http://www.aspfaq.com/
(Reverse address to reply.)
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?> All I want to know is would this code work?
Well, did you try it? What happened?
--
http://www.aspfaq.com/
(Reverse address to reply.)
INDEXES: Is this correct
I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know i
s
would this code work?> All I want to know is would this code work?
Well, did you try it? What happened?
http://www.aspfaq.com/
(Reverse address to reply.)
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know i
s
would this code work?> All I want to know is would this code work?
Well, did you try it? What happened?
http://www.aspfaq.com/
(Reverse address to reply.)
INDEXES: Is this correct
I have a temp table and created two indexes on it.
Please See below
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?
> All I want to know is would this code work?
Well, did you try it? What happened?
http://www.aspfaq.com/
(Reverse address to reply.)
Please See below
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?
> All I want to know is would this code work?
Well, did you try it? What happened?
http://www.aspfaq.com/
(Reverse address to reply.)
Wednesday, March 7, 2012
Indexes confusion
I have a temp table and created two indexes on it.
Please See below
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?
Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>
|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> is
>
>
|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...[vbcol=seagreen]
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
>
>
Please See below
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?
Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>
|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> is
>
>
|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...[vbcol=seagreen]
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
>
>
Indexes confusion
I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know i
s
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> is
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...[vbcol=seagreen]
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at th
e
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they'
re
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table
on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table witho
ut
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
>
>
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know i
s
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> is
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...[vbcol=seagreen]
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at th
e
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they'
re
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table
on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table witho
ut
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
>
>
Indexes confusion
I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
--
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> > I have a temp table and created two indexes on it.
> > Please See below
> > --
> > CREATE TABLE #Invoice (
> > InvoiceID int NOT NULL,
> > CustomerKey varchar(20),
> > DebtorId int,
> > Reference varchar(50),
> > BalanceDue money,
> > DebtID int,
> > Reason varchar(100),
> > DebtStatus int,
> > )
> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> > ---
> >
> > I use DebtID as index because I use it again as a inner join to this table
> > in another query. So I assume it will make much faster. All I want to know
> is
> > would this code work?
> >
> >
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
--
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>> Already answered, please see your other thread, and these articles:
>> http://www.aspfaq.com/5007
>> http://www.aspfaq.com/5003
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "raj" <raj@.discussions.microsoft.com> wrote in message
>> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
>> > I have a temp table and created two indexes on it.
>> > Please See below
>> > --
>> > CREATE TABLE #Invoice (
>> > InvoiceID int NOT NULL,
>> > CustomerKey varchar(20),
>> > DebtorId int,
>> > Reference varchar(50),
>> > BalanceDue money,
>> > DebtID int,
>> > Reason varchar(100),
>> > DebtStatus int,
>> > )
>> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
>> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
>> > ---
>> >
>> > I use DebtID as index because I use it again as a inner join to this
>> > table
>> > in another query. So I assume it will make much faster. All I want to
>> > know
>> is
>> > would this code work?
>> >
>> >
>>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> > I'm sorry , but I couldn't find the answer in the newsgroup.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >> Already answered, please see your other thread, and these articles:
> >>
> >> http://www.aspfaq.com/5007
> >> http://www.aspfaq.com/5003
> >>
> >> --
> >> http://www.aspfaq.com/
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >> "raj" <raj@.discussions.microsoft.com> wrote in message
> >> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> >> > I have a temp table and created two indexes on it.
> >> > Please See below
> >> > --
> >> > CREATE TABLE #Invoice (
> >> > InvoiceID int NOT NULL,
> >> > CustomerKey varchar(20),
> >> > DebtorId int,
> >> > Reference varchar(50),
> >> > BalanceDue money,
> >> > DebtID int,
> >> > Reason varchar(100),
> >> > DebtStatus int,
> >> > )
> >> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> >> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> >> > ---
> >> >
> >> > I use DebtID as index because I use it again as a inner join to this
> >> > table
> >> > in another query. So I assume it will make much faster. All I want to
> >> > know
> >> is
> >> > would this code work?
> >> >
> >> >
> >>
> >>
> >>
>
>
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
--
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> > I have a temp table and created two indexes on it.
> > Please See below
> > --
> > CREATE TABLE #Invoice (
> > InvoiceID int NOT NULL,
> > CustomerKey varchar(20),
> > DebtorId int,
> > Reference varchar(50),
> > BalanceDue money,
> > DebtID int,
> > Reason varchar(100),
> > DebtStatus int,
> > )
> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> > ---
> >
> > I use DebtID as index because I use it again as a inner join to this table
> > in another query. So I assume it will make much faster. All I want to know
> is
> > would this code work?
> >
> >
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
--
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>> Already answered, please see your other thread, and these articles:
>> http://www.aspfaq.com/5007
>> http://www.aspfaq.com/5003
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "raj" <raj@.discussions.microsoft.com> wrote in message
>> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
>> > I have a temp table and created two indexes on it.
>> > Please See below
>> > --
>> > CREATE TABLE #Invoice (
>> > InvoiceID int NOT NULL,
>> > CustomerKey varchar(20),
>> > DebtorId int,
>> > Reference varchar(50),
>> > BalanceDue money,
>> > DebtID int,
>> > Reason varchar(100),
>> > DebtStatus int,
>> > )
>> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
>> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
>> > ---
>> >
>> > I use DebtID as index because I use it again as a inner join to this
>> > table
>> > in another query. So I assume it will make much faster. All I want to
>> > know
>> is
>> > would this code work?
>> >
>> >
>>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> > I'm sorry , but I couldn't find the answer in the newsgroup.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >> Already answered, please see your other thread, and these articles:
> >>
> >> http://www.aspfaq.com/5007
> >> http://www.aspfaq.com/5003
> >>
> >> --
> >> http://www.aspfaq.com/
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >> "raj" <raj@.discussions.microsoft.com> wrote in message
> >> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> >> > I have a temp table and created two indexes on it.
> >> > Please See below
> >> > --
> >> > CREATE TABLE #Invoice (
> >> > InvoiceID int NOT NULL,
> >> > CustomerKey varchar(20),
> >> > DebtorId int,
> >> > Reference varchar(50),
> >> > BalanceDue money,
> >> > DebtID int,
> >> > Reason varchar(100),
> >> > DebtStatus int,
> >> > )
> >> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> >> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> >> > ---
> >> >
> >> > I use DebtID as index because I use it again as a inner join to this
> >> > table
> >> > in another query. So I assume it will make much faster. All I want to
> >> > know
> >> is
> >> > would this code work?
> >> >
> >> >
> >>
> >>
> >>
>
>
Subscribe to:
Posts (Atom)