ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,536 through 8,550 (of 10,143 total)

RE: Query Performance

Sure, briefly (it's nearly 10pm here).
Everything above this line [Troubleshoot from here down04-07-09] makes sense, nothing makes sense below the line. I'm guessing it should read something like
INSERT INTO...
^{“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

April 7, 2009 at 2:45 pm

#973826
RE: Query Performance

My mistake Donato, the top line was eaten:
DECLARE @Startdate DATETIME, @Enddate DATETIME
SELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00', 101),
@Enddate = convert(datetime, '02-APR-09' + ' 20:00:00', 101)
--
SELECT itl.user_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

April 7, 2009 at 2:16 pm

#973807
RE: Query Performance

Can you run this please, with and without the derived table which is currently commented out, and post back the run times:SELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00',...
^{“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

April 7, 2009 at 1:58 pm

#973783
RE: Update Statement with an outer apply

Stephen, what do you get when you run this:
SELECT *
FROM co_customer_x_address
where cxa_cst_key = '18599564-1e26-4f0d-b95b-000d09d32406'
^{“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

April 7, 2009 at 1:49 pm

#973771
RE: Update Statement with an outer apply

Stephen crocker (4/7/2009)
Nope, that only updates the single record that already has the cxa_on_hold_flag
What value does it have for cxa_adr_key?
^{“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

April 7, 2009 at 12:55 pm

#973728
RE: Query Performance

Change this...
---
d.loginTime,
---
d.loginTimeSeconds,
---
d.LogoutTime
---
to this...
---
MAX(d.loginTime) AS loginTime,
---
MAX(d.loginTimeSeconds) AS loginTimeSeconds,
---
MAX(d.LogoutTime) AS LogoutTime
---
^{“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

April 7, 2009 at 11:16 am

#973646
RE: Query Performance

Try this, Donato:
DECLARE @Startdate DATETIME, @Enddate DATETIME
SELECT @Startdate = convert(datetime, '02-APR-09' + ' 03:00:00', 101),
@Enddate = convert(datetime, '02-APR-09' + ' 20:00:00', 101)
--
SELECT itl.user_id, au.notes, au.shift,
--
sum(update_qty * m.avg_cubes)...
^{“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

April 7, 2009 at 11:03 am

#973623
RE: Query Performance

Haha that's okay mate, none of the speedy-up stuff has been done yet! 😀
^{“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

April 7, 2009 at 10:21 am

#973588
RE: Multi Column Sort

Ramesh (4/7/2009)
Guys, It looks like an interesting problem for me and I would like to try my hands on it. But the problem is that I still not completely...
^{“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

April 7, 2009 at 10:19 am

#973586
RE: How to insert into table based on previous results

No worries Flo. I wondered where you'd got to - you were right on the ball, then disappeared:w00t:
^{“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

April 7, 2009 at 9:44 am

#973537
RE: How to insert into table based on previous results

jbuttery (4/7/2009)
Thanks, Chris. That syntax looks good to me.
The link on the bottom of your posts is incorrect. It should be http://www.sqlservercentral.com/articles/Best+Practices/61537/
I finally read it and apologize for my post,...
^{“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

April 7, 2009 at 9:42 am

#973534
RE: Query Performance

As Grant and GSquared have pointed out, there's abundant scope for improvement in this query, to make it fast and to make it pretty. Here's the first query of 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

April 7, 2009 at 9:37 am

#973525
RE: How to insert into table based on previous results

That's it, but I've got a sneaky suspicion that the CAST has to be to VARCHAR, otherwise the zero prefix won't work.
SELECT ReviewDate, Hub, BankNumber, BranchNumber, AccountNumber =
RIGHT('00000000000000000'...
^{“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

April 7, 2009 at 8:45 am

#973439
RE: How to insert into table based on previous results

RIGHT('00000000000000000' + CAST(CAST('000000005121321212' AS BIGINT) + 1 AS VARCHAR(18)), 18)
^{“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

April 7, 2009 at 8:26 am

#973411
RE: How to insert into table based on previous results

What datatype is account number? That's where your problem is - it's either char or varchar and SQL Server tries to convert it to an int to add the 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

April 7, 2009 at 8:21 am

#973402

Viewing 15 posts - 8,536 through 8,550 (of 10,143 total)