ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,091 through 3,105 (of 10,143 total)

RE: Update based on multiple conditions in a single pass?

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 22, 2014 at 5:16 am

#1731365
RE: Where clause Integer Greater than

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
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 22, 2014 at 4:44 am

#1731360
RE: Where clause Integer Greater than

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 22, 2014 at 4:09 am

#1731352
RE: Index grows really fast!

-- 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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 22, 2014 at 3:13 am

#1731344
RE: Spid of -3 holding permanent lock on database and tempdb does not have any tables after restart

Spid -3 is a deferred transaction, Paul Randall blogs about it here[/url].
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 22, 2014 at 2:51 am

#1731335
RE: Non Temp Temp Tables

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 22, 2014 at 2:27 am

#1731332
RE: Table Partiotion on Datetime column - nonclusterd index

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,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 21, 2014 at 9:56 am

#1731070
RE: Table Partiotion on Datetime column - nonclusterd index

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 21, 2014 at 9:32 am

#1731060
RE: CTE with Linked Server: Mulitpart Identifier could not be bound

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?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 21, 2014 at 9:08 am

#1731055
RE: Set datime and convert it

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 21, 2014 at 7:55 am

#1731019
RE: Set datime and convert it

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
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 21, 2014 at 7:32 am

#1731008
RE: Set datime and convert it

hoseam (7/21/2014)
I just corrected it and I get this error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Use CONVERT with...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 21, 2014 at 7:21 am

#1731004
RE: Set datime and convert it

What datatype is EVENTDATE?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 21, 2014 at 7:11 am

#1730996
RE: Stored procedure with results

Oracle, by any chance?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 21, 2014 at 6:53 am

#1730990
RE: Stored procedure with results

Not enough information. Can you post the stored procedure code?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

July 21, 2014 at 6:46 am

#1730987

Viewing 15 posts - 3,091 through 3,105 (of 10,143 total)