ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,296 through 5,310 (of 10,143 total)

RE: CTE Vs temp table

purushottam2 (1/25/2013)
Yes, vehicle is main table. So you mean first i should select data from vehicle table based on all filters into a temp table with index.
Then for other filters...
^{“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

January 25, 2013 at 7:36 am

#1581037
RE: CTE Vs temp table

purushottam2 (1/25/2013)
Hi Grant,
Thanks for your reply,
Surely i will remove coalesce. Should i remove nolock, does it also affect my performance. Performance is our first priority.
Most of the filtration 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

January 25, 2013 at 7:17 am

#1581026
RE: Check for existence of a single value for all rows in a column

Have a look at the article linked in my sig (please read this) for ddl and dml recipes. This will be much easier if folks have readily-consumable data to work...
^{“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

January 25, 2013 at 6:18 am

#1580985
RE: Get distinct value

Except for Sergiy's first query, the plans are very similar and are costed exactly equal. I'd guess that my APPLY version with two extra operators (Compute scalar and TOP, each...
^{“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

January 25, 2013 at 1:11 am

#1580853
RE: Reverse string without built in functions

Here's another method:
-- Parameters
DECLARE @String VARCHAR(8000)
SET @String = 'Reverse'
-- Local Variables
DECLARE @OutputString VARCHAR(8000); SET @OutputString = '';
DECLARE @StrLen INT; SET @StrLen = DATALENGTH(@String);
DECLARE @MappingTable TABLE (n INT PRIMARY KEY CLUSTERED);...
^{“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

January 24, 2013 at 8:47 am

#1580579
RE: Get distinct value

SQL006 (1/23/2013)
thanks chris for the reply
can you tell me the use of SELECT TOP 1 n=1 ....i never use this
SELECT
p.ProductID,
Relation = CASE x.n WHEN 1 THEN 'Linked' ELSE...
^{“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

January 24, 2013 at 1:04 am

#1580397
RE: Get distinct value

SELECT
p.ProductID,
Relation = CASE x.n WHEN 1 THEN 'Linked' ELSE 'Not Linked' END
FROM @product p
OUTER APPLY (
SELECT TOP 1 n=1
FROM @ProductRelation r
WHERE p.ProductID IN (r.FirstProductID, r.SecondProductID)
) x
ORDER...
^{“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

January 23, 2013 at 6:50 am

#1580051
RE: Indexed Views - why don't they improve performance?!!!!

Have you tried figuring out the filters and applying to the inner query? Something like this:
DECLARE @YEAR INT, @MONTH INT
SELECT @YEAR = 2005, @MONTH = 6
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET...
^{“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

January 23, 2013 at 6:38 am

#1580048
RE: Nested replaces ?

dwain.c (1/22/2013)
Gentlemen,
Allow me to enter a new dog in this race! Perhaps you'll find the new approach amusing. I call it the Nested Replace by Quirky Update (NRxQU...
^{“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

January 23, 2013 at 5:06 am

#1580010
RE: Add a column Count from another table

Have a look at the code I used for the sample data, if there's anything you are unsure of then ask. Note that the sample data tables I created 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

January 22, 2013 at 7:26 am

#1579629
RE: Add a column Count from another table

ncurran217 (1/22/2013)
Also, to get the numbers I posted as a result from my query I would have to post data with 100k rows.
Here's how to create some sample data -...
^{“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

January 22, 2013 at 7:08 am

#1579623
RE: Problem with CAST to VARCHAR with SUBSTRING Function

Welsh Corgi (1/22/2013)
The following works :
SELECT
CASE WHEN LEFT(Customer.StartDate, 2) = '98' THEN substring(convert(varchar(20),Customer.StartDate), 3, 2)
...
^{“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

January 22, 2013 at 6:47 am

#1579607
RE: Add a column Count from another table

ncurran217 (1/22/2013)
Ok I did not know I needed to post any data. ..
No worries, you're new here. Have a read of this article[/url], it will help you formulate some test...
^{“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

January 22, 2013 at 6:42 am

#1579605
RE: Nested replaces ?

dwain.c (1/22/2013)
Uh, just asking here but wouldn't it make sense to vary the strings and the replacements?
Or did I miss something?
Not often you miss something Dwain! You're right of course....
^{“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

January 22, 2013 at 6:23 am

#1579598
RE: Nested replaces ?

Can anyone join in?
Here's an efficient dynamic SQL version and a rCTE version. The rCTE version updates a million rows in about 30 seconds, the dynamic SQL version appears 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

January 22, 2013 at 5:37 am

#1579577

Viewing 15 posts - 5,296 through 5,310 (of 10,143 total)