ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,321 through 1,335 (of 10,143 total)

RE: explain composite index structure and work? How B Tree for composite index work internally?

And this[/url].
^{“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 8, 2016 at 6:47 am

#1894318
RE: require Optimize the sql query

vijay.vesanekar (8/8/2016)
Hi ,
I have implemented this logic at client side it helps to improve the performance
Before Logic (Time Taken) 18 Hr to 24 Hrfor 66 Rows
After change in logic...
^{“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 8, 2016 at 5:49 am

#1894300
RE: Improve reporting stored procedure execution time - tuning temporary tables?

Thanks.
Firstly, the estimate of the rowcount coming out of the table join is on the low side: as a result, the sort operator supporting the stream aggregate is twice 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

August 8, 2016 at 4:53 am

#1894289
RE: Improve reporting stored procedure execution time - tuning temporary tables?

radu.gheorghiu (8/8/2016)
I'm sorry, I'm posting this issue on multiple websites, lost track a bit and forgot to mention it was a simplified version of the original query (that's why there...
^{“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 8, 2016 at 2:38 am

#1894277
RE: Improve reporting stored procedure execution time - tuning temporary tables?

radu.gheorghiu (8/8/2016)
Thanks for the suggestions, actually the query posted above is a bit obfuscated, changed column names / some aliases etc. but the obfuscation from SQL Sentry worked much better...
^{“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 8, 2016 at 2:24 am

#1894275
RE: DynamicDateRangeGenerator

Hi Mitch, thanks for posting. It looks a little over-engineered for what it's supposed to be doing. This does the same much faster and with less code:
DECLARE @ExtraYearsEitherSide TINYINT =...
^{“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 8, 2016 at 2:09 am

#1894273
RE: Are the posted questions getting worse?

Hugo Kornelis (8/5/2016)
ChrisM@Work (8/5/2016)
I've seen you deal with Stump the Chump, Hugo. SQL Bits Nottingham I think - a presentation on MERGE. It worked just fine.
I did?
Really?
I always say that...
^{“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 5, 2016 at 9:18 am

#1894081
RE: Are the posted questions getting worse?

Hugo Kornelis (8/5/2016)
ChrisM@Work (8/5/2016)
Grant Fritchey (8/5/2016)
BLOB_EATER (8/5/2016)
I would like to get into the mind-set of speakers out there. Now, as it stands I have zero-interest in presenting/speaking due to 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

August 5, 2016 at 9:02 am

#1894076
RE: Are the posted questions getting worse?

Grant Fritchey (8/5/2016)
BLOB_EATER (8/5/2016)
I would like to get into the mind-set of speakers out there. Now, as it stands I have zero-interest in presenting/speaking due to a lack of knowledge.
My...
^{“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 5, 2016 at 8:22 am

#1894066
RE: Improve reporting stored procedure execution time - tuning temporary tables?

SQL Sentry Plan Explorer has an obfuscation option - so you can mask actual table and column names to get around NDA's. Since you've already published the original query 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

August 5, 2016 at 7:09 am

#1894047
RE: Help with splitting an address

TSQL Tryer (8/5/2016)
I have a field called Address1 in my table Table1.
Here is an example of data in that field -
8 Brick Lane and 11 Balkerne Drive or it could...
^{“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 5, 2016 at 1:58 am

#1893977
RE: fill a years worth of datetimes

DECLARE @EndDate DATETIME = GETDATE()
DECLARE @StartDate DATETIME = DATEADD(YEAR,-1,@EndDate)
SELECT @StartDate, @EndDate;
WITH t1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
t2 AS (SELECT n = 0 FROM t1 a, t1 b),
t4...
^{“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 4, 2016 at 9:49 am

#1893855
RE: Improve reporting stored procedure execution time - tuning temporary tables?

radu.gheorghiu (8/4/2016)
Ok, I will do so and post it here.
But if I look at the cached plans and this is the one whose last execution time was during 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

August 4, 2016 at 8:24 am

#1893798
RE: Improve reporting stored procedure execution time - tuning temporary tables?

radu.gheorghiu (8/4/2016)
The execution plan is taken from:
, cast(qp.query_plan as xml)[Execution plan]
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
cross apply sys.dm_exec_text_query_plan (qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
And as per documentation, 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 4, 2016 at 8:09 am

#1893789
RE: Improve reporting stored procedure execution time - tuning temporary tables?

radu.gheorghiu (8/4/2016)
1. It's the actual execution plan, not the estimated. The execution plan from above is taken from querying the DMVs mentioned in my post.
...
That makes it an estimated plan.
--...
^{“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 4, 2016 at 7:43 am

#1893779

Viewing 15 posts - 1,321 through 1,335 (of 10,143 total)