ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,591 through 7,605 (of 10,143 total)

RE: LIKE operator with millions of records giving slow performance

DROP TABLE #Exclusions
CREATE TABLE #Exclusions (ExclusionID INT, ExcludedURL VARCHAR(200))
INSERT INTO #Exclusions (ExclusionID, ExcludedURL)
SELECT 1, '%://llnw-1083.listen.com/6%.rad%' UNION ALL
SELECT 2, '%://fe5357.sidestep.com/servlet6/%' UNION ALL
SELECT 3, '%://cdn-62.blinkx.com/stream/%.flv%' UNION ALL
SELECT 4, '%://genio.virgilio.it/scripts/alice/ifr_virg_comu_genio_cachebust.js_728x90_rmt931648.html%' UNION ALL
SELECT 5,...
^{“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

July 30, 2010 at 5:26 am

#1200220
RE: LIKE operator with millions of records giving slow performance

Hi Raj Deep
Please could you provide a sample table with a few rows containing data similar to that found in your exclusion table? It should be in a format like...
^{“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

July 30, 2010 at 4:18 am

#1200197
RE: LIKE operator with millions of records giving slow performance

Eugene Elutin (7/29/2010)
Chris Morris-439714 (7/29/2010)
Eugene Elutin (7/29/2010)
Can you test how long the following query will run and how many records does it returns:
SELECT a.*
FROM dbo.Person a
LEFT JOIN dbo.Person_exclusion b
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

July 29, 2010 at 10:23 am

#1199917
RE: datediff on dates contained in single column

skcadavre (7/29/2010)
You cheated Chris! You changed his sample data 😛
I'm sure that what you posted is what he meant
LOL!
^{“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

July 29, 2010 at 9:35 am

#1199882
RE: datediff on dates contained in single column

skcadavre (7/29/2010)
whitlander (7/29/2010)
Does that help?
I'm feeling generous. . .
This is what you were asked to produce
SET DATEFORMAT dmy
GO
DECLARE @TABLE AS TABLE(
datasource_id VARCHAR(2),
EventType_ID VARCHAR(2),
User_ID VARCHAR(1),
period DATETIME,
Notes VARCHAR(255),...
^{“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

July 29, 2010 at 9:20 am

#1199875
RE: LIKE operator with millions of records giving slow performance

Eugene Elutin (7/29/2010)
Can you test how long the following query will run and how many records does it returns:
SELECT a.*
FROM dbo.Person a
LEFT JOIN dbo.Person_exclusion b
ON a.Name LIKE b.Name_similar
WHERE b.Name_similar...
^{“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

July 29, 2010 at 8:57 am

#1199854
RE: finding top 3 * based on some condition

malavika.ramanathan (7/29/2010)
hi all,
i have a query which gives the percentage of time against each user.
I have 6 records in total. I must now select top 3 records from it in...
^{“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

July 29, 2010 at 7:19 am

#1199775
RE: LIKE operator with millions of records giving slow performance

Santhosh Yamsani (7/29/2010)
-- update a set a.ExclusionID = b.ID
SELECT COUNT(*)
FROM dbo.Person a
join dbo.Person_exclusion b
on a.Name like b.Name_similar
Try running this statement to see...
^{“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

July 29, 2010 at 7:04 am

#1199758
RE: Ranking in stored procedure

Paul White NZ (7/29/2010)
Thank you Chris, that was very kind. 😎
No worries Paul I could see you were in deep water there, mate 😀
^{“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

July 29, 2010 at 5:20 am

#1199711
RE: Ranking in stored procedure

CREATE TABLE #SampleData (Name VARCHAR(10), Result INT)
INSERT INTO #SampleData (Name, Result)
SELECT 'Joan', 5 UNION ALL
SELECT 'Jhon', 5 UNION ALL
SELECT 'kris', 3 UNION ALL
SELECT 'alice', 2 UNION...
^{“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

July 29, 2010 at 5:05 am

#1199703
RE: Ranking in stored procedure

Look up DENSE_RANK() in BOL (Books On-line).
^{“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

July 29, 2010 at 3:01 am

#1199662
RE: Are the posted questions getting worse?

bitbucket-25253 (7/28/2010)
Are the OPs getting bummer and dumber or just lazy and lazier, have others noticed this behavior with fellow workers?
http://www.sqlservercentral.com/Forums/Topic960056-146-1.aspx?Update=1
That's just too funny, Ron!
bitbucket-25253 (7/28/2010)
Go to
http://technet.microsoft.com/en-us/default.aspx
Input "Upgrading 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

July 28, 2010 at 9:58 am

#1199338
RE: datediff on dates contained in single column

whitlander (7/28/2010)
Sorry about that. How's this?
INSERT INTO [PELVIS].[dbo].[Event]
([DataSource_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

July 28, 2010 at 9:37 am

#1199316
RE: datediff on dates contained in single column

Hi Andy, I'm sorry but I don't have time to convert your post into a set of statements which will populate the sample table.
It should look something like this:
INSERT INTO...
^{“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

July 28, 2010 at 9:01 am

#1199284
RE: datediff on dates contained in single column

Hi Andy
Can you please provide a CREATE TABLE script for table Event and a script to populate it with some dummy data which demonstrates your problem?
Is the table DataSource necessary?...
^{“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

July 28, 2010 at 6:00 am

#1199174

Viewing 15 posts - 7,591 through 7,605 (of 10,143 total)