ChrisM@Work

Forum Replies Created

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

RE: Query of 100 mil rows with multiple parameters

Once more with details:
[Expr1031] = Scalar Operator(CONVERT_IMPLICIT(int,[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[Year] as [FCL].[Year],0)),
[Expr1032] = Scalar Operator(CONVERT_IMPLICIT(int,[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[Week] as [FCL].[Week],0)),
[Expr1033] = Scalar Operator(CONVERT_IMPLICIT(nchar(3),[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[CurrencyCode] as [FCL].[CurrencyCode],0)),
[Expr1034] = Scalar Operator(CONVERT_IMPLICIT(decimal(18,5),[FlexQuotes].[dbo].[BRFCLSeaWeekHis].[RateTotal] as [FCL].[RateTotal],0)),
[Expr1035] = Scalar...
^{“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

March 4, 2014 at 9:42 am

#1693961
RE: Calculate and return the previous Date at 18:00 Hours

SELECT DATEDIFF(dd,0,GETDATE())-1
SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0)
SELECT DATEADD(hh,18,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,0))
Use q3. q1 and q2 help explain the algorithm.
^{“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

March 4, 2014 at 8:17 am

#1693917
RE: where exists and where not exists in sql rewrite as single query in sql server

SELECT --TOP 100 PERCENT
objectid AS companyid,
-- max(CASE WHEN symbolTypeid = 7 THEN CONVERT(int, symbolValue) ELSE NULL END) AS mergentId,
MAX(CASE WHEN symbolTypeid = 5 THEN symbolValue ELSE NULL...
^{“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

March 4, 2014 at 6:31 am

#1693861
RE: how to use One CTE value in another CTE ,in SQL 2008 R2

You've missed the FROM list in CTE3.
^{“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

March 4, 2014 at 6:22 am

#1693858
RE: Syntax advise please

igloo21 (3/4/2014)
Above bolded parts in the quotes display a contradiction. Did the query run correct when removing both the CASE statements or did it NOT run correct?
If it didn't run...
^{“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

March 4, 2014 at 6:11 am

#1693853
RE: Syntax advise please

sCustomer_number is float:
SELECT DISTINCT TOP (100) PERCENT
c.iCustomer_id,
c.sCustomer_number AS CustomerNo,
CASE WHEN c.sCustomer_number = 1 THEN 660 ELSE c.sCustomer_number END AS sCustomer_number,
c.sCustomer_name AS CustomerName,
c.sCustomer_state AS Customerstate,...
^{“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

March 4, 2014 at 5:36 am

#1693839
RE: Syntax advise please

SELECT DISTINCT TOP (100) PERCENT
c.iCustomer_id,
c.sCustomer_number AS CustomerNo,
CASE WHEN c.sCustomer_number = 'SL000000' THEN 660 ELSE c.sCustomer_number END AS sCustomer_number,
c.sCustomer_name AS CustomerName,
c.sCustomer_state AS Customerstate,
cc.CustomerType AS Channel,...
^{“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

March 4, 2014 at 5:22 am

#1693831
RE: Are the posted questions getting worse?

rodjkidd (3/4/2014)
Tom,
Shrewsbury - somewhere else that's on my "yet to visit" list. I'm getting almost as much out of this discussion as Gail is.
Deciding whether to wait for the discount...
^{“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

March 4, 2014 at 3:33 am

#1693798
RE: "good" plan, low reads, but still slow...

Change the key column order of the index nci_SalesDistrict so that UbiquitousTinyIntColum is the leading edge, followed by keysalesdistrict.
It will probably give you seeks. Better still, data should come off...
^{“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

March 4, 2014 at 2:20 am

#1693788
RE: Non Clustered index on Primary key is not used by the execution plan and showing Table scan

Remove the index hint from the query whilst you are still testing and developing. Index hints are used when you've exhausted other, simpler, possibilities.
Add rua.userIsBound to the index 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

March 4, 2014 at 1:54 am

#1693781
RE: Are the posted questions getting worse?

jcrawf02 (3/3/2014)
GilaMonster (3/3/2014)
I see this is going to require some careful planning...
*ahem. Cousin Chad and I respectfully submit an item for consideration upon your route:
http://en.wikipedia.org/wiki/Crawford_Castle
😀
It's tragic how little remains of...
^{“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

March 3, 2014 at 9:46 am

#1693626
RE: Are the posted questions getting worse?

paul.knibbs (3/3/2014)
ChrisM@Work (3/3/2014)
It is. I was going to suggest you take the coast road but we don't really have a joined-up one of those.
Or maybe head across the Severn Bridge...
^{“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

March 3, 2014 at 6:22 am

#1693530
RE: Are the posted questions getting worse?

GilaMonster (3/3/2014)
ChrisM@Work (3/3/2014)
GilaMonster (3/3/2014)
I'm going to picking your brain in the next month or so about vacation spots, I'm planning to take a off week after Bits, Wales looks close....
^{“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

March 3, 2014 at 6:02 am

#1693514
RE: Are the posted questions getting worse?

GilaMonster (3/3/2014)
rodjkidd (3/3/2014)
GilaMonster (3/3/2014)
BrainDonor (3/3/2014)
SQL Bits XII has finally been announced http://www.sqlbits.com/.
Wooot!
Next problem, what do I submit?
Almost posted last night that site was up. But apparently we shouldn't have noticed...
^{“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

March 3, 2014 at 5:24 am

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

A significant number of implicit conversions are interfering with optimisation of this query. Here's a few recommendations to start with:
Cast @YearFrom and @YearTo to same datatype as BRFCLSeaWeekHis.Year
Cast @QuoteType...
^{“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

March 3, 2014 at 1:57 am

#1693460

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