ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,286 through 3,300 (of 10,143 total)

RE: Issue with a slow Query

T.Ashish (6/9/2014)
Thanks Chris.
Yes, I had already tried creating # table as you advised. This was showing lot of improvement in i/o stats as main part was executing only 1 time...
^{“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

June 9, 2014 at 5:32 am

#1720046
RE: Issue with a slow Query

T.Ashish (6/9/2014)
Hi all,
I have attached a script and its execution plan for your review.
We are working for a long time on optimizing this script, and latest modification I did was...
^{“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

June 9, 2014 at 3:22 am

#1720023
RE: Just curious, what are your SQL pet peeves ?

SQL is delicious (6/6/2014)
Koen Verbeeck (6/6/2014)
gbritton1 (6/6/2014)
Koen Verbeeck (6/6/2014)
TomThomson (6/6/2014)
Just a couple of examples: A British colleague who held an A-level in math and a bachelor's in business swore...
^{“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

June 6, 2014 at 9:49 am

#1719737
RE: Interview Questions

julian.fletcher (6/6/2014)
.. I should have said that we're looking for developers to work on all parts of a particular product; both the C# and the SQL. We're not big enough...
^{“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

June 6, 2014 at 9:17 am

#1719726
RE: Interview Questions

julian.fletcher (6/5/2014)
I work for a company of about 50 people in Oxford and am involved in the recruitment process - we're looking for developers with at least a couple 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

June 6, 2014 at 8:56 am

#1719706
RE: Just curious, what are your SQL pet peeves ?

Koen Verbeeck (6/5/2014)
I prefer the alias with the equal sign. It is very easy to align everything, especially when you have larger expressions.
It's hard to believe this I know, but...
^{“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

June 6, 2014 at 6:24 am

#1719629
RE: No results...

pk2dpvp (6/6/2014)
If I change WHERE to AND it gives me this..:
(408014 row(s) affected)
which with where is:
(2054 row(s) affected)
---------------------------------------------
Isn't this because it doesn't know what to do with AND so 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

June 6, 2014 at 6:09 am

#1719622
RE: No results...

The first query may not be doing what you expect. If you reference a column from a left-joined table in the WHERE clause, the left join is converted to an...
^{“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

June 6, 2014 at 5:26 am

#1719608
RE: Trying to remove cursor

-- TESTING
-- Set up a CTE with agent numbers expanded out, randomly number the rows within each QueueID
;WITH RandomAgents AS (
SELECT QueueID, AgentID, rn = ROW_NUMBER() OVER(PARTITION BY...
^{“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

June 5, 2014 at 8:53 am

#1719272
RE: How to avoid while loop in this - taking lot of time to retrieve data

vinodkumark_mca (6/5/2014)
Hello,
Thanks for your feedback.
I have attached the related tables and functions in "Queries.txt" file.
The testing data is shared in "TestingData.xls" file.
Please look into issue and let me know if...
^{“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

June 5, 2014 at 6:39 am

#1719181
RE: Selecting Distinct From Multiple Tables

Hrhb.mail (6/5/2014)
Where are you adding your conditions? Can you post the code that gives you an error?
Thanks, I solved it.
This is my final stored procedure:
Create PROCEDURE [dbo].[NewsPageWise]
...
^{“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

June 5, 2014 at 6:16 am

#1719169
RE: how to remove next and previous record is amount = 0?

Jeff Moden (6/3/2014)
Eirikur Eiriksson (6/3/2014)
Jeff Moden (6/2/2014)
Unless this was an accidental duplication of data, there's no way that I'd do this. You're destroying historical data.
Second that but then again...
^{“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

June 3, 2014 at 8:27 am

#1718555
RE: Not Exists

ramana3327 (6/1/2014)
Actually I am getting some syntax error while executing that, so in the mean while I posted here to find any alternative.
ramana3327 (5/31/2014)
DELETE TableB WHERE studentID = @studentID
IF 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

June 2, 2014 at 6:48 am

#1718181
RE: t-sql 2008 exlain select

ScottPletcher (5/30/2014)
I think the overhead of XML is unwarranted here...
Yes you're right, with a fixed number of rows, FOR XML PATH is probably overkill.
^{“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

June 2, 2014 at 2:02 am

#1718124
RE: t-sql 2008 exlain select

Jeff Moden (5/30/2014)
Heh... not to worry. I started out by testing the other solutions and then went back to the original solution. I kept looking at it 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

June 2, 2014 at 2:01 am

#1718123

Viewing 15 posts - 3,286 through 3,300 (of 10,143 total)