August 18, 2006 at 10:30 am
I have an update statement that is being called in a stored procedure that isn't returning. (It is running for hours on a particular data set).
Here is the Update:
UPDATE #Table1 SET var18754= aggVar518, var18752= aggVar524, var18753= aggVar6428
FROM #Table1 FTable
INNER JOIN (SELECT TOP 100 Percent FT.Comp_ID, d.var518 as aggVar518,
d.var524 as aggVar524, d.var6428 as aggVar6428, FT.var1, FT.var4 FROM #FactTable FT
INNER JOIN [Table2] d ON FT.Comp_ID = d.Component
WHERE d.var2 >= FT.var1 AND d.var2 < FT.var4
ORDER BY d.var2 , d.var518 DESC ) myqry ON FTable.comp_ID = myQry.Comp_ID
AND FTable.var1 = myqry.var1 WHERE var18751 IS NULL
#Table1 has 269797 rows
Table2 has 310831 rows
Table Creates:
CREATE TABLE [#Table1] (
[Comp_ID] [int] NULL ,
[var1] [float] NULL ,
[var2] [float] NULL ,
[var4] [float] NULL ,
[var18754] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[var18753] [datetime] NULL ,
[var18752] [float] NULL ,
[var18751] [float] NULL ,
[var18757] [float] NULL ,
[var18759] [float] NULL ,
[var18761] [int] NULL ,
[RecID] [int] IDENTITY (1, 1) NOT NULL ,
[ColID] [int] NULL ,
[PipeLength] [float] NULL
) ON [PRIMARY]
GO
Indexes
#Table1 clustered located on PRIMARY Comp_ID, var2
#table1 nonclustered located on PRIMARY Comp_ID, var1, var4
CREATE TABLE [Table2] (
[Component] [int] NULL ,
[var2] [float] NULL ,
[var518] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[var6428] [datetime] NULL ,
[var524] [float] NULL ,
[var525] [float] NULL ,
[Collector_ID] [int] NULL ,
[Parent_CollectorID] [int] NULL ,
[Comp_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [tinyint] NULL
) ON [PRIMARY]
GO
Index:
Table2 clustered located on PRIMARY Component, var2
I also tried breaking out the derived table into a temp table first. But this runs indefinitely.
SELECT FT.Comp_ID,
d.var518, --as aggVar518,
d.var524, --as aggVar524,
d.var6428, --as aggVar6428,
FT.var1,
FT.var4
into #temp
FROM #Table1 Cast FT
INNER JOIN [Table2] d ON FT.Comp_ID = d.Component
WHERE d.var2 >=FT.var1 AND d.var2 < FT.var4
ORDER BY d.var2 , d.var518 DESC
Any suggestions?
Thanks,
Daniel
August 18, 2006 at 10:56 am
To start with, remove the ORDER BY clause!
N 56°04'39.16"
E 12°55'05.25"
August 18, 2006 at 11:02 am
Try this one!
UPDATE FTable
SET FTable.var18754 = d.Var518,
FTable.var18752 = d.Var524,
FTable.var18753 = d.Var6428
FROM #Table1 FTable
INNER JOIN #FactTable FT ON FT.Comp_ID = FTable.comp_ID AND FT.var1 = FTable.var1
INNER JOIN [Table2] d ON d.Component = FT.Comp_ID AND d.var2 >= FT.var1 AND d.var2 < FT.var4
WHERE FTable.var18751 IS NULL
But I think you are missing something. You call some field "agg...". Does that mean that there should be some kind of aggregation? SUM? COUNT?
N 56°04'39.16"
E 12°55'05.25"
August 18, 2006 at 4:33 pm
thanks for the input. I will try.
Daniel
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply