Sql 2005 CTP not handling query

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

Transact-SQL (2000)

Sql 2005 CTP not handling query

Author

Topic

neo302
Starting Member

30 Posts

Posted - 2006-12-19 : 11:36:16

This query worked forever until after I installed the CTP Service pack 2.

SELECT c.a, c.b, c.c
INTO TEMPDB.dbo.MyTempTable
FROM Server1.Hist.dbo.CTable c
WHERE c.HDATE = 20061208
AND exists (select * from Server2.Tra.dbo.accts a
where c.Aid = a.Aid
and a.AType & 256 = 256)
AND c.Eid in (1, 2, 3)
AND exists (select * from Server1.stats.dbo.gem g
where c.Eid = g.Eid
and c.Sym = g.Sym
and exists (select * from Server1.stats.dbo.gem h
where g.GE = h.GE
and h.Eid = 16))

The bolded section sends the DB into la la land. If I remove it, it finishes in no time. Otherwise, forget it.

I have keys and indexes where appropriate.

Any help would be appreciated.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:48:13

If you remove the bold section, you end up with a syntax error due to missing paranthesis.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:50:05

This is your code rewritten

SELECT		c.a,
		c.b,
		c.c
INTO		TEMPDB.dbo.MyTempTable 
FROM		Server1.Hist.dbo.CTable AS c
WHERE		c.HDATE = 20061208 
		AND c.Eid in (1, 2, 3)
		AND exists (select * from Server2.Tra.dbo.accts a where c.Aid = a.Aid and a.AType & 256 = 256)
		AND exists (select * from Server1.stats.dbo.gem g where c.Eid = g.Eid and c.Sym = g.Sym and exists (select * from Server1.stats.dbo.gem h where g.GE = h.GE and h.Eid = 16))

It doesn't look right to me...

Peter Larsson
Helsingborg, Sweden

neo302
Starting Member

30 Posts

Posted - 2006-12-19 : 11:50:12

quote:
Originally posted by Peso

If you remove the bold section, you end up with a syntax error due to missing paranthesis.

Peter Larsson
Helsingborg, Sweden

Obviously if you remove the bolded section, you would put a new one after the g.Sym)

neo302
Starting Member

30 Posts

Posted - 2006-12-19 : 11:52:26

quote:

Originally posted by Peso

This is your code rewritten

SELECT		c.a,
		c.b,
		c.c
INTO		TEMPDB.dbo.MyTempTable 
FROM		Server1.Hist.dbo.CTable AS c
WHERE		c.HDATE = 20061208 
		AND c.Eid in (1, 2, 3)
		AND exists (select * from Server2.Tra.dbo.accts a where c.Aid = a.Aid and a.AType & 256 = 256)
		AND exists (select * from Server1.stats.dbo.gem g where c.Eid = g.Eid and c.Sym = g.Sym and exists (select * from Server1.stats.dbo.gem h where g.GE = h.GE and h.Eid = 16))

It doesn't look right to me...

Peter Larsson
Helsingborg, Sweden

Thanks for looking. I'm sorry it doesn't look right. It works typically. I modded the column names, but it's the same query I have been using a very long time.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 11:58:53

Try this ANSI JOIN rewrite

SELECT DISTINCT	c.a,
		c.b,
		c.c
INTO		TEMPDB.dbo.MyTempTable 
FROM		Server1.Hist.dbo.CTable AS c
INNER JOIN	Server2.Tra.dbo.accts AS a ON a.Aid = c.Aid AND a.AType & 256 = 256
INNER JOIN	Server1.stats.dbo.gem AS g ON g.Eid = c.Eid and g.Sym = c.Sym
INNER JOIN	Server1.stats.dbo.gem AS h ON h.GE = g.GE and h.Eid = 16
WHERE		c.HDATE = 20061208 
		AND c.Eid in (1, 2, 3)

Peter Larsson
Helsingborg, Sweden

neo302
Starting Member

30 Posts

Posted - 2006-12-19 : 12:09:25

quote:

Originally posted by Peso

Try this ANSI JOIN rewrite

SELECT DISTINCT	c.a,
		c.b,
		c.c
INTO		TEMPDB.dbo.MyTempTable 
FROM		Server1.Hist.dbo.CTable AS c
INNER JOIN	Server2.Tra.dbo.accts AS a ON a.Aid = c.Aid AND a.AType & 256 = 256
INNER JOIN	Server1.stats.dbo.gem AS g ON g.Eid = c.Eid and g.Sym = c.Sym
INNER JOIN	Server1.stats.dbo.gem AS h ON h.GE = g.GE and h.Eid = 16
WHERE		c.HDATE = 20061208 
		AND c.Eid in (1, 2, 3)

Peter Larsson
Helsingborg, Sweden

Hey Peter. That runs, but still goes to la la land or takes too long. I waited over 1 min. When I do the following, it's done in 10 seconds. I hate having to do this though as before this update, it was not an issue.

select distinct g.Eid, g.Sym
into UseThese
from Server1.stats.dbo.gem g
where exists (select * from Server1.stats.dbo.gem h
where g.GE = h.GE
and h.Eid = 16)

SELECT c.a, c.b, c.c
INTO TEMPDB.dbo.MyTempTable
FROM Server1.Hist.dbo.CTable c
WHERE c.HDATE = 20061208
AND exists (select * from Server2.Tra.dbo.accts a
where c.Aid = a.Aid
and a.AType & 256 = 256)
AND c.Eid in (1, 2, 3)
AND exists (select * from UseThese g
where c.Eid = g.Eid
and c.Sym = g.Sym)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources