ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,576 through 1,590 (of 10,143 total)

RE: Need help on query performance

jc85 (5/25/2016)
ChrisM@Work (5/25/2016)
jc85 (5/25/2016)
ChrisM@Work (5/25/2016)
Only one clustered index per table, but the index can contain more than one column.
"Different result" is too vague, please explain.
Thanks, added.
Incorrect result is returned, 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

May 25, 2016 at 4:43 am

#1881277
RE: Need help on query performance

Sergiy (5/25/2016)
jc85 (5/25/2016)
Thanks ChrisM@Work.
The query took quite some time to complete.
Additional info provided in case you need it.
ann_events_Tech_Details
[start_time] - clustered index
[id] - Unique, non-clustered index (PK)
ann_ReportItem
[source] - non-clustered index
[call_flow_name]...
^{“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 25, 2016 at 4:40 am

#1881275
RE: Need help on query performance

jc85 (5/25/2016)
ChrisM@Work (5/25/2016)
Only one clustered index per table, but the index can contain more than one column.
"Different result" is too vague, please explain.
Thanks, added.
Incorrect result is returned, it is not...
^{“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 25, 2016 at 4:13 am

#1881271
RE: Need help on query performance

Only one clustered index per table, but the index can contain more than one column.
"Different result" is too vague, please explain.
^{“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 25, 2016 at 3:55 am

#1881269
RE: Need help on query performance

-- Try this too.
-- Add [source] to the clustered index, initially AFTER [start_time]
SELECT
[date] = CAST(b.start_time AS DATE),
[report_item] = (b.[source] + '-' + 'a.report_item'),
[EN] = SUM(CASE WHEN b.lang...
^{“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 25, 2016 at 3:48 am

#1881266
RE: Need help on query performance

Can you display the 21 rows from table ann_ReportItem?
Ideally you would do this by posting a script to create a table then a series of inserts to populate it 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

May 25, 2016 at 3:32 am

#1881263
RE: Need help on query performance

There are a few changes I'd make to your query. Mostly it's about aligning the date filters properly. Try this:
SELECT
[date] = CAST(b.start_time AS DATE),
[report_item] = ( a .source...
^{“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 25, 2016 at 2:39 am

#1881261
RE: Need help on query performance

jc85 (5/25/2016)
Sergiy (5/24/2016)
This is exactly the problem.
Make both PK's non-clustered and create a new clustered index on [start_time].
That's what you've got:
WHERE (dateadd(dd,0, datediff(dd,0,b.start_time))) >= @StartDate
AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate
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

May 25, 2016 at 1:51 am

#1881257
RE: Split string using multiple delimiters

Rick Harker (5/18/2016)
ChrisM@Work (5/18/2016)
Iwas Bornready (5/18/2016)
Thanks for the script.
Reported as spam
Spam?
Yes, according to several definitions of spam found on Google. Here's one good contender: "•multiple copies of an identical or...
^{“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 24, 2016 at 8:06 am

#1881103
RE: Need help on query performance

Don't you have any ordinary (nonclustered) indexes on these tables?
^{“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 24, 2016 at 4:34 am

#1880990
RE: Need help on query performance

Henning Rathjen (5/24/2016)
Hi jc85,
first I would try to use
CONVERT(char(10),b.start_time,121)
instead of dateadd(dd, 0, datediff(dd, 0, b.start_time)).
Furthermore I advise you not to use functions in WHERE caluses. Modify your parameters so...
^{“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 24, 2016 at 3:19 am

#1880981
RE: Need help on query performance

jc85 (5/24/2016)
The new query is slower.
AS Gail said, and the execution plan of the new query please. "Actual" plans as .sqlplan file attachments.
^{“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 24, 2016 at 3:10 am

#1880978
RE: Need help on query performance

It's good news. Try this:
SELECT
CAST(b.start_time AS DATE) as date,
(a.source + '-' + a.report_item) as report_item,
SUM(CASE WHEN (b.lang = 'EN' AND b.sel_test = 1 AND a.report_item = 'Test')
...
^{“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 24, 2016 at 2:33 am

#1880966
RE: Need help on query performance

Can you please confirm which version of SQL Server you are using? You've posted in the SQL Server 7,2000 forum section.
^{“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 24, 2016 at 2:05 am

#1880958
RE: Inline query to perform specific operation

The STUFF function would be good for this too:
SELECT *, CompanyAddress = STUFF(CompanyAddress,pos+1,0,' ')
FROM #SearchRow
CROSS APPLY (SELECT pos = PATINDEX('%[0-9][a-z]%', CompanyAddress)) x
WHERE x.pos > 0
^{“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 19, 2016 at 5:13 am

#1880135

Viewing 15 posts - 1,576 through 1,590 (of 10,143 total)