ekkaldevi.naveen (7/22/2014)

---

Hi,

I am having a table "Test" with 70 columns. Out of them a composite primary key is defined with 12 columns.

There are around 4 million records in that...

Try this. It might give you some ideas.

SELECT

cp.ServerID

, ds.[server]

, cp.MetricID

, dm.BaseMetric

, cp.MetricValue

, x.RAGLevel AS RAGSTATUS

--INTO RAGTemp

FROM DataTable cp

LEFT JOIN DimExServer ds

ON ds.ServerID = cp.ServerID

LEFT JOIN DimExMetric dm

ON...

Something like this?

DECLARE @MinPeriod int

SELECT @MinPeriod = MIN(a.period)

FROM agr.dbo.atrans a

CROSS APPLY (

SELECT CutOffTid = ISNULL(MAX(ccas_id),0)

FROM dbo.fbs_trans

) x

WHERE a.agrtid > x.CutOffTid

Have you tried timing each statement?

DECLARE @Starttime DATETIME = GETDATE()

DECLARE @CutOffTid int

SELECT @CutOffTid = isnull(max(ccas_id),0)

FROM dbo.fbs_trans (NOLOCK)

print 'Cut Off ID is ' + cast(@CutOffTid as char)

SELECT Starttime = @Starttime, Endtime...

-- change the index to support a nested loops join between rs and r

-- with seeks to r on id and residual predicate of RNT and Man:

create nonclustered index...

Spid -3 is a deferred transaction, Paul Randall blogs about it here[/url].

Hugo Kornelis (7/21/2014)

---

Creating permanent tables in tempdb this way is tricky. They will behave "sort of" like permanent tables (they will not be removed when the batch ends or when...

Minnu (7/21/2014)

---

Actually Performance is not a major issue in my case, trying to implement the table management.

i want to store the older records away by creating partitions.

my overrall request is,...

Minnu (7/21/2014)

---

Select query is taking long time on this table, there are more than 10000 inserts per day.

and this table is mainly used for OLAP process only.

Finally...performance of table needs...

The query you posted - the opening post - doesn't contain the three-part names which are commonly associated with linked servers. How are you running this query?

DROP TABLE #Events

CREATE TABLE #Events (ID INT IDENTITY(1,1) NOT NULL, EVENTDATE DATETIME)

INSERT INTO #Events VALUES

('2014-07-20 14:42:33.360'),

('2014-07-19 14:42:33.360'),

('2014-07-18 14:42:33.360'),

('2014-07-17 14:42:33.360'),

('2014-07-16 14:42:33.360'),

('2014-07-15 14:42:33.360'),

('2014-07-14 14:42:33.360'),

('2014-07-13 14:42:33.360'),

('2014-07-12 14:42:33.360')

-- filter the table for dates between...

hoseam (7/21/2014)

---

declare @ToDate datetime

declare @FromDate datetime

set @FromDate = '1 Feb 2014'

set @ToDate = '28 Feb 0014'

SELECT EVENTDATE,'' AS BRANCHCD, BUSINESS_AREA, CREATE_DATE, WORK_TYPE, QUEUE, STATUS, CREATE_AGE, CATEGORYCD, DELAYREASON1, DELAYREASON2

...

Use CONVERT with...

What datatype is EVENTDATE?

Oracle, by any chance?