ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,476 through 2,490 (of 10,143 total)

RE: Avoid LEFT join

mohanaprabhu.v (6/4/2015)
SELECT tblInvoice.*, tblCustomer.FirstName + SPACE(1) +
tblCustomer.LastName as CustomerName FROM tblInvoice WITH(NOLOCK)
LEFT JOIN tblCustomer WITH(NOLOCK) ON tblInvoice.CustomerNo = tblCustomer.CustomerNo
please help us to improve this query performance level without...
^{“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

June 4, 2015 at 3:52 am

#1802923
RE: Writing Better T-SQL: Top-Down Design May Not be the Best Choice

Nice thought-provoking article SQLMickey. Here's a thought for you:
"Let’s take a look at a ~~stored procedure~~ query written four different ways."
Whilst a junior developer might select one of the four...
^{“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

June 2, 2015 at 5:54 am

#1802250
RE: Get Work system - avoid duplicate inserts

There are many impediments to performance in your query. You want it to run as quickly as possible. Test this as a functional equivalent.
;
WITH TheChosenOne AS (
SELECT TOP 1...
^{“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

June 2, 2015 at 2:22 am

#1802230
RE: Date/Time Calculations for Workplan times

-- Sample data setup
drop TABLE #StaffHours
CREATE TABLE #StaffHours (RecID INT, StaffID INT, StartDate DATETIME, EndDate DATETIME)
INSERT INTO #StaffHours (RecID, StaffID, StartDate, EndDate) VALUES
(17,969,'2015-05-18 00:00:00.000','2015-05-18 06:00:00.000'),
(18,969,'2015-05-18 18:00:00.000','2015-05-19 06:00:00.000'),
(19,969,'2015-05-19 18:00:00.000','2015-05-20 06:00:00.000'),
(20,969,'2015-05-20 18:00:00.000','2015-05-21...
^{“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

June 1, 2015 at 8:43 am

#1802012
RE: Need Help with Column Grouping

;
WITH SalesPerEmp AS (
SELECT
e.firstname + N' ' + e.lastname AS FullName,
COUNT(*) AS TotalOrders
FROM dbo.Employees e
INNER JOIN Sales.Orders o
ON e.empid = o.empid
GROUP BY e.firstname + N' ' + e.lastname
)
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

June 1, 2015 at 6:38 am

#1801960
RE: Get Work system - avoid duplicate inserts

Some ideas here[/url]. As Gianluca says, difficult to tell what is going wrong with no code. Your statement could be something like this:
UPDATE UnassignedWork
SET unassigned = 1
OUTPUT
INSERTED.column1,...
^{“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

June 1, 2015 at 4:02 am

#1801942
RE: Tuning expensive query III

WhiteLotus (5/28/2015)
Hi All,
I am tuning this query ( Please see the bottom query)
<<snip>>
Please help how to optimize it .. Really appreciate it
Cheers
Optimising performance of queries is much easier if...
^{“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

May 29, 2015 at 2:33 am

#1801494
RE: Are the posted questions getting worse?

Ed has the crystal ball today, plus nerves of steel and the patience of a person with a tent guarded by a hippo..
^{“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

May 28, 2015 at 9:39 am

#1801298
RE: INSERT INTO not inserting enough rows

richardmgreen1 (5/28/2015)
Hi Chris
I'll take all the help I can get (I'm always willing to learn).
I still don't understand why (without any changes) I could select 53 records last week 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

May 28, 2015 at 8:43 am

#1801278
RE: INSERT INTO not inserting enough rows

richardmgreen1 (5/28/2015)
Hi Sean
It's quite a monster select statement that normally runs quite happily (or so we thought).
You're basically correct. When the select runs on it's own for some records...
^{“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

May 28, 2015 at 8:15 am

#1801264
RE: INSERT INTO not inserting enough rows

richardmgreen1 (5/28/2015)
Hi Sean
It's quite a monster select statement that normally runs quite happily (or so we thought).
You're basically correct. When the select runs on it's own for some records...
^{“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

May 28, 2015 at 8:09 am

#1801261
RE: Are the posted questions getting worse?

Brandie Tarvin (5/27/2015)
GilaMonster (5/27/2015)
Looks like I hit a few nerves with Sunday's editorial....
Here on the site? Or are you referring to the World Cup convo?
The Scientific Method. Million-row tests &...
^{“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

May 27, 2015 at 9:24 am

#1800985
RE: optimizing SUM and CASE statements

Have some kip first 🙂
This article [/url]describes how to capture and send/attach a .sqlplan file (execution plan export file).
^{“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

May 27, 2015 at 9:09 am

#1800976
RE: optimizing SUM and CASE statements

Have a look at row 16 of my test set and compare it to your CASE statements.
^{“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

May 27, 2015 at 8:34 am

#1800959
RE: optimizing SUM and CASE statements

mattech06 (5/27/2015)
I've run that but what does it tell me or what am I expecting to see?
Incorrect results. I'm genuinely surprised you can't see it.
^{“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

May 27, 2015 at 8:18 am

#1800944

Viewing 15 posts - 2,476 through 2,490 (of 10,143 total)