August 21, 2006 at 3:34 am
Please,
can anybody give me a hint on why the below runs in 27 seconds with the subquerry (or with a joined table) and in only 9 seconds when the 15 ID's are hardcoded in the set?
If so how do I modify the querry so it can runs dynamicly in 9 seconds?
Thanks in advance
Henrik Brondum
select f.year_id, f.line_id, f.ver_id, f.cust1_id
from
epm02.finloc_base
where
f.year_id=80000003 and
f.line_id in (80000908,80001733,80000957) and
f.ver_id=80000007 and
f.unit_id in
--(select mem_id from dbo.conc_table00)
(80000002,
80000003,
80000006,
80000007,
80000008,
80000009,
80000021,
80000023,
80000045,
80000093,
80000099,
80000115,
80000119,
80000128,
80000131
)
August 21, 2006 at 4:00 am
IN -- > is running faster bcoz the table is not sccaned here it provides direct values to its execution plan.
For more fast performance use the Exists :
Your modified Query :
select f.year_id, f.line_id, f.ver_id, f.cust1_id
from
epm02.finloc_base as f
where
f.year_id=80000003 and
f.line_id in (80000908,80001733,80000957) and
f.ver_id=80000007 and Exists (select mem_id from dbo.conc_table00 where mem_id=f.unit_id)
Thanks..
Amit Gupta
August 21, 2006 at 4:57 am
What about an inner join?
select f.year_id,
f.line_id,
f.ver_id,
f.cust1_id
from epm02.finloc_base
inner join dbo.conc_table00 a on a.mem_id = f.unit_id
where f.year_id = 80000003
and f.line_id in (80000908, 80001733, 80000957)
and f.ver_id = 80000007
N 56°04'39.16"
E 12°55'05.25"
August 21, 2006 at 7:59 am
I would definitely use the JOIN solution... just check indexes on both tables and make sure execution plan is using them.
August 21, 2006 at 11:07 am
We don't know enough about the tables to determine if a JOIN is correct.
>>select mem_id from dbo.conc_table00
If mem_id is not unique, using a JOIN instead of EXISTS will generate dupes in the resultset.
Post the DDL, including primary key and unique constraints.
August 21, 2006 at 2:17 pm
All,
Thanks for a lot of replys enlightening for me. Unfortunately none of the suggestion makes things go really fast. The inner join is the best cutting down to 19 seconds - still 10 seconds more than the hardcoded version.
(The table dbo.conc_table00 has been populated for this purpose with just the 15 mem_id in the set in one column. It has an index)
The index on the FINLOC_BASE table is a combined key of 10 columns (Year_id,Line_id, unit_id, ver_id,cust1_id, cust2_id,cust3_id,cust4_id,cust5_id,cust6_id) this index cannot be changed since the table has 80000000 records and we cannot affort more than one index.
Regards
Henrik
August 22, 2006 at 1:04 am
Just a question, did you try to compare execution plans for different queries (hardcoded, join)? It is always good to start with that if you have problems with performance. Is the JOIN using an index?
You say that you can't afford more than one index.. why? Maybe you can not afford to have only one index, if you want to reach good performance... An index composed of 10 columns is something that helps you if the queries consistently make use of all or most of them. We also have some huge tables with millions of rows in our DB, and they always have at least 3 indexes - separate one-column index for each FK column. Did you try to split up the index into several, or add some one-column index(es)? Sometimes it can help a lot (and sometimes it doesn't help... you have to test it in your environment). Is this index also Primary Key, or is the table without PK? Is this index clustered or not? All this can influence performance.
Generally, it is not the best solution to use as first column(s) in a composite index something that has very limited range (like Year) - it should be the most distinguishing column. Another thing is the design doesn't seem to be the best (unless this is a table for reporting, but then it shouldn't have 80M rows), because you have 6 columns called cust_id + number. If all the 6 columns have the same meaning, then it should be only one column, and if there are several customers for one "record", there should be several rows in this table.
However, take this just as a hint what to consider - I know very well that there are a) things you can't influence and b) things that work very differently depending on environment and have to be tested for each system.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply