Flat file to table

Hi,

I have a set of flat files and transforming it to SQL server. If I do that in 2000 it was done with in 45 seconds for 1.5 M records. If I do the same in SSIS it takes 3 minutes. Why there is difference in time that too lower when compared to the previous version. I used the data access mode as "Fast load". Am I missing anything while doing through SSIS?

There's so many "it depends" answers to this its not really worth posting a possible reason.

What exactly is the data flow doing? Where is the bottleneck?

-Jamie

|||

Its a very straight transformation. CSV file to a table and all the fields are set as Varchar,

- No validations made on the transformation

- No Calculations.

- No aggregations

again its a very straight transformation.

|||one thing i forget to mention. In 2000 I am using the global variable for looping the source files. In SSIS i used "For each loop" container.|||

And where is the bottleneck? Is it in sourcing the data or loading it to the target?

Check this out for tips on diagnosing bottlenecks:

http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx

-Jamie

|||

Jamie,

Thanks for sending the link, I will go through it in the evening as I am now in office. In the mean time I fixed and the performance is increased from 3 minutes to just 21 seconds (2000 took 45 seconds for the same transformation). The change I made is previously it was Native OLE DB but I changed it to MS OLE DB. If you find time could you please send any link or explain how this has created the dramatic change in performance.

Thanks for your time.

|||

I'm not sure what you mean by "native OLE DB". Can you send a link to the OLE DB driver that you were using?

-Jamie

|||

Jamie,

The link you provided was awesome. Thanks to Donald farmer for wonderful explanation and for you to identifing it to me on the right time.

Initially i had the provider as "Native OLE DB\SQL Native client" in the connection manager when it gives outpu on 3 minutes. When I changed this to "Native OLE DB \ Microsft OLE DB Provider for SQL server" it was processint the same task in less than 30 minutes. Is this due to the driver? how do i choose the best dirver?

|||

Dhanasu wrote:

...it was processint the same task in less than 30 minutes...

Based on your above comment, I'm assuming you mean "30 seconds" not 30 minutes.

|||Yes you're correct. it is 30 seconds.|||

That is an interesting observation. I would expect the opposite results, as SQL Native Client is the more recent provider.

It is almost certain that the difference lies in the used provider. I would try to ask why that is on the Data Access forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thanks.

sql

First time, it times-out. After that it is fine.

I have a stored procedure that runs slowly and takes over 60 seconds sometimes, but almost all of the time it takes only one or two seconds. In both cases the same input data is used and there have been no changes to the table. The slow running is after the query has not been run for several days.

In the background 16 records are inserted every minute. The table has 5 million records. There are two indexes on the table one is clustered the other is unclustered. They are very well tuned. The record contains a two integers and 90 floats.

The stored procedure gets one record from the database which is the latest record for a particular entity. If I go to the web site after a few days the page times out. If I go to Mgmt Studio, and run the sp form there it takes a very long time but eventually comes back with the correct answer. If execute the stored procedure after that the response is a second or two using the same input data. If I change the input data the response is equally as fast. The web site also works very quickly too.

What is going on and how do I fix it?

Pops73

Try running the SP with SET STATISTICS IO ON, then look at the messages tab. It sounds like the first time you run the query, it is having to do physical reads as opposed to logical reads. The second time you run it, it is doing logical reads, which would be much, much faster.|||

Thank you for the response and it sounds reasonable. I restarted the server which I thought would clear the buffer cache and cause the problem to occur. But it didn't after the restart the timeout did not occur. Response was good. Is the buffer cache saved through a restart of a server? How can I clear the cache to easily reproduce the problem and gather the statistics?

Pops73

|||

Restarting the server does clear the the buffer cache. You can run the command DBCC FREEPROCCACHE to clear out the procedure cache, and DBCC DROPCLEANBUFFERS to flush the data cache.

http://msdn2.microsoft.com/en-US/library/ms187762.aspx

Run these commands:

DBCC FREEPROCCACHE

GO

CHECKPOINT

GO

DBCC DROPCLEANBUFFERS

|||

Thank you for your help. This still does not reproduce the problem.

Table '#7F0FE051'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 607, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

More info -- I moved the indexes. I have the data in one file group with three files and the indexes in another file group with two files. I still saw the problem.

running Sql Server 2005 sp1

I had the problem yesterday in another db instance of the same applicaiton on another machine. This table only had one record instead of 5 million. Two different queries will show the same problem

Part of the table definition

CREATE TABLE [dbo].[TempMtrScan](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[ShovelID] [int] NOT NULL,

[DateTime] [datetime] NOT NULL,

[FHoistNDE] [float] NULL,

[FHoistDE] [float] NULL,

[FHoistIP] [float] NULL,

[FHoistFLD] [float] NULL, ............

Query 1

INSERT INTO @.TempMtrScanUnpivot ([ShovelID],[TempMtrScanName], [Temperature])

SELECT ShovelID, TempMtrScanName, Temperature

FROM

(SELECT ShovelID,[DateTime],

FHoistNDE,FHoistDE,

RHoistNDE,RHoistDE,

CrowdNDE,CrowdDE,

SwingNDE,SwingDE,

RearSwingNDE,RearSwingDE,

RPropelNDE,RPropelDE,

LPropelNDE,LPropelDE,

LFrontSwingNDE,LFrontSwingDE,

RFrontSwingNDE,RFrontSwingDE,

TransPhaseA,TransPhaseB,TransPhaseC,

DriveInletTemp1,DriveInletTemp2,DriveInletTemp3,DriveInletTemp4,

DriveInletTemp5,DriveInletTemp6,DriveInletTemp7,DriveInletTemp8,

CwdMotorTemp,CwdMotorWindABTemp,CwdMotorWindBCTemp,CwdMotorWindCATemp,

PplRhtMtrMotorTemp,PplRhtMtrMotorWindABTemp,PplRhtMtrMotorWindBCTemp,PplRhtMtrMotorWindCATemp,

SwgLFntMtrMotorTemp,SwgLFntMtrMotorWindABTemp,SwgLFntMtrMotorWindBCTemp,SwgLFntMtrMotorWindCATemp,

HstRearMtrMotorTemp,HstRearMtrMotorWindABTemp,HstRearMtrMotorWindBCTemp,HstRearMtrMotorWindCATemp,

PplLftMtrMotorTemp,PplLftMtrMotorWindABTemp,PplLftMtrMotorWindBCTemp,PplLftMtrMotorWindCATemp,

SwgRFntMtrMotorTemp,SwgRFntMtrMotorWindABTemp,SwgRFntMtrMotorWindBCTemp,SwgRFntMtrMotorWindCATemp,

HstFntMtrMotorTemp,HstFntMtrMotorWindABTemp,HstFntMtrMotorWindBCTemp,HstFntMtrMotorWindCATemp,

SwgRearMtrMotorTemp,SwgRearMtrMotorWindABTemp,SwgRearMtrMotorWindBCTemp,SwgRearMtrMotorWindCATemp,

SwgFntMtrMotorTemp, SwgFntMtrMotorWindABTemp, SwgFntMtrMotorWindBCTemp, SwgFntMtrMotorWindCATemp, ACS800Temp,

TempDiffPhsU, TempDiffPhsV, TempDiffPhsW

FROM dbo.TempMtrScan

WHERE [Datetime] =

(SELECT MAX([datetime])

FROM dbo.TempMtrScan

WHERE ShovelID = @.ShovelID)

AND ShovelID = @.ShovelID) A

UNPIVOT (

Temperature

FOR TempMtrScanName

IN ( FHoistNDE,FHoistDE,

RHoistNDE,RHoistDE,

CrowdNDE,CrowdDE,

SwingNDE,SwingDE,

RearSwingNDE,RearSwingDE,

RPropelNDE,RPropelDE,

LPropelNDE,LPropelDE,

LFrontSwingNDE,LFrontSwingDE,

RFrontSwingNDE,RFrontSwingDE,

TransPhaseA,TransPhaseB,TransPhaseC,

DriveInletTemp1,DriveInletTemp2,DriveInletTemp3,DriveInletTemp4,

DriveInletTemp5,DriveInletTemp6,DriveInletTemp7,DriveInletTemp8,

CwdMotorTemp,CwdMotorWindABTemp,CwdMotorWindBCTemp,CwdMotorWindCATemp,

PplRhtMtrMotorTemp,PplRhtMtrMotorWindABTemp,PplRhtMtrMotorWindBCTemp,PplRhtMtrMotorWindCATemp,

SwgLFntMtrMotorTemp,SwgLFntMtrMotorWindABTemp,SwgLFntMtrMotorWindBCTemp,SwgLFntMtrMotorWindCATemp,

HstRearMtrMotorTemp,HstRearMtrMotorWindABTemp,HstRearMtrMotorWindBCTemp,HstRearMtrMotorWindCATemp,

PplLftMtrMotorTemp,PplLftMtrMotorWindABTemp,PplLftMtrMotorWindBCTemp,PplLftMtrMotorWindCATemp,

SwgRFntMtrMotorTemp,SwgRFntMtrMotorWindABTemp,SwgRFntMtrMotorWindBCTemp,SwgRFntMtrMotorWindCATemp,

HstFntMtrMotorTemp,HstFntMtrMotorWindABTemp,HstFntMtrMotorWindBCTemp,HstFntMtrMotorWindCATemp,

SwgRearMtrMotorTemp,SwgRearMtrMotorWindABTemp,SwgRearMtrMotorWindBCTemp,SwgRearMtrMotorWindCATemp,

SwgFntMtrMotorTemp, SwgFntMtrMotorWindABTemp, SwgFntMtrMotorWindBCTemp, SwgFntMtrMotorWindCATemp, ACS800Temp,

TempDiffPhsU, TempDiffPhsV, TempDiffPhsW

)

) AS unpvt

Table '#7F0FE051'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 607, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query 2

SELECT

(max(T.FHoistNDE)* 1.8 + 32) as "FHoistNDE" ,

(max(T.FHoistDE)* 1.8 + 32) as "FHoistDE" ,

(max(T.FHoistIP)* 1.8 + 32) as "FHoistIP" ,

(max(T.FHoistFLD)* 1.8 + 32) as "FHoistFLD" ,

(max(T.RHoistNDE)* 1.8 + 32) as "RHoistNDE" ,

(max(T.RHoistDE)* 1.8 + 32) as "RHoistDE" ,

(max(T.RHoistIP)* 1.8 + 32) as "RHoistIP" ,

(max(T.RHoistFLD)* 1.8 + 32) as "RHoistFLD" ,

(max(T.CrowdNDE)* 1.8 + 32) as "CrowdNDE" ,

(max(T.CrowdDE) * 1.8 + 32) as "CrowdDE" ,

(max(T.CrowdIP) * 1.8 + 32) as "CrowdIP" ,

(max(T.CrowdFLD) * 1.8 + 32) as "CrowdFLD" ,

(max(T.SwingNDE)* 1.8 + 32) as "SwingNDE" ,

(max(T.SwingDE) * 1.8 + 32) as "SwingDE" ,

(max(T.SwingIP) * 1.8 + 32) as "SwingIP" ,

(max(T.SwingFLD)* 1.8 + 32) as "SwingFLD" ,

(max(T.RearSwingNDE)* 1.8 + 32) as "RearSwingNDE" ,

(max(T.RearSwingDE) * 1.8 + 32) as "RearSwingDE" ,

(max(T.RearSwingIP) * 1.8 + 32) as "RearSwingIP" ,

(max(T.RearSwingFLD)* 1.8 + 32) as "RearSwingFLD" ,

(max(T.RPropelNDE) * 1.8 + 32) as "RPropelNDE" ,

(max(T.RPropelDE)* 1.8 + 32) as "RPropelDE" ,

(max(T.RPropelIP) * 1.8 + 32) as "RPropelIP" ,

(max(T.RPropelFLD) * 1.8 + 32) as "RPropelFLD" ,

(max(T.LPropelNDE) * 1.8 + 32) as "LPropelNDE" ,

(max(T.LPropelDE) * 1.8 + 32) as "LPropelDE" ,

(max(T.LPropelIP) * 1.8 + 32) as "LPropelIP" ,

(max(T.LPropelFLD) * 1.8 + 32) as "LPropelFLD" ,

(max(T.LFrontSwingNDE)* 1.8 + 32) as "LFrontSwingNDE" ,

(max(T.LFrontSwingDE)* 1.8 + 32) as "LFrontSwingDE" ,

(max(T.LFrontSwingIP) * 1.8 + 32) as "LFrontSwingIP" ,

(max(T.LFrontSwingFLD) * 1.8 + 32) as "LFrontSwingFLD" ,

(max(T.RFrontSwingNDE) * 1.8 + 32) as "RFrontSwingNDE" ,

(max(T.RFrontSwingDE) * 1.8 + 32) as "RFrontSwingDE" ,

(max(T.RFrontSwingIP)* 1.8 + 32) as "RFrontSwingIP" ,

(max(T.RFrontSwingFLD) * 1.8 + 32) as "RFrontSwingFLD" ,

(max(T.TransPhaseA)* 1.8 + 32) as "TransPhaseA" ,

(max(T.TransPhaseB)* 1.8 + 32) as "TransPhaseB" ,

(max(T.TransPhaseC)* 1.8 + 32) as "TransPhaseC" ,

(max(T.DriveInletTemp1)* 1.8 + 32) as "DriveInletTemp1" ,

(max(T.DriveInletTemp2)* 1.8 + 32) as "DriveInletTemp2" ,

(max(T.DriveInletTemp3)* 1.8 + 32) as "DriveInletTemp3" ,

(max(T.DriveInletTemp4)* 1.8 + 32) as "DriveInletTemp4" ,

(max(T.DriveInletTemp5)* 1.8 + 32) as "DriveInletTemp5" ,

(max(T.DriveInletTemp6)* 1.8 + 32) as "DriveInletTemp6" ,

(max(T.DriveInletTemp7)* 1.8 + 32) as "DriveInletTemp7" ,

(max(T.DriveInletTemp8)* 1.8 + 32) as "DriveInletTemp8" ,

(max(T.CwdMotorTemp)* 1.8 + 32) as "CwdMotorTemp" ,

(max(T.CwdMotorWindABTemp)* 1.8 + 32) as "CwdMotorWindABTemp" ,

(max(T.CwdMotorWindBCTemp)* 1.8 + 32) as "CwdMotorWindBCTemp" ,

(max(T.CwdMotorWindCATemp)* 1.8 + 32) as "CwdMotorWindCATemp" ,

(max(T.PplRhtMtrMotorTemp)* 1.8 + 32) as "PplRhtMtrMotorTemp" ,

(max(T.PplRhtMtrMotorWindABTemp)* 1.8 + 32) as "PplRhtMtrMotorWindABTemp" ,

(max(T.PplRhtMtrMotorWindBCTemp)* 1.8 + 32) as "PplRhtMtrMotorWindBCTemp" ,

(max(T.PplRhtMtrMotorWindCATemp)* 1.8 + 32) as "PplRhtMtrMotorWindCATemp" ,

(max(T.SwgLFntMtrMotorTemp)* 1.8 + 32) as "SwgLFntMtrMotorTemp" ,

(max(T.SwgLFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgLFntMtrMotorWindABTemp" ,

(max(T.SwgLFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgLFntMtrMotorWindBCTemp" ,

(max(T.SwgLFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgLFntMtrMotorWindCATemp" ,

(max(T.HstRearMtrMotorTemp)* 1.8 + 32) as "HstRearMtrMotorTemp" ,

(max(T.HstRearMtrMotorWindABTemp)* 1.8 + 32) as "HstRearMtrMotorWindABTemp" ,

(max(T.HstRearMtrMotorWindBCTemp)* 1.8 + 32) as "HstRearMtrMotorWindBCTemp" ,

(max(T.HstRearMtrMotorWindCATemp)* 1.8 + 32) as "HstRearMtrMotorWindCATemp" ,

(max(T.PplLftMtrMotorTemp)* 1.8 + 32) as "PplLftMtrMotorTemp" ,

(max(T.PplLftMtrMotorWindABTemp)* 1.8 + 32) as "PplLftMtrMotorWindABTemp" ,

(max(T.PplLftMtrMotorWindBCTemp)* 1.8 + 32) as "PplLftMtrMotorWindBCTemp" ,

(max(T.PplLftMtrMotorWindCATemp)* 1.8 + 32) as "PplLftMtrMotorWindCATemp" ,

(max(T.SwgRFntMtrMotorTemp)* 1.8 + 32) as "SwgRFntMtrMotorTemp" ,

(max(T.SwgRFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgRFntMtrMotorWindABTemp" ,

(max(T.SwgRFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgRFntMtrMotorWindBCTemp" ,

(max(T.SwgRFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgRFntMtrMotorWindCATemp" ,

(max(T.HstFntMtrMotorTemp)* 1.8 + 32) as "HstFntMtrMotorTemp" ,

(max(T.HstFntMtrMotorWindABTemp)* 1.8 + 32) as "HstFntMtrMotorWindABTemp" ,

(max(T.HstFntMtrMotorWindBCTemp)* 1.8 + 32) as "HstFntMtrMotorWindBCTemp" ,

(max(T.HstFntMtrMotorWindCATemp)* 1.8 + 32) as "HstFntMtrMotorWindCATemp" ,

(max(T.SwgRearMtrMotorTemp)* 1.8 + 32) as "SwgRearMtrMotorTemp" ,

(max(T.SwgRearMtrMotorWindABTemp)* 1.8 + 32) as "SwgRearMtrMotorWindABTemp" ,

(max(T.SwgRearMtrMotorWindBCTemp)* 1.8 + 32) as "SwgRearMtrMotorWindBCTemp" ,

(max(T.SwgRearMtrMotorWindCATemp)* 1.8 + 32) as "SwgRearMtrMotorWindCATemp" ,

(max(T.SwgFntMtrMotorTemp)* 1.8 + 32) as "SwgFntMtrMotorTemp" ,

(max(T.SwgFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgFntMtrMotorWindABTemp" ,

(max(T.SwgFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgFntMtrMotorWindBCTemp" ,

(max(T.SwgFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgFntMtrMotorWindBCTemp" ,

(max(T.ACS800Temp)* 1.8 + 32) as "ACS800Temp" ,

(max(T.TempDiffPhsU)* 1.8 + 32) as "TempDiffPhsU" ,

(max(T.TempDiffPhsV)* 1.8 + 32) as "TempDiffPhsV" ,

(max(T.TempDiffPhsW)* 1.8 + 32) as "TempDiffPhsW" ,

Max(T.DateTime) as "TemperatureTime"

FROM TempMtrScan T

JOIN Shovel S ON T.ShovelID = S.ID

WHERE (S.ID =@.ShovelID) AND T.DATETIME <= @.EndTime AND T.DATETIME >= @.StartTime

Group by ShovelID, datepart(yy,T.DateTime), datepart(dy,T.DateTime),datepart(hh,T.DateTime),datepart(mi,T.DateTime)/@.Duration

order by Max(T.DateTime) asc

(1943 row(s) affected)

Table 'Shovel'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 3, logical reads 38911, physical reads 493, read-ahead reads 736, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Warning: Null value is eliminated by an aggregate or other SET operation.

First time, it times out. After that it is fine.

I have a stored procedure that runs slowly and takes over 60 seconds sometimes, but almost all of the time it takes only one or two seconds. In both cases the same input data is used and there have been no changes to the table. The slow running is after the query has not been run for several days.

In the background 16 records are inserted every minute. The table has 5 million records. There are two indexes on the table one is clustered the other is unclustered. They are very well tuned. The record contains a two integers and 90 floats.

The stored procedure gets one record from the database which is the latest record for a particular entity. If I go to the web site after a few days the page times out. If I go to Mgmt Studio, and run the sp form there it takes a very long time but eventually comes back with the correct answer. If execute the stored procedure after that the response is a second or two using the same input data. If I change the input data the response is equally as fast. The web site also works very quickly too.

What is going on and how do I fix it?

Pops73

Try running the SP with SET STATISTICS IO ON, then look at the messages tab. It sounds like the first time you run the query, it is having to do physical reads as opposed to logical reads. The second time you run it, it is doing logical reads, which would be much, much faster.|||

Thank you for the response and it sounds reasonable. I restarted the server which I thought would clear the buffer cache and cause the problem to occur. But it didn't after the restart the timeout did not occur. Response was good. Is the buffer cache saved through a restart of a server? How can I clear the cache to easily reproduce the problem and gather the statistics?

Pops73

|||

Restarting the server does clear the the buffer cache. You can run the command DBCC FREEPROCCACHE to clear out the procedure cache, and DBCC DROPCLEANBUFFERS to flush the data cache.

http://msdn2.microsoft.com/en-US/library/ms187762.aspx

Run these commands:

DBCC FREEPROCCACHE

GO

CHECKPOINT

GO

DBCC DROPCLEANBUFFERS

|||

Thank you for your help. This still does not reproduce the problem.

Table '#7F0FE051'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 607, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

More info -- I moved the indexes. I have the data in one file group with three files and the indexes in another file group with two files. I still saw the problem.

running Sql Server 2005 sp1

I had the problem yesterday in another db instance of the same applicaiton on another machine. This table only had one record instead of 5 million. Two different queries will show the same problem

Part of the table definition

CREATE TABLE [dbo].[TempMtrScan](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[ShovelID] [int] NOT NULL,

[DateTime] [datetime] NOT NULL,

[FHoistNDE] [float] NULL,

[FHoistDE] [float] NULL,

[FHoistIP] [float] NULL,

[FHoistFLD] [float] NULL, ............

Query 1

INSERT INTO @.TempMtrScanUnpivot ([ShovelID],[TempMtrScanName], [Temperature])

SELECT ShovelID, TempMtrScanName, Temperature

FROM

(SELECT ShovelID,[DateTime],

FHoistNDE,FHoistDE,

RHoistNDE,RHoistDE,

CrowdNDE,CrowdDE,

SwingNDE,SwingDE,

RearSwingNDE,RearSwingDE,

RPropelNDE,RPropelDE,

LPropelNDE,LPropelDE,

LFrontSwingNDE,LFrontSwingDE,

RFrontSwingNDE,RFrontSwingDE,

TransPhaseA,TransPhaseB,TransPhaseC,

DriveInletTemp1,DriveInletTemp2,DriveInletTemp3,DriveInletTemp4,

DriveInletTemp5,DriveInletTemp6,DriveInletTemp7,DriveInletTemp8,

CwdMotorTemp,CwdMotorWindABTemp,CwdMotorWindBCTemp,CwdMotorWindCATemp,

PplRhtMtrMotorTemp,PplRhtMtrMotorWindABTemp,PplRhtMtrMotorWindBCTemp,PplRhtMtrMotorWindCATemp,

SwgLFntMtrMotorTemp,SwgLFntMtrMotorWindABTemp,SwgLFntMtrMotorWindBCTemp,SwgLFntMtrMotorWindCATemp,

HstRearMtrMotorTemp,HstRearMtrMotorWindABTemp,HstRearMtrMotorWindBCTemp,HstRearMtrMotorWindCATemp,

PplLftMtrMotorTemp,PplLftMtrMotorWindABTemp,PplLftMtrMotorWindBCTemp,PplLftMtrMotorWindCATemp,

SwgRFntMtrMotorTemp,SwgRFntMtrMotorWindABTemp,SwgRFntMtrMotorWindBCTemp,SwgRFntMtrMotorWindCATemp,

HstFntMtrMotorTemp,HstFntMtrMotorWindABTemp,HstFntMtrMotorWindBCTemp,HstFntMtrMotorWindCATemp,

SwgRearMtrMotorTemp,SwgRearMtrMotorWindABTemp,SwgRearMtrMotorWindBCTemp,SwgRearMtrMotorWindCATemp,

SwgFntMtrMotorTemp, SwgFntMtrMotorWindABTemp, SwgFntMtrMotorWindBCTemp, SwgFntMtrMotorWindCATemp, ACS800Temp,

TempDiffPhsU, TempDiffPhsV, TempDiffPhsW

FROM dbo.TempMtrScan

WHERE [Datetime] =

(SELECT MAX([datetime])

FROM dbo.TempMtrScan

WHERE ShovelID = @.ShovelID)

AND ShovelID = @.ShovelID) A

UNPIVOT (

Temperature

FOR TempMtrScanName

IN ( FHoistNDE,FHoistDE,

RHoistNDE,RHoistDE,

CrowdNDE,CrowdDE,

SwingNDE,SwingDE,

RearSwingNDE,RearSwingDE,

RPropelNDE,RPropelDE,

LPropelNDE,LPropelDE,

LFrontSwingNDE,LFrontSwingDE,

RFrontSwingNDE,RFrontSwingDE,

TransPhaseA,TransPhaseB,TransPhaseC,

DriveInletTemp1,DriveInletTemp2,DriveInletTemp3,DriveInletTemp4,

DriveInletTemp5,DriveInletTemp6,DriveInletTemp7,DriveInletTemp8,

CwdMotorTemp,CwdMotorWindABTemp,CwdMotorWindBCTemp,CwdMotorWindCATemp,

PplRhtMtrMotorTemp,PplRhtMtrMotorWindABTemp,PplRhtMtrMotorWindBCTemp,PplRhtMtrMotorWindCATemp,

SwgLFntMtrMotorTemp,SwgLFntMtrMotorWindABTemp,SwgLFntMtrMotorWindBCTemp,SwgLFntMtrMotorWindCATemp,

HstRearMtrMotorTemp,HstRearMtrMotorWindABTemp,HstRearMtrMotorWindBCTemp,HstRearMtrMotorWindCATemp,

PplLftMtrMotorTemp,PplLftMtrMotorWindABTemp,PplLftMtrMotorWindBCTemp,PplLftMtrMotorWindCATemp,

SwgRFntMtrMotorTemp,SwgRFntMtrMotorWindABTemp,SwgRFntMtrMotorWindBCTemp,SwgRFntMtrMotorWindCATemp,

HstFntMtrMotorTemp,HstFntMtrMotorWindABTemp,HstFntMtrMotorWindBCTemp,HstFntMtrMotorWindCATemp,

SwgRearMtrMotorTemp,SwgRearMtrMotorWindABTemp,SwgRearMtrMotorWindBCTemp,SwgRearMtrMotorWindCATemp,

SwgFntMtrMotorTemp, SwgFntMtrMotorWindABTemp, SwgFntMtrMotorWindBCTemp, SwgFntMtrMotorWindCATemp, ACS800Temp,

TempDiffPhsU, TempDiffPhsV, TempDiffPhsW

)

) AS unpvt

Table '#7F0FE051'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 607, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query 2

SELECT

(max(T.FHoistNDE)* 1.8 + 32) as "FHoistNDE" ,

(max(T.FHoistDE)* 1.8 + 32) as "FHoistDE" ,

(max(T.FHoistIP)* 1.8 + 32) as "FHoistIP" ,

(max(T.FHoistFLD)* 1.8 + 32) as "FHoistFLD" ,

(max(T.RHoistNDE)* 1.8 + 32) as "RHoistNDE" ,

(max(T.RHoistDE)* 1.8 + 32) as "RHoistDE" ,

(max(T.RHoistIP)* 1.8 + 32) as "RHoistIP" ,

(max(T.RHoistFLD)* 1.8 + 32) as "RHoistFLD" ,

(max(T.CrowdNDE)* 1.8 + 32) as "CrowdNDE" ,

(max(T.CrowdDE) * 1.8 + 32) as "CrowdDE" ,

(max(T.CrowdIP) * 1.8 + 32) as "CrowdIP" ,

(max(T.CrowdFLD) * 1.8 + 32) as "CrowdFLD" ,

(max(T.SwingNDE)* 1.8 + 32) as "SwingNDE" ,

(max(T.SwingDE) * 1.8 + 32) as "SwingDE" ,

(max(T.SwingIP) * 1.8 + 32) as "SwingIP" ,

(max(T.SwingFLD)* 1.8 + 32) as "SwingFLD" ,

(max(T.RearSwingNDE)* 1.8 + 32) as "RearSwingNDE" ,

(max(T.RearSwingDE) * 1.8 + 32) as "RearSwingDE" ,

(max(T.RearSwingIP) * 1.8 + 32) as "RearSwingIP" ,

(max(T.RearSwingFLD)* 1.8 + 32) as "RearSwingFLD" ,

(max(T.RPropelNDE) * 1.8 + 32) as "RPropelNDE" ,

(max(T.RPropelDE)* 1.8 + 32) as "RPropelDE" ,

(max(T.RPropelIP) * 1.8 + 32) as "RPropelIP" ,

(max(T.RPropelFLD) * 1.8 + 32) as "RPropelFLD" ,

(max(T.LPropelNDE) * 1.8 + 32) as "LPropelNDE" ,

(max(T.LPropelDE) * 1.8 + 32) as "LPropelDE" ,

(max(T.LPropelIP) * 1.8 + 32) as "LPropelIP" ,

(max(T.LPropelFLD) * 1.8 + 32) as "LPropelFLD" ,

(max(T.LFrontSwingNDE)* 1.8 + 32) as "LFrontSwingNDE" ,

(max(T.LFrontSwingDE)* 1.8 + 32) as "LFrontSwingDE" ,

(max(T.LFrontSwingIP) * 1.8 + 32) as "LFrontSwingIP" ,

(max(T.LFrontSwingFLD) * 1.8 + 32) as "LFrontSwingFLD" ,

(max(T.RFrontSwingNDE) * 1.8 + 32) as "RFrontSwingNDE" ,

(max(T.RFrontSwingDE) * 1.8 + 32) as "RFrontSwingDE" ,

(max(T.RFrontSwingIP)* 1.8 + 32) as "RFrontSwingIP" ,

(max(T.RFrontSwingFLD) * 1.8 + 32) as "RFrontSwingFLD" ,

(max(T.TransPhaseA)* 1.8 + 32) as "TransPhaseA" ,

(max(T.TransPhaseB)* 1.8 + 32) as "TransPhaseB" ,

(max(T.TransPhaseC)* 1.8 + 32) as "TransPhaseC" ,

(max(T.DriveInletTemp1)* 1.8 + 32) as "DriveInletTemp1" ,

(max(T.DriveInletTemp2)* 1.8 + 32) as "DriveInletTemp2" ,

(max(T.DriveInletTemp3)* 1.8 + 32) as "DriveInletTemp3" ,

(max(T.DriveInletTemp4)* 1.8 + 32) as "DriveInletTemp4" ,

(max(T.DriveInletTemp5)* 1.8 + 32) as "DriveInletTemp5" ,

(max(T.DriveInletTemp6)* 1.8 + 32) as "DriveInletTemp6" ,

(max(T.DriveInletTemp7)* 1.8 + 32) as "DriveInletTemp7" ,

(max(T.DriveInletTemp8)* 1.8 + 32) as "DriveInletTemp8" ,

(max(T.CwdMotorTemp)* 1.8 + 32) as "CwdMotorTemp" ,

(max(T.CwdMotorWindABTemp)* 1.8 + 32) as "CwdMotorWindABTemp" ,

(max(T.CwdMotorWindBCTemp)* 1.8 + 32) as "CwdMotorWindBCTemp" ,

(max(T.CwdMotorWindCATemp)* 1.8 + 32) as "CwdMotorWindCATemp" ,

(max(T.PplRhtMtrMotorTemp)* 1.8 + 32) as "PplRhtMtrMotorTemp" ,

(max(T.PplRhtMtrMotorWindABTemp)* 1.8 + 32) as "PplRhtMtrMotorWindABTemp" ,

(max(T.PplRhtMtrMotorWindBCTemp)* 1.8 + 32) as "PplRhtMtrMotorWindBCTemp" ,

(max(T.PplRhtMtrMotorWindCATemp)* 1.8 + 32) as "PplRhtMtrMotorWindCATemp" ,

(max(T.SwgLFntMtrMotorTemp)* 1.8 + 32) as "SwgLFntMtrMotorTemp" ,

(max(T.SwgLFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgLFntMtrMotorWindABTemp" ,

(max(T.SwgLFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgLFntMtrMotorWindBCTemp" ,

(max(T.SwgLFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgLFntMtrMotorWindCATemp" ,

(max(T.HstRearMtrMotorTemp)* 1.8 + 32) as "HstRearMtrMotorTemp" ,

(max(T.HstRearMtrMotorWindABTemp)* 1.8 + 32) as "HstRearMtrMotorWindABTemp" ,

(max(T.HstRearMtrMotorWindBCTemp)* 1.8 + 32) as "HstRearMtrMotorWindBCTemp" ,

(max(T.HstRearMtrMotorWindCATemp)* 1.8 + 32) as "HstRearMtrMotorWindCATemp" ,

(max(T.PplLftMtrMotorTemp)* 1.8 + 32) as "PplLftMtrMotorTemp" ,

(max(T.PplLftMtrMotorWindABTemp)* 1.8 + 32) as "PplLftMtrMotorWindABTemp" ,

(max(T.PplLftMtrMotorWindBCTemp)* 1.8 + 32) as "PplLftMtrMotorWindBCTemp" ,

(max(T.PplLftMtrMotorWindCATemp)* 1.8 + 32) as "PplLftMtrMotorWindCATemp" ,

(max(T.SwgRFntMtrMotorTemp)* 1.8 + 32) as "SwgRFntMtrMotorTemp" ,

(max(T.SwgRFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgRFntMtrMotorWindABTemp" ,

(max(T.SwgRFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgRFntMtrMotorWindBCTemp" ,

(max(T.SwgRFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgRFntMtrMotorWindCATemp" ,

(max(T.HstFntMtrMotorTemp)* 1.8 + 32) as "HstFntMtrMotorTemp" ,

(max(T.HstFntMtrMotorWindABTemp)* 1.8 + 32) as "HstFntMtrMotorWindABTemp" ,

(max(T.HstFntMtrMotorWindBCTemp)* 1.8 + 32) as "HstFntMtrMotorWindBCTemp" ,

(max(T.HstFntMtrMotorWindCATemp)* 1.8 + 32) as "HstFntMtrMotorWindCATemp" ,

(max(T.SwgRearMtrMotorTemp)* 1.8 + 32) as "SwgRearMtrMotorTemp" ,

(max(T.SwgRearMtrMotorWindABTemp)* 1.8 + 32) as "SwgRearMtrMotorWindABTemp" ,

(max(T.SwgRearMtrMotorWindBCTemp)* 1.8 + 32) as "SwgRearMtrMotorWindBCTemp" ,

(max(T.SwgRearMtrMotorWindCATemp)* 1.8 + 32) as "SwgRearMtrMotorWindCATemp" ,

(max(T.SwgFntMtrMotorTemp)* 1.8 + 32) as "SwgFntMtrMotorTemp" ,

(max(T.SwgFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgFntMtrMotorWindABTemp" ,

(max(T.SwgFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgFntMtrMotorWindBCTemp" ,

(max(T.SwgFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgFntMtrMotorWindBCTemp" ,

(max(T.ACS800Temp)* 1.8 + 32) as "ACS800Temp" ,

(max(T.TempDiffPhsU)* 1.8 + 32) as "TempDiffPhsU" ,

(max(T.TempDiffPhsV)* 1.8 + 32) as "TempDiffPhsV" ,

(max(T.TempDiffPhsW)* 1.8 + 32) as "TempDiffPhsW" ,

Max(T.DateTime) as "TemperatureTime"

FROM TempMtrScan T

JOIN Shovel S ON T.ShovelID = S.ID

WHERE (S.ID =@.ShovelID) AND T.DATETIME <= @.EndTime AND T.DATETIME >= @.StartTime

Group by ShovelID, datepart(yy,T.DateTime), datepart(dy,T.DateTime),datepart(hh,T.DateTime),datepart(mi,T.DateTime)/@.Duration

order by Max(T.DateTime) asc

(1943 row(s) affected)

Table 'Shovel'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 3, logical reads 38911, physical reads 493, read-ahead reads 736, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Warning: Null value is eliminated by an aggregate or other SET operation.

First time it times out after that it is fine

I have a stored procedure that runs slowly and takes over 60 seconds sometimes, but almost all of the time it takes only one or two seconds. In both cases the same input data is used and there have been no changes to the table. The slow running is after the query has not been run for several days.

In the background 16 records are inserted every minute. The table has 5 million records. There are two indexes on the table one is clustered the other is unclustered. They are very well tuned. The record contains a two integers and 90 floats.

The stored procedure gets one record from the database which is the latest record for a particular entity. If I go to the web site after a few days the page times out. If I go to Mgmt Studio, and run the sp form there it takes a very long time but eventually comes back with the correct answer. If execute the stored procedure after that the response is a second or two using the same input data. If I change the input data the response is equally as fast. The web site also works very quickly too.

What is going on and how do I fix it?

Pops73

Try running the SP with SET STATISTICS IO ON, then look at the messages tab. It sounds like the first time you run the query, it is having to do physical reads as opposed to logical reads. The second time you run it, it is doing logical reads, which would be much, much faster.|||

Thank you for the response and it sounds reasonable. I restarted the server which I thought would clear the buffer cache and cause the problem to occur. But it didn't after the restart the timeout did not occur. Response was good. Is the buffer cache saved through a restart of a server? How can I clear the cache to easily reproduce the problem and gather the statistics?

Pops73

|||

Restarting the server does clear the the buffer cache. You can run the command DBCC FREEPROCCACHE to clear out the procedure cache, and DBCC DROPCLEANBUFFERS to flush the data cache.

http://msdn2.microsoft.com/en-US/library/ms187762.aspx

Run these commands:

DBCC FREEPROCCACHE

GO

CHECKPOINT

GO

DBCC DROPCLEANBUFFERS

|||

Thank you for your help. This still does not reproduce the problem.

Table '#7F0FE051'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 607, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

More info -- I moved the indexes. I have the data in one file group with three files and the indexes in another file group with two files. I still saw the problem.

running Sql Server 2005 sp1

I had the problem yesterday in another db instance of the same applicaiton on another machine. This table only had one record instead of 5 million. Two different queries will show the same problem

Part of the table definition

CREATE TABLE [dbo].[TempMtrScan](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[ShovelID] [int] NOT NULL,

[DateTime] [datetime] NOT NULL,

[FHoistNDE] [float] NULL,

[FHoistDE] [float] NULL,

[FHoistIP] [float] NULL,

[FHoistFLD] [float] NULL, ............

Query 1

INSERT INTO @.TempMtrScanUnpivot ([ShovelID],[TempMtrScanName], [Temperature])

SELECT ShovelID, TempMtrScanName, Temperature

FROM

(SELECT ShovelID,[DateTime],

FHoistNDE,FHoistDE,

RHoistNDE,RHoistDE,

CrowdNDE,CrowdDE,

SwingNDE,SwingDE,

RearSwingNDE,RearSwingDE,

RPropelNDE,RPropelDE,

LPropelNDE,LPropelDE,

LFrontSwingNDE,LFrontSwingDE,

RFrontSwingNDE,RFrontSwingDE,

TransPhaseA,TransPhaseB,TransPhaseC,

DriveInletTemp1,DriveInletTemp2,DriveInletTemp3,DriveInletTemp4,

DriveInletTemp5,DriveInletTemp6,DriveInletTemp7,DriveInletTemp8,

CwdMotorTemp,CwdMotorWindABTemp,CwdMotorWindBCTemp,CwdMotorWindCATemp,

PplRhtMtrMotorTemp,PplRhtMtrMotorWindABTemp,PplRhtMtrMotorWindBCTemp,PplRhtMtrMotorWindCATemp,

SwgLFntMtrMotorTemp,SwgLFntMtrMotorWindABTemp,SwgLFntMtrMotorWindBCTemp,SwgLFntMtrMotorWindCATemp,

HstRearMtrMotorTemp,HstRearMtrMotorWindABTemp,HstRearMtrMotorWindBCTemp,HstRearMtrMotorWindCATemp,

PplLftMtrMotorTemp,PplLftMtrMotorWindABTemp,PplLftMtrMotorWindBCTemp,PplLftMtrMotorWindCATemp,

SwgRFntMtrMotorTemp,SwgRFntMtrMotorWindABTemp,SwgRFntMtrMotorWindBCTemp,SwgRFntMtrMotorWindCATemp,

HstFntMtrMotorTemp,HstFntMtrMotorWindABTemp,HstFntMtrMotorWindBCTemp,HstFntMtrMotorWindCATemp,

SwgRearMtrMotorTemp,SwgRearMtrMotorWindABTemp,SwgRearMtrMotorWindBCTemp,SwgRearMtrMotorWindCATemp,

SwgFntMtrMotorTemp, SwgFntMtrMotorWindABTemp, SwgFntMtrMotorWindBCTemp, SwgFntMtrMotorWindCATemp, ACS800Temp,

TempDiffPhsU, TempDiffPhsV, TempDiffPhsW

FROM dbo.TempMtrScan

WHERE [Datetime] =

(SELECT MAX([datetime])

FROM dbo.TempMtrScan

WHERE ShovelID = @.ShovelID)

AND ShovelID = @.ShovelID) A

UNPIVOT (

Temperature

FOR TempMtrScanName

IN ( FHoistNDE,FHoistDE,

RHoistNDE,RHoistDE,

CrowdNDE,CrowdDE,

SwingNDE,SwingDE,

RearSwingNDE,RearSwingDE,

RPropelNDE,RPropelDE,

LPropelNDE,LPropelDE,

LFrontSwingNDE,LFrontSwingDE,

RFrontSwingNDE,RFrontSwingDE,

TransPhaseA,TransPhaseB,TransPhaseC,

DriveInletTemp1,DriveInletTemp2,DriveInletTemp3,DriveInletTemp4,

DriveInletTemp5,DriveInletTemp6,DriveInletTemp7,DriveInletTemp8,

CwdMotorTemp,CwdMotorWindABTemp,CwdMotorWindBCTemp,CwdMotorWindCATemp,

PplRhtMtrMotorTemp,PplRhtMtrMotorWindABTemp,PplRhtMtrMotorWindBCTemp,PplRhtMtrMotorWindCATemp,

SwgLFntMtrMotorTemp,SwgLFntMtrMotorWindABTemp,SwgLFntMtrMotorWindBCTemp,SwgLFntMtrMotorWindCATemp,

HstRearMtrMotorTemp,HstRearMtrMotorWindABTemp,HstRearMtrMotorWindBCTemp,HstRearMtrMotorWindCATemp,

PplLftMtrMotorTemp,PplLftMtrMotorWindABTemp,PplLftMtrMotorWindBCTemp,PplLftMtrMotorWindCATemp,

SwgRFntMtrMotorTemp,SwgRFntMtrMotorWindABTemp,SwgRFntMtrMotorWindBCTemp,SwgRFntMtrMotorWindCATemp,

HstFntMtrMotorTemp,HstFntMtrMotorWindABTemp,HstFntMtrMotorWindBCTemp,HstFntMtrMotorWindCATemp,

SwgRearMtrMotorTemp,SwgRearMtrMotorWindABTemp,SwgRearMtrMotorWindBCTemp,SwgRearMtrMotorWindCATemp,

SwgFntMtrMotorTemp, SwgFntMtrMotorWindABTemp, SwgFntMtrMotorWindBCTemp, SwgFntMtrMotorWindCATemp, ACS800Temp,

TempDiffPhsU, TempDiffPhsV, TempDiffPhsW

)

) AS unpvt

Table '#7F0FE051'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 607, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query 2

SELECT

(max(T.FHoistNDE)* 1.8 + 32) as "FHoistNDE" ,

(max(T.FHoistDE)* 1.8 + 32) as "FHoistDE" ,

(max(T.FHoistIP)* 1.8 + 32) as "FHoistIP" ,

(max(T.FHoistFLD)* 1.8 + 32) as "FHoistFLD" ,

(max(T.RHoistNDE)* 1.8 + 32) as "RHoistNDE" ,

(max(T.RHoistDE)* 1.8 + 32) as "RHoistDE" ,

(max(T.RHoistIP)* 1.8 + 32) as "RHoistIP" ,

(max(T.RHoistFLD)* 1.8 + 32) as "RHoistFLD" ,

(max(T.CrowdNDE)* 1.8 + 32) as "CrowdNDE" ,

(max(T.CrowdDE) * 1.8 + 32) as "CrowdDE" ,

(max(T.CrowdIP) * 1.8 + 32) as "CrowdIP" ,

(max(T.CrowdFLD) * 1.8 + 32) as "CrowdFLD" ,

(max(T.SwingNDE)* 1.8 + 32) as "SwingNDE" ,

(max(T.SwingDE) * 1.8 + 32) as "SwingDE" ,

(max(T.SwingIP) * 1.8 + 32) as "SwingIP" ,

(max(T.SwingFLD)* 1.8 + 32) as "SwingFLD" ,

(max(T.RearSwingNDE)* 1.8 + 32) as "RearSwingNDE" ,

(max(T.RearSwingDE) * 1.8 + 32) as "RearSwingDE" ,

(max(T.RearSwingIP) * 1.8 + 32) as "RearSwingIP" ,

(max(T.RearSwingFLD)* 1.8 + 32) as "RearSwingFLD" ,

(max(T.RPropelNDE) * 1.8 + 32) as "RPropelNDE" ,

(max(T.RPropelDE)* 1.8 + 32) as "RPropelDE" ,

(max(T.RPropelIP) * 1.8 + 32) as "RPropelIP" ,

(max(T.RPropelFLD) * 1.8 + 32) as "RPropelFLD" ,

(max(T.LPropelNDE) * 1.8 + 32) as "LPropelNDE" ,

(max(T.LPropelDE) * 1.8 + 32) as "LPropelDE" ,

(max(T.LPropelIP) * 1.8 + 32) as "LPropelIP" ,

(max(T.LPropelFLD) * 1.8 + 32) as "LPropelFLD" ,

(max(T.LFrontSwingNDE)* 1.8 + 32) as "LFrontSwingNDE" ,

(max(T.LFrontSwingDE)* 1.8 + 32) as "LFrontSwingDE" ,

(max(T.LFrontSwingIP) * 1.8 + 32) as "LFrontSwingIP" ,

(max(T.LFrontSwingFLD) * 1.8 + 32) as "LFrontSwingFLD" ,

(max(T.RFrontSwingNDE) * 1.8 + 32) as "RFrontSwingNDE" ,

(max(T.RFrontSwingDE) * 1.8 + 32) as "RFrontSwingDE" ,

(max(T.RFrontSwingIP)* 1.8 + 32) as "RFrontSwingIP" ,

(max(T.RFrontSwingFLD) * 1.8 + 32) as "RFrontSwingFLD" ,

(max(T.TransPhaseA)* 1.8 + 32) as "TransPhaseA" ,

(max(T.TransPhaseB)* 1.8 + 32) as "TransPhaseB" ,

(max(T.TransPhaseC)* 1.8 + 32) as "TransPhaseC" ,

(max(T.DriveInletTemp1)* 1.8 + 32) as "DriveInletTemp1" ,

(max(T.DriveInletTemp2)* 1.8 + 32) as "DriveInletTemp2" ,

(max(T.DriveInletTemp3)* 1.8 + 32) as "DriveInletTemp3" ,

(max(T.DriveInletTemp4)* 1.8 + 32) as "DriveInletTemp4" ,

(max(T.DriveInletTemp5)* 1.8 + 32) as "DriveInletTemp5" ,

(max(T.DriveInletTemp6)* 1.8 + 32) as "DriveInletTemp6" ,

(max(T.DriveInletTemp7)* 1.8 + 32) as "DriveInletTemp7" ,

(max(T.DriveInletTemp8)* 1.8 + 32) as "DriveInletTemp8" ,

(max(T.CwdMotorTemp)* 1.8 + 32) as "CwdMotorTemp" ,

(max(T.CwdMotorWindABTemp)* 1.8 + 32) as "CwdMotorWindABTemp" ,

(max(T.CwdMotorWindBCTemp)* 1.8 + 32) as "CwdMotorWindBCTemp" ,

(max(T.CwdMotorWindCATemp)* 1.8 + 32) as "CwdMotorWindCATemp" ,

(max(T.PplRhtMtrMotorTemp)* 1.8 + 32) as "PplRhtMtrMotorTemp" ,

(max(T.PplRhtMtrMotorWindABTemp)* 1.8 + 32) as "PplRhtMtrMotorWindABTemp" ,

(max(T.PplRhtMtrMotorWindBCTemp)* 1.8 + 32) as "PplRhtMtrMotorWindBCTemp" ,

(max(T.PplRhtMtrMotorWindCATemp)* 1.8 + 32) as "PplRhtMtrMotorWindCATemp" ,

(max(T.SwgLFntMtrMotorTemp)* 1.8 + 32) as "SwgLFntMtrMotorTemp" ,

(max(T.SwgLFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgLFntMtrMotorWindABTemp" ,

(max(T.SwgLFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgLFntMtrMotorWindBCTemp" ,

(max(T.SwgLFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgLFntMtrMotorWindCATemp" ,

(max(T.HstRearMtrMotorTemp)* 1.8 + 32) as "HstRearMtrMotorTemp" ,

(max(T.HstRearMtrMotorWindABTemp)* 1.8 + 32) as "HstRearMtrMotorWindABTemp" ,

(max(T.HstRearMtrMotorWindBCTemp)* 1.8 + 32) as "HstRearMtrMotorWindBCTemp" ,

(max(T.HstRearMtrMotorWindCATemp)* 1.8 + 32) as "HstRearMtrMotorWindCATemp" ,

(max(T.PplLftMtrMotorTemp)* 1.8 + 32) as "PplLftMtrMotorTemp" ,

(max(T.PplLftMtrMotorWindABTemp)* 1.8 + 32) as "PplLftMtrMotorWindABTemp" ,

(max(T.PplLftMtrMotorWindBCTemp)* 1.8 + 32) as "PplLftMtrMotorWindBCTemp" ,

(max(T.PplLftMtrMotorWindCATemp)* 1.8 + 32) as "PplLftMtrMotorWindCATemp" ,

(max(T.SwgRFntMtrMotorTemp)* 1.8 + 32) as "SwgRFntMtrMotorTemp" ,

(max(T.SwgRFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgRFntMtrMotorWindABTemp" ,

(max(T.SwgRFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgRFntMtrMotorWindBCTemp" ,

(max(T.SwgRFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgRFntMtrMotorWindCATemp" ,

(max(T.HstFntMtrMotorTemp)* 1.8 + 32) as "HstFntMtrMotorTemp" ,

(max(T.HstFntMtrMotorWindABTemp)* 1.8 + 32) as "HstFntMtrMotorWindABTemp" ,

(max(T.HstFntMtrMotorWindBCTemp)* 1.8 + 32) as "HstFntMtrMotorWindBCTemp" ,

(max(T.HstFntMtrMotorWindCATemp)* 1.8 + 32) as "HstFntMtrMotorWindCATemp" ,

(max(T.SwgRearMtrMotorTemp)* 1.8 + 32) as "SwgRearMtrMotorTemp" ,

(max(T.SwgRearMtrMotorWindABTemp)* 1.8 + 32) as "SwgRearMtrMotorWindABTemp" ,

(max(T.SwgRearMtrMotorWindBCTemp)* 1.8 + 32) as "SwgRearMtrMotorWindBCTemp" ,

(max(T.SwgRearMtrMotorWindCATemp)* 1.8 + 32) as "SwgRearMtrMotorWindCATemp" ,

(max(T.SwgFntMtrMotorTemp)* 1.8 + 32) as "SwgFntMtrMotorTemp" ,

(max(T.SwgFntMtrMotorWindABTemp)* 1.8 + 32) as "SwgFntMtrMotorWindABTemp" ,

(max(T.SwgFntMtrMotorWindBCTemp)* 1.8 + 32) as "SwgFntMtrMotorWindBCTemp" ,

(max(T.SwgFntMtrMotorWindCATemp)* 1.8 + 32) as "SwgFntMtrMotorWindBCTemp" ,

(max(T.ACS800Temp)* 1.8 + 32) as "ACS800Temp" ,

(max(T.TempDiffPhsU)* 1.8 + 32) as "TempDiffPhsU" ,

(max(T.TempDiffPhsV)* 1.8 + 32) as "TempDiffPhsV" ,

(max(T.TempDiffPhsW)* 1.8 + 32) as "TempDiffPhsW" ,

Max(T.DateTime) as "TemperatureTime"

FROM TempMtrScan T

JOIN Shovel S ON T.ShovelID = S.ID

WHERE (S.ID =@.ShovelID) AND T.DATETIME <= @.EndTime AND T.DATETIME >= @.StartTime

Group by ShovelID, datepart(yy,T.DateTime), datepart(dy,T.DateTime),datepart(hh,T.DateTime),datepart(mi,T.DateTime)/@.Duration

order by Max(T.DateTime) asc

(1943 row(s) affected)

Table 'Shovel'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TempMtrScan'. Scan count 3, logical reads 38911, physical reads 493, read-ahead reads 736, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Warning: Null value is eliminated by an aggregate or other SET operation.

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000. Since then the following has occurred.
1.Downloaded and Installed but not using the MS Office interop stuff (Client)
2.Downloaded and Installed MDAC 2.8(Client)
3.Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
(Server)
Thank you in advance for your help.
Other items that may help:
I have an Access 2003 ADP on the client that uses the same SQL Server DB and
it also takes 15 seconds before displaying the table objects. However, if I
close the adp and reopen it within 15 seconds, the tables display
immediately. If I close the adp and wait longer than 15 seconds, it takes 15
seconds to display the tables. Once the tables are displayed, access to data
within any table is sub second.
I tried this with my .Net application. Same result. If I restart my
application within 15 seconds, then access to SQL Server is sub-second. If I
restart my application after 15 seconds, then it takes 15 seconds to connect.
I have another adp on the Server that points to the same SQL Server
database. It opens and displays the tables immediately.
Enterprise Manager runs on the Server. As I drill down the tree to
databases>Tables>TblX>Return all rows responds as quickly as I can click the
nodes etc. However, if I right click on the database (or any of the standard
DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
first time.
Jim
Please refer to original. Sent duplicate in error. Sorry
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater if I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. However
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Server.
> I have not always had this problem. Unfortunately, I can’t say what I did
> that caused it, but the problem did not exist when I was running VS.Net 2003
> and the Server was running MSDE 2000. Since then the following has occurred.
> 1.Downloaded and Installed but not using the MS Office interop stuff (Client)
> 2.Downloaded and Installed MDAC 2.8(Client)
> 3.Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
> (Server)
> Thank you in advance for your help.
> Other items that may help:
> I have an Access 2003 ADP on the client that uses the same SQL Server DB and
> it also takes 15 seconds before displaying the table objects. However, if I
> close the adp and reopen it within 15 seconds, the tables display
> immediately. If I close the adp and wait longer than 15 seconds, it takes 15
> seconds to display the tables. Once the tables are displayed, access to data
> within any table is sub second.
> I tried this with my .Net application. Same result. If I restart my
> application within 15 seconds, then access to SQL Server is sub-second. If I
> restart my application after 15 seconds, then it takes 15 seconds to connect.
> I have another adp on the Server that points to the same SQL Server
> database. It opens and displays the tables immediately.
> Enterprise Manager runs on the Server. As I drill down the tree to
> databases>Tables>TblX>Return all rows responds as quickly as I can click the
> nodes etc. However, if I right click on the database (or any of the standard
> DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
> first time.
> --
> Jim

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000.
Jim
PLease Refer to my original. Sent Duplicate in error. Sorry.
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater if I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. However
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Server.
> I have not always had this problem. Unfortunately, I can’t say what I did
> that caused it, but the problem did not exist when I was running VS.Net 2003
> and the Server was running MSDE 2000.
> --
> Jim

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000. Since then the following has occurred.
1.Downloaded and Installed but not using the MS Office interop stuff (Client)
2.Downloaded and Installed MDAC 2.8(Client)
3.Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
(Server)
Thank you in advance for your help.
Other items that may help:
I have an Access 2003 ADP on the client that uses the same SQL Server DB and
it also takes 15 seconds before displaying the table objects. However, if I
close the adp and reopen it within 15 seconds, the tables display
immediately. If I close the adp and wait longer than 15 seconds, it takes 15
seconds to display the tables. Once the tables are displayed, access to data
within any table is sub second.
I tried this with my .Net application. Same result. If I restart my
application within 15 seconds, then access to SQL Server is sub-second. If I
restart my application after 15 seconds, then it takes 15 seconds to connect.
I have another adp on the Server that points to the same SQL Server
database. It opens and displays the tables immediately.
Enterprise Manager runs on the Server. As I drill down the tree to
databases>Tables>TblX>Return all rows responds as quickly as I can click the
nodes etc. However, if I right click on the database (or any of the standard
DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
first time.
Jim
PLEASE REFER TO MY ORIGINAL. I submitted 4. The first 3 came back with
Posting Error. Sorry.
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater if I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. However
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Server.
> I have not always had this problem. Unfortunately, I can’t say what I did
> that caused it, but the problem did not exist when I was running VS.Net 2003
> and the Server was running MSDE 2000. Since then the following has occurred.
> 1.Downloaded and Installed but not using the MS Office interop stuff (Client)
> 2.Downloaded and Installed MDAC 2.8(Client)
> 3.Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
> (Server)
> Thank you in advance for your help.
> Other items that may help:
> I have an Access 2003 ADP on the client that uses the same SQL Server DB and
> it also takes 15 seconds before displaying the table objects. However, if I
> close the adp and reopen it within 15 seconds, the tables display
> immediately. If I close the adp and wait longer than 15 seconds, it takes 15
> seconds to display the tables. Once the tables are displayed, access to data
> within any table is sub second.
> I tried this with my .Net application. Same result. If I restart my
> application within 15 seconds, then access to SQL Server is sub-second. If I
> restart my application after 15 seconds, then it takes 15 seconds to connect.
> I have another adp on the Server that points to the same SQL Server
> database. It opens and displays the tables immediately.
> Enterprise Manager runs on the Server. As I drill down the tree to
> databases>Tables>TblX>Return all rows responds as quickly as I can click the
> nodes etc. However, if I right click on the database (or any of the standard
> DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
> first time.
> --
> Jim

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if
I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server
.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000. Since then the following has occurred.
1. Downloaded and Installed but not using the MS Office interop stuff (Clien
t)
2. Downloaded and Installed MDAC 2.8 (Client)
3. Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
(Server)
Thank you in advance for your help.
Other items that may help:
I have an Access 2003 ADP on the client that uses the same SQL Server DB and
it also takes 15 seconds before displaying the table objects. However, if I
close the adp and reopen it within 15 seconds, the tables display
immediately. If I close the adp and wait longer than 15 seconds, it takes 15
seconds to display the tables. Once the tables are displayed, access to data
within any table is sub second.
I tried this with my .Net application. Same result. If I restart my
application within 15 seconds, then access to SQL Server is sub-second. If I
restart my application after 15 seconds, then it takes 15 seconds to connect
.
I have another adp on the Server that points to the same SQL Server
database. It opens and displays the tables immediately.
Enterprise Manager runs on the Server. As I drill down the tree to
databases>Tables>TblX>Return all rows responds as quickly as I can click the
nodes etc. However, if I right click on the database (or any of the standard
DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
first time.
JimPLEASE REFER TO MY ORIGINAL. I submitted 4. The first 3 came back with
Posting Error. Sorry.
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater i
f I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The dela
y
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. Howeve
r
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Serv
er.
> I have not always had this problem. Unfortunately, I can’t say what I di
d
> that caused it, but the problem did not exist when I was running VS.Net 20
03
> and the Server was running MSDE 2000. Since then the following has occurre
d.
> 1. Downloaded and Installed but not using the MS Office interop stuff (Cli
ent)
> 2. Downloaded and Installed MDAC 2.8 (Client)
> 3. Downloaded and Installed SQL Server 2000 Developer for Enterprise Mgr.
> (Server)
> Thank you in advance for your help.
> Other items that may help:
> I have an Access 2003 ADP on the client that uses the same SQL Server DB a
nd
> it also takes 15 seconds before displaying the table objects. However, if
I
> close the adp and reopen it within 15 seconds, the tables display
> immediately. If I close the adp and wait longer than 15 seconds, it takes
15
> seconds to display the tables. Once the tables are displayed, access to da
ta
> within any table is sub second.
> I tried this with my .Net application. Same result. If I restart my
> application within 15 seconds, then access to SQL Server is sub-second. If
I
> restart my application after 15 seconds, then it takes 15 seconds to conne
ct.
> I have another adp on the Server that points to the same SQL Server
> database. It opens and displays the tables immediately.
> Enterprise Manager runs on the Server. As I drill down the tree to
> databases>Tables>TblX>Return all rows responds as quickly as I can click t
he
> nodes etc. However, if I right click on the database (or any of the standa
rd
> DB’s (Master, Model, etc) and select properties, it takes 15 seconds the
> first time.
> --
> Jim

First Connection to SQL Server 2000 is slow (takes 15 seconds)

I have a client machine running VS.Net 2003, using TCP/IP to connect to a
server running SQL Server 2000 Developer. The first time I connect to the
server it always takes 15 seconds. Subsequent work on the server is very
quick but that first 15 seconds is extremely painful. It doesn’t mater if
I
perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The delay
is the same if I run an application from within the IDE or from a build. I
read about similar problems being solved with upgrades to MDAC 2.6. However
the Client runs 2.8 and the server runs 2.7 so that resolution does not
appear to apply. My Client is the only machine that connects with SQL Server
.
I have not always had this problem. Unfortunately, I can’t say what I did
that caused it, but the problem did not exist when I was running VS.Net 2003
and the Server was running MSDE 2000.
--
JimPLease Refer to my original. Sent Duplicate in error. Sorry.
"AdvanTouch" wrote:

> I have a client machine running VS.Net 2003, using TCP/IP to connect to a
> server running SQL Server 2000 Developer. The first time I connect to the
> server it always takes 15 seconds. Subsequent work on the server is very
> quick but that first 15 seconds is extremely painful. It doesn’t mater i
f I
> perform a DA.Fill(DS) or a cmd.open(), it still takes 15 seconds. The dela
y
> is the same if I run an application from within the IDE or from a build. I
> read about similar problems being solved with upgrades to MDAC 2.6. Howeve
r
> the Client runs 2.8 and the server runs 2.7 so that resolution does not
> appear to apply. My Client is the only machine that connects with SQL Serv
er.
> I have not always had this problem. Unfortunately, I can’t say what I di
d
> that caused it, but the problem did not exist when I was running VS.Net 20
03
> and the Server was running MSDE 2000.
> --
> Jim