ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,721 through 3,735 (of 10,143 total)

RE: Cursor and update

phineas629 (1/24/2014)
... I am required to use the cursor...
Perfectly reasonable so long as this is homework. If not, then do it properly:
UPDATE Outlet.tblProductMaster SET
Product_Status = CASE
WHEN Seller_No = @strSellerNo1...
^{“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

January 24, 2014 at 3:08 am

#1683049
RE: Optimize Update Statement

If you're wondering what the variables are for in the UPDATE, they collect some column values as they are before any updates have been performed:
DROP TABLE #EXPORTMAIN
CREATE TABLE #EXPORTMAIN (
ID...
^{“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

January 24, 2014 at 2:41 am

#1683041
RE: Optimize Update Statement

Same statement rewritten as UPDATE FROM, with nonsense logic commented out:
-- Formatted
DECLARE @tempS AS nvarchar(150)
DECLARE @tempW AS nvarchar(50)
DECLARE @tempQ As nvarchar(5)
UPDATE e SET
@tempS = SKU1, --...
^{“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

January 24, 2014 at 2:35 am

#1683040
RE: Optimize Update Statement

Formatted for readability:
-- Formatted
DECLARE @tempS AS nvarchar(150)
DECLARE @tempW AS nvarchar(50)
DECLARE @tempQ As nvarchar(5)
UPDATE dbo.EXPORTMAIN SET
@tempS = SKU1,
@tempW = WHSELOC1,
@tempQ = QTY1,
SKU1 = Case
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

January 24, 2014 at 1:59 am

#1683025
RE: Rank over with Clustered Index

lianvh 89542 (1/23/2014)
Thank you for the feedback. I am sorry for expressing myself incorrectly. Business wanted the data on screen more precisely ordered. It was up to Dev and 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

January 23, 2014 at 3:56 am

#1682699
RE: Rank over with Clustered Index

lianvh 89542 (1/23/2014)
Thank you for the feedback. There was a business requirement for the concatenated clustered index . The rank over was then needed to order the rows more accurately...
^{“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

January 23, 2014 at 2:20 am

#1682659
RE: How to display minimum values only in table?

Prassad Dabbada V R (1/21/2014)
Write CTE
check below link for example on CTE
http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER-2008
The examples are fine but the author of the article clearly knows little about CTE's and slips on a...
^{“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

January 22, 2014 at 7:19 am

#1682388
RE: Help needed to Process Optimization

Before considering esoteric wait stats and such, your code would benefit from an overhaul to bring your join syntax up to date. Your first query equates to this:
UPDATE pro SET...
^{“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

January 22, 2014 at 6:41 am

#1682367
RE: how to avoid sub queries and FUNCTIONS in where clause ?

kbhanu15 (1/22/2014)
HI Thanks for your reply but the given query is present in a view
i think it wont work in views?
view details:
create view Screen_RatingsData_ProbabilityOfDefault_vw
as
select...
^{“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

January 22, 2014 at 4:34 am

#1682329
RE: how to avoid sub queries and FUNCTIONS in where clause ?

kbhanu15 (1/22/2014)
HI ,
the below query taking 5 minutes time, i want to rewrite this query ?
query details :
select pd.companyId, asOfDate as scoreDate, Convert(numeric(38,10),pd.PDValue) * 100 as zscore
from pdDataMarketSignal_vw...
^{“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

January 22, 2014 at 3:07 am

#1682307
RE: How to optimize case statement

Like Gail, I don't think this expression is going to have much impact on performance. Maintainability is another story - updating n queries to account for product changes leaves 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

January 22, 2014 at 1:56 am

#1682289
RE: Replacing recurring characters in a string with single character

mickyT (1/21/2014)
ChrisM@Work (1/21/2014)
Couple of small changes to MickeyT's otherwise excellent function:
Thanks Chris, I missed that I had messed up the result. I made some changes for performance to what...
^{“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

January 22, 2014 at 1:36 am

#1682285
RE: Running Total

Ford Fairlane (1/19/2014)
Thanks for the heads up Jeff, how would you tackle it then ?
Start by reducing the noise to see what's going on:
SELECT
x.YearMonth,
[DEPTH] = (
SELECT
SUM(b.DEPTH)
FROM HOLELOCATION b
CROSS...
^{“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

January 21, 2014 at 7:19 am

#1681893
RE: select latest records

abhas (1/21/2014)
Thanks All,
I am doing the same.
SELECT
ROW_NUMBER() OVER (PARTITION BY t.CustomerName ORDER BY t.DateAdded DESC) AS RowNumber
from tblCustomer. but it is giving 1,2,3.....RowNumber.
I want again repeat RowNumber to each...
^{“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

January 21, 2014 at 6:48 am

#1681872
RE: Replacing recurring characters in a string with single character

Couple of small changes to MickeyT's otherwise excellent function:
DECLARE @code nvarchar(4000);
SET @code = '99944777777775588888888888812' ;
WITH cteTally as (
SELECT TOP(LEN(@code) - 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))...
^{“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

January 21, 2014 at 6:46 am

#1681871

Viewing 15 posts - 3,721 through 3,735 (of 10,143 total)