Re: Trying to optimize a query with a bunch of INNER JOINs
From: Scott Lyon (scott.RED.lyon.WHITE_at_rapistan.BLUE.com)
Date: 10/05/04
- Next message: Bonj: "Re: order of columns"
- Previous message: David Portas: "Re: Insert into Variable field"
- In reply to: Dan Guzman: "Re: Trying to optimize a query with a bunch of INNER JOINs"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 5 Oct 2004 12:42:24 -0400
By the way, I forgot to mention, I do have it set up with foreign key
constraints for each of the lookup tables.
Not sure if this affects my question, but in reality, my 'source' table is
actually a normalized set of tables (with primary keys and foreign key
constraints on each), joined together with INNER JOINs.
Thanks!
-Scott
"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:%23DkOZ%23tqEHA.1452@TK2MSFTNGP10.phx.gbl...
> Since you are joining on the primary key of the lookup tables, I would
> expect your query to perform reasonably well. You might try updating
> statistics and creating foreign key constraints if you haven't already
done
> so. A non-clustered index on DataStr, Code1_ID, Code2_ID, Code3_ID will
> cover this particular query but I wouldn't think this would be needed on a
> table with only a few thousand rows.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Scott Lyon" <scott.RED.lyon.WHITE@rapistan.BLUE.com> wrote in message
> news:eR6I32tqEHA.1964@TK2MSFTNGP12.phx.gbl...
> > I've got a table of data that also contains an ID column for a bunch of
> > look-up tables.
> >
> > For example, let's assume I've got 3 look-up tables (each of which will
> > have
> > from 2 to 20 possible values):
> >
> > Code1:
> > Code1_ID int (PK)
> > Code1_value char(1)
> >
> > Code2:
> > Code2_ID int (PK)
> > Code2_value char(1)
> >
> > Code3:
> > Code3_ID int (PK)
> > Code3_value char(1)
> >
> >
> > My data table (just a portion of it) is like this:
> >
> > Data1:
> > DataStr char(50)
> > Code1_ID int
> > Code2_ID int
> > Code3_ID int
> >
> >
> > Now, I want to have a query (in a stored procedure) to get all records
in
> > Data1, with the value (not the ID) for each of the lookup tables
> > associated
> > with it.
> >
> >
> > I know that the most direct way to do this is to join all the tables,
like
> > this:
> >
> > SELECT Data1.DataStr, Code1.Code1_value, Code2.Code2_value,
> > Code3.Code3_value
> > FROM Data1
> > INNER JOIN Code1
> > ON Code1.Code1_ID = Data1.Code1_ID
> > INNER JOIN Code2
> > ON Code1.Code2_ID = Data1.Code2_ID
> > INNER JOIN Code3
> > ON Code1.Code3_ID = Data1.Code3_ID
> >
> >
> > Unfortunately, in my real example (which has 10 lookup tables, and in
> > which
> > the source is thousands of rows long), the query is taking FAR too long
to
> > come back.
> >
> >
> > So I'm trying to figure out if there's a more efficient way to do this,
> > especially considering how relatively small (as I mentioned, 2 to 20
rows
> > at
> > most) the look-up tables are.
> >
> >
> > Would it help if I first read in each look-up table into a temporary
table
> > in my stored procedure? Or would that be pointless, taking exactly the
> > same
> > time as just joining to the look-up tables themselves?
> >
> >
> > Or should I create indexes for some of that, and if so, indexes for
which:
> > the lookup tables or the codeX_ID rows in the data table?
> >
> >
> > What would be the best way to speed up my query, while still returning
the
> > data I need?
> >
> >
> > Thanks!
> > -Scott
> >
> >
>
>
- Next message: Bonj: "Re: order of columns"
- Previous message: David Portas: "Re: Insert into Variable field"
- In reply to: Dan Guzman: "Re: Trying to optimize a query with a bunch of INNER JOINs"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
