ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,586 through 3,600 (of 10,143 total)

RE: Query of 100 mil rows with multiple parameters

Thanks.
Try placing the nonclustered index keys the other way around:
CREATE NONCLUSTERED INDEX [IX_BRFCLSeaWeekHis_QuoteType] ON [dbo].[BRFCLSeaWeekHis]
(
Year, [QuoteType] ASC
)
INCLUDE (
[CurrencyCode],
[RateTotal],
[SeaTotal],
[Total])
^{“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

February 28, 2014 at 9:28 am

#1693148
RE: Error: Each GROUP BY expression must contain at least one column that is not an outer reference.

Any time Dale.
Check out the two APPLY articles by Paul White in my signature too, they are an excellent walkthrough with examples.
^{“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

February 28, 2014 at 8:43 am

#1693108
RE: Query of 100 mil rows with multiple parameters

mark.kristensen (2/28/2014)
I have attached a file to the first post - is that what you need or if not, then please tell me how to get it 🙂
Execution plan with...
^{“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

February 28, 2014 at 8:31 am

#1693094
RE: Query of 100 mil rows with multiple parameters

mark.kristensen (2/28/2014)
Hi Chris,
Thanks for the leads, but even with recompile and changed index, the time is the same.
In relation to your first post, then I will not likely get anymore...
^{“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

February 28, 2014 at 7:49 am

#1693078
RE: To dynamically change values in this short script based on 3 values

From an earlier post: "Here's a table of the possible combinations of specialties and capabilities (the 'YES' column should be ignored):"
Do you have a table which looks similar 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

February 28, 2014 at 7:01 am

#1693045
RE: Query of 100 mil rows with multiple parameters

For a quick win, change your non-clustered index like so:
CREATE NONCLUSTERED INDEX [IX_BRFCLSeaWeekHis_QuoteType] ON [dbo].[BRFCLSeaWeekHis]
(
[QuoteType] ASC, Year
)
INCLUDE (
[CurrencyCode],
[RateTotal],
[SeaTotal],
[Total])
and your query like so:
WHERE QuoteType = @QuoteType
AND FCL.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

February 28, 2014 at 6:49 am

#1693033
RE: Query of 100 mil rows with multiple parameters

Hi Mark
Two or three points to consider.
Firstly this is a "Catch-all query". In a nutshell, the number of rows returned could vary dramatically depending upon the parameters passed 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

February 28, 2014 at 6:45 am

#1693028
RE: Error: Each GROUP BY expression must contain at least one column that is not an outer reference.

Dale Magnant (2/27/2014)
...(By the way, the last line of query assigned an alias of 'x', I assume you meant 'p' and changed the line accordingly.)
Yep, sorry, my mistake.
Dale Magnant (2/27/2014)
Chris,
After...
^{“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

February 28, 2014 at 1:57 am

#1692969
RE: Error: Each GROUP BY expression must contain at least one column that is not an outer reference.

UPDATE m Set
POObligations = x.POObligations
FROM MDEP m
CROSS APPLY (
SELECT
POObligations = (SUM(PO_DX.QuantityOrdered * PO_DX.Price) - SUM(Distinct PO_MX.PaymentPosted))
* SUM(Distinct(cast(PO_MX.PrimarySplit as decimal(5,2))/100))
FROM PO_MX
INNER JOIN PO_DX
ON PO_DX.PONumber = PO_MX.PONumber
WHERE PO_MX.MDEP...
^{“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

February 27, 2014 at 1:59 am

#1692576
RE: How to optimize a query that's running slow on Nested Loops (Inner Join)

Please post the actual execution plan as a .sqlplan file attachment.
^{“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

February 27, 2014 at 1:47 am

#1692575
RE: Advice -- How to find out the common denominator

It sounds as if this is in the design phase - if this is the case, then consider pivoting the table you display here as your starting point, so columns...
^{“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

February 26, 2014 at 6:43 am

#1692257
RE: How to return something when there is no record

Luis Cazares (2/25/2014)
Chris M already gave a solution, but with no DDL and sample data, I'm not giving an example.
It's not possible to give a good example without table aliases...
^{“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

February 26, 2014 at 2:24 am

#1692178
RE: Insert with self reference

Two excellent articles by Paul White covering APPLY are linked in my sig.
If you take a closer look at the SELECT query I wrote, it's designed to show the update...
^{“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

February 26, 2014 at 2:02 am

#1692171
RE: Are the posted questions getting worse?

Stefan Krzywicki (2/25/2014)
The Dixie Flatline (2/25/2014)
rodjkidd (2/25/2014)
Steve Jones - SSC Editor (2/24/2014)
I went to the Rock and Roll Hall of Fame in Cleveland a few weeks ago. It was pretty...
^{“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

February 26, 2014 at 1:52 am

#1692167
RE: View with errors still saved, and still works

pdanes (2/25/2014)
So if the query gives me what I need, I should ignore whatever the design view says? I'm kind of leery about ignoring warning messages - I always wonder...
^{“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

February 25, 2014 at 7:11 am

#1691858

Viewing 15 posts - 3,586 through 3,600 (of 10,143 total)