ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,156 through 4,170 (of 10,143 total)

RE: Is overpunch amenable to cross apply?

Stefan_G (8/13/2013)
If you want maximum performance you could use something like this:
create function fixop2(@a varchar(20)) returns table
as
return select
case
when right(@a,1) between 'A' and 'I' then +cast(left(@a, len(@a)-1) as int)*10 + (ascii(right(@a,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

August 13, 2013 at 6:29 am

#1640895
RE: Update takes long time

Also, actual plan for this query:
SELECT
i.Item_SK,
[Class]= i.class,
Department= i.dept,
Division= i.div,
Subclass= i.subclass,
[Rows]= COUNT(*)
FROM SalesDate s
INNER JOIN Item i
ON i.Item_SK = s.Item_SK
WHERE s.dateid >= '20110201'
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

August 13, 2013 at 5:57 am

#1640881
RE: Update takes long time

An execution plan or two would help loads. I'd suggest an estimated plan for the query shown, and an actual plan for a shorter time period so you're not waiting...
^{“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 13, 2013 at 5:48 am

#1640877
RE: Update the column with names

SELECT
t.blp_proposalno,
t.blp_documentattach,
t.blp_fund,
t.blp_branch,
x.[bld_Document attach]
FROM #t t
OUTER APPLY (
SELECT [bld_Document attach] =
STUFF((
SELECT ',' + bld_document
FROM #m m
WHERE m.bld_fund = t.blp_fund
AND t.blp_documentattach LIKE '%'+CAST(m.bld_documentid AS VARCHAR(3))+'%'
FOR XML...
^{“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 13, 2013 at 4:52 am

#1640855
RE: Case statements advice - query case field?

Really? It's just a query. What error message does RS fart out?
^{“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 13, 2013 at 4:21 am

#1640848
RE: How to update Flag based on Maximum Amount

Stefan's code and my code now return a result set which exactly matches your posted requirements. If your requirements have extended or changed, then please use a sample data 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

August 13, 2013 at 4:09 am

#1640846
RE: Case statements advice - query case field?

SELECT
x.Name,
base.ls_id,
base.date_lease_start,
base.date_lease_end,
base.lease_term,
base.amount_current_rent
FROM base_table AS base
LEFT OUTER JOIN thing1_table AS thing1 ON base.as_id = thing1.as_id
LEFT OUTER JOIN thing2_table AS thing2 ON base.bl_id = thing2.bl_id
LEFT OUTER...
^{“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 13, 2013 at 4:07 am

#1640845
RE: to find avg between intervel

Run this query and study the results. If you are still unsure then ask:
SELECT
DateAndTime,
AbsoluteMinutes, -- minutes since 19000101.
AbsoluteMinutes/60.00, -- INT divided by DECIMAL: decimal fraction retained.
AbsoluteMinutes/60, -- INT divided...
^{“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 13, 2013 at 2:11 am

#1640819
RE: Help required for performance tuning a view

Picking the right columns to use for a covering index is mostly straightforward. List all of the columns used in the (sub)query. If the column is used as a filter...
^{“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 12, 2013 at 9:36 am

#1640637
RE: Help required for performance tuning a view

Try this index, Mike:
CREATE INDEX ix_Test ON Jobtran (oper_num, trans_num DESC, job, suffix) INCLUDE (emp_num)
Edit: CREATE INDEX ix_New ON Jobtran (job, suffix, oper_num, trans_num DESC) INCLUDE (emp_num)
^{“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 12, 2013 at 8:20 am

#1640615
RE: Help required for performance tuning a view

It is referenced:
CASE -- All std time captured on Move*, as this is where quantity is recorded
-- where Move trans types don't exist, qty is recorded on other trans types...
^{“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 12, 2013 at 7:40 am

#1640587
RE: Help required for performance tuning a view

This, I think, would make a reasonable test query:
SELECT
jt.job,jt.suffix, jt.oper_num, jt.trans_num, jt.emp_num,
x.oper_num, x.trans_num, x.emp_num,
jt2.trans_num
FROM jobtran jt (NOLOCK)
OUTER APPLY (
SELECT TOP 1
jtx.emp_num, jtx.oper_num, jtx.trans_num
FROM jobtran jtx(NOLOCK)
WHERE jtx.job =...
^{“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 12, 2013 at 7:17 am

#1640571
RE: Help required for performance tuning a view

I'd focus on the one table for now, which gives you this query:
SELECT
jt.trans_num,
emp_num = CASE
WHEN jt.emp_num IS NOT NULL
THEN jt.emp_num
ELSE
(SELECT TOP 1
jtx.emp_num
FROM jobtran jtx(NOLOCK)
WHERE jtx.job = jt.job
ANDjtx.suffix =...
^{“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 12, 2013 at 7:08 am

#1640562
RE: Help required for performance tuning a view

mike.dinnis (8/12/2013)
The main query (from which this was the identified 'slow' bit) uses; emp_num, trans_type, a_hrs, qty_moved, wc and CreateDate in the returned fields; job, suffix, wc, oper_num and trans_num...
^{“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 12, 2013 at 6:46 am

#1640551
RE: Help required for performance tuning a view

What other columns do you need from this table?
^{“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 12, 2013 at 6:28 am

#1640538

Viewing 15 posts - 4,156 through 4,170 (of 10,143 total)