ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,491 through 5,505 (of 10,143 total)

RE: TSQL Case Statement help

ScottPletcher (10/3/2012)
<<snip>>
I consider 20-30% relatively much slower, particularly given how basic the task was.
A 5 min query then takes ~6+ mins instead. Not tragic, obviously, but significant.
The figures are...
^{“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

October 3, 2012 at 9:04 am

#1544989
RE: find increase/decrease amount

sayedkhalid99 (10/2/2012)
i have changed the my input and output and add date and comments to make it clear, for multiple entry with in a month it shall take the last...
^{“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

October 3, 2012 at 7:39 am

#1544912
RE: Are the posted questions getting worse?

Koen Verbeeck (10/3/2012)
Brandie Tarvin (10/3/2012)
Koen Verbeeck (9/28/2012)
I know Europe is small, that's why we shipped all the village idiots to another continent to make more room 😀
That would be Australia,...
^{“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

October 3, 2012 at 4:50 am

#1544799
RE: TSQL Case Statement help

ScottPletcher (10/2/2012)
SQL Server is optimized to do table lookups, whereas CASE statements are comparatively very slow.
Therefore, I suggest using a lookup table, as suggested by others. You absolutely want...
^{“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

October 3, 2012 at 3:45 am

#1544758
RE: TSQL Case Statement help

John Mitchell-245523 (10/2/2012)
I think I would create a lookup table, like this:
CREATE TABLE CodeActions (
Code char(4)
,TheActionvarchar(12)
)
INSERT INTO CodeActions (Code,TheAction)
VALUES
('ISSP','Install'),
('IECO','Install'),
('IECM','Install'),
('IESP','Install'),
('IEHD','Install'),
('ISHD','Install'),
('FRSI','Install'),
('SB42','Service Call'),
('SB4W','Service Call'),
('HD42','Service Call'),
('HD4W','Service Call'),
('SA2C','Service Call'),
('SA2W','Service Call'),
('HD2C','Service Call'),
('HD2W','Service Call'),
('SNCO','Service Call')
That way you...
^{“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

October 3, 2012 at 1:51 am

#1544713
RE: TSQL Case Statement help

CELKO (10/2/2012)
...NO! We hate UDFs; they are not declarative, do not optimize ...
Nonsense. The OP has explained that (s)he is a novice, Joe, and is willing to learn or wouldn't...
^{“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

October 3, 2012 at 1:50 am

#1544712
RE: Get the overall status of a particular product

Smash125 (10/2/2012)
How about this one correct me if i am wrong
;WITH C AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Status ORDER BY Product) AS Rn FROM #TestData
),
C1 AS
(
SELECT *,MIN(Rn)OVER(PARTITION BY Status) MinRn FROM...
^{“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

October 2, 2012 at 9:10 am

#1544443
RE: Get the overall status of a particular product

Smash125 (10/2/2012)
You are correct. Just wanted to know in one of the queries
select Product, MIN(status)
FROM MyCTE
GROUP BY Product
the second column says MIN(status) just wanted to know which...
^{“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

October 2, 2012 at 8:51 am

#1544426
RE: INVALID OBJECT NAME

deepeshdhake (10/1/2012)
Hello,
I have wrote one store proc. So, in the stored procedure, it is using one table which is in the different database. When I am trying to execute 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

October 2, 2012 at 8:45 am

#1544420
RE: SUBSTRING And CHARINDEX to find start/end of string

Eliza (9/30/2012)
Hi all,
I have a big long text string that can vary in lengh and contrain audit information about data that has changed in the database...
Thanks
Eliza
Which version of SQL Server...
^{“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

October 2, 2012 at 8:40 am

#1544415
RE: Get the overall status of a particular product

Change the order of your test data and see of this solution works:
DROP TABLE #TestData
CREATE TABLE #TestData
(
Product NVARCHAR(100),
Parts NVARCHAR(100),
Status NVARCHAR(100)
)
INSERT INTO #TestData(Product,Parts,Status)
SELECT 'Laptop1','mouse','OK' UNION ALL
SELECT 'Laptop1','screen','OK' UNION ALL
...
^{“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

October 2, 2012 at 8:20 am

#1544406
RE: how to avoid duplicate business logic

norbert.manyi (10/2/2012)
ChrisM@Work (10/2/2012)
If you can't code it up as a view, then it's unlikely that you can code it up as a query.
You could still do it as a quite...
^{“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

October 2, 2012 at 8:03 am

#1544395
RE: how to avoid duplicate business logic

norbert.manyi (10/2/2012)
Views would be a better option than functions - coded properly they won't hammer performance quite like the suggested UDF. You can't find a good example right 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

October 2, 2012 at 7:27 am

#1544369
RE: TSQL Case Statement help

waqqas.zia (10/2/2012)
Ok so my case statement should always read @jdt_jty_code?
Yes - the @ tells SQL server that the object is a variable. Your scalar UDF works with changes;
create FUNCTION [dbo].[Tester]...
^{“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

October 2, 2012 at 7:23 am

#1544365
RE: TSQL Case Statement help

You're better off using inline table-valued functions. Here's an example:
ALTER FUNCTION [dbo].[iTVF_Tester]
(@jdt_jty_code varchar(50))
RETURNS TABLE AS RETURN
SELECT ReturnValue
FROM (VALUES
('ISCO','Install'),
('ISSP','Install'),
('IECO','Install'),
('IECM','Install'),
('IESP','Install'),
('IEHD','Install'),
('ISHD','Install'),
('FRSI','Install'),
('SB42','Service Call'),
('SB4W','Service Call'),
('HD42','Service Call'),
('HD4W','Service Call'),
('SA2C','Service Call'),
('SA2W','Service Call'),
('HD2C','Service Call'),
('HD2W','Service Call'),
('SNCO','Service Call')
) x...
^{“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

October 2, 2012 at 7:19 am

#1544361

Viewing 15 posts - 5,491 through 5,505 (of 10,143 total)