ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,481 through 3,495 (of 10,143 total)

RE: Are the posted questions getting worse?

Sean Lange (3/28/2014)
dwain.c (3/28/2014)
Ville-Pekka Vahteala (3/27/2014)
Luis Cazares (3/27/2014)
How is it possible that someone writes a cursor instead of using SUM()?
I'm afraid that this database is full of these pieces of...
^{“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

March 28, 2014 at 7:53 am

#1701101
RE: Are the posted questions getting worse?

WayneS (3/27/2014)
Luis Cazares (3/27/2014)
How is it possible that someone writes a cursor instead of using SUM()?
I'm afraid that this database is full of these pieces of sh code and 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

March 28, 2014 at 3:17 am

#1701012
RE: Are the posted questions getting worse?

Luis Cazares (3/27/2014)
How is it possible that someone writes a cursor instead of using SUM()?
I'm afraid that this database is full of these pieces of sh code and it's slower...
^{“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

March 28, 2014 at 3:11 am

#1701011
RE: Simple Query taking 8 secs

Can you post the actual plan for the most recent version?
^{“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

March 27, 2014 at 6:39 am

#1700652
RE: Simple Query taking 8 secs

Have you tried resolving out the tables from the strings? Something like this:
SELECT * -- change to column name
INTO #OrgCommCode
FROM dbo.Fn_splitstrings(@p_OrgCommCode, ',')
SELECT * -- change to column name
INTO #CommCode
FROM dbo.Fn_splitstrings(@p_CommCode,...
^{“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

March 26, 2014 at 7:31 am

#1700277
RE: Simple Query taking 8 secs

You could replace four of those UNIONs with one:
SELECT *
FROM #tempac
UNION ALL
SELECT e.*
FROM ( -- e
SELECT
d.finyr,
d.minyr,
d.finmonno,
d.finmonname,
d.[activitytypeorder],
d.activitytype,
m.ha_code,
d.nhs_e_contract_id,
d.age_flag,
d.Number
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

March 25, 2014 at 8:17 am

#1699899
RE: UPDATE not updating data in table

joepacelli (3/19/2014)
The UPDATE looks like
You wouldn't believe how often folks have been caught out by omitting a seemingly inocuous bit of code from a query, only to find it's 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

March 25, 2014 at 7:52 am

#1699880
RE: Multiple updates in 1 table from another table

Look up UPDATE...FROM in Books Online. There are examples which very closely match your requirement.
^{“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

March 25, 2014 at 7:47 am

#1699878
RE: Need a query to select aggregate data by buckets

SQL Guy 1 (3/25/2014)
That was rather simplified example. In reality I have a table with 150 mln records, and it is partitioned by integer column (it is some 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

March 25, 2014 at 7:44 am

#1699873
RE: Reg bedrents calculation in HIS stored procedure

This is the finest example I’ve seen in a long time of how not to code against SQL Server.
Picking out single rows from one table at a time 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

March 25, 2014 at 4:07 am

#1699770
RE: Long Running SQL Query .. Looking for recommendations for improvement.

Lynn Pettis (3/24/2014)
Also, it could just be me, but after downloading the estimated execution plan I was unable to open it.
Me neither, think it was truncated.
However: very high row...
^{“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

March 24, 2014 at 10:54 am

#1699615
RE: Long Running SQL Query .. Looking for recommendations for improvement.

Your GROUP BY eliminates the need for DISTINCT
SELECT --DISTINCT
s.site_number AS Site,
s.city AS City,
s.state AS State,
SUM(CASE WHEN hp.histConfID LIKE '%Generator$20Start$2fStop'
THEN CONVERT(DECIMAL(10, 2), h.numeric_value / 100 *...
^{“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

March 24, 2014 at 9:12 am

#1699573
RE: Select Top x% From Group Based on Total

tfendt (3/24/2014)
ChrisM@Work (3/24/2014)
tfendt (3/24/2014)
ChrisM@Work (3/24/2014)
Have a look at the NTILE function in Books Online (SQL Server help).
NTILE(n) splits the data into n equal-sized partitions numbered 1 to n 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

March 24, 2014 at 9:00 am

#1699568
RE: Select Top x% From Group Based on Total

tfendt (3/24/2014)
ChrisM@Work (3/24/2014)
Have a look at the NTILE function in Books Online (SQL Server help).
NTILE(n) splits the data into n equal-sized partitions numbered 1 to n in a user-defined...
^{“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

March 24, 2014 at 8:46 am

#1699558
RE: Select Top x% From Group Based on Total

Have a look at the NTILE function in Books Online (SQL Server help).
NTILE(n) splits the data into n equal-sized partitions numbered 1 to n in a user-defined order 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

March 24, 2014 at 8:23 am

#1699543

Viewing 15 posts - 3,481 through 3,495 (of 10,143 total)