ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,061 through 3,075 (of 10,143 total)

RE: SQL procedure Query Performance issue

That's a bit rubbish isn't it. Try this if you get the opportunity. It's only a select, no update:
DECLARE @tsql VARCHAR(8000)
SET @tsql = '
SELECT
E.UCELL_ID As LocalCellID
,RNC As RNC
,Cast( Cast(YEAR_ID...
^{“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, 2014 at 5:12 am

#1734394
RE: Row_number() over (partition by ....order by)

shashikantnist (7/31/2014)
In the Given Query a new column will create at run time with name EmailNumber and that number will update the variable where the SET statement have been used.
What...
^{“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, 2014 at 1:42 am

#1734357
RE: Split the time for every 30 mins and get the maximum dateand time record

stevenb 63624 (7/31/2014)
Another option is
SELECT *
FROM (
SELECT id
,rowid
,value
,readingdate
,ROW_NUMBER() OVER (
PARTITION BY id
,datepart(hh, readingdate)
,CASE
WHEN datepart(n, readingdate) <= 30
THEN 0
ELSE 1
END ORDER BY id ASC
,readingdate DESC
) PartID
FROM #tempreadings
) a
WHERE partid...
^{“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 31, 2014 at 7:23 am

#1734056
RE: Are the posted questions getting worse?

Koen Verbeeck (7/31/2014)
Lynn Pettis (7/31/2014)
Lynn Pettis (7/31/2014)
SQLRNNR (7/30/2014)
Lynn Pettis (7/30/2014)
Really?? Let's put a 500 million row table into an in-memory table.
It could work - depending on what the data 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

July 31, 2014 at 7:00 am

#1734042
RE: Split the time for every 30 mins and get the maximum dateand time record

WITH DistinctDays AS (
SELECT DISTINCT ID, ReadingdateDT = CAST(CAST(Readingdate AS DATE) AS DATETIME)
FROM #tempreadings
)
SELECT *
FROM DistinctDays d
CROSS APPLY (
SELECT Timeslot = DATEADD(minute,n*30,ReadingdateDT)
...
^{“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 31, 2014 at 6:55 am

#1734034
RE: SQL procedure Query Performance issue

mallikachowdhary 98955 (7/29/2014)
Sure Chris, I'll try this right away.
I would like to share a new learning I made today , earlier when I was trying to run the query 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

July 31, 2014 at 2:01 am

#1733947
RE: Are the posted questions getting worse?

patrickmcginnis59 10839 (7/30/2014)
Grant Fritchey (7/30/2014)
patrickmcginnis59 10839 (7/30/2014)
Its sort of a free market thing, if people decide that SO/SE isn't useful, it could go away. I'm hoping it sticks around because...
^{“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 30, 2014 at 9:58 am

#1733690
RE: need repetition of a table when joined with another table

Thanks for the feedback. Here are a couple of ways of expanding on the number of rows you have to generate:
WITH iTally (n) AS (
SELECT TOP(SELECT TOP(1) [Rows] = 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

July 30, 2014 at 6:06 am

#1733537
RE: need repetition of a table when joined with another table

phani.gudmines (7/29/2014)
Hi chris
seems that you are using 1,2,3,4,5 staticly.we dont know how may rows we get as described.i have just given u a sample data.
Thanks
How many rows might you...
^{“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 29, 2014 at 7:40 am

#1733203
RE: need repetition of a table when joined with another table

Sample data setup:
DROP TABLE #Table1
CREATE TABLE #Table1 (id int, value int)
INSERT INTO #Table1 (id, value) VALUES
(1, 11),
(2, 12),
(3, 13),
(4, 14)
DROP TABLE #table2
CREATE TABLE #table2 (id1 INT, value1 INT)
INSERT INTO #table2...
^{“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 29, 2014 at 7:22 am

#1733194
RE: need repetition of a table when joined with another table

SELECT id, value, id1, value1
FROM #Table1 t1
CROSS JOIN (SELECT TOP(SELECT TOP(1) [Rows] = COUNT(*) FROM #table2 GROUP BY id1 ORDER BY COUNT(*) DESC) n FROM (VALUES (1), (2), (3), (4),...
^{“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 29, 2014 at 7:22 am

#1733192
RE: Are the posted questions getting worse?

Greg Edwards-268690 (7/29/2014)
bantrim (7/29/2014)
Ed Wagner (7/29/2014)
Greg Edwards-268690 (7/29/2014)
Winter and Road Construction.
In Minnesota, there is a pothole season too.
Plenty of time here too, although I'd venture to say someone taking in...
^{“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 29, 2014 at 6:59 am

#1733181
RE: SQL procedure Query Performance issue

It's a procedure on the calling server. You have a few options here:
Save the query as a view on the remote server and call the view from the procedure.
Change the...
^{“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 29, 2014 at 2:37 am

#1733115
RE: SQL procedure Query Performance issue

mallikachowdhary 98955 (7/26/2014)
Thanks Lutz for the recommendation...Can you please send the steps or the final query to run.
As it is the production server I'm not allowed to make frequent changes....
^{“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 29, 2014 at 2:27 am

#1733112
RE: SQL procedure Query Performance issue

The easiest solution would be to save the query as a view on server ENIQSQLSERVER.
^{“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 29, 2014 at 2:17 am

#1733109

Viewing 15 posts - 3,061 through 3,075 (of 10,143 total)