ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,411 through 1,425 (of 10,143 total)

RE: Are the posted questions getting worse?

BWFC (7/6/2016)
Phil Parkin (7/5/2016)
tindog (7/5/2016)
Michael L John (7/5/2016)
Can anyone in the U.K. (or is it K minus U these days?) make a recommendation for technical training? We have 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

July 6, 2016 at 1:47 am

#1888621
RE: Stored Procedure execution

In addition to everything Grant has said, it's worth noting that the performance of any query depends upon the accuracy of the information which SQL Server can obtain from 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

July 5, 2016 at 9:46 am

#1888497
RE: Stored Procedure execution

Grant Fritchey (7/5/2016)
If there are differences in structure, are there also differences in the amount of data? Take away different data types and different indexes (which makes comparisons impossible really),...
^{“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 5, 2016 at 6:16 am

#1888435
RE: Query

shirolkar.anand (7/5/2016)
Hi,
Please try using following code-
DECLARE @T TABLE (ID INT, NAME VARCHAR(1))
INSERT @T VALUES (1000,'A'),(990,'C'),(980,'A'),(970,'A'),(960,'B'),(950,'C'),(930,'A')
SELECT * FROM(SELECT ROW_NUMBER() OVER( PARTITION BY NAME ORDER BY ID DESC)ROWNUM,ID,NAME FROM @T )T
WHERE...
^{“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 5, 2016 at 5:42 am

#1888425
RE: Stored Procedure execution

Raghavender (7/5/2016)
But I did not understand why substring made all the noise.
Then how it worked after changing it to like 'UD1\%'
The expression LIKE 'UD1\%' is SARGable, meaning SQL Server can...
^{“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 5, 2016 at 5:41 am

#1888424
RE: Stored Procedure execution

Can we have an actual plan from the fast query please? Thanks.
Here are some suggested improvements for your WHERE clause:
WHERE UD.EmployeeType IN (0,1)
AND NL.NetworkLogonId LIKE 'UD1\%'
--AND SUBSTRING(NL.NetworkLogonId, 1, CHARINDEX('\', NL.NetworkLogonId)-1)...
^{“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 5, 2016 at 3:30 am

#1888399
RE: Stored Procedure execution

Raghavender (7/5/2016)
ChrisM@Work (7/5/2016)
Raghavender (7/5/2016)
Please find the attachment.
1027.sqlplan is test server where query executing with in 3 sec.
1048.sqlplan is production where we have the issue.
Can you please add OPTION(MAXDOP 1) 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

July 5, 2016 at 2:35 am

#1888391
RE: Stored Procedure execution

Raghavender (7/5/2016)
Please find the attachment.
1027.sqlplan is test server where query executing with in 3 sec.
1048.sqlplan is production where we have the issue.
Can you please add OPTION(MAXDOP 1) to the 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 5, 2016 at 2:26 am

#1888388
RE: Query

durga.palepu (7/4/2016)
Hi Chris,
What's the problem with my query? you've just replaced COUNT() with MAX() right?
I could test my version of the query successfully.
Faulty logic. It's the maximum ID value 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

July 4, 2016 at 10:16 am

#1888340
RE: Stored Procedure execution

Raghavender (7/4/2016)
I have executed updatestats and updateusage on both the databases.
One observation is it is blocking it self.
I can see n number of processes with same SPID. In that where...
^{“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 4, 2016 at 9:48 am

#1888336
RE: Query

jagjitsingh (7/4/2016)
Thanks Crism
Can u little quide how it works
Thanks
Sure. Run the CTE part and you will see:
select id, name
, row_number() over(partition by name order by id desc) as...
^{“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 4, 2016 at 7:29 am

#1888304
RE: Query

with ctenames
as
(
select id, name
, row_number() over(partition by name order by id desc) as rownum
,MAX(ID) over(partition by name ) maxid
from #name
)
select id, name
from ctenames
where rownum <=3 --Replace this with store...
^{“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 4, 2016 at 7:03 am

#1888300
RE: Help with SQL please

Well done dandrews, and many thanks for the feedback.
^{“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 4, 2016 at 6:04 am

#1888283
RE: Help with SQL please

dandrews 45259 (7/4/2016)
No Chris, if the Warehouse is BE for a works order record, its sales order is also a works order with its own related sales order.
072216 is 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

July 4, 2016 at 5:34 am

#1888275
RE: Query

jagjitsingh (7/4/2016)
Hi
I have 1 query which returns record order by ID like this. Now i want that in addition to the below Query i want another query to group by...
^{“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 4, 2016 at 5:06 am

#1888268

Viewing 15 posts - 1,411 through 1,425 (of 10,143 total)