Re: how can i make this query run faster

From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 12/28/04 

Date: Tue, 28 Dec 2004 13:01:10 +0100

alvis,

As others have mentioned: your query is very hard to read, because of
your table naming and especially because of the JOIN .. JOIN .. ON .. ON
syntax. I would definitely rewrite that so that each JOIN is immediately
followed by its ON clause (so: JOIN .. ON .. JOIN .. ON ..).

1. Table tbl_ldf_uw is LEFT JOINed to tbl_ldf_loaninit, but then table
tbl_ldf_last_pmt is INNER JOINed to tbl_ldf_uw. This means that this
LEFT JOIN can be changed to INNER JOIN.

2. Table tbl_myprograms is LEFT JOINed to tbl_ldf_mtgtype, but then
table tbl_su_Investors is INNER JOINed to tbl_myprograms. This means
that this LEFT JOIN can be changed to INNER JOIN.

3. Table tbl_comtrades is LEFT JOINed to tbl_prc_lock, but then table
tbl_su_Investors is INNER JOINed to tbl_comtrades. This means that this
LEFT JOIN can be changed to INNER JOIN.

4. Since this is probably a star schema around tbl_ldf_loaninit, I would
change View_Impounds RIGHT JOIN tbl_ldf_loaninit into tbl_ldf_loaninit
LEFT JOIN View_Impounds

5. Table tbl_ldf_borrower is LEFT JOINed to tbl_ldf_loaninit, but then
in the WHERE clause, you filter tbl_ldf_borrower on a non-NULL value
(WHERE tbl_ldf_borrower.borid = 1). This means a lot! It means the LEFT
JOIN of tbl_ldf_borrower, the RIGHT JOIN of tbl_ldf_ccinit, the RIGHT
JOIN of View_int_InvestorCharges and the RIGHT JOIN of
view_2ndry_master_query can all be converted to INNER JOINs. This fact
is also confirmed by the fact that you filter tbl_ldf_loaninit on a
non-NULL value.

If you place all JOINs in a logical order and place each ON-clause
immediately after the accompanying JOIN, then cases like the ones above
will show automatically.

For the query optimizer there is a big difference between an inner or
outer join. An outer join usually means that the optimizer has to use a
specific access path (starting with 'outer' table), or perform a
table/index scan. An inner join gives much greater choice in index use
(and choice of access paths). Because of this, an inner join is
typically preferred over an outer join.

Others have already mentioned that a query with this many tables cannot
be easily discussed in a forum like this. Not only does your first query
(I haven't looked at anything after the UNION ALL) have 28 joins, but
judging the names also involves 7 views, which probably also contain
joins. So all we can do is offer generic advice.

6. If you have an outer join, then make sure the join criteria of the
inner table are indexed. For example: tbl_ldf_loaninit LEFT OUTER JOIN
tbl_ldf_shipping ON tbl_ldf_loaninit.loannum = tbl_ldf_shipping.loannum
Because of the outer join, the query optimizer probably wants to access
tbl_ldf_shipping first, and then lookup the accompanying rows in
tbl_ldf_shipping. So in this example, make sure that
tbl_ldf_shipping(loannum) is indexed.

7. I am guessing that not all grouping columns will result in more rows
in the resultset. For example: if there is only one row in
tbl_ldf_mtgtype for each loannum, then IMO it would be better to choose
'any' processorname instead of grouping on processorname, to choose
'any' programcode instead of grouping on programcode. Unfortunately,
there is no ANY aggregate in SQL-Server, but you could use MAX instead.
That would really clean up the GROUP BY clause, and make the query
easier to understand.

  SELECT ..., tbl_ldf_mtgtype.processorname, ...
  FROM ...
  INNER JOIN tbl_ldf_mtgtype ON tbl_ldf_loaninit.loannum =
tbl_ldf_mtgtype.loannum
  GROUP BY ..., tbl_ldf_mtgtype.processorname, ...

would then become

  SELECT ..., MAX(tbl_ldf_mtgtype.processorname) AS Processorname, ...
  FROM ...
  INNER JOIN tbl_ldf_mtgtype ON tbl_ldf_loaninit.loannum =
tbl_ldf_mtgtype.loannum
  GROUP BY ...

This also makes it easier to change expressions like
"tbl_co_employees.last_name + ' ' + tbl_co_employees.first_name",
because they will then only be mentioned once in the query instead of
twice. If the expression does need grouping in the exemple of
tbl_co_employees, then in most cases you could group on the primary key
(instead of some combination of columns).

8. The filtering on init_sold_date / init_fund_dt will only work as
expected if the datatype is smalldatetime. Because of the continuous
nature of time, the following syntax is preferred for dates:
      tbl_ldf_loaninit.init_sold_date >= '20041101'
  AND tbl_ldf_loaninit.init_sold_date < '20041201'

Hope this helps,
Gert-Jan

Relevant Pages

  • Re: Relational question
    ... left outer join ... >) as AB inner join ( ... >>Let's look at the first LEFT JOIN query: ... >>which we'll refer to collectively as and the constituent queries ...
    (microsoft.public.sqlserver.programming)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • RE: Aggregates, Joins, I am totally stuck on this issue
    ... a left outer join it makes it an inner join? ... I have two aggregate queries which are grouped on ... >> query and then create an append query which puts the results of this master ... >> a LEFT OUTER JOIN and run it and what do it get? ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
Loading