SQL Tune up - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Tune up

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2006-06-16 : 13:37:16

I have a simple sql that is taking more than hour to run. Is there a better way to write the sql below. Will appreciate any advice.

All I am trying to grab is the customer Id (cust id) for records that has SYS_ID and KY_NM appears more than once.

select cust_id
from
cust c1,
(
select SYS_ID,KY_NM
from cust
where
END_TMST = '9999-09-09-00.00.00.000000'
group by SYS_ID,KY_NM
having count(*) >1
) as c2
where c1.SYS_ID = c2.SYS_ID
and c1.KY_NM = c2.KY_NM

Thanks

Raj

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2006-06-16 : 16:47:02

Raj, How many records are there? any Indexes present?

With Regards
BSR

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 17:22:01

I can't tell if there is much speed difference since I don't have original table data. Could be some hundreds of rows, could be millions.

create index ix_cust on cust (END_TMST, sysid, ky_nm, custid) -- create covered index.

-- do the work
select distinct	c.CustID
from		cust c
inner join	(
			select		SysID,
					KY_NM
			from		cust
			where		END_TMST = '9999-09-09-00.00.00.000000'
			group by	SysID,
					KY_NM
			having		count(*) > 1
		) z on z.SysID = c.SysID AND z.KY_NM = c.KY_NM

Peter Larsson
Helsingborg, Sweden

rkumar28
Starting Member

49 Posts

Posted - 2006-06-16 : 17:22:11

Thanks for the reply. There are over 10 million records. I think the query above is alright. There are no index on the two fields used in the join. Probably this is slowing down the query. I will create the Index as suggested.

Raj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 17:29:02

quote:
Originally posted by rkumar28

Thanks for the reply. There are over 10 million records. I think the query above is alright. There are no index on the two fields used in the join. Probably this is slowing down the query. I will create the Index as suggested.

Raj

After you create the index, run your old query and post time it took here. Then run my query and post here the time the query took, ofr compariaon.

It is not every day I get the chance to test different techniques on large tables.

The techniques I am talking about is "INNER JOIN" and "CROSS JOIN WITH WHERE". Books Online says there shall be no speed difference between the two techniques.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources