ChrisM@Work

Forum Replies Created

Viewing 15 posts - 181 through 195 (of 10,143 total)

RE: Pass Table Name by joining Constant String and A Field Value from Same Row

sam.cool.just4u - Friday, November 30, 2018 4:19 AM
Thanks for the response. Sorry this won't work in my scenario.
As the Number of 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

November 30, 2018 at 6:12 am

#2015097
RE: Need a suggestion for my SQL requirement

IF OBJECT_ID('tempdb..#dbo_Employees') IS NOT NULL DROP TABLE #dbo_Employees;
SELECT * INTO #dbo_Employees FROM (VALUES
(1,CAST('FN1' AS VARCHAR(10)), CAST('MN1' AS VARCHAR(10)), CAST('LN1' AS VARCHAR(10)), CAST('D1' AS VARCHAR(10)), CAST('2018-01-01' AS DATE)),
^{“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

November 30, 2018 at 3:32 am

#2015071
RE: Slow SQL Script

AhTu_SQL2k+ - Thursday, November 29, 2018 10:25 PM
Hi ChrisM,
Timeout issue has been resolved. Below steps are the changes highlighted by your previous...
^{“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

November 30, 2018 at 1:56 am

#2015059
RE: poor performing report query against table without a PK

Just this, please:
DECLARE @BULKID varchar(50) = 5006
SELECT
[Offer_ID],
[Offer_Generate_Date],
[Serial_Number],
[BAN],
[BulkID]
INTO #AssurantManufacturerReceived
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward]
WHERE QR.BulkID = @BULKID
^{“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

November 29, 2018 at 9:51 am

#2014970
RE: poor performing report query against table without a PK

ChrisM@Work - Thursday, November 29, 2018 3:01 AM
That plan has some strange features, e.g. estimated row count for BrandID of over 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

November 29, 2018 at 9:37 am

#2014967
RE: poor performing report query against table without a PK

polkadot - Thursday, November 29, 2018 9:19 AM
@frederico_fonseca - I will send the query and execution plan in private email tonight when...
^{“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

November 29, 2018 at 9:26 am

#2014962
RE: sql performance question

Thom A - Thursday, November 29, 2018 2:21 AM
Maybe the DISTINCT, which just gets more expensive the more rows and columns 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

November 29, 2018 at 3:04 am

#2014911
RE: poor performing report query against table without a PK

That plan has some strange features, e.g. estimated row count for BrandID of over a million for just 9 rows, and parallelism...
Can you try this please, and post...
^{“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

November 29, 2018 at 3:01 am

#2014910
RE: poor performing report query against table without a PK

@Polkadot, there is an error in your code:
FROM [#Assurant Manufacturer Received] QR
WHERE QR.[Offer_Generate_Date] BETWEEN @FROM_DATE AND @TO_DATE
should be
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.[Offer_Generate_Date] BETWEEN...
^{“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

November 29, 2018 at 2:47 am

#2014909
RE: Slow SQL Script

AhTu_SQL2k+ - Wednesday, November 28, 2018 8:29 AM
ChrisM@Work - Wednesday, November 28, 2018 7:03 AM
^{“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

November 28, 2018 at 8:44 am

#2014818
RE: poor performing report query against table without a PK

This could offer a perf lift:

SELECT QR.[Offer_ID]
,QR.[Offer_Generate_Date]
,QR.[Offer_Update_Date]
,QR.[Manufacturer]
,QR.[Model]
,QR.[Serial_Number]
,QR.[Application_User_ID]...
^{“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

November 28, 2018 at 8:03 am

#2014800
RE: Slow SQL Script

Also:
Add [qty] to index [s_dopicklot].[NonClusteredIndex-20181127-173726]
Add [qty_pick] and [qty_send] to index [s_dodetl].[_dta_index_s_dodetl_18_591549391__K1_K2_K23_K13_K6]
This could remove the two key lookups from the 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

November 28, 2018 at 7:25 am

#2014794
RE: Slow SQL Script

Data type mismatches, where join columns between two tables are of two different types, are causing you some grief:
CONVERT_IMPLICIT(nchar(30),
[TotalERP_winwin].[dbo].[s_dodetl].[part_code] as .[part_code]
,0) =
[TotalERP_winwin].[dbo].[s_locqty].[part_code] as [l].[part_code]
^{“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

November 28, 2018 at 7:03 am

#2014790
RE: Slow SQL Script

AhTu_SQL2k+ - Wednesday, November 28, 2018 6:43 AM
Hi ChrisM,
My colleague helped me to try out your restructured script however, the speed 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

November 28, 2018 at 6:53 am

#2014788
RE: Parallel Query Plan Choices On (near) identical Environments

This problem would be far easier to investigate if both plans were available to examine. Is this something you can do?
^{“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

November 28, 2018 at 3:59 am

#2014765

Viewing 15 posts - 181 through 195 (of 10,143 total)