ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,141 through 1,155 (of 10,143 total)

RE: Prod vs Dev execution mystery

Yuri55 (10/12/2016)
You mean it can be (let say, potentially) bad query itself? I know it's not the best and we have some ideas how to improve it but even did...
^{“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

October 12, 2016 at 7:47 am

#1906122
RE: Is there any difference between the 2

mcampoam (10/12/2016)
Hi,
Any difference in results or performance between the 2 examples? Names of tables/columns are generic below:
#1
update customer t1
set column_1 = x.value
FROM
(select id
, case
when column_2 is null ...
^{“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

October 12, 2016 at 5:32 am

#1906074
RE: Prod vs Dev execution mystery

Can you post the actual execution plan of the update run in dev as a .sqlplan attachment please? As Jeff has pointed out, there may be tuning opportunities.
^{“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

October 12, 2016 at 4:53 am

#1906069
RE: t-sql 2012 update primary key value into column of the second table

wendy elizabeth (10/11/2016)
In a t-sql 2012, I want to place the new lockid values obtain from the first insert statement (for the lock table) indiviudally into the second...
^{“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

October 12, 2016 at 2:32 am

#1906050
RE: How to Make a SQL Query Run Faster

Grant Fritchey (10/11/2016)
DesNorton (10/11/2016)
Grant Fritchey (10/11/2016)
What everyone else has said, plus.
If you're going to attempt to tune the query, you absolutely have to have the execution plans. There is no...
^{“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

October 11, 2016 at 8:24 am

#1905908
RE: How to Make a SQL Query Run Faster

It shouldn't be too hard to replace this awful mess with something sensible and streamlined.
Here's an attempt to rewrite cte2 along more conventional lines. It's about 10 minutes worth
of effort...
^{“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

October 11, 2016 at 3:49 am

#1905842
RE: How to Make a SQL Query Run Faster

This query has more repeats than the Disney channel. It's pointless attempting to tune it as it is. Pass it on to a professional TSQL dev to rewrite from scratch....
^{“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

October 11, 2016 at 2:21 am

#1905833
RE: Are the posted questions getting worse?

Jeff Moden (10/8/2016)
Eirikur Eiriksson (10/8/2016)
Ouch ...biting the tongue....
😎
We all know the reason for the given answer... she simply doesn't know any better method even after she's been shown how 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

October 10, 2016 at 1:53 am

#1905596
RE: t-sql 2012 link 3 tables

pietlinden (10/7/2016)
Isn't this as simple as
School--(1,M)--Locker--(1,M)--LockerAssignment--(M,1)---Student
and
LockerAssignment--(M,1)--SchoolYear
From that you can join the tables and get all the current locker assignments etc.
I guess it could be, if there's 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

October 7, 2016 at 9:32 am

#1905414
RE: t-sql 2012 link 3 tables

wendy elizabeth (10/7/2016)
Since the data is duplicated for each school per school year,...
I didn't say that. I said that if you query for any school year, you 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

October 7, 2016 at 9:13 am

#1905405
RE: ranking query with nulls

Eirikur Eiriksson (10/7/2016)
Quick suggestion
😎
USE TEEST;
GO
IF OBJECT_ID(N'dbo.temp') IS NOT NULL DROP TABLE dbo.temp;
create table dbo.temp(Companyid varchar(10) not null,companyname varchar(100) not null,DateFrom datetime not null, DateTo datetime null);
INSERT INTO temp values('A1','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

October 7, 2016 at 8:11 am

#1905383
RE: ranking query with nulls

Your request is almost certainly possible, however your own difficulty with it hints that there may be a simpler option. What about this?
SELECT t.*
FROM #temp2 t
WHERE t.DateTo IS NULL...
^{“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

October 7, 2016 at 7:26 am

#1905368
RE: Add column value depending on other value in same row

Super_Grover (10/7/2016)
Okay, so I created and (partly) filled the three lookup tables and took a go on Scott's approach.
Turned out I was thinking waaay too complex. I ended up with...
^{“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

October 7, 2016 at 6:42 am

#1905359
RE: t-sql 2012 link 3 tables

wendy elizabeth (10/6/2016)
The school year is used. These 3 tables are joined to the following tables to determine what the school year is.
Note: I did not list the following...
^{“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

October 7, 2016 at 3:34 am

#1905329
RE: conditional where clause

This becomes a little more interesting if you increase the complexity of the query:
CREATE PROCEDURE [dbo].[test04]
(@NUM INT)
AS
SET NOCOUNT ON;
IF (@NUM > 0)
BEGIN
SELECT p.n --
FROM dbo.Numbers NM
INNER...
^{“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

October 7, 2016 at 3:02 am

#1905324

Viewing 15 posts - 1,141 through 1,155 (of 10,143 total)