Jul 27, 2006
I am cleaning up a large database table that has Date keys instead of real DateTimes. To do this, I am running the following query...
UPDATE MQIC.DBO.OBSERVATION_F
SET MQIC.DBO.OBSERVATION_F.OBS_DATE = MQIC.DBO.DATE_D.ACTUAL_DATE
FROM MQIC.DBO.OBSERVATION_F INNER JOIN MQIC.DBO.DATE_D
ON MQIC.DBO.OBSERVATION_F.DATE_KEY = MQIC.DBO.DATE_D.DATE_KEY
where Actual_Date is what is being stored, and the Date_Key is to be dropped.
The particulars are this -
Date_D table - 92,000 rows - 40 MB
Observation_F - 2,000,000 rows - 3.2 GB
This is being run on a remotedly hosted rack server with an AMD processor, 1 GB RAM, 60 GB harddisk space, 20 GB used.
SQL-Server 2005 Express - SP1
If I do the same query as a SELECT Statement,
UPDATE MQIC.DBO.OBSERVATION_F
SELECT MQIC.DBO.OBSERVATION_F.DATE_KEY, MQIC.DBO.DATE_D.ACTUAL_DATE
FROM MQIC.DBO.OBSERVATION_F INNER JOIN MQIC.DBO.DATE_D
ON MQIC.DBO.OBSERVATION_F.DATE_KEY = MQIC.DBO.DATE_D.DATE_KEY
it runs to completion in about 15 min - during the entire time there is extensive used of CPU from Task Manager.
If I do the above statement, it seems to use lots of resources (50% +) for about 5 min, then falls to 5%. It just seems to sit there, for an hour + at which time I've killed the query.
This is actually the second time I tried this. The first time was on a different machine, with the P4, 3GB RAM, plenty of disk space, and using SQL-Server 2005 Standard - SP1. Exactly the same decrease in resources happened, and even though it ran several hours, no results.
Any thoughts here - not waiting long enough, memory leaks, etc.?
Thanks!
View 6 Replies
View Related
Jan 18, 2008
I have one query with 3 statments in it, which then creates a table called HCSReturnFile. My problem is that I have an order by in my query, but when I go to create a table that puts all 2 statements into 1, it does not do the order by. What am I doing wrong? Below is my Query.
USE [Impact_PROD]
GO
/****** Object: StoredProcedure [dbo].[p_GenerateHCSReturnFileUPDATE] Script Date: 01/18/2008 14:20:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Wendy & Mike
-- Create date: 11/08/2007
---Create Date to Production - 12-06-2007
-- Description:This was a touch one.
---This report drop both Queries into a tabled called HCSRetrunFile
-- =============================================
ALTER PROCEDURE [dbo].[p_GenerateHCSReturnFileUPDATE]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF OBJECT_ID('IMPACT_PROD..HCSReturnFile') IS NOT NULL
DROP TABLE HCSReturnFile
CREATE TABLE [dbo].[HCSReturnFile](
[EventNumber] [varchar](50) NULL,
[ClaimNumber1] [varchar](50) NOT NULL,
[Resolution] [varchar](50) NULL,
[Resolution2] [varchar](50) NULL,
[ProviderType] [varchar](2) NULL,
[Negotiation] [varchar](50) NULL,
[NEGOYESORNO] [varchar](50) NULL,
[ProviderID] [varchar](50) NOT NULL,
[HCSAuthorizationID] [varchar](20) NULL,
[PROLASTNAME] [varchar](20) NULL,
[proFirstName] [varchar](15) NULL,
[ProOffice] [varchar](35) NULL,
[TOTALBILLEDAMT] [varchar](50) NOT NULL,
[SAVINGS] [varchar](50) NOT NULL,
[OONNEGO] [varchar](50) NOT NULL,
[CLM_ATT1] [varchar](50) NULL,
[CLM_ATT2] [varchar](50) NULL,
[CLM_ATT3] [varchar](50) NULL,
[CLM_ATT4] [varchar](50) NULL,
[CLM_ATT5] [varchar](50) NULL,
[NoteData] [varchar](8000) NULL)
INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile]
([EventNumber]
,[ClaimNumber1]
,[Resolution]
,[Resolution2]
,[ProviderType]
,[Negotiation]
,[NEGOYESORNO]
,[ProviderID]
,[HCSAuthorizationID]
,[PROLASTNAME]
,[proFirstName]
,[ProOffice]
,[TOTALBILLEDAMT]
,[SAVINGS]
,[OONNEGO]
,[CLM_ATT1]
,[CLM_ATT2]
,[CLM_ATT3]
,[CLM_ATT4]
,[CLM_ATT5]
,[NoteData])
SELECT Distinct
--n.note_sys,
--c.CLM_id1 AS 'ClaimNumber',
e.EVE_id1 AS 'EventNumber',
ClaimNumber1 = '',
--e.EVE_clm As 'EventEventTable',
--c.CLM_elrc AS 'EventClaimTable',
e.eve_resl1 as 'Resolution',
p.EVEP_RESL1 as 'Resolution2',
p.evep_reas as 'ProviderType',
p.evep_nego as 'Negotiation',
--e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
ProviderID = '',
e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
p.EVEP_LNAME AS PROLASTNAME,
p.evep_fname AS proFirstName,
p.evep_offic AS ProOffice,
--c.CLM_EDID AS 'LOCKDATE',
TOTALBILLEDAMT = '',
SAVINGS = '',
OONNEGO = '',
c.CLM_ATT1,
c.CLM_ATT2,
c.CLM_ATT3,
c.CLM_ATT4,
c.CLM_ATT5,
n.NoteData
FROM dbo.EVE e
RIGHT JOIN dbo.clm c
ON e.EVE_id1 = c.clm_id1
RIGHT JOIN dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
JOIN dbo.notes2 n
ON n.eve_id1 = e.EVE_id1
--JOIN evep p2
--ON p.evep_id1 = p2.evep_id1
Where
e.eve_resl1 = 'CL'and
p.EVEP_RESL1 <> 'NG' and
p.evep_reas <>'FA'--and ---means closes Events
--p2.evep_nego = 'Y' and
--((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)
--e.EVE_STAT = 'CL'and
--e.EVE_id1 IN ('00101965','00102080','00102084','00101962','00101963') --and
Order by e.eve_resl1,p.EVEP_RESL1
INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile]
([EventNumber]
,[ClaimNumber1]
,[Resolution]
,[Resolution2]
,[ProviderType]
,[Negotiation]
,[NEGOYESORNO]
,[ProviderID]
,[HCSAuthorizationID]
,[PROLASTNAME]
,[proFirstName]
,[ProOffice]
,[TOTALBILLEDAMT]
,[SAVINGS]
,[OONNEGO]
,[CLM_ATT1]
,[CLM_ATT2]
,[CLM_ATT3]
,[CLM_ATT4]
,[CLM_ATT5]
,[NoteData])
SELECT Distinct
--n.note_sys,
--c.CLM_id1 AS 'ClaimNumber',
e.EVE_id1 AS 'EventNumber',
ClaimNumber1 = '',
--e.EVE_clm As 'EventEventTable',
--c.CLM_elrc AS 'EventClaimTable',
e.eve_resl1 as 'Resolution',
p.EVEP_RESL1 as 'Resolution2',
p.evep_reas as 'ProviderType',
p.evep_nego as 'Negotiation',
--e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
ProviderID = '',
e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
p.EVEP_LNAME AS PROLASTNAME,
p.evep_fname AS proFirstName,
p.evep_offic AS ProOffice,
--c.CLM_EDID AS 'LOCKDATE',
TOTALBILLEDAMT = '',
SAVINGS = '',
OONNEGO = '',
c.CLM_ATT1,
c.CLM_ATT2,
c.CLM_ATT3,
c.CLM_ATT4,
c.CLM_ATT5,
n.NoteData
FROM dbo.EVE e
RIGHT JOIN dbo.clm c
ON e.EVE_id1 = c.clm_id1
RIGHT JOIN dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
JOIN dbo.notes2 n
ON n.eve_id1 = e.EVE_id1
--JOIN evep p2
--ON p.evep_id1 = p2.evep_id1
Where
((e.eve_resl1 = '' and p.evep_reas = 'HO')or (e.eve_resl1 = '' and p.evep_reas = 'PH') or (e.eve_resl1 = '' and p.evep_reas = 'AN'))
Order by e.eve_resl1,p.EVEP_RESL1
--and ---means Open Events will not have claims attached to them. we just need the fields in this report.
--p2.evep_nego = 'Y' and
--((LTRIM(p.evep_id2) <> '0002' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)
INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile]
([EventNumber]
,[ClaimNumber1]
,[Resolution]
,[Resolution2]
,[ProviderType]
,[Negotiation]
,[NEGOYESORNO]
,[ProviderID]
,[HCSAuthorizationID]
,[PROLASTNAME]
,[proFirstName]
,[ProOffice]
,[TOTALBILLEDAMT]
,[SAVINGS]
,[OONNEGO]
,[CLM_ATT1]
,[CLM_ATT2]
,[CLM_ATT3]
,[CLM_ATT4]
,[CLM_ATT5]
,[NoteData])
SELECT Distinct
c.CLM_elrc AS 'EventNumber',
CASE c.clm_ips
WHEN 'C' THEN c.clm_pclm
ELSE c.clm_id1
END as "claimnumber1", --ClmClaims."CLAIMNUMBER",
--c.CLM_PCLM as "CLAIMNUMBER",
--n.note_sys,
--c.clm_id1,
--e.EVE_clm As 'EventEventTable',
e.eve_resl1 as 'Resolution',
p.EVEP_RESL1 as 'Resolution2',
p.evep_reas as 'ProviderType',
p.evep_nego as 'Negotiation',
--e.EVE_STAT AS 'STATUS',
e.EVE_SW01 AS 'NEGOYESORNO',
c.CLM_5 AS 'ProviderID',
e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME',
--p.EVEP_FNAME AS 'PROVIDERFIRSTNAME',
--p.EVEP_OFFIC AS 'PROVIDEROFFICE',
p.evep_lname AS ProLastName,
p.evep_fname AS proFirstName,
p.evep_offic AS ProOffice,
c.CLM_MCHG AS 'TOTALBILLEDAMT',
c.CLM_SPPO AS 'SAVINGS',
c.CLM_55d AS 'OONNEGO',
c.CLM_ATT1,
c.CLM_ATT2,
c.CLM_ATT3,
c.CLM_ATT4,
c.CLM_ATT5,
n.NoteData
FROM dbo.EVE e
Right Join dbo.clm c
ON e.EVE_id1 = c.clm_elrc
Right join dbo.EVEP p
ON e.eve_id1 = p.EVEP_id1
--JOIN evep p2
--ON p.evep_id1 = p2.evep_id1
JOIN dbo.notes2 n
ON n.eve_id1 = e.EVE_id1
Where
p.EVEP_RESL1 = 'NG'and
clm_adjto = '' ---"NG"This report is going to be changed to the negoitated report the field is going to be "NG"
--e.eve_resl1 = 'NG'and
--p2.evep_nego = 'Y' and
--((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001'AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)
Order by e.eve_resl1,p.EVEP_RESL1
END
View 3 Replies
View Related
