ChrisM@Work

Forum Replies Created

Viewing 15 posts - 991 through 1,005 (of 10,143 total)

RE: Hi ,how to code the below procedure with loop.

skmoh2 (1/10/2017)
...PSEDOCODE:
Loop1 : Table1_Peggging read Sales Order Material(SKU) Per sales Order
Loop2: Read full table2 for above Material. (Based on our example data you will get 7 records)
...
^{“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 10, 2017 at 6:43 am

#1921597
RE: Any tips on speeding this SQL up would be most helpful

Arjun Sivadasan (1/9/2017)
Well done! I think you only need to add following condition after the group by.
having sum(charge_amount) > 0
Edit: Your original query checks for sum(charge_qty) > 0. Do both...
^{“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 10, 2017 at 1:59 am

#1921556
RE: Any tips on speeding this SQL up would be most helpful

douglas.t (1/9/2017)
@chrism-2@Work - Your query did run faster, however it is not unique and so it brings up 9,266 rows instead of the 2,570.
Multiple rows per day I guess:
WITH charges...
^{“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 10, 2017 at 1:14 am

#1921550
RE: Performance tuning for a query

Luis Cazares (1/9/2017)
ChrisM@Work (1/9/2017)
Or build a cross-tab into an APPLY block:
I usually avoid APPLY with correlated suubqueries because it tends to run the query row by row. Obviously, this 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

January 9, 2017 at 9:58 am

#1921442
RE: Performance tuning for a query

Luis Cazares (1/9/2017)
mishrakanchan86 (1/9/2017)
Hi ALl
I have a view which looks something like below.
Select
* from T1 with(NoLock)
Inner Hash Join T2 With (NoLck) on T1.PID = T2.PID
Inner...
^{“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 9, 2017 at 9:04 am

#1921422
RE: Any tips on speeding this SQL up would be most helpful

The execution plan indicates that the subquery picking up the row with the max date is the most expensive operation.
If charge_date is unique within each partition of a_site_id/charge_code, then this...
^{“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 9, 2017 at 2:37 am

#1921319
RE: Query took a nose dive

LeeFAR (1/6/2017)
Thanks Jeff and Chris. I cannot thank you enough for taking your time to help me.
Chris, I did see that the table vars are causing the estimated rows...
^{“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 6, 2017 at 9:33 am

#1921153
RE: Query took a nose dive

LeeFAR (1/6/2017)
Here is the t-sql in the store procedure that is running the query that is taking the time. The execution plan that is bad is what is produced...
^{“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 6, 2017 at 9:19 am

#1921150
RE: Query took a nose dive

LeeFAR (1/5/2017)
It looks like it may have been a while.
Attached is the query plan. The main query that is the trouble is Query 3. As 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 6, 2017 at 1:51 am

#1921046
RE: Any tips on speeding this SQL up would be most helpful

douglas.t (1/5/2017)
Thanks for the effort. Your code is taking longer than mine though on a year's data. I don't know how best to export and post the execution...
^{“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 6, 2017 at 1:26 am

#1921042
RE: Select * return 24 while select count(1) return 3956892

halifaxdal (1/5/2017)
You got it, but can you elaborate this? wouldn't that mean potentially every query must do the rowcount reset?
Thanks.
https://msdn.microsoft.com/en-us/library/ms188774.aspx
and
https://msdn.microsoft.com/en-us/library/ms190356.aspx
It only has to be reset once it's been set. I...
^{“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 5, 2017 at 9:58 am

#1920921
RE: Select * return 24 while select count(1) return 3956892

Run
SET ROWCOUNT 0
and try again.
^{“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 5, 2017 at 9:45 am

#1920915
RE: Query took a nose dive

Can you post, as a .sqlplan attachment, the actual (as opposed to estimated) execution plan for "SELECT query that joins to this table", please? Thanks.
^{“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 5, 2017 at 9:37 am

#1920910
RE: Any tips on speeding this SQL up would be most helpful

You're reading the base tables quite a few times. You should be able to extract your result set on a single read of the two tables, from what I 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

January 4, 2017 at 4:20 am

#1920563
RE: Nested loop no join predicate

Run the code of the three relevant CTE's separately to identify which one is responsible, then doublecheck the execution plan. The optimiser throws up false positives from time to time,...
^{“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 4, 2017 at 2:06 am

#1920550

Viewing 15 posts - 991 through 1,005 (of 10,143 total)