ChrisM@Work

Forum Replies Created

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

RE: Reverse Treeview

Jeff Moden (10/14/2011)
To add to ChrisM's code, please see the following article on how to display smaller hierarchies likes this...
http://www.sqlservercentral.com/articles/T-SQL/72503/
Teach a guy to fish - I'd like to add that...
^{“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 14, 2011 at 9:15 am

#1394706
RE: Name concatenations

Closer, but still no cigar:
SELECT
RTRIM(First_name) + ' ' + RTRIM(Last_Name),
RTRIM(First_name) + CASE WHEN RIGHT(First_name,1) = ' ' THEN ' ' ELSE '' END + RTRIM(Last_Name),
...
^{“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 14, 2011 at 8:32 am

#1394673
RE: Query Optimization using Indexes

Ninja's_RGR'us (10/14/2011)
adlakha.22 (10/14/2011)
Your query worked great when I tried with distinct.
But still its taking 83 Seconds to execute.
Do you have any other idea to make it more optimize.
Thanks in advance
Post...
^{“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 14, 2011 at 8:13 am

#1394658
RE: Reverse Treeview

;WITH tree AS (
SELECT DISTINCT uid, cat_parent, cat_name, node = 0
FROM #temp where cat_parent = 0
UNION ALL
SELECT t.uid, t.cat_parent, t.cat_name, node = l.node+1
FROM tree L
INNER JOIN #temp t ON l.uid...
^{“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 14, 2011 at 7:27 am

#1394637
RE: Finding the column containing the minimum value

20x faster! I'm going home...:blush:
^{“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 14, 2011 at 7:11 am

#1394632
RE: Finding the column containing the minimum value

Anytime Phil. Test them both, I reckon Lowell's MegaCASE will win hands down.
^{“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 14, 2011 at 7:04 am

#1394623
RE: Finding the column containing the minimum value

Lowell (10/14/2011)
maybe it's just me, but both my solution and Chris's seem to produce the exact same execution plan?, but Chris's is 48% relative to the batch where mine...
^{“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 14, 2011 at 6:50 am

#1394614
RE: Finding the column containing the minimum value

Something similar to this outperforms the obvious pivot approach for removing the blanks from address elements:
select t.*, x.ColIndex
from @Tab1 t
CROSS APPLY (SELECT TOP 1 ColIndex from (
SELECT ColIndex =...
^{“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 14, 2011 at 6:34 am

#1394600
RE: [SQL Server 2003] [Urgent] Performance issue

Can you confirm which version of SQL Server you are using? There's no 2003 version.
^{“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 13, 2011 at 7:49 am

#1394025
RE: Regarding SQL Query optimization

Junglee_George (10/13/2011)
Chris, whats on your mmind..Which Method is better..first or second..??
Easy - test them 😉
It's an unrestricted update though, one pass is likely to be better than two or more.
^{“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 13, 2011 at 6:02 am

#1393954
RE: Regarding SQL Query optimization

No problem. This relies heavily on the testing framework which Jeff Moden et al set up for his excellent string splitter article[/url].
Here's the rCTE code:
CREATE FUNCTION [dbo].[DS8K_rCTEa](@pString [varchar](8000), @pDelimiter [char](1))
RETURNS...
^{“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 13, 2011 at 5:59 am

#1393952
RE: Regarding SQL Query optimization

codebyo (10/13/2011)
You could try running a test example using the estimated execution plan button and the percentage taken by each method. I can tell you that the single UPDATE affecting...
^{“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 13, 2011 at 5:26 am

#1393930
RE: Query Optimization using Indexes

Bearing in mind that you are aggregating quite a few columns from a million or so rows down to 140,000, this query will always take some time to run. However,...
^{“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 13, 2011 at 5:15 am

#1393925
RE: Summarize Results from two tables, using one query.

Good catch, Drew. I was about to point it out with evidence:
USE tempdb
GO
CREATE TABLE tblUCSU (strlocation VARCHAR(10), strPen INT)
INSERT INTO tblUCSU (strlocation, strPen)
SELECT 'London', 1 UNION ALL
SELECT 'New York',...
^{“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, 2011 at 8:48 am

#1393508
RE: How to return Null or value when nothing is returned from Query

Never miss a (nearly) free opportunity to gather some information:
SELECT Result = NULLIF(COUNT(*),0) FROM atable
^{“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, 2011 at 6:55 am

#1393395

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