ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,641 through 2,655 (of 10,143 total)

RE: IMprove performance

ramrajan (2/11/2015)
The below query taking very long time to up few records also,
Can anyone please rewrite this query for good performance with INNER joins.
SELECT
...
^{“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

February 11, 2015 at 9:13 am

#1776983
RE: Analysis Services data set used in Reporting Services. Incorrect number sort order 1,154,165,2,3,345 etc

Debbie Edwards (2/11/2015)
Hi,
I have come across a niggle that I cant seem to resolve.
I have a number field and my report is ordered against it. However, When I looked I...
^{“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

February 11, 2015 at 5:30 am

#1776886
RE: Multiple execution plan for 1 query.

New Born DBA (2/6/2015)
I have this SQL statement with 2 different execution plan. I am also attaching both execution plan. No one has complained about the performance yet. Just curios...
^{“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

February 9, 2015 at 3:44 am

#1776325
RE: Are the posted questions getting worse?

Sioban Krzywicki (2/4/2015)
Grant Fritchey (2/4/2015)
Sioban Krzywicki (2/4/2015)
Some of it is probably the sport you're focusing on. Two of the sports were football and field events (shot put, discus, javelin). It...
^{“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

February 5, 2015 at 7:09 am

#1775679
RE: Are the posted questions getting worse?

Grant Fritchey (2/5/2015)
ChrisM@Work (2/5/2015)
Grant Fritchey (2/4/2015)
SQLRNNR (2/4/2015)
Grant Fritchey (2/4/2015)
Sioban Krzywicki (2/4/2015)
Some of it is probably the sport you're focusing on. Two of the sports were football and field events (shot...
^{“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

February 5, 2015 at 5:53 am

#1775663
RE: Are the posted questions getting worse?

Grant Fritchey (2/4/2015)
SQLRNNR (2/4/2015)
Grant Fritchey (2/4/2015)
Sioban Krzywicki (2/4/2015)
Some of it is probably the sport you're focusing on. Two of the sports were football and field events (shot put, discus, javelin)....
^{“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

February 5, 2015 at 3:52 am

#1775626
RE: Having trouble converting dates

dwain.c (2/4/2015)
Quite interesting.
This illustrates the problem.
CREATE VIEW dbo.Test
WITH SCHEMABINDING
AS
SELECT
RecordLastChangedDateKey=20150204,
x=CAST('20150204' AS DATE) ;
GO
CREATE UNIQUE CLUSTERED INDEX IDX_t1 ON dbo.Test (RecordLastChangedDateKey, x);
GO
SELECT *
FROM dbo.Test;
GO
DROP VIEW dbo.Test;
Perhaps this solves it?
CREATE VIEW dbo.Test
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

February 5, 2015 at 1:50 am

#1775597
RE: Please Help Improve My While Loop

Byzza (2/3/2015)
I'm sure this is by far not the best way to do things, buts its been the only way I've worked out so far.
I have 2 tables - 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

February 4, 2015 at 6:17 am

#1775279
RE: Having trouble converting dates

theboyholty (2/4/2015)
ChrisM@Work (2/4/2015)
SELECT RecordLastChangedDateKey, CAST(CAST(RecordLastChangedDateKey AS CHAR(8))AS DATE)
FROM (SELECT RecordLastChangedDateKey = CAST(20150204 AS INT)) im
Thanks but its still giving me the message "The view contains a convert that is imprecise...
^{“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

February 4, 2015 at 6:04 am

#1775274
RE: Having trouble converting dates

theboyholty (2/4/2015)
I'm trying to create an indexed view. In my table there are 5 dates, all integers in their native form e.g. 20150204. I want to out put this 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

February 4, 2015 at 5:55 am

#1775268
RE: split full name

SELECT
d.Fullname,
Lastname = LEFT(d.Fullname, pos1-2), -- -2 removes the comma, could use REPLACE
FirstName = SUBSTRING(d.Fullname, pos1+1, (pos2-pos1)-1),
MidddleName = SUBSTRING(d.Fullname,pos2+1,8000), -- could use REPLACE to remove period
x1.*, x2.* -- included...
^{“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

February 4, 2015 at 5:37 am

#1775259
RE: Help with a query

-- Before attempting to fix the results set of your query,
-- you might want to do some remedial work on the components of it.
-- Here's a little sample set...
^{“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

February 4, 2015 at 3:51 am

#1775238
RE: Convert rows to columns using dynamic PIVOT table

It's easy once you've worked through your first example. Here's another article written by Jeff Moden:
http://www.sqlservercentral.com/articles/Crosstab/65048/%5B/url%5D
A couple of minor changes to your query which may make the job easier:
SELECT --...
^{“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

February 3, 2015 at 8:15 am

#1775041
RE: WHERE 1 = 1,who uses this besides me

Resender (2/2/2015)
ok this is something of a best practice of mine in my queries
I always do
WHERE 1 = 1
I can't remember where I picked this habit up and I'm 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

February 2, 2015 at 9:49 am

#1774817
RE: Need assistance with Update Query

Your query isn't properly formed, the subquery (timesheets join employeemaster) isn't correlated with the UPDATE target leavehoursearned.
Start by writing a SELECT query which correctly joins all three tables 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

February 2, 2015 at 6:54 am

#1774767

Viewing 15 posts - 2,641 through 2,655 (of 10,143 total)