ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,726 through 1,740 (of 10,143 total)

RE: Are the posted questions getting worse?

Grumpy DBA (2/23/2016)
^ It goes back to the old proverb I threw out in another thread... Give a man a fish, he eats for a day. Teach a man...
^{“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 23, 2016 at 5:50 am

#1860367
RE: Stored procedure running much longer than its statements

Your two queries are logically different - sorry if I was ambiguous, what I meant was remove the NULL checks on the join columns only, like this:
SELECT COUNT(1)
FROM PaleoData.Tax.PodrobnostiTmp...
^{“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 23, 2016 at 3:26 am

#1860325
RE: Stored procedure running much longer than its statements

Check that "auto create statistics" is enabled for the database.
Inner joins eliminate null values of the join columns:
inner join PaleoData.Tax.PodrobnostiTmp P2
on p1.Family_13000 = p2.Family_13000
you don't need to check...
^{“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 23, 2016 at 2:40 am

#1860320
RE: Suggest Index

sushil_dwid (2/23/2016)
Hi,
Could you please suggest the index on table(dbo.person) that can fast my below sql..
select Region,type,UID,Week
(select Region,type,UID,
CASE
WHEN epu.updatetime...
^{“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 23, 2016 at 2:23 am

#1860310
RE: Character string conversion to date type. char(8) to Date ( from char(8) (23022016) to date(dd/mm/yyyy) (23/02/2016)

spaghettidba (2/23/2016)
There is no built-in CONVERT style that will handle the format DDMMYYYY. This means that you will have to code it yourself. This should do:
CONVERT(date, RIGHT(Date1,2) + SUBSTRING(Date1, 3,2)...
^{“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 23, 2016 at 2:13 am

#1860307
RE: current and time-expired rows.

The fastest index for the 2006 query was index 3, which isn't covering. Can you post the execution plan you get for this?
^{“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 19, 2016 at 9:58 am

#1859884
RE: Can Pivot query do

Have you tried a crosstab query? Immensely useful technique to learn, easier to figure out than PIVOT (and usually faster too).
This excellent article by Jeff Moden [/url]shows you 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

February 19, 2016 at 5:53 am

#1859783
RE: current and time-expired rows.

Preliminary results:
create index ix_helper01 ON #TestTable (StartDate,EndDate) INCLUDE (wide)
create index ix_helper02 ON #TestTable (EndDate,StartDate) INCLUDE (wide)
DECLARE @Now DATETIME = GETDATE()
select sum(datalength(wide)) Some_field_information
from #testtable WITH (INDEX (ucx_Stuff))
where StartDate < '20160301'...
^{“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 18, 2016 at 10:01 am

#1859631
RE: How to find account number of bank from given records in table

Nikku (2/18/2016)
Hi All
I have two tables t1 & t2 which contains the same records. t1 is for bank1 & t2 is for bank2
i.e t1
accno name balance
001000 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

February 18, 2016 at 6:04 am

#1859534
RE: How to extract overlapping date ranges from a table of date ranges

ben.brugman (2/18/2016)
Jeff Moden (2/16/2016)
ben.brugman (2/16/2016)
Still working on situations where we want to select a specific period and subscribers at that moment.
Please see the following article for that...
Did read your article....
^{“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 18, 2016 at 1:55 am

#1859467
RE: Pivot based off two columns

imba (2/17/2016)
I am speechless. THANK YOU SOOOOO MUCH.
You're welcome, thanks for the feedback.
Be sure to read Dwain's excellent article linked by Luis.
^{“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 18, 2016 at 1:35 am

#1859464
RE: Pivot based off two columns

SELECT COL1, COL2, COL3, x.*
FROM #TEST
CROSS APPLY (VALUES (ITEM1, DESC1),(ITEM2, DESC2),(ITEM3, DESC3)) x (COL4, COL5)
^{“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 17, 2016 at 7:43 am

#1859259
RE: ROW_NUMBER() function with a difference

Mark Cowne (2/17/2016)
A couple of tweaks to Chris's query should do it
Funny - the first time I saw this technique used was in one of your posts, Mark.
Thanks for...
^{“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 17, 2016 at 6:48 am

#1859241
RE: ROW_NUMBER() function with a difference

richardmgreen1 (2/17/2016)
Thanks both
I'm testing both and they are very close to what I need.
Chris - yours is closest to what I need but I have found a bit of an...
^{“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 17, 2016 at 5:11 am

#1859220
RE: Five previous working days

hoseam (2/17/2016)
Can I get different columns like 'Today', 'Yesterday', 'A day before Yesterday', '3 days prior Today' and '4 days prior Today'?
SELECT
[Today] = MAX(CASE WHEN rn = 5 THEN...
^{“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 17, 2016 at 4:08 am

#1859215

Viewing 15 posts - 1,726 through 1,740 (of 10,143 total)