How to improve performance of the report's display while updating the table?

Hello, I need help to improve the performance of select statments in my repo
rt. My report is selecting (lots of data) from a attendance table, while a t
rigger is inserting more than 4000 rows of data into the table.
Can I use nolock table hints in my report? How can I use it?
For example,
select * from table a, table b, table c, table d
Should I write like this for the nolock when I want only the table b use nol
ock table hints:-
select * from table a, table b with (nolock), table c, table dAriel
Are you sure that these tables have no JOIN's condition?
This is a mistake which most users make when using the old-style JOIN syntax
is not specifying the join condition. The result set of your query is
Cartesian Product of four tables. it would generate every possible
combination of rows between them.
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:CBE065F9-8781-4727-BE82-2ECED244C30A@.microsoft.com...
> Hello, I need help to improve the performance of select statments in my
report. My report is selecting (lots of data) from a attendance table, while
a trigger is inserting more than 4000 rows of data into the table.
> Can I use nolock table hints in my report? How can I use it?
> For example,
> select * from table a, table b, table c, table d
> Should I write like this for the nolock when I want only the table b use
nolock table hints:-
> select * from table a, table b with (nolock), table c, table d|||As Uri says, make sure you have the correct join syntax. (The example you
posted is incorrect.) But the direct answer to your question is yes
select * From table with (nolock)
You WILL see data in the process of being changed in a transaction for which
the transaction has NOT yet committed ( dirty data)... Also look up in books
on line (readpast).
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:CBE065F9-8781-4727-BE82-2ECED244C30A@.microsoft.com...
> Hello, I need help to improve the performance of select statments in my
report. My report is selecting (lots of data) from a attendance table, while
a trigger is inserting more than 4000 rows of data into the table.
> Can I use nolock table hints in my report? How can I use it?
> For example,
> select * from table a, table b, table c, table d
> Should I write like this for the nolock when I want only the table b use
nolock table hints:-
> select * from table a, table b with (nolock), table c, table d|||Thanks Uri and Wayne.
Yes, my table got join statements within.
select * from table a, table b with (nolock), table c and table d
where a.id = b.id and
b.id = c.id and
c.id = d.id;
// the b table I assume is the attendance table.
uri, did you mean that my result will be wrong after I use the nolock syntax
?
Or you mean it will be wrong in terms of some of the data it linked will be
deleted or updated?
Thanks Again|||Uri was saying that your select has an error in it, but it does not. ( He
probably was just looking quickly.) Each one of the tables is in the where
clause, but you are using an OLD style syntax which is VERY prone to the
kinds of errors that Uri mentioned... Why don't you spend some time getting
used to and using the ANSI style join syntax - you'll be better off ... IE
select * from a inner join b with (nolock) on a.id = b.id
inner join c on b.id = c.id
inner join d on c.id = d.id;
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:8647D87B-C082-485D-8BFF-1B822C33FA8D@.microsoft.com...
> Thanks Uri and Wayne.
> Yes, my table got join statements within.
> select * from table a, table b with (nolock), table c and table d
> where a.id = b.id and
> b.id = c.id and
> c.id = d.id;
> // the b table I assume is the attendance table.
> uri, did you mean that my result will be wrong after I use the nolock
syntax?
> Or you mean it will be wrong in terms of some of the data it linked will
be deleted or updated?
> Thanks Again
>

how to improve performance of LEFT JOIN

I am developing reporting service and using lots of 'LEFT OUTER JOIN',
I am worried about the performance and want to use some subquery to
improve
the performance.
Could I do that like below,

[the origin source]
SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.item1 = TableB.item1
WHERE TableA.item2 = 'xxxx'
TableB.item2 > yyyy AND TableB.item2 < zzzz

I add the subquery to query every table before 'LEFT JOIN'
-----------------------
SELECT *
FROM
(SELECT *
FROM TableA
WHERE TableA.item2 = 'xxxx'
) TableC
LEFT OUTER JOIN
(SELECT *
FROM TableB
WHERE TableB.item2 > yyyy AND TableB.item2 < zzzz
) TableD
ON TableC.item1 = TableD.item1
WHERE TableC.item2 = 'xxxx'
TableD.item2 > yyyy AND TableD.item2 < zzzz
-----------------------

Can anyone give me some suggestion?
Thanks a lot.

Leland HuangBe careful. Your LEFT JOIN is actually an INNER JOIN in disguise (as well
as having a syntax error). Try:

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.item1 = TableB.item1
AND TableB.item2 > yyyy AND TableB.item2 < zzzz
WHERE TableA.item2 = 'xxxx'

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

<lelandhuang@.gmail.com> wrote in message
news:1147934223.710966.324990@.j33g2000cwa.googlegr oups.com...
I am developing reporting service and using lots of 'LEFT OUTER JOIN',
I am worried about the performance and want to use some subquery to
improve
the performance.
Could I do that like below,

[the origin source]
SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.item1 = TableB.item1
WHERE TableA.item2 = 'xxxx'
TableB.item2 > yyyy AND TableB.item2 < zzzz

I add the subquery to query every table before 'LEFT JOIN'
-----------------------
SELECT *
FROM
(SELECT *
FROM TableA
WHERE TableA.item2 = 'xxxx'
) TableC
LEFT OUTER JOIN
(SELECT *
FROM TableB
WHERE TableB.item2 > yyyy AND TableB.item2 < zzzz
) TableD
ON TableC.item1 = TableD.item1
WHERE TableC.item2 = 'xxxx'
TableD.item2 > yyyy AND TableD.item2 < zzzz
-----------------------

Can anyone give me some suggestion?
Thanks a lot.

Leland Huang|||lelandhuang@.gmail.com (lelandhuang@.gmail.com) writes:
> I am developing reporting service and using lots of 'LEFT OUTER JOIN',
> I am worried about the performance and want to use some subquery to
> improve
> the performance.
> Could I do that like below,
> [the origin source]
> SELECT *
> FROM TableA
> LEFT OUTER JOIN TableB
> ON TableA.item1 = TableB.item1
> WHERE TableA.item2 = 'xxxx'
> TableB.item2 > yyyy AND TableB.item2 < zzzz
> I add the subquery to query every table before 'LEFT JOIN'
> -----------------------
> SELECT *
> FROM
> (SELECT *
> FROM TableA
> WHERE TableA.item2 = 'xxxx'
> ) TableC
> LEFT OUTER JOIN
> (SELECT *
> FROM TableB
> WHERE TableB.item2 > yyyy AND TableB.item2 < zzzz
> ) TableD
> ON TableC.item1 = TableD.item1
> WHERE TableC.item2 = 'xxxx'
> TableD.item2 > yyyy AND TableD.item2 < zzzz
> -----------------------

This is a meaningless rewrite of the query, that at worst could server
to confuse the optimizer to give you a worse query plan. At best, the
optimizer will recast the second query into the first.

As Tom notes, the outer join is probably not correctly written. Assuming
that the query should read:

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.item1 = TableB.item1
AND TableB.item2 > yyyy AND TableB.item2 < zzz
WHERE TableA.item2 = 'xxxx'

The most important for the query to perform well, is that you have a
clustered index on TableA.item2 and an index (clustered or non-clustered)
on TableB.item1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx