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.
>
Showing posts with label tablea. Show all posts
Showing posts with label tablea. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
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.
>
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.
>
Saturday, February 25, 2012
Query not returning all results
OK, interesting problem I haven't encountered before.
If I do the following query:
Select * from TableA where SequenceNo = 555 and Column = 6 and ClientNo = '123456'
I get 1 row (which is correct).
If I run the same query without the ClientNo specification, I get 4 results,
but Clientno 123456 isn't in the results.
If I run the same query, without the ClientNo specification, without the *
(i.e. manually listing each column), then I get 20 results, which is correct.
Why is this happening? Anyone know how to resolve this?
Manually listing each column is so not an option. Why isn't "Select * from
TableA where SequenceNo = 555 and Column = 6" to returning all 20 valid
results?
FYI: SequenceNo and Column are datatypes int, Clientno is varchar.
I've tried with and without single quotes, and also adding in "where
sequenceno is not null" kind of logic, too.
Argh!!!!!
--
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBANever mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!|||Sounds like you've got data corruption or, at least, integrity issues.
Probably because of the use of SELECT * and such.
If you are explicit about everything you do, then there will never be a
question concerning what it is you want.
Leave everything to defaults and you will get ambiguity, which is that start
of corruption.
Sorry to disappoint you, but you need to fix your data or start being more
explicit.
You could try a TOP clause to find the row that is causing you grief and,
then, manually fix the data.
Sincerely,
Anthony Thomas
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:773DC2E5-81C5-4AEA-A563-020065BE8943@.microsoft.com...
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!
If I do the following query:
Select * from TableA where SequenceNo = 555 and Column = 6 and ClientNo = '123456'
I get 1 row (which is correct).
If I run the same query without the ClientNo specification, I get 4 results,
but Clientno 123456 isn't in the results.
If I run the same query, without the ClientNo specification, without the *
(i.e. manually listing each column), then I get 20 results, which is correct.
Why is this happening? Anyone know how to resolve this?
Manually listing each column is so not an option. Why isn't "Select * from
TableA where SequenceNo = 555 and Column = 6" to returning all 20 valid
results?
FYI: SequenceNo and Column are datatypes int, Clientno is varchar.
I've tried with and without single quotes, and also adding in "where
sequenceno is not null" kind of logic, too.
Argh!!!!!
--
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBANever mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!|||Sounds like you've got data corruption or, at least, integrity issues.
Probably because of the use of SELECT * and such.
If you are explicit about everything you do, then there will never be a
question concerning what it is you want.
Leave everything to defaults and you will get ambiguity, which is that start
of corruption.
Sorry to disappoint you, but you need to fix your data or start being more
explicit.
You could try a TOP clause to find the row that is causing you grief and,
then, manually fix the data.
Sincerely,
Anthony Thomas
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:773DC2E5-81C5-4AEA-A563-020065BE8943@.microsoft.com...
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!
Query not returning all results
OK, interesting problem I haven't encountered before.
If I do the following query:
Select * from TableA where SequenceNo = 555 and Column = 6 and ClientNo =
'123456'
I get 1 row (which is correct).
If I run the same query without the ClientNo specification, I get 4 results,
but Clientno 123456 isn't in the results.
If I run the same query, without the ClientNo specification, without the *
(i.e. manually listing each column), then I get 20 results, which is correct
.
Why is this happening? Anyone know how to resolve this?
Manually listing each column is so not an option. Why isn't "Select * from
TableA where SequenceNo = 555 and Column = 6" to returning all 20 valid
results?
FYI: SequenceNo and Column are datatypes int, Clientno is varchar.
I've tried with and without single quotes, and also adding in "where
sequenceno is not null" kind of logic, too.
Argh!!!!!
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBANever mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!|||Sounds like you've got data corruption or, at least, integrity issues.
Probably because of the use of SELECT * and such.
If you are explicit about everything you do, then there will never be a
question concerning what it is you want.
Leave everything to defaults and you will get ambiguity, which is that start
of corruption.
Sorry to disappoint you, but you need to fix your data or start being more
explicit.
You could try a TOP clause to find the row that is causing you grief and,
then, manually fix the data.
Sincerely,
Anthony Thomas
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:773DC2E5-81C5-4AEA-A563-020065BE8943@.microsoft.com...
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!
If I do the following query:
Select * from TableA where SequenceNo = 555 and Column = 6 and ClientNo =
'123456'
I get 1 row (which is correct).
If I run the same query without the ClientNo specification, I get 4 results,
but Clientno 123456 isn't in the results.
If I run the same query, without the ClientNo specification, without the *
(i.e. manually listing each column), then I get 20 results, which is correct
.
Why is this happening? Anyone know how to resolve this?
Manually listing each column is so not an option. Why isn't "Select * from
TableA where SequenceNo = 555 and Column = 6" to returning all 20 valid
results?
FYI: SequenceNo and Column are datatypes int, Clientno is varchar.
I've tried with and without single quotes, and also adding in "where
sequenceno is not null" kind of logic, too.
Argh!!!!!
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBANever mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!|||Sounds like you've got data corruption or, at least, integrity issues.
Probably because of the use of SELECT * and such.
If you are explicit about everything you do, then there will never be a
question concerning what it is you want.
Leave everything to defaults and you will get ambiguity, which is that start
of corruption.
Sorry to disappoint you, but you need to fix your data or start being more
explicit.
You could try a TOP clause to find the row that is causing you grief and,
then, manually fix the data.
Sincerely,
Anthony Thomas
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:773DC2E5-81C5-4AEA-A563-020065BE8943@.microsoft.com...
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!
Query not returning all results
OK, interesting problem I haven't encountered before.
If I do the following query:
Select * from TableA where SequenceNo = 555 and Column = 6 and ClientNo =
'123456'
I get 1 row (which is correct).
If I run the same query without the ClientNo specification, I get 4 results,
but Clientno 123456 isn't in the results.
If I run the same query, without the ClientNo specification, without the *
(i.e. manually listing each column), then I get 20 results, which is correct.
Why is this happening? Anyone know how to resolve this?
Manually listing each column is so not an option. Why isn't "Select * from
TableA where SequenceNo = 555 and Column = 6" to returning all 20 valid
results?
FYI: SequenceNo and Column are datatypes int, Clientno is varchar.
I've tried with and without single quotes, and also adding in "where
sequenceno is not null" kind of logic, too.
Argh!!!!!
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!
|||Sounds like you've got data corruption or, at least, integrity issues.
Probably because of the use of SELECT * and such.
If you are explicit about everything you do, then there will never be a
question concerning what it is you want.
Leave everything to defaults and you will get ambiguity, which is that start
of corruption.
Sorry to disappoint you, but you need to fix your data or start being more
explicit.
You could try a TOP clause to find the row that is causing you grief and,
then, manually fix the data.
Sincerely,
Anthony Thomas
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:773DC2E5-81C5-4AEA-A563-020065BE8943@.microsoft.com...
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!
If I do the following query:
Select * from TableA where SequenceNo = 555 and Column = 6 and ClientNo =
'123456'
I get 1 row (which is correct).
If I run the same query without the ClientNo specification, I get 4 results,
but Clientno 123456 isn't in the results.
If I run the same query, without the ClientNo specification, without the *
(i.e. manually listing each column), then I get 20 results, which is correct.
Why is this happening? Anyone know how to resolve this?
Manually listing each column is so not an option. Why isn't "Select * from
TableA where SequenceNo = 555 and Column = 6" to returning all 20 valid
results?
FYI: SequenceNo and Column are datatypes int, Clientno is varchar.
I've tried with and without single quotes, and also adding in "where
sequenceno is not null" kind of logic, too.
Argh!!!!!
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!
|||Sounds like you've got data corruption or, at least, integrity issues.
Probably because of the use of SELECT * and such.
If you are explicit about everything you do, then there will never be a
question concerning what it is you want.
Leave everything to defaults and you will get ambiguity, which is that start
of corruption.
Sorry to disappoint you, but you need to fix your data or start being more
explicit.
You could try a TOP clause to find the row that is causing you grief and,
then, manually fix the data.
Sincerely,
Anthony Thomas
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:773DC2E5-81C5-4AEA-A563-020065BE8943@.microsoft.com...
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!
Subscribe to:
Posts (Atom)