ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,721 through 9,735 (of 10,143 total)

RE: Matching recoreds

What are the three tables? How are they related?
^{“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

July 31, 2008 at 4:07 am

#851123
RE: Can this be optimized?

You're welcome Jo, 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

July 31, 2008 at 4:04 am

#851122
RE: Can this be optimized?

Hi Jo
If this is a training exercise then you are going to learn loads about normalisation and database layout, at least until you throw your computer out of the window...
^{“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

July 31, 2008 at 3:51 am

#851113
RE: Usage of "Tally Table"

Chris, Karthik's corrected code has the same functionality but was it by design? 😛
^{“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

July 31, 2008 at 3:43 am

#851110
RE: How to get Sequential ordering of data based on Ids Passed to IN Keyword

How are you passing in your ID's?
Here's a possibility:
SELECT a.Col1
FROM table1 a
INNER JOIN (
SELECT 1 AS RowID, 2 AS col_ID UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3,...
^{“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

July 31, 2008 at 3:39 am

#851108
RE: Can this be optimized?

Hi Jo
This is intended to replace your stored procedure. Please note that it's untested because I don't have your data, you should test the output against your sproc.
SELECT VBR_ID, VDR_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

July 31, 2008 at 3:18 am

#851099
RE: Can this be optimized?

Hi Jo, yes it is, thanks.
I'll use the view, and select from it to create the temp table with the calculations incorporated into it. Ideally you would query from...
^{“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

July 31, 2008 at 2:23 am

#851071
RE: Usage of "Tally Table"

karthikeyan (7/31/2008)
I think if we change his code from
select
a.N as AccountNum
,b.N...
^{“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

July 31, 2008 at 2:13 am

#851061
RE: Usage of "Tally Table"

I see your point Karthik.
Chris's code generates repeats of 100, yours generates repeats of 1000 (multipled by the row count in your tally table). Do you think it's possible...
^{“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

July 31, 2008 at 2:05 am

#851051
RE: Can this be optimized?

Hi Jo
You could convert the cursor-based update of the temp table to a set-based update like this:
UPDATE #TMP_VBR
SET
tempr1 = CASE WHEN tempr1 <> 0 THEN tempr1 + tempreti...
^{“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

July 31, 2008 at 1:55 am

#851044
RE: Usage of "Tally Table"

Test them, Karthik!
The main difference that I can see is that Chris's version will always give 10,000 rows. The number of rows your version will give, will depend on how...
^{“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

July 31, 2008 at 1:39 am

#851039
RE: JOINING OF TWO TABLES

karthikeyan (7/30/2008)
Create View v1
as
Select Eno,Ename from e1.dbo.emp
where Eno = 5
Union All
Select Eno,Ename from e1.dbo.empmast
where Eno = 5
Now,
Select Eno,Ename from v1
Karthik, why not just join the two tables on Eno?...
^{“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

July 31, 2008 at 1:19 am

#851028
RE: JOINING OF TWO TABLES

VENUGOPAL.RR (7/30/2008)
HI
i have two tables with different number of rows and no identity columns in each one
i want to join them and retrieve in single table
please help me...
^{“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

July 31, 2008 at 1:17 am

#851027
RE: UK Cultures

James A. Lawrence (7/30/2008)
And wanted to mention that I have the English to thank for oh so many hours-upon-hours of good music. The classic stuff of course like Rolling Stones,...
^{“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

July 31, 2008 at 12:02 am

#851000
RE: UK Cultures

James A. Lawrence (7/30/2008)
I see....searching online for a good air fare now.:w00t:
:hehe:
^{“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

July 30, 2008 at 2:39 pm

#850863

Viewing 15 posts - 9,721 through 9,735 (of 10,143 total)