ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,376 through 3,390 (of 10,143 total)

RE: Slow self join

Bouke Bruinsma (5/6/2014)
ChrisM@home (5/6/2014)
Without sample data to test against...
Try this.
;WITH Updater AS (
SELECT
FTEHrsAVG,
NewFTEHrsAVG = AVG(CASE WHEN FlexVast = 'VAST' THEN FTEHrs ELSE NULL END)
...
^{“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

May 7, 2014 at 2:21 am

#1711375
RE: Why is this fast query slow in Visual Studio

Begin by eliminating the obvious differences - your parameters are different datatypes between the two environments. The datatype of your parameters should match the datatype of the table columns wherever...
^{“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

May 7, 2014 at 2:16 am

#1711374
RE: Hanging WHILE loop

WHILE affects only the statement immediately afterwards. If this statement is a batch defined by BEGIN and END, then the statements in the batch will be processed until the WHILE...
^{“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

May 7, 2014 at 2:06 am

#1711369
RE: forming a dynamic query

Jeff Moden (5/6/2014)
Sean Lange (5/1/2014)
The first and the biggest issue is this is wide open to sql injection.
In most cases, I'd strongly agree with you but... with two DATE, one...
^{“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

May 7, 2014 at 1:57 am

#1711367
RE: TSQL dilemma

Variation on David's;
DROP TABLE #Sample;
CREATE TABLE #Sample (a INT, b INT, c INT);
INSERT INTO #Sample (a, b, c)
SELECT * FROM (VALUES (12345, 10000, 1),
(12345, 10000, 2),
(12345, 10000, 3),
(12345, 10001, 1),
(12346,...
^{“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

May 7, 2014 at 1:45 am

#1711365
RE: Help Needed in Complex Logic

Sean Lange (5/6/2014)
ChrisM@Work (5/6/2014)
PIVOT / UNPIVOT haven't been told about the output column limitation of SELECT. Here's how to get 10,001 output columns from a SELECT:
DROP table #Sample
Create table #Sample...
^{“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

May 6, 2014 at 9:32 am

#1711142
RE: Help Needed in Complex Logic

PIVOT / UNPIVOT haven't been told about the output column limitation of SELECT. Here's how to get 10,001 output columns from a SELECT:
DROP table #Sample
Create table #Sample (Empid int primary...
^{“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

May 6, 2014 at 9:08 am

#1711125
RE: Help Needed in Complex Logic

ChrisM@Work (5/6/2014)
KGJ-Dev (5/6/2014)
thank you and am aware of this limitation. Is it possible to give me the dynamic query? i am planing to retrieve first 10000 rows for testing.
10,000...
^{“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

May 6, 2014 at 8:13 am

#1711083
RE: Help Needed in Complex Logic

KGJ-Dev (5/6/2014)
thank you and am aware of this limitation. Is it possible to give me the dynamic query? i am planing to retrieve first 10000 rows for testing.
10,000 rows...
^{“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

May 6, 2014 at 6:42 am

#1711028
RE: TSQL dilemma

It won't need a genius, only a decent explanation. Perhaps a model would help. Try tinkering with this for starters:
SELECT [identifying field], A, B, C,
MAX(B) OVER(PARTITION BY [identifying field]),
CASE WHEN...
^{“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

May 6, 2014 at 5:18 am

#1711004
RE: Help!!!

saifmahatab (5/5/2014)
Thanks but its not working..I tried I need to create a temp table where it stores the timing of a select query that I have takes time to run
Sean'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

May 6, 2014 at 2:57 am

#1710967
RE: Werid Behaviour of TSQL

psingla (5/6/2014)
I have complex view and try to fetch data from it.
In following code query 2 takes 1 sec to execute and query 1 keeps on running for hours.
I tried...
^{“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

May 6, 2014 at 2:31 am

#1710962
RE: SQL Server Read ismore

yuvipoy (5/5/2014)
yuvipoy (4/22/2014)
After doing the recommendation i now see that the query is using index seek with 84%.
So my question is index seek is better than index scan
previously my 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

May 6, 2014 at 2:27 am

#1710959
RE: forming a dynamic query

You have three options: dynamic sql, a catch-all query, or using IF blocks to test the parameters and run whichever query fits the parameters.
Catch-all queries are popular but come...
^{“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

May 6, 2014 at 2:04 am

#1710954
RE: Unable to sum column values

vigneshkumart50 (5/2/2014)
No I don't get sum for 2 nd column
Have you checked to see if the values actually exist?
SELECT *
FROM (VALUES (CAST(0 AS DECIMAL(10,4)),CAST(0 AS DECIMAL(10,4))), (10,100), (20,NULL), (NULL,300),(40,400)
)...
^{“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

May 2, 2014 at 9:48 am

#1710391

Viewing 15 posts - 3,376 through 3,390 (of 10,143 total)