March 14, 2008 at 12:34 pm
Can someone please help? I cannot figure out why this query is taking so long to return results. I'm forced to use index hints because SQL Server (2000) is unable to determine what indexes to use. The tables were creating using definitions from our ERP, so unfortunately no primary/foreign keys were setup.
What is odd is that if the date range in my where clause is 7 or less days, results are returned relatively quick. Yet, once you exceed 7 days, it takes a long time.
My query is as follows:
select
a_date,
a_nbr,
b_cust,
(select e_desc from e where e_item = b_item) as 'e_desc',
b_part,
(select f_name from f where b_part = f_name) as 'f_name',
b_qty,
c_cost
from
a with (index(a_idx))
inner join
b with (index(b_idx)) on (a_nbr = b_nbr)
left outer join
c with (index(c_idx)) on (b_nbr = c_nbr and b_inv = c_inv and b_line = c_line)
where
a_date between '2008-01-01' and '2008-01-15'
Any help would be appreciated.
Thanks.
March 14, 2008 at 3:20 pm
Can you post the table structure and index defs please? Also, if possible, the execution plan (without the index hints in place) (run SET SHOWPLAN_ALL ON before the queries) in an attachment.
How many rows are affected by the query? How many in the table a?
In future, please post SQL 2000 questions in the SQL 2000 forums.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 14, 2008 at 3:30 pm
Part of your problem could be the two corelated queries in the select statement.
Try this code and let us know if it work and still returns what you expect:
select
a_date,
a_nbr,
b_cust,
e_desc, -- (select e_desc from e where e_item = b_item) as 'e_desc',
b_part,
f_name, -- (select f_name from f where b_part = f_name) as 'f_name',
b_qty,
c_cost
from
a -- with(index(a_idx))
inner join b -- with(index(b_idx))
on (a_nbr = b_nbr)
inner join e
on (e_item = b_item)
inner join f
on (b_part = f_name)
left outer join c -- with(index(c_idx))
on (b_nbr = c_nbr
and b_inv = c_inv
and b_line = c_line)
where
a_date between '2008-01-01' and '2008-01-15'
😎
March 17, 2008 at 7:35 am
Thanks for the help and sorry for posting in the incorrect section.
Table A (ih_hist) currently has 3,656,530 rows. Table B (idh_hist) has 61,206,053. If I run the query with a range of 28 days, approxiately 620K rows are affected.
Attached is the execution plan, as well as the table/index definitions for the Table A (ih_hist) and Table B (idh_hist).
Here is the query used to generate the execution plan.
SET SHOWPLAN_ALL ON
select
ih_inv_date,
ih_nbr,
ih_cust,
cm_sort,
idh_part,
pt_desc1,
idh_qty_inv,
xxidh_mtl_std
from
ih_hist inner join cm_mstr on (cm_addr = ih_cust)
inner join
idh_hist on (ih_inv_nbr = idh_inv_nbr and ih_nbr = idh_nbr)
inner join
pt_mstr on (pt_part = idh_part)
left outer join
xxidh_cost2 on (xxidh_inv_nbr = idh_inv_nbr and xxidh_nbr = idh_nbr and xxidh_line = idh_line)
where
(ih_inv_date between '2008-03-10' and '2008-03-16')
Thanks again for the help.
March 17, 2008 at 8:00 am
I have looked at the two files with CREATE Table statements, and I'm not going to do anything with those as it is too hard to see what's what. Also, there are 5 tables involved and I could only see 2 in the files.
Suggestion, provide well formatted DDL for all 5 tables (including indexes) that includes the fields involved in the query plus those in indexes. We don't need any extranous info if it isn't involved in the query.
😎
March 17, 2008 at 9:27 am
Sorry about that Lynn. Hope these attachments are OK.
March 17, 2008 at 1:35 pm
Hi,
Just to add to my existing post. The following query takes less then 2 minutes to complete. If I remove the index hints, it takes over 40 minutes.
[font="Courier New"]select ih_inv_date, idh_part, idh_qty_inv
from ih_hist with (index(ih_hist##ih_inv_date)) inner join idh_hist with (index(idh_hist##idh_invln)) on (ih_inv_nbr = idh_inv_nbr and ih_nbr = idh_nbr)
where (ih_inv_date between '2008-01-27' and '2008-02-23')[/font]
Besides the Execution Plan, is there something else I should be looking at to try and determine what is going on?
March 17, 2008 at 1:45 pm
The execution plan is the key.
You're looking for Table Scans and Index Scans, first.
The other thing I'd look at, based on what you've said so far, is the statistics on the table and the indexes. If they're horribly out of date, or if the indexes are horribly fragmented, I'd seriously look at updating those. The database will sometimes ignore indexes that are badly out of date and/or badly fragmented (which might explain why you have to give it query hints to get it to use them).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 1:47 pm
In the execution plan, I see two table scans and several hash joins. Those are going to slow you down.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 1:54 pm
To handle the first table scan, create an index on idh_inv_nbr, idh_nbr, idh_line, idh_part, idh_qty_inv on table idh_hist. (Make sure you have the columns in the index in that sequence.)
create index IDX_IDH_Hist_Inv_Nbr_Part_Qty
on dbo.idh_hist(idh_inv_nbr, idh_nbr, idh_line, idh_part, idh_qty_inv)
Should do it for that one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 2:00 pm
The bookmark lookup on ih_hist can probably be sped up with:
create index IDX_IH_Hist_Inv_Nbr_Cust on
dbo.ih_hist(ih_inv_date, ih_inv_nbr, ih_nbr, ih_cust)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 2:07 pm
You also have a table scan on xxidh_cost2. If that has more than about 200 rows in it, create a covering index. Include columns xxidh_inv_nbr, xxidh_nbr, xxidh_line, xxidh_mtl_std, in that sequence.
(On all of these, you may want to play around with the sequence of columns that are in Where and Join clauses, to put the most selective first. You may also want to move the ones that are only in the Select clause, to Include, instead of being part of the actual index. If you aren't comfortable with those concepts, use them as presented.)
Sorry about answering this in pieces. The tables were messy enough that it took breaking it apart that way for me to think it through.
Also, keep in mind, before creating ANY of these indexes, that they will take up disk space, they will slightly slow down Insert/Update/Delete actions on those tables, and they will need to be maintained (defragmentation and statistics updated). The maintenance can be automatic, but do watch out for disk use.
Once you have these indexes created, or something comparable as per my suggestions above, check the query plan and see if it's gotten rid of the table scans and bookmark lookups and is working on index seeks instead. That's the goal on slow selects.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 18, 2008 at 8:51 am
Thank GSquared! I really appreciate your help.
If I could ask another question?
These queries will be used as data sources to build OLAP cubes (Cognos Transformer). Depending on which cube I'm building, I may be requesting a different set of fields from each of these tables. Should I be creating a specific index for each of these queries?
For example, anytime idh_hist is queried, idh_inv_nbr, idh_nbr, idh_line, idh_part, idh_qty_inv are always requested. But, in certain cases, I may also request other fields. Should I create one index that includes those five fields, and then another that includes those five, plus the other fields I require?
Thanks again.
March 18, 2008 at 9:13 am
hmm... Covering indexes unfortunately can fall into the "law of diminishing returns". Meaning - you're going to end up hitting the size limit sooner or later. Also - the bigger the index - the less efficient it's going to be.
You'd get some more mileage if you leverage the INCLUDE clause for creating indexes. It will allow you to have values included to be used in the select clause (not order/where/join), without jamming up or bloating your "key" size. Keep in mind that lots of indexes, especially big ones, will slow down your inserting/updating/deleting activities, so don't go crazy with the indexes.
I think you will find it's a "your mileage may vary" thing. Of course - depending on how slow things are - it might be worth creating temporary indexes and tearing them down when you're done....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 11:55 am
99% of the time, the same fields are used in my order/where/join clauses. It sounds as though "include" may help out quite a bit.
Also, I created indexes on my two main tables (ih_hist, idh_hist) as recommended by GSquared and results are being return much, much faster (1.2M records in 1 minute, 2 seconds).
Thanks again for all the help.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply