Looking to optimize a query

Hi, I have a query which is rather heavy which calculates commissions. It
is essentially a table of sales which relate to a table of commission
ranges and exceptions. Due to the way in which it works using left/right
joins does not work.
So, what I have done is a select in the following way
select
field1, field2, field3,
(select top 1 comission from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn)
from sales
and this works. However, I have to select more than one field from the comm
table, so I have
select
field1, field2, field3,
(select top 1 comission from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn),
(select top 1 range from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn),
(select top 1 comissionname from comm
where key1=field2 or key2=field2 or key3=field3
order by nnnn)
from sales
so in essence I am matching a row from the sales table to a single row in
the commisions table and taking three fields from the commisions table.
my problem ? - performance.
Is there any better way to do this? Is there a syntax to pull more than one
field from a subselect into the main query? Fields comission, range and
comissionname are all from the same row.
Alternatly is there any way to get a right join to only join the first row
which matches and not all ?
Regards
Ian MurphyIan Murphy wrote:
> Hi, I have a query which is rather heavy which calculates commissions. It
> is essentially a table of sales which relate to a table of commission
> ranges and exceptions. Due to the way in which it works using left/right
> joins does not work.
> So, what I have done is a select in the following way
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> and this works. However, I have to select more than one field from the com
m
> table, so I have
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 range from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 comissionname from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> so in essence I am matching a row from the sales table to a single row in
> the commisions table and taking three fields from the commisions table.
> my problem ? - performance.
> Is there any better way to do this? Is there a syntax to pull more than on
e
> field from a subselect into the main query? Fields comission, range and
> comissionname are all from the same row.
> Alternatly is there any way to get a right join to only join the first row
> which matches and not all ?
> Regards
> Ian Murphy
Haven't had my caffeine injection yet this morning, but I think
something like this is what you want. You should also review the
execution plan to look for potential bottlenecks:
SELECT
sales.field1,
sales.field2,
sales.field3,
comm.commission,
comm.range,
comm.commissionname
FROM sales sales
LEFT JOIN comm comm
ON
(
sales.field1 = comm.key1
OR sales.field2 = comm.key2
OR sales.field3 = comm.key3
)
WHERE comm.nnnnn =
(
SELECT MIN(nnnnn)
FROM comm
WHERE
(
sales.field1 = comm.key1
OR sales.field2 = comm.key2
OR sales.field3 = comm.key3
)
)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ian,
There are a few options. Here is one of them...
SELECT field1, field2, field3, comission, range, comissionname
FROM comm
INNER JOIN (
SELECT key1, key2, key3, MIN(nnnn) AS min_nnnn
FROM comm
GROUP BY key1, key2, key3
) T
ON T.key1 = comm.key1
AND T.key2 = comm.key2
AND T.key3 = comm.key3
AND T.min_nnnn = comm.nnnn
RIGHT JOIN sales
ON field1 = comm.key1
AND field2 = comm.key2
AND field3 = comm.key3
Hope this helps,
Gert-Jan
Ian Murphy wrote:
> Hi, I have a query which is rather heavy which calculates commissions. It
> is essentially a table of sales which relate to a table of commission
> ranges and exceptions. Due to the way in which it works using left/right
> joins does not work.
> So, what I have done is a select in the following way
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> and this works. However, I have to select more than one field from the com
m
> table, so I have
> select
> field1, field2, field3,
> (select top 1 comission from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 range from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn),
> (select top 1 comissionname from comm
> where key1=field2 or key2=field2 or key3=field3
> order by nnnn)
> from sales
> so in essence I am matching a row from the sales table to a single row in
> the commisions table and taking three fields from the commisions table.
> my problem ? - performance.
> Is there any better way to do this? Is there a syntax to pull more than on
e
> field from a subselect into the main query? Fields comission, range and
> comissionname are all from the same row.
> Alternatly is there any way to get a right join to only join the first row
> which matches and not all ?
> Regards
> Ian Murphy|||hmm, hadn't thought of using a subselect in the where condition. I'll have
a go and see if it speeds things up a bit.
Thanks
Ian|||On Mon, 17 Jul 2006 07:17:28 -0500, Tracy McKibben wrote:

>Haven't had my caffeine injection yet this morning, but I think
>something like this is what you want.
Hi Tracy,
Now that you've had your first cup of coffee, I'm sure you'll agree that
Ian will have to replace WHERE with AND in order to make it work as
required.

> You should also review the
>execution plan to look for potential bottlenecks:
>SELECT
> sales.field1,
> sales.field2,
> sales.field3,
> comm.commission,
> comm.range,
> comm.commissionname
>FROM sales sales
>LEFT JOIN comm comm
> ON
> (
> sales.field1 = comm.key1
> OR sales.field2 = comm.key2
> OR sales.field3 = comm.key3
> )
>WHERE comm.nnnnn =
> (
> SELECT MIN(nnnnn)
> FROM comm
> WHERE
> (
> sales.field1 = comm.key1
> OR sales.field2 = comm.key2
> OR sales.field3 = comm.key3
> )
> )
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Mon, 17 Jul 2006 07:17:28 -0500, Tracy McKibben wrote:
>
> Hi Tracy,
> Now that you've had your first cup of coffee, I'm sure you'll agree that
> Ian will have to replace WHERE with AND in order to make it work as
> required.
>
No, actually I intended that to be a WHERE clause. I think in this
instance it will work either way. '
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Tue, 18 Jul 2006 15:32:16 -0500, Tracy McKibben wrote:

>Hugo Kornelis wrote:
>No, actually I intended that to be a WHERE clause. I think in this
>instance it will work either way. '
Hi Tracy,
If every row in the sales table has at least one matching row in the
comm table, then it will work either way - but in that case, I see no
reason to use the LEFT JOIN instead of an INNER JOIN.
If there might be rows in the sales tabel without matching rows in the
comm table, then the LEFT JOIN will retain those rows, with NULL for all
values from the comm table (just as in Ian's original query) - but the
WHERE clause will throw them out after all, since it attempts to compare
NULL to the result of a subquery - and a comparison that involves NULL
can never evaluate to True.
Of course, since Ian didn't post anything to test it with, we don't know
if there can be any sales without commission in his database. :-)
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> If every row in the sales table has at least one matching row in the
> comm table, then it will work either way - but in that case, I see no
> reason to use the LEFT JOIN instead of an INNER JOIN.
> If there might be rows in the sales tabel without matching rows in the
> comm table, then the LEFT JOIN will retain those rows, with NULL for all
> values from the comm table (just as in Ian's original query) - but the
> WHERE clause will throw them out after all, since it attempts to compare
> NULL to the result of a subquery - and a comparison that involves NULL
> can never evaluate to True.
> Of course, since Ian didn't post anything to test it with, we don't know
> if there can be any sales without commission in his database. :-)
>
Doh! I see what you're talking about now... You're right, that belongs
in the join...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||There can indeed be sales without commissions. This is the easy part to
deal with. What has caused me a nightmare with this query is that the
commission is calculated depending on the discount. But the commission can
be different for the product, product family or the client in question. AND
it is applied in ranges, so a discount of 5% will earn 10% commission but a
discount of 15% will earn 3% and so on.
There is a simple order of priority to the above.
Thus for each line there are lots of possible commissions which could apply
(or none) but only the first should be selected, hence my solution of
select field1, field2, field3,
(select top 1 commission from comm table where xxxxxxx order by
zzzz,yyy,xxx)
but maybe I have to take another look at using a join.
Ian|||Unfortunatly I already tried that route. I can't use the min() function
because there is no row which will have a min value. The order of the
subselect will be by family, productid and client ref, meaning I don't have
a numeric or alphanumeric value which will be the lowest.
I can order the subselect so that the value I am after is the top row but
can't then pick it for the join.
I think my solution will be to subselect an index value from the comms
table and use that for the left join.
Thanks
Ian