ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,216 through 1,230 (of 10,143 total)

RE: SQL view performance problem

farazkhan1981 (9/7/2016)
I have a view that has 6 datasets created within it. ...
Those aren't datasets, they are CTE's, which are somewhat like views in that they are more or less...
^{“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

September 8, 2016 at 2:05 am

#1899704
RE: Filling Buckets

ashishkumarrai (9/6/2016)
Hi
Thanks for the post, really useful. I am working on similar stuff. My problem is the value the above query is using is static. What will be the...
^{“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

September 6, 2016 at 10:15 am

#1899392
RE: Are the posted questions getting worse?

Y.B. (9/6/2016)
ChrisM@Work (9/6/2016)
Sean Lange (9/6/2016)
Luis Cazares (9/6/2016)
Sean Lange (9/6/2016)
I tried my hand at curing my own bacon for the first time. It came out of the cure Saturday morning and...
^{“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

September 6, 2016 at 9:58 am

#1899385
RE: Are the posted questions getting worse?

Sean Lange (9/6/2016)
Luis Cazares (9/6/2016)
Sean Lange (9/6/2016)
I tried my hand at curing my own bacon for the first time. It came out of the cure Saturday morning and hit the...
^{“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

September 6, 2016 at 9:35 am

#1899377
RE: String Match Help

The Dixie Flatline (9/5/2016)
koti.raavi (9/5/2016)
Yeah i do match on both member Id and name as well.
My case member id is not unique
Thanks,
Dhana
We do something similar, matching on license numbers...
^{“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

September 6, 2016 at 1:50 am

#1899265
RE: stored procedure for tagging same contact information

The Dixie Flatline (9/5/2016)
So you have to try all possible matches and just take the one with the lowest group number?
That's a fair description of what the code does -...
^{“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

September 6, 2016 at 1:44 am

#1899264
RE: String Match Help

Try this fuzzy-matching function:
/****** Object: UserDefinedFunction [dbo].[IF_Levenshtein02] Script Date: 27/01/2014 20:12:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- this will score around 10,000 word pairs per second on...
^{“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

September 5, 2016 at 8:28 am

#1899204
RE: Last day of month in a WHERE clause

The Dixie Flatline (8/12/2016)
I had to change EoMonth('1/1/2016',(N-1)) to EoMonth('1/1/2016',(1-n)) for it to work with dates in the past.
An alternative is to just back up the starting date to...
^{“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

September 5, 2016 at 1:55 am

#1899165
RE: stored procedure for tagging same contact information

The Dixie Flatline (9/2/2016)
What should the result be if a row matches two or more established groups? For example, there is a 'Smith' group and a 'Jones'...
^{“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

September 5, 2016 at 1:48 am

#1899163
RE: Why does the query optimizer choose this way?

ScottPletcher (9/2/2016)
If the overall number of pages to be read is small either way, it's just not worth the optimizer's time to keep going.
SQL naturally favors the clustered index. ...
^{“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

September 2, 2016 at 10:14 am

#1899013
RE: Why does the query optimizer choose this way?

GilaMonster (9/2/2016)
Abort reason is "Good enough plan found" (properties of the SELECT). Optimisation has multiple phases, "Good enough plan found" means that at the end of one phase the cheapest...
^{“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

September 2, 2016 at 7:45 am

#1898983
RE: stored procedure for tagging same contact information

raymond.wee.823 (9/2/2016)
Hi, it looked promising. However, I tried it with 10000 records and it took 10 minutes just to go through the first 1000 records so it is a performance...
^{“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

September 2, 2016 at 5:40 am

#1898965
RE: stored procedure for tagging same contact information

raymond.wee.823 (9/2/2016)
Hi, it looked promising. However, I tried it with 10000 records and it took 10 minutes just to go through the first 1000 records so it is a performance...
^{“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

September 2, 2016 at 5:24 am

#1898963
RE: stored procedure for tagging same contact information

Here's a slightly different version, replacing table reads with table spools:
WITH
Firstpass AS (SELECT ID, [Name], Phone, Email, [Group],
grp1 = MIN(ID) OVER(PARTITION BY [Name]) FROM #Temp),
Secondpass AS (SELECT 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

September 2, 2016 at 4:51 am

#1898957
RE: stored procedure for tagging same contact information

-- Set up some sample data to test against
-- This is not part of the solution
-- Note: the ID column is required, you could generate it on-the-fly using ROW_NUMBER()
SELECT 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

September 2, 2016 at 3:58 am

#1898951

Viewing 15 posts - 1,216 through 1,230 (of 10,143 total)