ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,031 through 6,045 (of 10,143 total)

RE: how long it takes to retrieve top 10000 records

Gazareth (7/25/2012)
Phil Parkin (7/25/2012)
ChrisM@Work (7/25/2012)
This looks very much like a homework question, in which case Anthony's reply is a good place to start.
Don't forget that TOP is meaningless without...
^{“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

July 25, 2012 at 6:51 am

#1517310
RE: how long it takes to retrieve top 10000 records

Phil Parkin (7/25/2012)
ChrisM@Work (7/25/2012)
This looks very much like a homework question, in which case Anthony's reply is a good place to start.
Don't forget that TOP is meaningless without 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

July 25, 2012 at 6:17 am

#1517297
RE: Query help to update with next records

Jeff Moden (7/22/2012)
ChrisM@home (7/22/2012)
Without an ORDER BY, TOP is meaningless:
SELECT
t1.col1,
col2 = ISNULL(t1.col2, x.col2)
FROM @T t1
OUTER APPLY (
SELECT TOP 1
t2.col2
FROM @T t2
WHERE t2.col1 > t1.col1
AND t2.col2...
^{“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

July 25, 2012 at 4:12 am

#1517261
RE: Get running total in the following query

beeramgopi (7/25/2012)
DECLARE @CumCredit TABLE
(RowId INT, CustomerId INT, TransId INT, Pts INT)
INSERT INTO @CumCredit (Rowid, CustomerID, TransId, pts)
select 1,123,121,10
union
select 2,123,131,20
union
select 3,123,141,15
select * from @CumCredit
select a.*, (select sum(Pts) from @CumCredit where...
^{“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

July 25, 2012 at 4:02 am

#1517258
RE: how long it takes to retrieve top 10000 records

krishna.netblogs (7/24/2012)
Hi All,
I have a table Tbl1 with the below properties
having 200 lakhs records.
30 Columns
6 columns are configured with Non-Unique,Non-Clustered Index
1 Column with Clustered Index
I have to execute the below...
^{“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

July 25, 2012 at 2:22 am

#1517212
RE: How do I add the datepart (year, incidentdate) within the sql dynamic string

Ajdba (7/24/2012)
Sorry, I meant to say I need to know how can prevent from sql injection
Best way is not to use dynamic SQL. Are you sure you need to?
SELECT
IncidentIdNbr,...
^{“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

July 24, 2012 at 8:23 am

#1516836
RE: When I use multiple cases only the first one gets executed

cascaded (CROSS) APPLY is ideal for this:
DROP TABLE #Sample
CREATE TABLE #Sample (username VARCHAR(20), gender CHAR(1), agegroup VARCHAR(3), profiles VARCHAR(20), profile_new VARCHAR(20))
INSERT INTO #Sample
SELECT username = 'Edward', gender = 'm', agegroup...
^{“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

July 24, 2012 at 7:13 am

#1516769
RE: Get running total in the following query

XMLSQLNinja (7/23/2012)
ChrisM@home (7/20/2012)
XMLSQLNinja (7/18/2012)
...
There are other ways to do this but using recursion is generally the fastest.
Recursion is the third fastest; CLR > "Quirky Update" > rCTE > cursor >...
^{“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

July 24, 2012 at 5:18 am

#1516723
RE: Today's Random Word!

SQLRNNR (7/23/2012)
C6H12O6
6-(hydroxymethyl)oxane-2,3,4,5-tetrol
^{“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

July 23, 2012 at 9:51 am

#1516319
RE: Query help to update with next records

You guys have been having way too much fun while I've been away.
The plan for this query (once the missing ORDER BY is in place)
lokeshvij (7/22/2012)
Here it is (there can...
^{“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

July 23, 2012 at 2:35 am

#1516104
RE: update statment help

CELKO (7/12/2012)
... WHERE S.sales_order_id = D.sales_order_id;
Better duck now, Joe.
^{“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

July 12, 2012 at 9:48 am

#1512146
RE: Loop within a Loop

Not all experienced SQL Server developers appreciate the importance of code performance - as a relative newcomer it may have passed you by, even after lurking around here for a...
^{“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

July 12, 2012 at 9:44 am

#1512144
RE: Date formating problem

ash.rvp1 (7/12/2012)
Thank you for your feedback.
How does DR_Premier_PO_O fit into all this? There are various references to it in your script but none of them make much sense. If it'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

July 12, 2012 at 8:49 am

#1512120
RE: Loop within a Loop

SGT_squeequal (7/12/2012)
i used the loop before any of the set based ooptions appeard plenty to ponder for the future :);-)
If you chose a looping method over 5 faster, simpler set-based...
^{“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

July 12, 2012 at 8:32 am

#1512105
RE: update statment help

Luis Cazares (7/12/2012)
In my experience, Smash would need a slightly different approach if he needs the total due for each contact, because Chris' query will only return the total 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

July 12, 2012 at 7:30 am

#1512048

Viewing 15 posts - 6,031 through 6,045 (of 10,143 total)