complex joins

All,
I have a messy query with alot of where clause. here is the original
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1,
tbl_2,
#TempTbl
where tbl_2.ticketnum = tbl_1.ticketnum
and #TempTbl.productserial = tbl_1.productserial
and #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
I have altered the query to jjoin on the first 2 where's like this
select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
join #TempTbl as c on c.productserial = a.productserial
where #TempTbl.issuercode = tbl_1.issuercode
and #TempTbl.productcode = tbl_1.productcode
and tbl_2.price = #TempTbl.price
and convert(varchar(10),tbl_1.setldate,101) =
convert(varchar(10),#TempTbl.settlement,101)
and tbl_1.acctnum = #TempTbl.accountNumber
and #TempTbl.rownum = @.ctr
and tbl_2.trademethod = 'T'
and tbl_1.TradeType = 'S'
and tbl_1.status <> 'C'
and tbl_2.timestamp >= @.tbl_2From
and tbl_2.timestamp <= @.tbl_2To
and substring(tbl_1.misccode,8,1) = 1
and (substring(tbl_1.misccode,13,1)= '0' or substring(tbl_1.misccode,13,1)
is null)
In this example I still have a ton of where clause that says
" where a.col x = b.col x
and
a.col y = b.col y"
I think that there has to be a more efficient way of dealing with this!
Please help!
D"Detroit" <Detroit@.discussions.microsoft.com> wrote in message
news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
> All,
> I have a messy query with alot of where clause. here is the original
>
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1,
> tbl_2,
> #TempTbl
> where tbl_2.ticketnum = tbl_1.ticketnum
> and #TempTbl.productserial = tbl_1.productserial
> and #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> I have altered the query to jjoin on the first 2 where's like this
> select @.Count = count(*), @.TicketTotal = sum(tbl_2.qty) /1000 from
> tbl_1 as a join tbl_2 as b on a.ticketnum = b.ticketnum
> join #TempTbl as c on c.productserial = a.productserial
>
> where #TempTbl.issuercode = tbl_1.issuercode
> and #TempTbl.productcode = tbl_1.productcode
> and tbl_2.price = #TempTbl.price
> and convert(varchar(10),tbl_1.setldate,101) =
> convert(varchar(10),#TempTbl.settlement,101)
> and tbl_1.acctnum = #TempTbl.accountNumber
> and #TempTbl.rownum = @.ctr
> and tbl_2.trademethod = 'T'
> and tbl_1.TradeType = 'S'
> and tbl_1.status <> 'C'
> and tbl_2.timestamp >= @.tbl_2From
> and tbl_2.timestamp <= @.tbl_2To
> and substring(tbl_1.misccode,8,1) = 1
> and (substring(tbl_1.misccode,13,1)= '0' or
> substring(tbl_1.misccode,13,1)
> is null)
>
> In this example I still have a ton of where clause that says
> " where a.col x = b.col x
> and
> a.col y = b.col y"
> I think that there has to be a more efficient way of dealing with this!
> Please help!
> D
Without looking closely at your statements, if these are the necessary
conditions for the join to work properly and for the data you wish to
retrieve, then there is really no way around the issue with your current
schema.
You could simplify this procedure a bit by creating some views and the
querying on those views, however, at some point in the process, all of these
join conditions are going to need to occur.
Your other option is to change your schema. You *could* denormalize tables
and decrease the number of join issues and so forth, but this is generally
not recommended.
Rick Sawtell
MCT, MCSD, MCDBA|||I'd say that you need to properly NORMALIZE this beast. However, there are
a few issues here:
SELECT @.Count = COUNT(*)
,@.TicketTotal = SUM(tbl_2.qty) / 1000
FROM tbl_1
INNER JOIN
tbl_2
ON tbl_1.ticketnum = tbl_2.ticketnum
INNER JOIN
#TempTbl
ON #TempTbl.productserial = tbl_1.productserial
AND #TempTbl.issuercode = tbl_1.issuercode
AND #TempTbl.productcode = tbl_1.productcode
AND tbl_1.acctnum = #TempTbl.accountNumber
WHERE #TempTbl.rownum = @.ctr
AND tbl_2.trademethod = 'T'
AND tbl_1.TradeType = 'S'
AND tbl_1.status <> 'C'
AND tbl_2.timestamp BETWEEN @.tbl_2From AND @.tbl_2To
AND tbl_2.price = #TempTbl.price
AND CONVERT(VARCHAR(10), tbl_1.setldate, 101) =
CONVERT(VARCHAR(10), #TempTbl.settlement, 101)
AND SUBSTRING(tbl_1.misccode,8,1) = 1
AND (SUBSTRING(tbl_1.misccode,13,1)= '0'
OR SUBSTRING(tbl_1.misccode,13,1) IS NULL
)
Let's start by using proper syntax: JOIN conditions should be ANSI compliant
and be seperated from the Restriction Clause.
Ok, so the temp table has a composite key, could be better, but four columns
isn't too bad; however, there were numerous conditions that one couldn't
tell if it was a JOIN condition or a resultset restriction. Those I left in
the WHERE clause.
Most of what's there is properly formed restrictions; however, the equality
of price between the tables serves what purpose? This could be a JOIN
condition, but seems odd but shouldn't kill the performance.
Here's the good stuff. Those datetime to character conversions will kill
you. Is there some point in striping the time portion?
The substrings will kill your performance too. But, even better, you are
conditioning on multiple values within the same field just different
positions. You could have considered a LIKE operation in stead:
tbl_1.misccode LIKE '_______1____0%'. However, because you haven't prefixed
the first position, there aren't too many index strategies that will
help...like none. Then there is the AND value OR IS NULL. You can't
produce a NULL value from a SUBSTRING function unless the whole column were
NULL to begin with but, then, so would have the prior evaluation to 1 on
position 8--which was stated as a numeric but should have been character '1'
to boot.
Yes this could use some help, like some design as to what we are really
aiming at here.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:urkdFoaxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> "Detroit" <Detroit@.discussions.microsoft.com> wrote in message
> news:89A81763-5B8D-4876-9552-7C364B5A7A03@.microsoft.com...
>
> Without looking closely at your statements, if these are the necessary
> conditions for the join to work properly and for the data you wish to
> retrieve, then there is really no way around the issue with your current
> schema.
> You could simplify this procedure a bit by creating some views and the
> querying on those views, however, at some point in the process, all of
these
> join conditions are going to need to occur.
> Your other option is to change your schema. You *could* denormalize
tables
> and decrease the number of join issues and so forth, but this is generally
> not recommended.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>