ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,026 through 2,040 (of 10,143 total)

RE: Are the posted questions getting worse?

Ed Wagner (10/20/2015)
Luis Cazares (10/20/2015)
Brandie Tarvin (10/20/2015)
Ed Wagner (10/20/2015)
Alvin Ramard (10/19/2015)
From: http://www.sqlservercentral.com/Forums/Topic1720717-1292-4.aspx#bm1729123
ITU_dk2012 (10/19/2015)
... I think I should post this into another SSC forum because I am not getting any...
^{“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, 2015 at 8:16 am

#1834393
RE: Query on a large database

sreeya (10/20/2015)
Thanks all for your suggestions. Here is more information on the table design etc:
I will need to create a report on below tables.
Order Facts Table has below columns:
ID bigint...
^{“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, 2015 at 7:53 am

#1834371
RE: Concatenate Random Date + Random Time = Error!

Jayanth_Kurup (10/20/2015)
select dateadd(s,-1*rand()*10000000 , getdate())
How would you scale this up to a million rows?
^{“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, 2015 at 5:14 am

#1834311
RE: Concatenate Random Date + Random Time = Error!

Here's another way, a million random-ish datetimes between 1947 and today:
-- A million datetimes between 1947 and today:
;WITH
t1 (x) AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (x)),
t2 (x)...
^{“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, 2015 at 3:51 am

#1834281
RE: Tempdb Spill level1

Martin Stephenson (10/19/2015)
There is a mismatch in estimated count of 17 vs actual of 13k, this becomes visible after the join to the table with the suggested missing index. Not...
^{“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, 2015 at 10:04 am

#1834127
RE: Query to Split the String as rows and columns in SQL

DECLARE @StartTime DATETIME
DECLARE @Item VARCHAR(20) = ''
SET @StartTime = GETDATE()
SELECT @Item = @Item + ','+d2.Item
FROM dbo.DelimitedSplit8K_LEAD ('Param1=3;param2=4;param4=testval;param6=11',';') d1
CROSS APPLY dbo.DelimitedSplit8K_LEAD (d1.Item, '=') d2
WHERE d2.ItemNumber = 2
ORDER BY d1.ItemNumber
SELECT...
^{“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, 2015 at 9:43 am

#1834121
RE: Tempdb Spill level1

Martin Stephenson (10/19/2015)
I have a query where a sort operation is flagging a spill into tempdb in the execution plan. There are no Order by sort operations in the query....
^{“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, 2015 at 9:34 am

#1834117
RE: Are the posted questions getting worse?

Alvin Ramard (10/19/2015)
Jeff Moden (10/18/2015)
I realize this post should probably be under "Jobs" but I thought I'd give the dedicated denizens of the thread first crack at it.
Any "hybrid" DBAs...
^{“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, 2015 at 9:30 am

#1834115
RE: Which is more efficient? WHERE SUBSTRING = ...or... WHERE fldField LIKE 'x%'

mister.magoo (10/16/2015)
Isn't it also the case that using LIKE allows the QO to use statistics to estimate the selectivity of the query.
According to this White Paper: https://msdn.microsoft.com/en-us/library/dd535534(v=sql.100).aspx
String Summary Statistics
SQL Server...
^{“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, 2015 at 9:13 am

#1834108
RE: Speed up following query (Second opinion needed)

Resender (10/19/2015)
Ok,the execution plan of my query & your solution ChrisM@Work
Thanks. Your tables are heaps. Clustered indexes are strongly recommended.
Does the query really return 60 odd million rows? It can't,...
^{“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, 2015 at 7:58 am

#1834075
RE: Speed up following query (Second opinion needed)

So you have some improvement. Can you post the actual execution plan please (not a picture of it, a .sqlplan file attachment)? Cheers.
^{“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, 2015 at 7:42 am

#1834069
RE: Speed up following query (Second opinion needed)

USE [Global_Storage]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[stg].[Report_Global_Storage_Capacity]') AND type in (N'U'))
DROP TABLE [stg].[Report_Global_Storage_Capacity]
GO
with DatesPresent as (
SELECT
dp_year = YEAR(Date_),
dp_Month = MONTH(Date_),
FirstOfThisMonth = CAST(YEAR(Date_) AS VARCHAR(4))...
^{“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, 2015 at 5:36 am

#1834030
RE: Split values in 12 months

sharonsql2013 (10/16/2015)
Hi
I need to split the amount equally into 12 months from Jan 2015 through Dec 2015.
There is no date column in the table and the total amount has...
^{“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 16, 2015 at 10:16 am

#1833861
RE: Simplifying while loop query

rs80 (10/16/2015)
Does the original code that I posted with the while loop and the CROSS JOIN/APPLY have the same performance? I would think that the CROSS JOIN has 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

October 16, 2015 at 10:07 am

#1833859
RE: Removing cursor..help please

Junglee_George (10/16/2015)
Hello
I need help in removing cursor in my table valued function with alternate code. Thanks in advance.
ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE
(
itemSqlId SMALLINT,
itemIncId INT,
itemMetaTableSqlId SMALLINT,
itemMetaTableIncId INT
)
AS
BEGIN
---- Get static...
^{“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 16, 2015 at 7:11 am

#1833796

Viewing 15 posts - 2,026 through 2,040 (of 10,143 total)