ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,776 through 5,790 (of 10,143 total)

RE: How to avoid this T-SQL statement affecting my TempDB?

Thanks for the feedback. The original table had an unnecessary join which the new query eliminates, and if you can remove the DISTINCT now, then you remove a memory-hogging SORT...
^{“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 30, 2012 at 6:14 am

#1531816
RE: Select table names from queries

dwain.c (8/29/2012)
RBarryYoung (8/29/2012)
Unless the queries all strictly follow the same very simple format, this is going to fall somewhere between "Impossible" and "Incredibly Hard" to do in SQL.
If the queries...
^{“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 30, 2012 at 6:03 am

#1531812
RE: Getting long time (Need perform sp)

Pick a chunk of the query at the bottom of your stored procedure, run it in a query window with a couple of sensible parameters, and have a look at...
^{“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 30, 2012 at 3:38 am

#1531768
RE: format for phone number

Numeric datatypes are inappropriate because telephone numbers may have embedded spaces, non-numeric characters and leading zero's. Length isn't constant either, so most folks use VARCHAR.
^{“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 29, 2012 at 9:44 am

#1531485
RE: Create view with no outer joins

jdbrown239 (8/29/2012)
Chris..That worked great, thanks and thank all you guys for the input.
You're welcome jd, thanks for the feedback.
^{“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 29, 2012 at 9:07 am

#1531450
RE: How to avoid this T-SQL statement affecting my TempDB?

Try putting the FOR XML PATH stringinator into a CROSS APPLY, where it can crunch up the rows of PSFDOC separately from the rest of the query. You might 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

August 29, 2012 at 9:01 am

#1531446
RE: How to diagnose a query with a sub-query against another server.

Larry Kruse (8/29/2012)
I have a problem with performance of a query embedded in some 3rd party software.
The query (see below) performs a select of some fields on one server WHERE...
^{“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 29, 2012 at 6:54 am

#1531363
RE: Stored procedure in Where clause

The stored procedure returns the result set you want - why complicate it (and double the execution time)?
You may have simplified things a little to help - in which case,...
^{“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 29, 2012 at 6:49 am

#1531358
RE: UPDATE when the values are the same

Edit: dog removed from pile.
^{“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 29, 2012 at 6:42 am

#1531353
RE: How to update two tables data with JOIN in Single query

dwain.c (8/29/2012)
Yep. That's the same error I got.
Whatever it is saying, it's odd.
It refers to "fields" too - and took me ages to call them "columns" when I moved...
^{“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 29, 2012 at 6:22 am

#1531340
RE: increment values on day basis on column

dwain.c (8/29/2012)
ChrisM@Work (8/29/2012)
dwain.c (8/29/2012)
Had to make a slight correction to my prior post and add a question.
-- casting GETDATE as INT gives different results before and after midday ...
^{“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 29, 2012 at 6:18 am

#1531334
RE: increment values on day basis on column

dwain.c (8/29/2012)
Had to make a slight correction to my prior post and add a question.
-- casting GETDATE as INT gives different results before and after midday
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

August 29, 2012 at 6:04 am

#1531323
RE: I Have A Problem With Primavera 6

walidkilani (8/29/2012)
1. Query the USESSION table for the users 'session_id':
select session_id from USESSION where delete_session_id is NULL and user_id = (select user_id from users where...
^{“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 29, 2012 at 5:58 am

#1531321
RE: How to update two tables data with JOIN in Single query

dwain.c (8/29/2012)
Mark-101232 (8/29/2012)
If you're willing to jump through a load of hoops, you can update both tables with a view
CREATE TABLE ABC (Id INT NOT NULL , Name VARCHAR(10), Tab...
^{“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 29, 2012 at 5:56 am

#1531319
RE: How to update two tables data with JOIN in Single query

dwain.c (8/29/2012)
Yeah, so now what I haven't tested it with is:
Since you can use both OUTPUT and OUTPUT INTO within the same INSERT, UPDATE, DELETE or MERGE, can you also...
^{“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 29, 2012 at 5:05 am

#1531305

Viewing 15 posts - 5,776 through 5,790 (of 10,143 total)