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

query not working

Hi
look at the queries
1.
Examination_timetable consists of rows like this
ClassId ExamDate SubId Invigilation
CLD00001 3/12/2006 SUB00001 STA00001,STA00002
select invigilation from Examination_timetable where examdate > '2/12/2006'
and subid='SUB00001' and classid='CLD00001'
then i got following correct answer which is correct.
invigilation
STA00001,STA00002
2.
select staffid from staff_details,faculty_type where facultytype='teaching'
and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
('STA00001','STA00002')
then i didnt get any rows which is also correct, since except
STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
teaching staff's id's).
3.
now i joine teh both 1st and 2nd queries like this
select staffid from staff_details,faculty_type where facultytype='teaching'
and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
(select invigilation from Examination_timetable where examdate > '2/12/2006'
and subid='SUB00001' and classid='CLD00001')
then i'm getting result like this, whics is wrong result
StaffId
STA00001
STA00002
it shouldn't display any rows since no rows matches the given condition.
why it is displaying wrong answer when i mixed 2 separates queries in to
single query, where both individually giving correct answer?
hope you got my query.
can any one tell me where i went wrong?
thanx in advance
yoshithaHow about
select staffid from staff_details INNER JOIN faculty_type
ON staff_details.ftypeid=faculty_type.ftypeid INNER JOIN
Examination_timetable ON staff_details.staffid NOT LIKE '%' +
Examination_timetable.Invigilation + '%'
where facultytype='teaching'
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Hi
> look at the queries
> 1.
> Examination_timetable consists of rows like this
> ClassId ExamDate SubId Invigilation
> CLD00001 3/12/2006 SUB00001 STA00001,STA00002
>
> select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001'
>
> then i got following correct answer which is correct.
> invigilation
> STA00001,STA00002
>
> 2.
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> ('STA00001','STA00002')
> then i didnt get any rows which is also correct, since except
> STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
> teaching staff's id's).
>
> 3.
> now i joine teh both 1st and 2nd queries like this
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> (select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001')
>
> then i'm getting result like this, whics is wrong result
> StaffId
> STA00001
> STA00002
> it shouldn't display any rows since no rows matches the given condition.
> why it is displaying wrong answer when i mixed 2 separates queries in to
> single query, where both individually giving correct answer?
> hope you got my query.
> can any one tell me where i went wrong?
> thanx in advance
> yoshitha
>
>
>|||Your usage of IN is wrong. When you get the "invigilationID", it is a comma
separated string. However, for IN to search through this, you cannot
substitute this string directly for the IN clause, but rather, you need to
do it dynamically. Here is an example that shows how it does not work:
=====
DECLARE @.testString VARCHAR(100)
SET @.testString = 'Test1, Test2'
SELECT CASE WHEN 'Test1' IN (@.testString) THEN 1 ELSE 0 END
=====
When you execute the above snippet, you will always get 0 (although Test1 is
present inside the string). To get around this problem, you can do the
following
(1) Use dynamic SQL to build your query as shown:
=====
DECLARE @.testString VARCHAR(100)
DECLARE @.strSQL VARCHAR(8000)
SET @.testString = '''Test1'', ''Test2'''
SET @.strSQL = 'SELECT CASE WHEN ''Test1'' IN (' + @.testString + ') THEN 1
ELSE 0 END'
EXEC (@.strSQL)
=====
(2) Use CHARINDEX as shown:
=====
DECLARE @.testString VARCHAR(100)
SET @.testString = 'Test1, Test2'
SELECT CASE WHEN CHARINDEX('Test1', @.testString) > 0 THEN 1 ELSE 0 END
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Hi
> look at the queries
> 1.
> Examination_timetable consists of rows like this
> ClassId ExamDate SubId Invigilation
> CLD00001 3/12/2006 SUB00001 STA00001,STA00002
>
> select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001'
>
> then i got following correct answer which is correct.
> invigilation
> STA00001,STA00002
>
> 2.
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> ('STA00001','STA00002')
> then i didnt get any rows which is also correct, since except
> STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
> teaching staff's id's).
>
> 3.
> now i joine teh both 1st and 2nd queries like this
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> (select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001')
>
> then i'm getting result like this, whics is wrong result
> StaffId
> STA00001
> STA00002
> it shouldn't display any rows since no rows matches the given condition.
> why it is displaying wrong answer when i mixed 2 separates queries in to
> single query, where both individually giving correct answer?
> hope you got my query.
> can any one tell me where i went wrong?
> thanx in advance
> yoshitha
>
>
>|||still it is displaying both ids.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eO5oVEHMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> How about
> select staffid from staff_details INNER JOIN faculty_type
> ON staff_details.ftypeid=faculty_type.ftypeid INNER JOIN
> Examination_timetable ON staff_details.staffid NOT LIKE '%' +
> Examination_timetable.Invigilation + '%'
> where facultytype='teaching'
>
>
> "yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
> news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>|||Well, can you post DDL+ sample data + expected result for both tables?
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:e9CoRrHMGHA.2992@.tk2msftngp13.phx.gbl...
> still it is displaying both ids.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eO5oVEHMGHA.1032@.TK2MSFTNGP11.phx.gbl...
>