ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,201 through 1,215 (of 10,143 total)

RE: delete millions of rows

Eric M Russell (9/13/2016)
ChrisM@Work (9/13/2016)
Eric M Russell (9/12/2016)
4 million rows is actually not a lot, but it depends on the speed of your storage system and whether the table will...
^{“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 13, 2016 at 9:59 am

#1900599
RE: delete millions of rows

Jeff Moden (9/13/2016)
ChrisM@Work (9/13/2016)
Cache priming can make a very significant difference to batch delete times.
Curious... isn't that just moving the problem to something else? You still have 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 13, 2016 at 9:09 am

#1900581
RE: delete millions of rows

Eric M Russell (9/12/2016)
4 million rows is actually not a lot, but it depends on the speed of your storage system and whether the table will need to remain online...
^{“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 13, 2016 at 7:40 am

#1900541
RE: Filling Buckets

ashishkumarrai (9/13/2016)
ChrisM@Work (9/13/2016)
ashishkumarrai (9/13/2016)
ChrisM@Work (9/9/2016)
This should be a step in the right direction:
DROP TABLE #Buckets
CREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);
INSERT INTO #Buckets
VALUES ( '1', 85, 0...
^{“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 13, 2016 at 3:15 am

#1900481
RE: Filling Buckets

ashishkumarrai (9/13/2016)
ChrisM@Work (9/9/2016)
This should be a step in the right direction:
DROP TABLE #Buckets
CREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);
INSERT INTO #Buckets
VALUES ( '1', 85, 0 ) ,
...
^{“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 13, 2016 at 2:48 am

#1900468
RE: query slow with variable, fast with constant .. using option (recompile) inside if exists

sqld-_-ba (9/10/2016)
nevermind, just thought of workaround - I can insert record into table and then check rowcount
insert into #tmp select
top 1 1 FROM CHANGETABLE(CHANGES dbo.UserPrefs, @last_sync_version) AS ct
WHERE...
^{“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 12, 2016 at 7:17 am

#1900310
RE: Execution plan ignoring a Temp Table being used to filter the results

TheSQLGuru (9/12/2016)
ChrisM@Work (9/12/2016)
Phil Parkin (9/12/2016)
Don't explicitly drop the #temp table
Hi Chris. Can you explain a little how not dropping the temp table affects things, please?
From memory Phil, if you explicitly...
^{“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 12, 2016 at 6:57 am

#1900304
RE: Execution plan ignoring a Temp Table being used to filter the results

Phil Parkin (9/12/2016)
Don't explicitly drop the #temp table
Hi Chris. Can you explain a little how not dropping the temp table affects things, please?
From memory Phil, if you explicitly drop 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 12, 2016 at 6:27 am

#1900296
RE: Execution plan ignoring a Temp Table being used to filter the results

The Dixie Flatline (9/12/2016)
When you join two tables, the optimizer should know which one is the smaller. It doesn't have to follow the sequence in which the tables...
^{“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 12, 2016 at 6:20 am

#1900293
RE: Execution plan ignoring a Temp Table being used to filter the results

We use this method quite a lot for breaking down processing queries running in production, so that the number of rows processed is low enough to avoid locks escalating 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 12, 2016 at 6:03 am

#1900291
RE: PERFORMANCE ISSUE ON SQL SERVER 2008

First, simplify your query. Second, split it into two different queries. The execution plan for a single orderID is unlikely to be the same as the execution plan for them...
^{“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 9, 2016 at 9:30 am

#1900092
RE: Filling Buckets

This should be a step in the right direction:
DROP TABLE #Buckets
CREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);
INSERT INTO #Buckets
VALUES ( '1', 85, 0 ) ,
...
^{“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 9, 2016 at 7:46 am

#1900038
RE: Is Division by Zero NULL?

Gregory Hart (9/8/2016)
OK, ... let this post/thread be a lesson to all you "know-it-all" experts. Lessons learned:
Not all UDF are bad, ... if correctly written, they can be tremendous 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

September 9, 2016 at 3:01 am

#1899979
RE: Finding long running query and killing it

Chitown (9/8/2016)
Expert, I am working on scheduling a job which runs every 10 minutes and kill any active session (which is active for more than 10 minutes) from a specific...
^{“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 10:04 am

#1899860
RE: Linked Servers help

farhan.appxtech (9/8/2016)
From very long time I was looking for this topic on linked servers. Kepp updating such relevant posts.
Reported as spam.
^{“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 6:10 am

#1899766

Viewing 15 posts - 1,201 through 1,215 (of 10,143 total)