Hi there,

Need a little help with a certain query that's causing a lot of acid
in my stomach...

Have a table that stores sales measures for a given client. The sales
measures are stored per year and there could be multiple sales
measures every year per client. There is another field called last
update date. If there are multiple sales measures then need to select
the one that's been entered last based on this field. Also, if there
is no sales measure data for current year then I need to return the
last year's data for which sales measure has been entered. For
example: if client #1 has sales measure value of $200 for 1999 and
nothing since, then I need to return $200 for any year following 1999.

So the query would look something like this:

SELECT client_name, sm_dollars FROM <tables>

Based on the DDL at the bottom I would expect to get back: c1, 100;
c2, 200

The way I am doing it now is with correlated subqueries (3 to be
exact) that each do an aggregate and join back to the original table.
It works, but it is notoriously slow. SQL Server is scanning the
index and does a merge join which in a large query takes %95 of the
time. Here is the part of the query plan for it:

| | | | | | |--Merge
Join(Inner Join, MANY-TO-MANY
MERGE:([sales_measure].[client_id])=([sales_measure].[client_id]),RESIDUAL:(([sales_measure].[client_id]=[sales_measure].[client_id]
AND [sales_measure].[tax_year]=[sales_measure].[tax_year]) AND
[Expr1013]=[sales_measure].[last_update_date]))
| | | | | | |--Stream
Aggregate(GROUP BY:([sales_measure].[client_id],
[sales_measure].[tax_year])
DEFINE:([Expr1013]=MAX([sales_measure].[last_update_date])))
| | | | | | | |--Merge
Join(Inner Join, MERGE:([sales_measure].[client_id],
[Expr1010])=([sales_measure].[client_id], [sales_measure].[tax_year]),
RESIDUAL:([sales_measure].[client_id]=[sales_measure].[client_id] AND
[sales_measure].[tax_year]=[Expr1010]))
| | | | | | |
|--Stream Aggregate(GROUP BY:([sales_measure].[client_id])
DEFINE:([Expr1010]=MAX([sales_measure].[tax_year])))
| | | | | | | |
|--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),
ORDERED FORWARD)
| | | | | | |
|--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),
ORDERED FORWARD)
| | | | | | |--Index
Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),
ORDERED FORWARD)


There are two indexes on sales measure table:

sales_measure_pk - sales_measure_id (primary key) clustered
sales_measure_idx1 - client_id, tax_year, last_update_date, sm_dollars

sales_measure table has 800,000 rows in it.

Here is the rest of the DDL:

IF OBJECT_ID('dbo.client') IS NOT NULL
DROP TABLE dbo.client
GO
create table dbo.client (
client_id int identity primary key
, client_name varchar(100) NOT NULL)
GO
IF OBJECT_ID('dbo.sales_measure') IS NOT NULL
DROP TABLE dbo.sales_measure
GO
create table dbo.sales_measure(
sales_measure_id int identity primary key
, client_id int NOT NULL
, tax_year smallint NOT NULL
, sm_dollars money NOT NULL
, last_update_date datetime NOT NULL)
GO
CREATE INDEX sales_measure_idx1 ON sales_measure (client_id, tax_year,
last_update_date, sm_dollars)
GO
INSERT dbo.client(client_name)
SELECT 'c1' UNION SELECT 'c2' UNION SELECT 'c3'
GO
INSERT dbo.sales_measure(client_id, tax_year, sm_dollars,
last_update_date)
SELECT 1, 2004, 100, '1/4/2004'
UNION
SELECT 2, 2003, 100, '1/3/2004'
UNION
SELECT 2, 2004, 150, '1/4/2004'
UNION
SELECT 2, 2004, 200, '1/5/2004'


The view that I use to calculate sales measures:

CREATE VIEW sales_measure_vw AS
SELECT sm.*
FROM sales_measure sm
INNER JOIN (SELECT sm2.client_id, sm2.tax_year,
MAX(sm2.last_update_date) as last_update_date
FROM sales_measure sm2
INNER JOIN (SELECT sm4.client_id, MAX(sm4.tax_year)
as tax_year
FROM sales_measure sm4 GROUP BY
sm4.client_id) sm3
on sm3.client_id = sm2.client_id
and sm3.tax_year = sm2.tax_year
GROUP BY sm2.client_id, sm2.tax_year ) sm1
ON sm.client_id = sm1.client_id AND
sm.tax_year = sm1.tax_year AND
sm.last_update_date = sm1.last_update_date

Any advice on how to tame this would be appreciated. Also, any advice
on the indexes would help as well.

Thanks

Do you really have clients with 100 character names? Well, if you
don't take the time to design the columns in the tables properly, you
will. Why do you think that an IDENTITY can ever be used as a key? The
tax year is computed from the creation date, so you have a redundancy.
Isn't the real key for the sales measures is (client_id,
creation_date)? You are missing DRI. Unless you have only one client
and one sales measurement, you ought to use plural or collective nouns
for the table names; tables are sets, not scalars.

You used the proprietary MONEY data type instead of a valid SQL
datatype. The ISO-8601 standard date format "yyyy-mm-dd" is the only
one allowed in Standard SQL. So cleaning up the DDL, we get something
like:

CREATE TABLE Clients
(client_id INTEGER NOT NULL PRIMARY KEY,
client_name VARCHAR(35) NOT NULL);

CREATE TABLE SalesMeasures
(client_id INTEGER NOT NULL
REFERENCES Clients (client_id),
creation_date DATETIME NOT NULL,
sm_dollars DECIMAL(12,4) NOT NULL,
PRIMARY KEY (client_id, creation_date));

[quoted text, click to view]
sales measures are stored per year and there could be multiple sales
measures every year per client. There is another field [sic] called
last update date. If there are multiple sales measures then need to
select the one that's been entered last based on this field [sic]. <<

Fields and columns are totally different concepts. What you have is a
history of the Sales measures. You keep adding to it, not updating
it.

[quoted text, click to view]
need to return the last year's data for which sales measure has been
entered. For example: if client #1 has sales measure value of $200 for
1999 and nothing since, then I need to return $200 for any year
following 1999. <<

How do you tell if the $200 in the year 2000 is a real or constructed
value? You are destroying information. Missing data is what a NULL
is used for. If you want to do this, then do it in the front end.

One of the standard programming tricks in SQL is to build a Calendar
table to hold all of the temporal data for your enterprise - fiscal
years, reporting periods, work days, holidays, etc. Let's assume that
you have a VIEW or a derived table on this Calendar that returns just
a list of years for which you want reports.

SELECT CL.cal_year, C1.client_id, S1.creation_date, S1.sm_dollars
FROM ((SELECT DISTINCT EXTRACT(YEAR FROM cal_date) AS cal_year
FROM Calendar
WHERE cal_date BETWEEN @start_date AND @end_date)
CROSS JOIN
(SELECT client_name FROM Clients)) AS C1 (cal_year,
client_name)
LEFT OUTER JOIN
SalesMeasures AS S1
ON S1.creation_date
= (SELECT MAX(S2.creation_date)
FROM SalesMeasures AS S2
WHERE S1.client_id = S2.client_id
AND EXTRACT(YEAR FROM S2.creation_date)
= C1.cal_year);
The cross join builds all combinations of clients and years. The
outer join will give you the latest creation date within each year for

[posted and mailed, please reply in news]

Bob (boblotz2001@yahoo.com) writes:
[quoted text, click to view]

Could you assume that the most recent LastUpdated also refers to the
most recent year? Or could the figures for 2002 be updated after those
for 2002? If not, you could take a shortcut.

[quoted text, click to view]

Actually, those are derived tables, not correlated subqueries. A correlated
subquery correlates to the outer query, by refering to tables in the
outer query. Here, each derived table is independent of what is outside
of it.

If your query produces the right result, then I would not rewrite it.
Better look at the indexing. Here are some suggestions:

1) Drop sales_measure_id and make (client_id, tax_year, last_updated)
the primary key of the table. This saves four bytes per row from
each query, so you can only win. If you cannot drop this key, then
make it non-clustered.
2) Replace sales_measure_idx1, by two indexes a non-clustered index on
(client_id, tax_year) and a clustered index on (client_id, tax_year,
last_updated). Possibly also a non-clustered index on this triplet
as well. Don't include sm_dollars in any index.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Hi Bill,

Thanks for your reply. Here are few comments:

[quoted text, click to view]

Actually, the tax year is not computed. A user can input sales
measure data for 5 years back or anytime for that metter. So the
fields need to be separated.

[quoted text, click to view]

There could be multiple entries per client per creation_date so the
combonation of these two fields is not unique.

[quoted text, click to view]

If I understand correctly, you are suggesting that I create at least
one entry for each client per year and enter NULL as a dollar amount.
I am not sure what you mean by "do it in the front end". I think I
would have to return a lot more data to the front end then I would
like.

[quoted text, click to view]

I am having difficulties running this SQL. There is no alias on the
first subquery in the cross join and also SQL Server is not
understanding some of the syntax.

Thanks

[quoted text, click to view]
measure data for 5 years back or anytime for that metter. So the
fields [sic] need to be separated. <<

That does not make any sense. If I put in *any* date, I can extract a
year from it.

[quoted text, click to view]
combination of these two fields is not unique. <<

So what is the real key? If you don't have one, then this is not a
real table and you cannot expect to get a relational answer to the
query.

[quoted text, click to view]
least one entry for each client per year and enter NULL as a dollar
amount. I am not sure what you mean by "do it in the front end". I
think I would have to return a lot more data to the front end then I
would like. <<

If you wanted one row per client per year, then the number of rows are
the same if you use NULL or a bogus "carry-forward" value. But the
NULL shows that there was actually no value for that client that year.
The NULL can then be handled in the front end application to "destroy
the truth" for your report.

[quoted text, click to view]

Since you cross-posted in the general database newsgroup, I gave you
Standard SQL-92 code (well, the at-sign -- aka "petite escarot" --
should have been colon). A little trick; you can test code for
conformance at this website:

Hi Erland,

Thanks for your reply. Your suggestion with indexes did not help.
The query plan did not change for the better with the suggested
indexes in place.

Thanks

Bob

[quoted text, click to view]