ChrisM@Work

Forum Replies Created

Viewing 15 posts - 601 through 615 (of 10,143 total)

RE: Optimize WHERE predicate

You could do with clustered indexes on those heap tables, something like
DDA_DESC
^{“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

August 24, 2017 at 5:00 am

#1956566

RE: Need help with "Where" clause

Chris Harshman - Monday, August 21, 2017 7:30 AM

it looks like your WHERE clause contradicts itself, the first combination of IDNum 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

August 21, 2017 at 7:37 am

#1955987

RE: Need help with "Where" clause

I think you're losing logical consistency with your AND / OR & braces. Try this:

WHERE

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 21, 2017 at 7:24 am

#1955984

RE: Query performance tuning - Slow execution time

-- Here's an attempt to remove the complex view vwDeals from the query. Run it and compare with the original.
-- Note that I can't find [AccountingDate] in...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 18, 2017 at 4:30 am

#1955798

RE: Using CTE to update multiple columns in table and loop on all rows in table

Have a play with these:
-- Amended sample set with full name
DROP TABLE dbo.SimpleH;CREATE TABLE dbo.SimpleH (ID INT NOT NULL PRIMARY KEY CLUSTERED, ParentID INT NULL, [FullName]...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 17, 2017 at 4:06 am

#1955629

RE: Query performance tuning - Slow execution time

ffarouqi - Wednesday, August 16, 2017 1:05 PM

ChrisM@Work - Wednesday, August 16, 2017 1:44 AM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 17, 2017 at 2:13 am

#1955622

RE: Query performance tuning - Slow execution time

-- Here's an alternative to vwReportMemberTypeResults_Employee
-- which reads DealsToEmployees and Employees only once, instead of four times.
-- Test it, when you're satisfied that it works, sub...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 17, 2017 at 2:05 am

#1955621

RE: Using CTE to update multiple columns in table and loop on all rows in table

DROP TABLE dbo.SimpleH;CREATE TABLE dbo.SimpleH (ID INT NOT NULL PRIMARY KEY CLUSTERED, ParentID INT NULL, [Name] VARCHAR(30) NOT NULL, [Level] TINYINT NULL)
INSERT INTO SimpleH (ID, ParentID, [Name])...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 16, 2017 at 8:08 am

#1955496

RE: Query performance tuning - taking more than 9 minutes

The sample data is quite a bit different to the actual data, it will be interesting to see how that pans out. It's so easy to switch between these two...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 16, 2017 at 7:31 am

#1955482

RE: Convert YYYY-MM-DD into Interger in Select, and still recognize INT date in the Join

quinn.jay - Tuesday, August 15, 2017 2:20 PM

Hello, I'm able to convert a yyyy-mm-dd formated DATE column into an Integer, but when...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 16, 2017 at 4:36 am

#1955426

RE: Query performance tuning - taking more than 9 minutes

Personally, I'd opt for a phase separation between the aggregation and the rest of the query, like this:
SELECT ObjectType, ObjectID, MAX_QueuedAt = MAX(QueuedAt)
INTO #SfQueuedTxUpdates
FROM dbo.SfQueuedTxUpdates

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 16, 2017 at 4:13 am

#1955421

RE: Query performance tuning - Slow execution time

ffarouqi - Tuesday, August 15, 2017 3:37 PM

Jonathan AC Roberts - Tuesday, August 15, 2017 1:52 PM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 16, 2017 at 1:44 am

#1955395

RE: Query performance tuning - Slow execution time

Eirikur Eiriksson - Tuesday, August 15, 2017 3:38 AM

ffarouqi - Monday, August 14, 2017 11:11 PM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 15, 2017 at 7:17 am

#1955281

RE: Introduce rows for ROW_NUMBER Solution

J Livingston SQL - Tuesday, August 8, 2017 9:33 AM

a helping hand 😀 (courtesy of http://www.convertcsv.com/csv-to-sql.htm ...simple cut and...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 8, 2017 at 9:44 am

#1954500

RE: Introduce rows for ROW_NUMBER Solution

CREATE TABLE

Johnny D - Tuesday, August 8, 2017 9:21 AM

Hi ChrisM,
I have never used APPLY but I am not sure how this...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

August 8, 2017 at 9:24 am

#1954491

Viewing 15 posts - 601 through 615 (of 10,143 total)