ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,056 through 5,070 (of 10,143 total)

RE: Looping without cursor not updating properly

Siobhan Perricone (3/12/2013)
ChrisM@Work (3/12/2013)
Gosh :blush: thanks!
A gentle reminder - always always test 🙂 If this solution works, it can be condensed and tweaked for performance.
Yeah, I'm testing now. Looks...
^{“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

March 12, 2013 at 7:57 am

#1596021
RE: How to pass BIGINT data type values to IN CLAUSE in sql server 2008?

This
declare @Rid bigint
set @Rid =106,108;
generates an error, because bigint datatype holds a single bigint, not a list of them. If @Rid was a character type then you would be able...
^{“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

March 12, 2013 at 7:51 am

#1596018
RE: How to create a query which kill CPU?

Set up a million-row (or more) test table (see Jeff Moden's articles) and do a triangular join running totals on it.
Gather index frag stats. This one is from Glen Berry's...
^{“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

March 12, 2013 at 7:34 am

#1596009
RE: Looping without cursor not updating properly

No worries mate. I had the day off yesterday and picked it up at home - when you hadn't posted up by this morning it was ready to go.
^{“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

March 12, 2013 at 7:28 am

#1596003
RE: Looping without cursor not updating properly

Gosh :blush: thanks!
A gentle reminder - always always test 🙂 If this solution works, it can be condensed and tweaked for performance.
^{“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

March 12, 2013 at 7:13 am

#1595984
RE: Looping without cursor not updating properly

This solution I belive matches your description:
IF OBJECT_ID('tempdb..#FirstChoice') IS NOT NULL DROP TABLE #FirstChoice;
IF OBJECT_ID('tempdb..#SecondChoice') IS NOT NULL DROP TABLE #SecondChoice;
IF OBJECT_ID('tempdb..#RemainingPitches') IS NOT NULL DROP TABLE #RemainingPitches;
IF OBJECT_ID('tempdb..#RemainingPunters') 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

March 12, 2013 at 6:53 am

#1595965
RE: Looping without cursor not updating properly

This proposed solution is slightly different.
Start by allocating punters picked at random, to pitches, based on punter's first choice. Using the sample set, most towns have all pitches allocated,...
^{“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

March 12, 2013 at 6:34 am

#1595953
RE: Query runs too slow!

This might give you a little boost too:
SELECT a.id, a.GUID, a.SubmissionID, b.id, b.GUID, b.SubmissionID, 1
FROM dbo.All_keys_ AS a
INNER JOIN dbo.Load1_keys_ AS b
on a.mkMatchKeyType1 = b.mkMatchKeyType1
WHERE NOT EXISTS (SELECT 1 from...
^{“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

March 12, 2013 at 5:34 am

#1595918
RE: Looping without cursor not updating properly

Here's a proposed solution. I've broken it up into stages using #temp tables which can be read for the purpose of verifying the results.
IF OBJECT_ID('tempdb..#FirstChoice') IS NOT NULL DROP TABLE...
^{“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

March 12, 2013 at 3:48 am

#1595870
RE: Looping without cursor not updating properly

Here's a working dataload:
-- set identity insert on for lottery
SET IDENTITY_INSERT survey.FirewoodLottery ON
-- entering sample data into lottery table
INSERT INTO [survey].[FirewoodLottery]
(FirewoodLottery_id --DateValue, Value, YearValue, Monthvalue,
,[Firstname]
,[MiddleInitial]
,[Lastname]
,[Suffix]
,[address]
,[city]
,[state]
,[zip]
,[phone]
,
,[postdate]
,127.0.0.1
,[choice1]
,[choice2]
,[assigned]
,[status]
,[District]
,[sortindex]
,[choice]
,[Notes]
--,[possible_dup]
)
select 1,'Wombat',NULL,'Champine',NULL,'15...
^{“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

March 12, 2013 at 3:33 am

#1595864
RE: Counter inside SELECT

RZ52 (3/7/2013)
...What I need to know is how I can make a counter inside a SELECT with a manually created seed.
Thanks again.
That's exactly what Jason's code is designed to do....
^{“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

March 7, 2013 at 9:00 am

#1594506
RE: Performing strange conditional COUNT

Nice one geezer 😎
It makes for a surprisingly succinct query.
^{“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

March 7, 2013 at 6:04 am

#1594406
RE: Query to get rows if value contains result of another query

winmansoft (3/7/2013)
ChrisM@Work (3/7/2013)
winmansoft (3/7/2013)
ChrisM@Work (3/7/2013)
yes - do this instead:
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'seh,jyo,mhf'
SELECT *
INTO #Elements
FROM dbo.DelimitedSplit8K (@Parameter,',')
...
^{“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

March 7, 2013 at 5:58 am

#1594404
RE: Query to find items purchased from multiple vendors

SELECT d.Item
FROM (
SELECT
a.Item,
Vendor_Code
FROM Purch_Inv_Line a
INNER JOIN Purch_Inv_Header a
ON a.[Document No_] = b.Item
AND b.[Posting Date] BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY a.Item, Vendor_Code
) d
GROUP BY d.Item
HAVING COUNT(*)...
^{“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

March 7, 2013 at 5:57 am

#1594403
RE: How to Convert Semi colon Separated Values into Column

Gosh :blush: thanks! Hang around here, read the articles...you'll soon pick it up.
^{“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

March 7, 2013 at 5:40 am

#1594393

Viewing 15 posts - 5,056 through 5,070 (of 10,143 total)