ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,436 through 3,450 (of 10,143 total)

RE: Are the posted questions getting worse?

Brandie Tarvin (4/22/2014)
ChrisM@Work (4/22/2014)
Does anyone around here know anything about output lists from index seek operators, by any chance? Don't all raise your hands at once, you might drop your...
^{“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

April 22, 2014 at 7:10 am

#1707745
RE: Query optimization help

EasyBoy (4/16/2014)
ChrisM@Work (4/16/2014)
EasyBoy (4/15/2014)
I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).
Did you look at the execution plans to see 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

April 22, 2014 at 7:03 am

#1707743
RE: Are the posted questions getting worse?

Greg Edwards-268690 (4/22/2014)
Ed Wagner (4/22/2014)
dwain.c (4/21/2014)
Greg Edwards-268690 (4/21/2014)
TomThomson (4/20/2014)
Greg Edwards-268690 (4/20/2014)
Bucket List Challenge - teach Jim to Filet a fish properly.
I'll keep trying, but I may fail miserably.
Fillet "a fish"...
^{“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

April 22, 2014 at 6:41 am

#1707729
RE: Query optimization help

TomThomson (4/18/2014)
Lynn Pettis (4/18/2014)
TomThomson (4/17/2014)
I may be confused here, but if I've understood correctly the optimiser might be able to do somewhat better with an index like this:-
CREATE NONCLUSTERED INDEX...
^{“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

April 22, 2014 at 1:57 am

#1707637
RE: Query optimization help

Lynn Pettis (4/17/2014)
ScottPletcher (4/17/2014)
You need to do further research before you change the existing index to a filtered index, because that index will no longer satisfy queries originally using 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

April 17, 2014 at 9:29 am

#1706812
RE: How to handle this

What are the business rules?
^{“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

April 17, 2014 at 7:17 am

#1706742
RE: Query optimization help

Except...the 70k read plan isn't from the index Gail suggested, and whatever index it is has several columns which aren't referenced by the query - hence the difference in reads.
I'd...
^{“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

April 17, 2014 at 5:50 am

#1706721
RE: Better way to write this query without calling a lookup for each column

Mansfield (4/16/2014)
Details a bit sparse, but try pivot the lookup table so that you can compare column to column?
Like this?
SELECT
KeyValue,
Flag4 = MAX(CASE WHEN ItemNumber = 4 AND Flag =...
^{“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

April 17, 2014 at 4:24 am

#1706691
RE: Using CAST or CONVERT to change data from string to datetime

CONVERT takes a style parameter which can be used to shape the output of a date-datatype to string-datatype conversion - which is the usage that most folks are familiar 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

April 17, 2014 at 2:30 am

#1706663
RE: Query optimization help

EasyBoy (4/17/2014)
Please find the updated sql plan.
Thanks! That's nice - the filtered index eliminates the need for an expensive residual predicate (the "filter" bit of the filtered seek Gail mentioned...
^{“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

April 17, 2014 at 2:16 am

#1706662
RE: Query optimization help

Try to obtain the same result set by whatever other means you can think of. Time the execution of each method and record those timings. Usually the fastest query is...
^{“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

April 16, 2014 at 8:08 am

#1706479
RE: Query optimization help

GilaMonster (4/16/2014)
The query is reading just over 14 million rows and aggregating that down to 1099. If it's reading 14 million rows from a table (the rows which satisfy 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

April 16, 2014 at 4:35 am

#1706391
RE: Query optimization help

EasyBoy (4/15/2014)
I did try almost all combination of altering columns for index, but somehow logical reads are same (67000).
You're retrieving 14+ million rows from one index or another on 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

April 16, 2014 at 3:14 am

#1706362
RE: SQL Server Read ismore

yuvipoy (4/15/2014)
ChrisM@Work (4/15/2014)
95% of the cost of the query is a key lookup of table2. The query uses index IDX_SE_CD_MEAS_FK to seek matches on column TCOL1. Since the output requires...
^{“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

April 16, 2014 at 1:55 am

#1706336
RE: Selecting Data from table with multiple conditions on multiple columns

Always start simple:
SELECT *
FROM Car
WHERE Price IN (@Price1,@price2,@price3)
AND Manufacture IN (@Manufacture1,@Manufacture2,@Manufacture3)
AND Model IN (@Model1,@Model2,@Model3)
AND City IN (@City1,@City2,@City3)
^{“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

April 16, 2014 at 1:46 am

#1706333

Viewing 15 posts - 3,436 through 3,450 (of 10,143 total)