ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,571 through 6,585 (of 10,143 total)

RE: Query Optimization using Indexes

Do you have results from the three test queries?
^{“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 20, 2011 at 3:36 am

#1397159
RE: Query Optimization using Indexes

adlakha.22 (10/20/2011)
Thanks for Reply Chris
I again Executed the Query , nd did't made any changes except column names to match them with group By Clause,
U can see Execution Plan attached.
How...
^{“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 20, 2011 at 2:35 am

#1397117
RE: Query Optimization using Indexes

Here are three test queries which will give an idea of baseline performance. Query 3 takes 10seconds to run against a similar table of 40 million rows here. Please change...
^{“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 20, 2011 at 2:02 am

#1397096
RE: Query Optimization using Indexes

adlakha.22 (10/20/2011)
Good morning
@chris-2 i am not able to get that Query3 ,
Every Morning I have a hope with u guys...Expecting some logical magic.
That improve my Query performance and 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

October 20, 2011 at 1:37 am

#1397084
RE: Query Optimization using Indexes

adlakha.22 (10/19/2011)
@frfernan
as u say "so I would prefer apply MAX to ordernumber and group by cono, orderno, ordersuf. That is, focus on the clustering keys."
I again do some changes in...
^{“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 19, 2011 at 2:45 am

#1396505
RE: Why does the same code yield different results running in T-SQL interactively vs. as a TVF?

We've all been there, Rich 😉
^{“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 18, 2011 at 10:18 am

#1396163
RE: Why does the same code yield different results running in T-SQL interactively vs. as a TVF?

Put @delim fore n aft to deal with single elements:
SET @DelimitedList = @delim + @DelimitedList + @delim;
Either outside the function or within...
^{“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 18, 2011 at 9:43 am

#1396105
RE: Calculating Stock Returns Using the ROW_NUMBER function and CTEs

pvoutov (10/18/2011)
There are two different ways to calculate weekly returns. The methodology you're using implies holding for an entire week (using weekly prices). A more complicated methodology would compound daily,...
^{“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 18, 2011 at 7:19 am

#1395902
RE: Query Optimization using Indexes

frfernan (10/18/2011)
Ninja's_RGR'us (10/18/2011)
Just a reminder of the goal of the OP. You guys were not supposed to play with the qry at all! 😀
Query Optimization using Indexes
Ha ha,...
^{“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 18, 2011 at 7:03 am

#1395891
RE: Query Optimization using Indexes

Ninja's_RGR'us (10/18/2011)
Just a reminder of the goal of the OP. You guys were not supposed to play with the qry at all! 😀
Query Optimization using Indexes
Couldn't help 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

October 18, 2011 at 6:55 am

#1395881
RE: Query Optimization using Indexes

adlakha.22 (10/18/2011)
Hi Chris
could u plz tell me wht do u mean by
"Now, with the third query removed from your batch, how long does the batch take to run? I'd expect...
^{“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 18, 2011 at 5:54 am

#1395838
RE: Query Optimization using Indexes

frfernan (10/18/2011)
... But you can join both tables trough the primary keys...
Can you please show what you mean?
^{“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 18, 2011 at 3:23 am

#1395751
RE: Calculating Stock Returns Using the ROW_NUMBER function and CTEs

Nice, concise, easily-readable article.
However, I'd change the key query because it reads the prices table twice. This reads it once:
;WITH PriceYearly_CTE2 AS
(SELECT
Period = ROW_NUMBER() OVER (ORDER BY [year]),...
^{“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 18, 2011 at 3:17 am

#1395749
RE: Query Optimization using Indexes

The plan for the orderlines query looks fine, however there's a third query shown:
insert [#orderlines_________________________________________________________________________________________________________000000000118] select * from [#orderlines_________________________________________________________________________________________________________000000000118]
I don't know where this has come from and you don't need...
^{“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 18, 2011 at 3:01 am

#1395742
RE: Query Optimization using Indexes

There is of course a cost associated with maintaining an index - another subject.
Here's a few changes. Check out how long it takes to run, then I guess it's 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 18, 2011 at 2:01 am

#1395730

Viewing 15 posts - 6,571 through 6,585 (of 10,143 total)