Showing posts with label folks. Show all posts
Showing posts with label folks. Show all posts

Monday, March 26, 2012

Performance degrading placing join in WHERE instead of FROM block (using =, =*, *=)

Hello folks,
first of all I really don't know how you gurus call this way of
writing joins:

SELECT
A.FIELD,
B.FIELD
FROM
TABLE_A A,
TABLE_B B
WHERE
A.ID_FIELD = B.ID_FIELD

I find this way very useful and readable. It works also with left and
right Joins (using *= or =* instead of = )

A friend of mine found that the inner join way (using = ) in Access is
much more slower than using the classic INNER JOIN TABLE ON FIELD
sintax. My question is: was MSSQL Server studied for using the short
way, or it is just a workaround found by someone? Is there a
performance degrade folllowing this way?

TIA,
tKtekanet (tekanet@.inwind.it) writes:
> first of all I really don't know how you gurus call this way of
> writing joins:
> SELECT
> A.FIELD,
> B.FIELD
> FROM
> TABLE_A A,
> TABLE_B B
> WHERE
> A.ID_FIELD = B.ID_FIELD
> I find this way very useful and readable.

The alternative way of writing this in so-called ANSI JOINS is:

SELECT a.field, b.field
FROM table_a a
JOIN table_b b ON a.id_field = b.id_field

These two are equvialent, both in terms of function and performance. The
optimizer will normalize both to the same internal representation.

Which one you prefer is a matter of taste. I used the method with
the join condition in the WHERE clause for many years, and I was
skeptic when I first saw the JOIN syntax. But I've changed my mind.
For a query that joins 7-8 tables and with multi-column conditions,
the JOIN syntax gives you a lot better overview, and it is also easier
to verify that you have included all conditions. The WHERE clause is
then left to proper filtering.

But, again, this is a matter of taste. Both ways of writing the JOIN is
OK by SQL Server and by ANSI.

> It works also with left and right Joins (using *= or =* instead of = )

But when it comes to outer joins, it's a whole other story. In short,
don't use *= and =*. They are deprecated, and there are all sorts of
issues with them. When it comes to outer joins, the ANSI syntax really
shines. You can do a full join, which you can't do with *=*, because
there is no such operator. You can outer join to a pair of tables which
is the result of an inner join. You can control evaluation order (which
matters for outer joins.) There is a whole lot more you can do - and
you can actuall see what you are doing. (Complex queries with *= are
far from clear-cut.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 21, 2012

Performance Confusion

Hi,
Can any of you folks take a minute to tell me why the results of the attached test are weird. I'm trying to come up with a method of allowing my business layer to interact with the data layer - specifically a nice, easy to maintain, but safe method of passing parameters without adversely impacting performance.

This is only a few of my sketchy ideas so far but I'm stuck because I cannot prove that using OR is slower than a straightforward = because each time I run the test the OR seems to perform better. Even changing the sequence in which the piece work still does alter the OR begin better than =.

And, of course, as that makes no sense whatsoever I cannto really progress.

Any thought would be appreciated. I hope I have altered the script enough that you can just run it straight in and see what I'm talking about. I only introduced the transactions to see if any of that stuff my be interfering.

Thanks.Presumably FLOWBOOL and FLOWUSER are UDTs? Anything special about them? Just bit and varchar?

EDIT - oops - FLOWBOOL is char(1) or similar|||Here are the results from my poor pathetic test box (maybe a PIII)

XML Join XML Variables OR Coalesce = Dynamic
---- ---- ---- ---- ---- ----
270 200 10 10 10 30

It pretty much shows what I knew before (openXML is an expensive operation). The main reason queries that use OR tend to be slower is that they have a reduced chance of hitting an index on the table. In this case, the optimizer may be smart enough to check the @.pName variable only once per query, instead of once per row in the resultset. This would strip out all of the extra comparisons. Whether that one comparison operation really matters that much, is a separate question. About all you can really say is to try to avoid using openXML in queries, and only use it to populate variables which are used in the queries.

[EDIT: Nice post, by the way]|||damn, sorry about those. Yes char(1) (don't ask!!!) and varchar(20)|||Wow, MCrowley, the performance differences you got are completely different than mine - i.e. that the xml route was soooo much slower. My real interest is in why the OR and the = methods don't differ significantly.|||Note - If you are using a small table and looping a query multiple times it might not give you accurate performance indications...

If the real table will be larger, indexes will play a more important role than a table small enough to fit in memory. You also need to consider caching of data rows in memory and execution plans...|||They do not differ significantly, because the OR gets its bad reputation when it misses an index. On your table (5 rows), that is not nearly a consideration. Try a test on a table of 10,000 rows or so, but make sure the equals hits the index. Otherwise, you just have two slow queries vying for the worst performance.|||Sorry, been away. Thanks for those responses.

I tried this on a much bigger table and the results were more like what I would have expected (well, some of them were). I'm disappointed, however, because I wanted to create a more clever mechanism of building the search parameters without having such a tight contract between say, C#, code and the database.

For a 36823 record table the results are a significantly different:

XML Join: 10953
XML Variables: 3640
Or: 3470
Coalesce: 5483
=: 16
Dynamic: 33

Surprises here, for me (non database person if you haven't guessed :)) are that dynamic sql takes second position, 'or' outperforms coalesce and that XML variables are quite performant!?