Join with where clause?
-
What do you mean by 'performance'? Took longer? More IO? More CPU? Which of those statistics are you using?
The statistics that you posted are the client statistics, not the server. Try statistics io and statistics time.
Two queries with the same execution plan will execute identically, same io, same CPU and, with identical server conditions, same time. Only thing that can cause a difference is whether or not the data is in the cache and whether the query has to be compiled or not. Hence the reason that if you're doing performance testing, you clear the procedure and data cache before each run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Dear Gila, by better performance i mean less CPU time.... and i explain:
I did the following.... (hopefully correct):
I ran on the QA the follwing queries:
SET STATISTICS TIME Off
DBCC DROPCLEANBUFFERS -- to clear the buffers
SET STATISTICS TIME On
--query 1: Using all consitions in the ON clause.
select o.Orderid, o.line, i.item
from items i inner join orderlines o
ON i.Itemid = o.Itemid
AND o.Qty >= 100
...and i get
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(500545 row(s) affected)
SQL Server Execution Times:
CPU time = 1531 ms, elapsed time = 3095 ms.
..........then i ran:
--query 3: Using only WHERE clause.
SET STATISTICS TIME OFF
DBCC DROPCLEANBUFFERS -- to clear the buffers
SET STATISTICS TIME on
select o.Orderid, o.line, i.item
from items i, orderlines o
WHERE i.Itemid = o.Itemid
AND o.Qty >= 100
..and i get
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(500545 row(s) affected)
SQL Server Execution Times:
CPU time = 1437 ms, elapsed time = 3174 ms.
-------------------------------------
I compared the CPU time of query 1: 1531ms with the CPU time of query 2: 1437ms....
Did i make something wrong
?Indeed seems strange to me that query3 spends less CPU time.
------------
When you 've got a hammer, everything starts to look like a nail... -
One thing to note with the times is that they vary with other server activity. I ran the following on my server 5 times and got the following results: (exactly the same query every time)
select
Personid, callno from person join call on person.personid = call.personid
1) SQL Server parse and compile time:
CPU time = 17 ms, elapsed time = 17 ms.
SQL Server Execution Times:
CPU time = 160 ms, elapsed time = 1268 ms.
(76902 row(s) affected)
2) SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 16 ms.
SQL Server Execution Times:
CPU time = 170 ms, elapsed time = 1303 ms.
(76902 row(s) affected)
3) SQL Server parse and compile time:
CPU time = 17 ms, elapsed time = 17 ms.
SQL Server Execution Times:
CPU time = 130 ms, elapsed time = 1389 ms.
(76902 row(s) affected)
4) SQL Server parse and compile time:
CPU time = 17 ms, elapsed time = 17 ms.
SQL Server Execution Times:
CPU time = 150 ms, elapsed time = 1287 ms.
(76902 row(s) affected)
5) SQL Server parse and compile time:
CPU time = 17 ms, elapsed time = 17 ms.
SQL Server Execution Times:
CPU time = 190 ms, elapsed time = 1395 ms.
(76902 row(s) affected)
I got a similar spread from running the one that joined in the where.
When trying to work out what queries are faster than others, I generally use the IOs (less is better) and the cost as given by the execution plan, usually relative to some benchmark query I've got. Times vary too much with system activity to be particuarly meaningful, except when looking at large changes (8 sec to 1.5 sec)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yes... i agree that the meannig of "perfomance" depends on many aspects, such as
-network trafic
-network bandwidth
-cpu
-Data/Address bus width
-how busy is sql server (sql server trafic... i can say
)When I count performance I always run the query 3-5 times and I get the average. The average of what? (you might say...), well the average of every value I count (cpu time, IOs, etc)
------------
When you 've got a hammer, everything starts to look like a nail... -
As well noted, there are different kinds of 'performance', and different ways to measure it.
As I see it, there are two kinds that sometimes get mixed up, but I believe they should be kept separate.
First, you have the 'real' performance of a given query, namely how fast and efficient the server can resolve the particular query for you. This is quite easy to measure, since there is only one point where to do it (on the server itself).
Personally, I believe the I/O to be the best performance unit regarding if the query is efficient or not. Time units can be pretty loose, and also be affected by other things out of your control, while I/O (and the query plan to illustrate it) is more 'true'.
The other end of performance is when you measure it at the client end. This is often called 'percieved performance', and is a measurement on how good it 'feels'. This is more tricky, since there are a lot more involved (network, client performance, application etc) before the result is readily presented on the client. It's not uncommon that the server may resolve a query in subsecond time, but it takes ten seconds before the user 'feels' that it's done. (ie some Excel report or whatever). It's also not uncommon to then blame the server for being too slow, when it's really the inbetween layers that stand for most of the time.
Both kinds go together, though. The final judgement on if something's 'good enough' is often done from the percieved perfomance view, but when tuning, it's important to know in which place to tune what 'symptom'.
/Kenneth
-
How do LEFT JOINS and WHERE clauses work when you include a CASE stsement in the SELECT clause?
e.g
SELECT
O.OrderNo,
CASE WHEN A.TopUpLoan = 1 THEN A.VehAmt ELSE 0 END As 'Metal Margin Net'
FROM
C3Reports.dbo.tbl_Order O
INNER JOIN C3Reports.dbo.tbl_Agreement A WITH (NOLOCK) ON O.OrderNo = A.OrderNo
WHERE
A.[Current]= 1
Would a LEFT JOIN still become an INNER JOIN if a CASE statement included a reference to the field?
-
Using a CASE function (not statement) in the SELECT clause has no effect on the joins.
-
Per ANSI SQL 1999 the WHERE clause in a JOIN operation is a filter like the AND operator, and no LEFT JOIN can never be INNER JOIN because OUTER JOIN defaults to a mathematical NULL because both tables are not equal so table position is fixed. While INNER JOIN both tables are equal so you can change table position as needed to take advantage of indexes and other optimization if you do that with OUTER JOIN get funny results. Hope this helps.
Kind regards,
Gift Peddie -
Yes - having played with test data I've now realised that. Thanks for the reply.
However, I came across another strange thing. Using test data:
Table A
ID
1
2
3
4
5
Table AD
ID ADID
1 1
1 2
2 3
2 4
When you run:
SELECT
A.A
,CASE WHEN A.A = 1 THEN 1 ELSE 0 END As 'Count'
FROM
A
LEFT JOIN AD ON A.[A] = AD.[A] AND A.A = 1
you get 6 records with two 1's returned.
If you run:
SELECT
A.A
,CASE WHEN A.A = 1 THEN 1 ELSE 0 END As 'Count'
FROM
A
LEFT JOIN AD ON A.[A] = AD.[A] AND A.A = 3
you get 5 records with 1 of each returned.
Why does the first query return all records that mtach the clause and only one instance of the other matches?
-
The left join means it will return at least one copy of every row from the left-hand table. The first ON clause has "A.A = 1", so the first row of A matches two rows of AD and is duplicated, giving you a total of 6. The second ON clause has "A.A = 3" which only matches one row, so you get 5 rows total. Try adding a field from AD to the SELECT clause and you may see it more clearly.
-
LEFT JOIN AD ON A.[A] = AD.[A] AND A.A = 1
LEFT JOIN AD ON A.[A] = AD.[A] AND A.A = 3
Both are not using the same filter.
Kind regards,
Gift Peddie -
Thanks. So specifying A.A = 1 will return all matches for AD.A = 1 , but only distinct rows from the rest of A?
-
The result is dependent on what is in your AND filter now 2000 is funny it let people write code that 2005 will reject so I think you should run your test with both.
Kind regards,
Gift Peddie
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply