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: M K: "Re: Want to Order By a field not group by"
- Previous message: M K: "RE: Want to Order By a field not group by"
- In reply to: Louis Davidson: "Re: Trying to optimize a query with a bunch of INNER JOINs"
- Next in thread: Louis Davidson: "Re: Trying to optimize a query with a bunch of INNER JOINs"
- Reply: Louis Davidson: "Re: Trying to optimize a query with a bunch of INNER JOINs"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 5 Oct 2004 12:47:04 -0400
Yes, I do have foreign keys on the main table, as well as primary keys on
everything.
One thing I do have (that I'm sure slows things down, but cannot be helped),
is that my source "table" is actually a handful of tables connected with
INNER and LEFT JOINs (but everything has primary keys, as well as foreign
keys where applicable).
One other unavoidable thing is that I'm doing a SUM() on one of the fields,
so I've therefore got a GROUP BY statement of everything else too.
Looking at the plan, it's saying that the SORT step is taking up 97% of the
cost. I can only assume this is because of the GROUP BY. But since I have
the SUM() in there, I can't exactly leave that part off the query.
Any ideas?
Thanks!
-Scott
"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:uZnbNTvqEHA.348@TK2MSFTNGP15.phx.gbl...
> As Mike says look to the plan. Look at the percentages of time it takes
to
> do each of the steps. Also, is there a WHERE clause on you main
statement?
> And do you have primary keys and foreign keys on all of the tables? You
may
> need foreign key indexes on your primary table, thought that totally
depends
> on what your where looks like.
>
> Post the plan if you can.
> use northwind
> go
> set showplan_text on
> go
> select * from customers
>
> returns:
>
> StmtText
> --------------------------------------------------------------------------
-- > ----- > |--Clustered Index > Scan(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers])) > > showplan_all gives more information but is a pain to post :) > > -- > -------------------------------------------------------------------------- -- > Louis Davidson - drsql@hotmail.com > SQL Server MVP > > Compass Technology Management - www.compass.net > Pro SQL Server 2000 Database Design - > http://www.apress.com/book/bookDisplay.html?bID=266 > Note: Please reply to the newsgroups only unless you are interested in > consulting services. All other replies may be ignored :) > > "Scott Lyon" <scott.RED.lyon.WHITE@rapistan.BLUE.com> wrote in message > news:edr%23DMuqEHA.592@TK2MSFTNGP11.phx.gbl... > > Considering my look-up tables are small, would it improve efficiency at > all > > to load those into variables (defined as tables), and join to those > > variables rather than joins to the actual tables themselves? > > > > What I'm thinking (and I admit I don't know how stored procedures work > > "behind the scenes", so this may be a moot point), is that if I have a > > lookup table consisting of only two rows/values, when I join to it, I'm > > retrieving those two values a BUNCH of times. Wouldn't it be faster if > those > > two values were already in memory? > > > > > > 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: M K: "Re: Want to Order By a field not group by"
- Previous message: M K: "RE: Want to Order By a field not group by"
- In reply to: Louis Davidson: "Re: Trying to optimize a query with a bunch of INNER JOINs"
- Next in thread: Louis Davidson: "Re: Trying to optimize a query with a bunch of INNER JOINs"
- Reply: Louis Davidson: "Re: Trying to optimize a query with a bunch of INNER JOINs"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
