Query performance

Hi
There are two tables TableA and TableB , where TableA consists of 100,000
rows and TableB consits of 60,000 rows.
In the TableA Id is the Primary Key (Clustered Index) and in the TableB
MessageId is the Foreign Key for TableA (Id), which is a non-Clustered Index.
When i Write a query by joining two tables which will give the rows that
satisfies some condition.
Ex: SELECT TableA.Id FROM TableA
INNER JOIN TableA.Id = TableB.MessageId
WHERE TableB.ResourceId = 12
This is taking more than a second to retrieve 1200 rows to get the matching
Ids with the specified criteria.
Cannot we decrease efficiency of the query ? what are the other options so
that the query performance is decreased.
maybe you can put in the tableA some of the most requested filters.
For example, add a resourceId column in tableA, this eliminate the join.
But before this, what is the principal usage of your database?
OLTP system? (so operational) or informational.
What response time do you expect?
I think your bottleneck is your processor, the tables are small. Look at the
statistics behind your queries to find what to do or change.
"Ramnadh" <Ramnadh@.discussions.microsoft.com> a crit dans le message de
news: C8D3EF70-DD5D-4C2E-B46F-DEADC291A8FC@.microsoft.com...
> Hi
> There are two tables TableA and TableB , where TableA consists of 100,000
> rows and TableB consits of 60,000 rows.
> In the TableA Id is the Primary Key (Clustered Index) and in the TableB
> MessageId is the Foreign Key for TableA (Id), which is a non-Clustered
> Index.
> When i Write a query by joining two tables which will give the rows that
> satisfies some condition.
> Ex: SELECT TableA.Id FROM TableA
> INNER JOIN TableA.Id = TableB.MessageId
> WHERE TableB.ResourceId = 12
> This is taking more than a second to retrieve 1200 rows to get the
> matching
> Ids with the specified criteria.
> Cannot we decrease efficiency of the query ? what are the other options so
> that the query performance is decreased.
|||Ramnadh,
The example you have here is not a valid query (it's an INNER JOIN
without an ON clause). In order to speculate on why a query is slower
than you like, it's important to see the exact query. Can you cut and
paste an example that you've tested in Query Analyzer, and if possible,
include the table definitions and indexes and constraints, as well?
Steve Kass
Drew University
Ramnadh wrote:

>Hi
>There are two tables TableA and TableB , where TableA consists of 100,000
>rows and TableB consits of 60,000 rows.
>In the TableA Id is the Primary Key (Clustered Index) and in the TableB
>MessageId is the Foreign Key for TableA (Id), which is a non-Clustered Index.
>When i Write a query by joining two tables which will give the rows that
>satisfies some condition.
>Ex: SELECT TableA.Id FROM TableA
> INNER JOIN TableA.Id = TableB.MessageId
> WHERE TableB.ResourceId = 12
>This is taking more than a second to retrieve 1200 rows to get the matching
>Ids with the specified criteria.
>Cannot we decrease efficiency of the query ? what are the other options so
>that the query performance is decreased.
>

Query performance

Hi
There are two tables TableA and TableB , where TableA consists of 100,000
rows and TableB consits of 60,000 rows.
In the TableA Id is the Primary Key (Clustered Index) and in the TableB
MessageId is the Foreign Key for TableA (Id), which is a non-Clustered Index.
When i Write a query by joining two tables which will give the rows that
satisfies some condition.
Ex: SELECT TableA.Id FROM TableA
INNER JOIN TableA.Id = TableB.MessageId
WHERE TableB.ResourceId = 12
This is taking more than a second to retrieve 1200 rows to get the matching
Ids with the specified criteria.
Cannot we decrease efficiency of the query ? what are the other options so
that the query performance is decreased.maybe you can put in the tableA some of the most requested filters.
For example, add a resourceId column in tableA, this eliminate the join.
But before this, what is the principal usage of your database?
OLTP system? (so operational) or informational.
What response time do you expect?
I think your bottleneck is your processor, the tables are small. Look at the
statistics behind your queries to find what to do or change.
"Ramnadh" <Ramnadh@.discussions.microsoft.com> a écrit dans le message de
news: C8D3EF70-DD5D-4C2E-B46F-DEADC291A8FC@.microsoft.com...
> Hi
> There are two tables TableA and TableB , where TableA consists of 100,000
> rows and TableB consits of 60,000 rows.
> In the TableA Id is the Primary Key (Clustered Index) and in the TableB
> MessageId is the Foreign Key for TableA (Id), which is a non-Clustered
> Index.
> When i Write a query by joining two tables which will give the rows that
> satisfies some condition.
> Ex: SELECT TableA.Id FROM TableA
> INNER JOIN TableA.Id = TableB.MessageId
> WHERE TableB.ResourceId = 12
> This is taking more than a second to retrieve 1200 rows to get the
> matching
> Ids with the specified criteria.
> Cannot we decrease efficiency of the query ? what are the other options so
> that the query performance is decreased.|||Ramnadh,
The example you have here is not a valid query (it's an INNER JOIN
without an ON clause). In order to speculate on why a query is slower
than you like, it's important to see the exact query. Can you cut and
paste an example that you've tested in Query Analyzer, and if possible,
include the table definitions and indexes and constraints, as well?
Steve Kass
Drew University
Ramnadh wrote:
>Hi
>There are two tables TableA and TableB , where TableA consists of 100,000
>rows and TableB consits of 60,000 rows.
>In the TableA Id is the Primary Key (Clustered Index) and in the TableB
>MessageId is the Foreign Key for TableA (Id), which is a non-Clustered Index.
>When i Write a query by joining two tables which will give the rows that
>satisfies some condition.
>Ex: SELECT TableA.Id FROM TableA
> INNER JOIN TableA.Id = TableB.MessageId
> WHERE TableB.ResourceId = 12
>This is taking more than a second to retrieve 1200 rows to get the matching
>Ids with the specified criteria.
>Cannot we decrease efficiency of the query ? what are the other options so
>that the query performance is decreased.
>