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

Performance comparison between VARCHAR and CLOB?

Any idea on performance comparison between VARCHAR and CLOB? For example, if I want to insert or update 4000 characters in a field of a row and I am confused whether that column's type would better be decalred as VARCHAR or CLOB, what do you suggest?I suggest you not declare anything as a CLOB in sql server.

performance : load xml file vs query xml field

Does anyone have any benchmarks or general knowledge regarding the performance System.XML 2.0 and SS05 for these two scenarios:

1. XPathDocument xpathdoc = New XPathDocument(pathToXMLFile);

2. storing the contents of the xml file in SS05 ( in a col of XML datatype ) , and using SQLHelper to load it into an xpathdoc
We haven't done such measurements. For case #2, you have a make a roundtrip to the server, which will be more costly than reading from the file, although I cannot say by how much.

I think a fairer comparison is to compare the processing for case #1 with running the query on the XML data type column at the server. XML indexes on the XML column can significantly speed up queries.

Thank you,

Shankar

Performance

In a table with 400.000 rows there is a ntext field that contains html
code of about 15/20 k for each row.
Do you think that if I set <field> = '' should I increase performance
for new insert or I have to delete rows?
Any help appreciated.
Regards.
Fabri
(Lattepi chi pu darti di pi?)Hi
No, leave it as NULL until you use it. With NULL, there is no pointer
allocated for the NTEXT data. As soon as you supply some value, the pointer
needs to be allocated against a page.
Regards
Mike
"Fabri" wrote:

> In a table with 400.000 rows there is a ntext field that contains html
> code of about 15/20 k for each row.
> Do you think that if I set <field> = '' should I increase performance
> for new insert or I have to delete rows?
> Any help appreciated.
> Regards.
> --
> Fabri
> (Lattepiù chi può darti di più?)
>|||Mike Epprecht (SQL MVP) wrote:
> Hi
> No, leave it as NULL until you use it. With NULL, there is no pointer
> allocated for the NTEXT data. As soon as you supply some value, the pointe
r
> needs to be allocated against a page.
Thx Mike, how can I "give" the space to the OS?
I tried so:
dbcc shrinkdatabase ('<db>', truncateonly)
but it seems to not give space to os...
dbcc shrinkdatabase ('<db>') results in very-very-long transaction and I
should avoid this solution...
Regards.
Fabri
(Lattepiù chi può darti di più?)|||Hi
If your data is not on different days then you could default the date when
you store it and then you would not need to use convert, otherwise you may
want to think about storing the time separately.
John
"vanitha" wrote:

> hi,
> my query
> Select A.COL1,B.COL1 from
> A,B
> where convert(varchar(8),A.COL3,108)= convert(varchar(8),B.COL3,108)
> provided COL3 is part of the key in both the tables.
> So if we try to convert the index of the table to time alone the performan
ce
> of the query is affected and it's taking a longer time to execute...
> how to solve this?
> thanks
> vanitha
>

percentage values in reports

hello,
i have a percentage field which returns 0.50 i want to report to show it as
50% is there any way to do this
appreciate any helpYes. Set the format property to p0 where p means percentage and 0 is the
precision p0 = 50% p1 = 50.4% p2=50.41% etc.
"repalley" wrote:
> hello,
> i have a percentage field which returns 0.50 i want to report to show it as
> 50% is there any way to do this
> appreciate any help

Percentage of occurrences of a value

I want to be able to retrieve the percentage that one value occurs in a field in a group. For example, a field that has either "Yes" or "No", if there are 3 "Yes" values and 1 "No" value (using mixed real and fake SQL..)

SELECT DATEPART(m,datefield), PECENTAGEOFAVALUE(YesNoField, 'Yes')
FROM sampletable
GROUP BY DATEPART(m,datefield)
Result
75

How can I do this? I know I can get the denominator by just doing a COUNT(*), but how do I count "Yes" only?

Thanks,
Kayda

Here it is..

Code Snippet

Create Table #data (

[Id] int ,

[Response] Char

);

Insert Into #data Values('1','Y');

Insert Into #data Values('2','N');

Insert Into #data Values('3','Y');

Insert Into #data Values('4','Y');

Insert Into #data Values('5','N');

Insert Into #data Values('6','N');

Insert Into #data Values('7','N');

Select

Isnull(Sum(Case When Response='Y' Then 1 End),0) / Sum(1.0) *100,

Isnull(Sum(Case When Response='N' Then 1 End),0) / Sum(1.0) *100

From

#Data

Code Snippet

--For Your query

SELECT

DATEPART(m,datefield)

Isnull(Sum(Case When YesNoField='Yes' Then 1 End),0) / Sum(1.0) * 100 PecentageofYes,

Isnull(Sum(Case When YesNoField='No' Then 1 End),0) / Sum(1.0) * 100 PecentageofNo

FROM

sampletable

GROUP BY

DATEPART(m,datefield)

|||

Here is an example:

Code Snippet

create table #t (

c1 datetime not null,

c2 char(1) not null

);

insert into #t values('20070101','y');

insert into #t values('20070115','n');

insert into #t values('20070125','y');

insert into #t values('20070205','y');

insert into #t values('20070217','n');

insert into #t values('20070225','n');

insert into #t values('20070227','n');

;with sums

as

(

select

convert(char(6), c1, 112) as ym,

sum(case when c2 = 'y' then 1 else 0 end) as sum_y,

sum(case when c2 = 'n' then 1 else 0 end) as sum_n,

count(*) over() as cnt

from

#t

group by

convert(char(6), c1, 112)

)

select

ym,

(sum_y * 100.00) / nullif(cnt, 0) as avg_y,

(sum_n * 100.00) / nullif(cnt, 0) as avg_n

from

sums

order by

ym

drop table #t

go

AMB