Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
================================================== =====
SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid =
commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
================================================== =======
TIA
Kay
hi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ================================================== =====
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ================================================== =======
>
> TIA
> Kay
>
>
|||Its tough to tell you without more information about these tables, their keys
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itself.
HTH
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ================================================== =====
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ================================================== =======
>
> TIA
> Kay
>
>
sql

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
========================================
===============
SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid =
commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
========================================
=================
TIA
Kayhi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ========================================
===============
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ========================================
=================
>
> TIA
> Kay
>
>|||Its tough to tell you without more information about these tables, their key
s
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itsel
f.
HTH
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ========================================
===============
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ========================================
=================
>
> TIA
> Kay
>
>

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
======================================================= SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid = commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
=========================================================
TIA
Kayhi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:
> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> =======================================================> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid => commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> =========================================================>
> TIA
> Kay
>
>|||Its tough to tell you without more information about these tables, their keys
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itself.
HTH
"Kay" wrote:
> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> =======================================================> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid => commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> =========================================================>
> TIA
> Kay
>
>

Programmatically deleting Indexes

How do you programmatically delete ALL indexes for all tables in SQL Server?
Is this possible? I was thinking about using SQL DMO to do this.
adv-thanks-anceYou can iterate through the Indexes collection of a table and then use the
Remove method to drop the index.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Deleting indexes through code" <Deleting indexes through
code@.discussions.microsoft.com> wrote in message
news:F01486EA-4B47-4995-A3F5-498F92D2342E@.microsoft.com...
> How do you programmatically delete ALL indexes for all tables in SQL
> Server?
> Is this possible? I was thinking about using SQL DMO to do this.
> adv-thanks-ance|||ALL of the indexes for the ENTIRE SQL Server (including all databases) ?
Might want to test that out prior to implementing it on a production
machine.
Check out the Remove method of the Indexes collection in SQL-DMO.
T-SQL - could try something like this for one db (not fully tested):
SELECT 'DROP INDEX [' + OBJECT_NAME(ID) + '.' + NAME + ']'
FROM SYSINDEXES
WHERE NAME NOT LIKE 'SYS%' AND NAME NOT LIKE 'DT%' AND NAME NOT LIKE '_WA%'
AND OBJECT_NAME(ID) NOT LIKE 'SYS%'
AND OBJECT_NAME(ID) NOT LIKE 'MS%' AND OBJECT_NAME(ID) NOT LIKE 'DT%'
AND INDID >= 1 AND INDID < 255
ORDER BY OBJECT_NAME(ID), NAME
Then copy/paste/run the resultset in a query window in QA. Since an index
associated with a constraint cannot be dropped outside of dropping the
constraint, you might have to remove some of the entries listed and drop the
constraint manually. But this might help get you started.
HTH
Jerry
"Deleting indexes through code" <Deleting indexes through
code@.discussions.microsoft.com> wrote in message
news:F01486EA-4B47-4995-A3F5-498F92D2342E@.microsoft.com...
> How do you programmatically delete ALL indexes for all tables in SQL
> Server?
> Is this possible? I was thinking about using SQL DMO to do this.
> adv-thanks-ance|||The thing about dropping and re-creating indexes is that it involves
interrupting access to the database tables. Perhaps what you are wanting to
do is periodically defragment indexes. DBCC INDEXDEFRAG does not interrupt
queries and is typically faster than REINDEX.
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Deleting indexes through code" <Deleting indexes through
code@.discussions.microsoft.com> wrote in message
news:F01486EA-4B47-4995-A3F5-498F92D2342E@.microsoft.com...
> How do you programmatically delete ALL indexes for all tables in SQL
> Server?
> Is this possible? I was thinking about using SQL DMO to do this.
> adv-thanks-ance|||Take a look at this example:
http://milambda.blogspot.com/2005/0...in-current.html
...and tailor it to suit your needs.
ML|||If you use this be sure you increase or have ample room in the transaction
log for that database.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:4F244CDB-0144-4EB9-ADAD-7F36D423CB42@.microsoft.com...
> Take a look at this example:
> [url]http://milambda.blogspot.com/2005/07/defragment-all-indexes-in-current.html[/url
]
> ...and tailor it to suit your needs.
>
> ML|||Unlike REINDEX, which rebuilds the entire index in one large transaction,
INDEXDEFRAG only re-aranges the physical position of specific index pages as
needed, and each unit of work is in a seperate transaction, so it would
typically use less transaction logging.
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EFAA[
/url]
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23YMV3N6wFHA.2540@.TK2MSFTNGP09.phx.gbl...
> If you use this be sure you increase or have ample room in the transaction
> log for that database.
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:4F244CDB-0144-4EB9-ADAD-7F36D423CB42@.microsoft.com...
>