ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,941 through 5,955 (of 10,143 total)

RE: Running Total

Hi Jay
We use VS2010Ultimate/AnkhSVN/SSDT for development here so I'm reasonably familiar with it - yet I'm not sure what you are trying to do or why. If you want 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

August 2, 2012 at 6:42 am

#1520770
RE: Implementing Surrogate Key

shahsn11 (8/2/2012)
How to implement surrogate key ?
CREATE TABLE MyTable (
MyTableID INT IDENTITY(1,1) PRIMARY KEY,
.
.
.
^{“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

August 2, 2012 at 6:35 am

#1520764
RE: T-SQL Advice on Update Statement Needed

Sometimes, just occasionally, when the target table isn't the first table listed in the FROM list, SQL Server gets its knickers in a twist. Try this:
UPDATE ceh
Set AEarned 1 =...
^{“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

August 2, 2012 at 6:32 am

#1520762
RE: Is there an joiner much like the 8kSplitter?

anthony.green (8/2/2012)
Thanks Lynn for Chris's modified function.
Both that function and the one in the DB backup I provided both execute in the same amount of time.
Anthony - "Chris's modified function"...
^{“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

August 2, 2012 at 3:48 am

#1520716
RE: trying to add alias for value generated by Where clause

XMLSQLNinja (8/1/2012)<<snip>>
Each of these guys will blast through several hundred thousand rows in seconds. QU is still new to me and I'm playing around with it - it is usually...
^{“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

August 2, 2012 at 2:03 am

#1520678
RE: Is there an joiner much like the 8kSplitter?

Lynn Pettis (8/1/2012)
ChrisM@Work (8/1/2012)
Lynn Pettis (8/1/2012)
I made a slight change to the function Chris wrote...
😀 😀
Take this as a "seal" (imagine a picture of a seal here) of approval? ...
^{“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

August 1, 2012 at 10:03 am

#1520380
RE: Is there an joiner much like the 8kSplitter?

Lynn Pettis (8/1/2012)
I made a slight change to the function Chris wrote...
😀 😀
^{“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

August 1, 2012 at 9:47 am

#1520367
RE: Is there an joiner much like the 8kSplitter?

anthony.green (8/1/2012)
Chris,
Just a simple execute the two views, the original came back in 12 seconds, yours was over 2 minutes.
I get around 15-60ms for either using the backup...
^{“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

August 1, 2012 at 8:49 am

#1520326
RE: Is there an joiner much like the 8kSplitter?

How did you time the runs, Anthony?
^{“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

August 1, 2012 at 8:03 am

#1520286
RE: Is there an joiner much like the 8kSplitter?

This function runs about 25% faster:
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER FUNCTION [dbo].[GetCIDs]
(@SID INT)
RETURNS @Result TABLE (LongCID VARCHAR(8000))
AS
BEGIN
DECLARE @Accumulator TABLE (seq INT, CID INT);
INSERT INTO...
^{“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

August 1, 2012 at 7:25 am

#1520245
RE: Running Total

jerome.morris (8/1/2012)
Chris M do I need to give more information ?
I can see the finish line almost 🙂
I'm sorry Jerome, looks like you need a c# developer to help 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

August 1, 2012 at 4:15 am

#1520166
RE: Count Per Hour By User By Day

texpic (8/1/2012)
That gets me the first report. Is there a way to dynamically create this report from those results? There would be more time periods and more users.
date...
^{“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

August 1, 2012 at 3:56 am

#1520159
RE: Count Per Hour By User By Day

SELECT Dept, UserId, x.[date], x.[Hour], COUNT(*)
FROM #CountsPerHour
CROSS APPLY (
SELECT
[date] = DATEADD(dd,0,DATEDIFF(dd,0,StartTime)),
[Hour] = DATEPART(hh,StartTime)
) x
WHERE Dept <> 2
AND StartTime >= @DateStart -- NOTE >=
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

August 1, 2012 at 3:30 am

#1520153
RE: trying to add alias for value generated by Where clause

Koen Verbeeck (8/1/2012)
ChrisM@Work (8/1/2012)
polkadot (7/31/2012)
... I need an alias for the column the WHERE STATEMENT generates...
The WHERE clause doesn't generate an output column. Ever.
There are four well-known ways 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

August 1, 2012 at 2:58 am

#1520145
RE: trying to add alias for value generated by Where clause

polkadot (7/31/2012)
... I need an alias for the column the WHERE STATEMENT generates...
The WHERE clause doesn't generate an output column. Ever.
There are four well-known ways to calculate running totals...
^{“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

August 1, 2012 at 2:17 am

#1520130

Viewing 15 posts - 5,941 through 5,955 (of 10,143 total)