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 and locking problems - urget

Dear Gurus,

A Client has the following problems/requests for their Production
databases, what is your professional/practical advises to tackle and
resolve these issues:

1)Number of Transactions per day The current database can currently
handle about 5000 order transactions per day

2)Table locking When doing row updates SQL Server will lock the
whole table. For example after a few instances of a driver updating an
order record it will lock the entire orders table

3)Reports When a user runs a report involving big quantities of
order-related data other processes will suffer occasional timeouts

4)Archiving There is no efficient way of archiving historical system
dataDavid McGeorge (soalvajavab1@.yahoo.com) writes:
> A Client has the following problems/requests for their Production
> databases, what is your professional/practical advises to tackle and
> resolve these issues:

I'm afraid that your questions are far too open-ended for it to be
possible to give an exhaustive answer in a newsgroup post. Had I been
a consultant, I would have been tempted to refer you the web site for
my business. Judging from your mail address you're a web programmer,
and from your questions it appears that you client is a need of someone
with SQL expertise.

> 1)Number of Transactions per day ? The current database can currently
> handle about 5000 order transactions per day

What do you mean with "can"? Is 5000 transactions/day the actual load?
Or is the database at the maximum of its capacity with that rate? I
would assume the former, since 5000 transactions/day is a low number,
unless the transactions are extremely complex.

> 2)Table locking ? When doing row updates SQL Server will lock the
> whole table. For example after a few instances of a driver updating an
> order record it will lock the entire orders table

This sounds like the indexing of the database needs to be improved.
If you say:

UPDATE tbl
SET col = 23
WHERE othercol = 12

And there is no index on othercol, SQL Server will have no choice but
to lock the entire table.

One tool to improve indexing in the database is the Index Tuning Wizard.
You give it a day's workload, and it will suggest suitable indexes to add.

> 3)Reports ? When a user runs a report involving big quantities of
> order-related data other processes will suffer occasional timeouts

Reports are often problem in OLTP databases, because they need to scan
lots of data. Indexing can help, but if user can build their own reports,
or there are report functions with lots of selection criteria, this
may not be feasible. Not the least, if the users can accept that an
odd report takes 20-30 minutes to run.

Therefore many sites sets up a report database, which is a copy of the
live database. Depending on the requirements, the report database can
be loaded from yesterday's backup, or you could keep it updated with
log shipping or replication. The latter is required if users must have
today's data in their reports, but it takes more effort to get there.

> 4)Archiving ? There is no efficient way of archiving historical system
> data

This point is completely impossible to address without knowing the
business requirements.

--
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 and data structure question

Hi SQL gurus,

I have a table structure question. I will have a table 'Models' that
has one to many 'incomes' and one to many 'costs'. These 2 entities
have exactly the same structure, which is 7 smallmoney and a name. Is
it better to create a table 'Incomes' and a table 'Costs', with both
the same number of fields like this :

Incomes
----
in_idmodel
in_1
in_2
in_3
in_4
in_5
in_6
in_7
in_name

Costs
----
c_idmodel
c_1
c_2
c_3
c_4
c_5
c_6
c_7
c_name

or is it better to create one single table that will contain both
entities like that :

Incomes_Costs
----
ic_idmodel
ic_1
ic_2
ic_3
ic_4
ic_5
ic_6
ic_7
ic_name
ic_isIncome

which only differs from the 2 above by the isIncome field to know which

row is an income and which row is a cost.

I'd like to know which method is the best in terms of performance and
general structure and would greatly appreciate if you explain a little
the reasons that drove you to suggest me a method over the other.

Thanks all for your time!

ibizaBy no means a "Guru"...but If I'm following you...

This is the age old question of "to normalize" or not to "normalize".

By splitting the tables you achieve a higher level of normalization.
This will cause queries etc to run somewhat quicker..but you wll take a
hit in terms of storage space.

By making one table...you will speed up your queries...but waste space.

HTH

MJKulangara
http://sqladventures.blogspot.com|||Ok, I am really speeding up my queries (by 1/1000ths of second -_-)with
two table? I thought it would be the inverse...how come?

And about the storage, why would it take more space? I mean, if I have
4 incomes and 4 costs splitted into two tables, it's about the same
amout of data as 4+4 rows in the same table (I suppose the only
difference is one bit for each row for the 1 table layout VS some tiny
space used for the definition of one more table in the 2 tables
layout?)

The point here is I want to get to know these little subtleties...

thanks for your advice!

ibiza|||ibiza (lambertb@.gmail.com) writes:
> I have a table structure question. I will have a table 'Models' that
> has one to many 'incomes' and one to many 'costs'. These 2 entities
> have exactly the same structure, which is 7 smallmoney and a name. Is
> it better to create a table 'Incomes' and a table 'Costs', with both
> the same number of fields like this :

That is not really possible to answer without further knowledge about
the business domain. And, even I would have it, I would maybe still be
a trade-off for me which way to do it.

A key here is how related they are. If they mirror each other, and
are two sides of the same coin, it may make sense to have them in same
table. If they are unrelated, they should not be in the same table.

Another observation:

> Incomes
> ----
> in_idmodel
> in_1
> in_2
> in_3
> in_4
> in_5
> in_6
> in_7
> in_name

Maybe it makes sense to have seven columns, but a more conventional
design would be to have a main table:

CREATE TABLE incomes (in_idmodel int NOT NULL,
in_name varchar(30) NOT NULL,
CONSTRAINT pk PRIMARY KEY(in_idmodel))

CREATE TABLE incomerows (in_idmodel int NOT NULL,
rowno smallint NOT NULL,
value smallmoney NOT NULL,
CONSTRAINT pk2 PRIMARY KEY (id_idmodel, rowno))

An important advantage is that this design is not tied to fixed number
of levels.

--
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|||hmmm. i'd think you would be happier with even more abstraction then
either option.

have one table for models. it has the modelid, and the modelname, and
one row for each model.
then have one table for "dollars."
this will have "dollarid", a flag for cost or income, and a human
name. there will be one row for each type of income or cost, so the
total rows will be something like 14.

then have the "xref" table. It will have modelid, dollarid, and
dollaramt.
this will have one row for each model for each cost/income.

the advantage is that over time new costs or incomes will show up, and
you can easily add them. further, you don't have to have really awful
program logic to add up all of your costs and income for a specfiic
model.
hope this helps,
doug

Performance and data structure question

Hi SQL gurus,
I have a table structure question. I will have a table 'Models' that
has one to many 'incomes' and one to many 'costs'. These 2 entities
have exactly the same structure, which is 7 smallmoney and a name. Is
it better to create a table 'Incomes' and a table 'Costs', with both
the same number of fields like this :
Incomes
in_idmodel
in_1
in_2
in_3
in_4
in_5
in_6
in_7
in_name
Costs
c_idmodel
c_1
c_2
c_3
c_4
c_5
c_6
c_7
c_name
or is it better to create one single table that will contain both
entities like that :
Incomes_Costs
ic_idmodel
ic_1
ic_2
ic_3
ic_4
ic_5
ic_6
ic_7
ic_name
ic_isIncome
which only differs from the 2 above by the isIncome field to know which
row is an income and which row is a cost.
I'd like to know which method is the best in terms of performance and
general structure and would greatly appreciate if you explain a little
the reasons that drove you to suggest me a method over the other.
Thanks all for your time!
ibiza
Ibiza,
for a while no response to your post, I'll post mine. I would not claim me
being an SQL guru (the reason people shied away from responding?), though I
consider myself a seasoned SQL Server professional.
To me there is no doubt that they should be two separate tables. Even
though they look structurally alike, logically they are different things.
They may be used together in some purpose (like calculating net profit), but
one is the cost and the other one is the income.
One thing made them seemingly alike is your way of naming the columns, which
I think is not so optimal and introduced the illusion. Instead of in_1,
in_2, c_1, c_2 etc., I would rather name them TV_ad, Newspaper_ad, etc.,
that way you know from the name of the columns what for income or expense is
being referred to, and you would not have the illusion that they might be
the same thing. Beyond, after the 7 or so items, when in the future you
realize there are other cost that not have an income counterpart (we
actually can not talk about counterpart, but just let's say so), you would
benefit from the separated tables.
Further beyond, I am questioning whether those two tables are the right
approach. To me I should have a income table and a cost table, with the
columns id, source (or expense_item for cost), amount. your id would map to
the Model table so you would not have the name in the cost or income tables.
You may want to have check tables for the source or expense_item as
constraint for input to the income or cost table. Whether to use these
check tables is a question of denormalization, you may choose not to use it,
just like you have chosen to denormalize your Models and Income/Cost tables.
Quentin
"ibiza" <lambertb@.gmail.com> wrote in message
news:1138898200.231048.286540@.g47g2000cwa.googlegr oups.com...
> Hi SQL gurus,
> I have a table structure question. I will have a table 'Models' that
> has one to many 'incomes' and one to many 'costs'. These 2 entities
> have exactly the same structure, which is 7 smallmoney and a name. Is
> it better to create a table 'Incomes' and a table 'Costs', with both
> the same number of fields like this :
> Incomes
> --
> in_idmodel
> in_1
> in_2
> in_3
> in_4
> in_5
> in_6
> in_7
> in_name
> Costs
> --
> c_idmodel
> c_1
> c_2
> c_3
> c_4
> c_5
> c_6
> c_7
> c_name
> or is it better to create one single table that will contain both
> entities like that :
> Incomes_Costs
> --
> ic_idmodel
> ic_1
> ic_2
> ic_3
> ic_4
> ic_5
> ic_6
> ic_7
> ic_name
> ic_isIncome
> which only differs from the 2 above by the isIncome field to know which
> row is an income and which row is a cost.
> I'd like to know which method is the best in terms of performance and
> general structure and would greatly appreciate if you explain a little
> the reasons that drove you to suggest me a method over the other.
> Thanks all for your time!
> ibiza
>
|||Thank you very much for your advice Quentin.
As for my simplistic naming of rows with only numbers, it's because an
income/cost consists of 7 values, each one corresponding to a year. The
middle column(in_4) corresponds to the value of the actual year, the 3
columns before correspond each to years before the actual year and the
3 columns after correspond each to years before the actual year. For
example, if the model was created in 2006, then in_1 would represent
income for year 2003, in_2 : 2004, in_3 : 2005, in_4 : 2006, in_5 :
2007, in_6 : 2008 and in_7 : 2009. As a models can be created for any
year, I cannot really associate each column of income/cost to a
name...I hope I'm not too foggy here and you still understand my logic.
Am I still doing it the correct way?
Thanks a lot!
ibiza

Performance and data structure question

Hi SQL gurus,
I have a table structure question. I will have a table 'Models' that
has one to many 'incomes' and one to many 'costs'. These 2 entities
have exactly the same structure, which is 7 smallmoney and a name. Is
it better to create a table 'Incomes' and a table 'Costs', with both
the same number of fields like this :
Incomes
--
in_idmodel
in_1
in_2
in_3
in_4
in_5
in_6
in_7
in_name
Costs
--
c_idmodel
c_1
c_2
c_3
c_4
c_5
c_6
c_7
c_name
or is it better to create one single table that will contain both
entities like that :
Incomes_Costs
--
ic_idmodel
ic_1
ic_2
ic_3
ic_4
ic_5
ic_6
ic_7
ic_name
ic_isIncome
which only differs from the 2 above by the isIncome field to know which
row is an income and which row is a cost.
I'd like to know which method is the best in terms of performance and
general structure and would greatly appreciate if you explain a little
the reasons that drove you to suggest me a method over the other.
Thanks all for your time!
ibizaIbiza,
for a while no response to your post, I'll post mine. I would not claim me
being an SQL guru (the reason people shied away from responding?), though I
consider myself a seasoned SQL Server professional.
To me there is no doubt that they should be two separate tables. Even
though they look structurally alike, logically they are different things.
They may be used together in some purpose (like calculating net profit), but
one is the cost and the other one is the income.
One thing made them seemingly alike is your way of naming the columns, which
I think is not so optimal and introduced the illusion. Instead of in_1,
in_2, c_1, c_2 etc., I would rather name them TV_ad, Newspaper_ad, etc.,
that way you know from the name of the columns what for income or expense is
being referred to, and you would not have the illusion that they might be
the same thing. Beyond, after the 7 or so items, when in the future you
realize there are other cost that not have an income counterpart (we
actually can not talk about counterpart, but just let's say so), you would
benefit from the separated tables.
Further beyond, I am questioning whether those two tables are the right
approach. To me I should have a income table and a cost table, with the
columns id, source (or expense_item for cost), amount. your id would map to
the Model table so you would not have the name in the cost or income tables.
You may want to have check tables for the source or expense_item as
constraint for input to the income or cost table. Whether to use these
check tables is a question of denormalization, you may choose not to use it,
just like you have chosen to denormalize your Models and Income/Cost tables.
Quentin
"ibiza" <lambertb@.gmail.com> wrote in message
news:1138898200.231048.286540@.g47g2000cwa.googlegroups.com...
> Hi SQL gurus,
> I have a table structure question. I will have a table 'Models' that
> has one to many 'incomes' and one to many 'costs'. These 2 entities
> have exactly the same structure, which is 7 smallmoney and a name. Is
> it better to create a table 'Incomes' and a table 'Costs', with both
> the same number of fields like this :
> Incomes
> --
> in_idmodel
> in_1
> in_2
> in_3
> in_4
> in_5
> in_6
> in_7
> in_name
> Costs
> --
> c_idmodel
> c_1
> c_2
> c_3
> c_4
> c_5
> c_6
> c_7
> c_name
> or is it better to create one single table that will contain both
> entities like that :
> Incomes_Costs
> --
> ic_idmodel
> ic_1
> ic_2
> ic_3
> ic_4
> ic_5
> ic_6
> ic_7
> ic_name
> ic_isIncome
> which only differs from the 2 above by the isIncome field to know which
> row is an income and which row is a cost.
> I'd like to know which method is the best in terms of performance and
> general structure and would greatly appreciate if you explain a little
> the reasons that drove you to suggest me a method over the other.
> Thanks all for your time!
> ibiza
>|||Thank you very much for your advice Quentin.
As for my simplistic naming of rows with only numbers, it's because an
income/cost consists of 7 values, each one corresponding to a year. The
middle column(in_4) corresponds to the value of the actual year, the 3
columns before correspond each to years before the actual year and the
3 columns after correspond each to years before the actual year. For
example, if the model was created in 2006, then in_1 would represent
income for year 2003, in_2 : 2004, in_3 : 2005, in_4 : 2006, in_5 :
2007, in_6 : 2008 and in_7 : 2009. As a models can be created for any
year, I cannot really associate each column of income/cost to a
name...I hope I'm not too foggy here and you still understand my logic.
Am I still doing it the correct way?
Thanks a lot!
ibiza

Performance and data structure question

Hi SQL gurus,
I have a table structure question. I will have a table 'Models' that
has one to many 'incomes' and one to many 'costs'. These 2 entities
have exactly the same structure, which is 7 smallmoney and a name. Is
it better to create a table 'Incomes' and a table 'Costs', with both
the same number of fields like this :
Incomes
--
in_idmodel
in_1
in_2
in_3
in_4
in_5
in_6
in_7
in_name
Costs
--
c_idmodel
c_1
c_2
c_3
c_4
c_5
c_6
c_7
c_name
or is it better to create one single table that will contain both
entities like that :
Incomes_Costs
--
ic_idmodel
ic_1
ic_2
ic_3
ic_4
ic_5
ic_6
ic_7
ic_name
ic_isIncome
which only differs from the 2 above by the isIncome field to know which
row is an income and which row is a cost.
I'd like to know which method is the best in terms of performance and
general structure and would greatly appreciate if you explain a little
the reasons that drove you to suggest me a method over the other.
Thanks all for your time!
ibizaIbiza,
for a while no response to your post, I'll post mine. I would not claim me
being an SQL guru (the reason people shied away from responding?), though I
consider myself a seasoned SQL Server professional.
To me there is no doubt that they should be two separate tables. Even
though they look structurally alike, logically they are different things.
They may be used together in some purpose (like calculating net profit), but
one is the cost and the other one is the income.
One thing made them seemingly alike is your way of naming the columns, which
I think is not so optimal and introduced the illusion. Instead of in_1,
in_2, c_1, c_2 etc., I would rather name them TV_ad, Newspaper_ad, etc.,
that way you know from the name of the columns what for income or expense is
being referred to, and you would not have the illusion that they might be
the same thing. Beyond, after the 7 or so items, when in the future you
realize there are other cost that not have an income counterpart (we
actually can not talk about counterpart, but just let's say so), you would
benefit from the separated tables.
Further beyond, I am questioning whether those two tables are the right
approach. To me I should have a income table and a cost table, with the
columns id, source (or expense_item for cost), amount. your id would map to
the Model table so you would not have the name in the cost or income tables.
You may want to have check tables for the source or expense_item as
constraint for input to the income or cost table. Whether to use these
check tables is a question of denormalization, you may choose not to use it,
just like you have chosen to denormalize your Models and Income/Cost tables.
Quentin
"ibiza" <lambertb@.gmail.com> wrote in message
news:1138898200.231048.286540@.g47g2000cwa.googlegroups.com...
> Hi SQL gurus,
> I have a table structure question. I will have a table 'Models' that
> has one to many 'incomes' and one to many 'costs'. These 2 entities
> have exactly the same structure, which is 7 smallmoney and a name. Is
> it better to create a table 'Incomes' and a table 'Costs', with both
> the same number of fields like this :
> Incomes
> --
> in_idmodel
> in_1
> in_2
> in_3
> in_4
> in_5
> in_6
> in_7
> in_name
> Costs
> --
> c_idmodel
> c_1
> c_2
> c_3
> c_4
> c_5
> c_6
> c_7
> c_name
> or is it better to create one single table that will contain both
> entities like that :
> Incomes_Costs
> --
> ic_idmodel
> ic_1
> ic_2
> ic_3
> ic_4
> ic_5
> ic_6
> ic_7
> ic_name
> ic_isIncome
> which only differs from the 2 above by the isIncome field to know which
> row is an income and which row is a cost.
> I'd like to know which method is the best in terms of performance and
> general structure and would greatly appreciate if you explain a little
> the reasons that drove you to suggest me a method over the other.
> Thanks all for your time!
> ibiza
>|||Thank you very much for your advice Quentin.
As for my simplistic naming of rows with only numbers, it's because an
income/cost consists of 7 values, each one corresponding to a year. The
middle column(in_4) corresponds to the value of the actual year, the 3
columns before correspond each to years before the actual year and the
3 columns after correspond each to years before the actual year. For
example, if the model was created in 2006, then in_1 would represent
income for year 2003, in_2 : 2004, in_3 : 2005, in_4 : 2006, in_5 :
2007, in_6 : 2008 and in_7 : 2009. As a models can be created for any
year, I cannot really associate each column of income/cost to a
name...I hope I'm not too foggy here and you still understand my logic.
Am I still doing it the correct way?
Thanks a lot!
ibiza

Percentiles in SQL Server 2005

Gurus,

I need to find the 50th, 75th and 90th percentiles for a set of values. For eg: if I have values 1, 2, 3, and 4 and need to get 2.5, 3.25 and 3.7 as the 50th, 75th and 90th percentiles. Since there was no aggregare function in SQL Server 2005 to deal with it, I think user defined aggregate functions was the answer. Can you direct me and give helpful tips.

Regards

Zacharia

You can use the new analytic functions like ROW_NUMBER, RANK, NTILE etc to calculate other functions. For example, you can simulate PERCENT_RANK function available in Excel like:

WITH test_ranked
AS
(
SELECT t.id, t.score

, RANK() OVER(ORDER BY t.score) as rank

, COUNT(*) OVER() as cnt
FROM test as t
)
SELECT p.id, p.score

, CASE p.cnt

-- for single row, the PERCENT_RANK is 0
WHEN 1 THEN CAST(0 AS FLOAT)
ELSE (p.rank-1.0)/(p.cnt -1.0)
END as percent_rank
FROM test_ranked as p
ORDER BY p.id;

|||

Thanks Umachandar for your reply. But I did not understand how the percentrank function can be used to solve this problem. In the above example of values 1, 2, 3 and 4 percentrank gives me a column of 0, 0.33, 0.66 and 1. The NTILE(100) returns a column of 1, 2, 3 and 4. How does that help when I need the values 2.5, 3.25 and 3.7 as the 50th, 75th and 90th percentiles. I do not really understand how the 75th and 90th percentiles are calculated. But for the 50th percentile it is the (n/2 +1)th value when n is odd and and avg(n/2, n/2 + 1) when n is even.

Sorry I do require more help

Zacharia

|||

Umachandar,

Thanks for your suggestion. I used the approach given here http://sqlteam.com/item.asp?ItemID=16480. Since I had Visual Studio 2005 Standard Edition, I could not create a User Defined Aggregate:-((. I relied on the stored procedure itself.

Zac