Slow query because of 1 join :(

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Slow query because of 1 join :(

Author

Topic

larryleisure
Starting Member

5 Posts

Posted - 2011-12-14 : 07:05:46

Hi Everyone,

I have a query that take 2mins to run, apparently because of the WHERE clause.
If I remove one of the joins then it takes 1 sec and result is the same and correct in both cases.

The Query is this:
select ARTDIM.PC as c0, ARTDIM.ARHG as c3, TIME_FK.YEAR as c6, TIME_FK.QUARTN as c7, TIME_FK.MONTHNL as c8, TIME_FK.PERIODE as c9,
SALESFACTS_F.TYPE as c10,FIRM.COMPANY as c11, FIRM.FIRMCODE as c12, FIRM.WAREHOUSECODE as c13, FIRM.FRMWHS as c14,ARTDIM.ARTSRT as c15, ARTDIM.ARTSRT_DESC as c16, ARTDIM.ARTSRT as c17
from PENTAHO_DW.ARTDIM ARTDIM, PENTAHO_DW.SALESFACTS_F SALESFACTS_F, PENTAHO_DW.DW_DATE_A TIME_FK, PENTAHO_DW.KLADIM FIRM
where SALESFACTS_F.ARNR41 = ARTDIM.ARNR30 and SALESFACTS_F.FKDT41 = TIME_FK.DATENUM
and SALESFACTS_F.KLNRADNR = FIRM.KLRNADNR and (TIME_FK.PERIODE = 201001 and TIME_FK.MONTHNL = 'Januari' and TIME_FK.QUARTN = 'Q1' and TIME_FK.YEAR = 2010)

No need to read it all, I've identified the problem but just don't know why it slows it down that much and how to fix it.

The prob is related to this part of the WHERE clause:
TIME_FK.PERIODE = 201001 and
TIME_FK.MONTHNL = 'Januari'
If I remove one of these 2 conditions then it works in 1 second and the result is the same as the same data is actually retrieved. Problem is that this is a SQL generated by a thirdparty application so I can't amend the SQL manually.

For info the TIME_FK table is setup like this:
YEAR | QUARTN | MONTHNL | PERIODE | DATE (dd/mm/yyyy)
2010 | Q1 | Januari | 201101 | 1/1/2010
2010 | Q1 | Januari | 201101 | 2/1/2010
2010 | Q1 | Januari | 201101 | 3/1/2010
2010 | Q1 | Januari | 201101 | 4/1/2010
2010 | Q1 | Januari | 201101 | etc...
2010 | Q3 | July | 201107 | 1/7/2010
2010 | Q3 | July | 201107 | 2/7/2010
2010 | Q3 | July | 201107 | 3/7/2010
etc..

Any idea why having either PERIODE or MONTHNL in the statement makes the query extremely slow whilst it's actually retrieving the same thing?
Thanks for any input!
Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 07:33:50

check if you've an index covering both these columns. if not present, create an index combining them and try the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

larryleisure
Starting Member

5 Posts

Posted - 2011-12-14 : 07:49:51

I do have an index on each of these columns separately, and also an index for these 2 columns combined.
Still, 1m59secs to retrieve the data, as opposed to 1sec if I remove either PERIODE or MONTHNL
uh :/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 07:59:18

quote:
Originally posted by larryleisure

I do have an index on each of these columns separately, and also an index for these 2 columns combined.
Still, 1m59secs to retrieve the data, as opposed to 1sec if I remove either PERIODE or MONTHNL
uh :/

hmm...why should you've both?
ok ...in any case did you analyse the query plan and check if index is getting used?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources