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 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
>
>
>