ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,146 through 5,160 (of 10,143 total)

RE: Grouping records by time interval

geert.de.vylder (2/28/2013)
Hi everyone,
I have been searching for a few days now to try to solve a problem, but without success. What I did find on the net wasn't what I...
^{“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

February 28, 2013 at 8:09 am

#1591992
RE: Low Performance - High CPU Problem and others...

Jeff Moden (2/27/2013)
GilaMonster (1/30/2013)
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/
No magic bullets.
And no, adding the pk column to NC indexes is not going to fix lookups, and is probably a waste of time.
I've had some success...
^{“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

February 28, 2013 at 2:40 am

#1591910
RE: Optimizing query

surindersinghthakur (2/27/2013)
Hi,
Can someone help me in optimizing below query, is there any other way where I can improve this query
SELECT A.Col1, COUNT(B.POST_DT) AS OD_NO
FROM (SELECT Col1, col3, Col2,
SUM(CASE...
^{“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

February 28, 2013 at 1:45 am

#1591898
RE: Getting minimum of top n rows without using subquery

winmansoft (2/27/2013)
You mean "select min([date]) from entries WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'"? But we want to select minimum from top 100 rows
The minimum from the top 100 rows (as...
^{“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

February 28, 2013 at 1:05 am

#1591891
RE: The Beer Cooler Thread

Morrison's are stocking a new Adnams ale, Ghost Ship. It's vaguely similar to the famous Thornbridge ales, Jaipur, Kipling and Wild Swan but a little sweeter. Lush even in our...
^{“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

February 28, 2013 at 12:55 am

#1591890
RE: Select.. where column1 in (@var)

Raul Undreiner (2/27/2013)
What am I missing?
Why would this work
SELECT *
FROM #MainQuery
WHERE column1 IN ('Value1','Value2')
and this not?
declare @var varchar(100)
set @TOB = '''Value1'',''Value2'''
SELECT *...
^{“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

February 27, 2013 at 9:16 am

#1591605
RE: UDF Help

Jeff Moden (2/27/2013)
TheSQLGuru (2/27/2013)
You may THINK you want to do a Scalar UDF, but you probably don't. I wrote a chapter for the SQL Server MVP Deep Dives 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

February 27, 2013 at 6:56 am

#1591537
RE: UDF Help

The second query is a red herring. It will do this:
SELECT @Pist = dbo.udfOrGetDetail (@OID,@OTID) +'<br />'
as many times as rows returned by everything after the FROM list. Each time...
^{“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

February 27, 2013 at 6:47 am

#1591527
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

Paul White (2/27/2013)
ChrisM@Work (2/27/2013)
Anyway, isn't it past your bedtime?
We can no longer be friends :laugh:
Slippers are in the 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

February 27, 2013 at 3:51 am

#1591484
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

Paul White (2/27/2013)
ChrisM@Work (2/27/2013)
I know of at least one other old git who will be cackling his face off reading these posts :laugh:
Oi!
Heh I didn't mean you specifically Paul, though...
^{“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

February 27, 2013 at 3:34 am

#1591477
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

mister.magoo (2/27/2013)
ChrisM@Work (2/27/2013)
...
About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.
I know...
^{“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

February 27, 2013 at 3:28 am

#1591473
RE: Getting minimum of top n rows without using subquery

winmansoft (2/27/2013)
I don't think that is correct,because our query returns min(date) from top 100 rows which satisfies where condition But i don't find that in your query
Looks good to me...
--...
^{“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

February 27, 2013 at 3:25 am

#1591470
RE: Getting minimum of top n rows without using subquery

winmansoft (2/27/2013)
What if we don't want to sort the date? Is there any easy method?
Use Kingston's solution. It doesn't use the sort operator. It uses a stream aggregate.
^{“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

February 27, 2013 at 3:24 am

#1591468
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
mister.magoo (2/26/2013)
ChrisM@Work (2/26/2013)
dwain.c (2/26/2013)
ChrisM@Work (2/26/2013)
peter-757102 (2/26/2013)
Using a binary comparisons is a good speedup for specific cases and good to be aware of and certainly worth mentioning in bold...
^{“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

February 27, 2013 at 1:51 am

#1591442
RE: Execute Stored Procedure Multiple Times

It might be worth converting (a version of) the sproc into a multi-statement table-valued function. The performance won't change but it would give you the flexibility of "running" the code...
^{“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

February 27, 2013 at 1:46 am

#1591439

Viewing 15 posts - 5,146 through 5,160 (of 10,143 total)