On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Saturday, May 31, 2008

Making a Multiple Table Query Faster

I don't totally understand this, but my experience has been that the second query shown will significantly improve the processing speed.

To improve speed:
  • use In-line views (virtual tables)
  • sort data before combining with another in-line view
  • don't move any data forward that isn't necessary (old keys)
  • collapse all data as soon as possible
Slower:
Select A.Name, A.City, B.Description
From Master A
Join Reference B
Using (Name)
Where Zip = '22042';
Faster:

Select A.Name, A.City, B.Description
From
(
Select Name, City
From Master
Where Zip = '22042'
Order By Name
) A
Join
(
Select Name, Descripition
From Reference
Where Zip = '22042'
Order By Name
) B
Using (Name);


Tiny link to this post: http://tinyurl.com/fast-query

No comments:

Post a Comment