ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,206 through 2,220 (of 10,143 total)

RE: Metrics Table or Performance Tuning

ScottPletcher (8/25/2015)
ChrisM@Work (8/25/2015)
ScottPletcher (8/21/2015)
ChrisM@Work (8/21/2015)
ScottPletcher (8/21/2015)
ChrisM@Work (8/21/2015)
The inner select can be accelerated with this index:
CREATE INDEX ix_Helper ON [cts].[exception_Main]
(productArea, reportable, reportYear, reportMonth, queueID)
INCLUDE (volume, cost, exceptionDateTime)
But with the...
^{“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

August 25, 2015 at 9:06 am

#1822223
RE: Metrics Table or Performance Tuning

ScottPletcher (8/21/2015)
ChrisM@Work (8/21/2015)
ScottPletcher (8/21/2015)
ChrisM@Work (8/21/2015)
The inner select can be accelerated with this index:
CREATE INDEX ix_Helper ON [cts].[exception_Main]
(productArea, reportable, reportYear, reportMonth, queueID)
INCLUDE (volume, cost, exceptionDateTime)
But with the best clustered...
^{“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

August 25, 2015 at 8:15 am

#1822210
RE: Case statement that contains multiplication

cory.bullard76 (8/25/2015)
the datatype for that field is a Varchar
Yes, but how many characters?
VARCHAR(n)
^{“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

August 25, 2015 at 7:37 am

#1822199
RE: Case statement that contains multiplication

cory.bullard76 (8/24/2015)
The values from labels.bagdisp range from 2-60. Is that what you are asking for?
Gail's asking you to look at the table definition. Easiest way to do that 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

August 25, 2015 at 7:29 am

#1822196
RE: udf with tally table runs slower than while loop

Josh Leane-155117 (8/25/2015)
Thanks for the additional suggestions Chris. I tried with the Tally table in the function, that slowed it slightly from 5 secs to 7 secs. Presumably...
^{“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

August 25, 2015 at 2:06 am

#1822141
RE: conversion failed on nested query

cajun_sql (8/24/2015)
Hi,
I'm working with a 'nested query,' which is needed to only select the non-top IDs of a resultset having more than one row with an ID in common.
In other...
^{“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

August 25, 2015 at 1:54 am

#1822139
RE: Select top 101

mariandalalau (8/24/2015)
...
The query is like this:
select top 100 GroupId, count(HouseId)
from House h
group by h.GroupId
order by max([DateCreated]) desc
...
The query is like this, or the query is this? If your actual 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

August 24, 2015 at 9:22 am

#1822005
RE: Skip and update every 'n' row

select
n,
NewColumn = CASE n%4 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' WHEN 0 THEN 'D' END
from ( -- just some sample...
^{“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

August 24, 2015 at 7:54 am

#1821961
RE: Designing Query

mitzyturbo (8/24/2015)
Sorry for the delay lads
Sean, apologies I'm on here long enough to know what I should have included with this one, please see below for sample data and existing...
^{“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

August 24, 2015 at 5:11 am

#1821910
RE: udf with tally table runs slower than while loop

Josh Leane-155117 (8/24/2015)
Hi Chris, I tried your suggestions,
1) clustered index on tally table didn't make any difference, still 120 secs on test data
2) using LEN(@vstrText) < N made huge...
^{“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

August 24, 2015 at 3:17 am

#1821895
RE: Are the posted questions getting worse?

I'd like to nominate Steve Munson for an honorary SSC sainthood for his handling of the OP in this thread. Steve stuck with it through to the end when mere...
^{“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

August 24, 2015 at 1:37 am

#1821891
RE: udf with tally table runs slower than while loop

There are three key problems with the way you have structured the tally table version.
Firstly, your tally table should have a unique clustered index on n, or define it...
^{“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

August 24, 2015 at 1:31 am

#1821890
RE: Metrics Table or Performance Tuning

ScottPletcher (8/21/2015)
ChrisM@Work (8/21/2015)
The inner select can be accelerated with this index:
CREATE INDEX ix_Helper ON [cts].[exception_Main]
(productArea, reportable, reportYear, reportMonth, queueID)
INCLUDE (volume, cost, exceptionDateTime)
But with the best clustered index now...
^{“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

August 21, 2015 at 8:24 am

#1821711
RE: how to overcome from the tempdb spill over ?

Some generic reformatting:
SELECT
DA.DeterminantID, DA.DeterminantGroupID, D.ModifiedDate, DAV.TypeID, DA.IncludeDataFormat, DA.ExcludeDataFormat, DAV.VALUE1, DAV.VALUE2, DA.AddDate,
CASE WHEN DF.TblName = 'Codes'
THEN CASE
WHEN DF.DeterminantField = 'CPT Codes' THEN 1
WHEN DF.DeterminantField = 'Revenue Codes' THEN 0
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

August 21, 2015 at 7:42 am

#1821688
RE: Linkserver issue

sammy10691 (8/21/2015)
HI All ,
I Am faing a problem while calling the code using a link server .The query is able to...
^{“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

August 21, 2015 at 6:43 am

#1821660

Viewing 15 posts - 2,206 through 2,220 (of 10,143 total)