ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,841 through 9,855 (of 10,143 total)

RE: Create Temporary Tables Dynamically in cursor loop

elmerbotha (6/23/2008)
Hi Jeff,
Yes, they have. I am still waiting for an answer from Chris. Hopefully there is a better solution than my temp table approach.
Thanks
Hello
Here's your data prepared as a...
^{“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 23, 2008 at 4:52 am

#832553
RE: Create Temporary Tables Dynamically in cursor loop

elmerbotha, can you please provide a larger sample size, say 5 or 10 rows for two metals? Presumably in your actual data you will have several rows like this:
Metal ...
^{“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 20, 2008 at 4:58 am

#831629
RE: Counting child rows in related table in 'main query'

Yep Rich it's more efficient. The correlated subquery is evaluated once for every row in the publishers table: it's slow and doesn't scale well. If you have a large 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 20, 2008 at 4:48 am

#831623
RE: Counting child rows in related table in 'main query'

Hello
Something like this is quite efficient:
SELECT p.Publisher, ISNULL(b.BookCount, 0) AS BookCount
FROM Publisher p
LEFT JOIN (
SELECT Publisher, COUNT(*) AS BookCount
FROM Books
GROUP BY Publisher) b
ON b.Publisher = p.Publisher
ORDER...
^{“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 20, 2008 at 4:29 am

#831617
RE: how to select 2nd toprow from emp table

smunson (6/11/2008)
You're quite right! And that's why you're an "Old Hand", and I'm still an "SSC Rookie". I was able to make the same change to...
^{“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 11, 2008 at 10:02 am

#827372
RE: how to select 2nd toprow from emp table

smunson (6/11/2008)
Actually, I tried that code and it fails on syntax, due to the ORDER BY clause being present in a subquery. Try the following that uses 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

June 11, 2008 at 9:35 am

#827358
RE: how to select 2nd toprow from emp table

This works too:
CREATE TABLE #Employees (Emp_No INT, EName VARCHAR(5), Sal INT)
INSERT INTO #Employees (Emp_No, EName, Sal)
SELECT 111, 'AAAA', 9999 UNION ALL
SELECT 222, 'bbbb', 9890 UNION ALL
SELECT 777, 'vvvvv',7879
SELECT TOP 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

June 11, 2008 at 4:59 am

#827144
RE: Query (duplicate rows)

rbarryyoung (6/3/2008)
Please do not make duplicate posts. It just annoys us and wastes everyones time.
Same topic posted three times. Only 885 more to go.
^{“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 4, 2008 at 6:10 am

#824107
RE: UK Cultures

Loner (5/6/2008)
(Bill Clinton, he was lucky). 😉
No kidding 😉
^{“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

May 8, 2008 at 8:37 am

#812449
RE: UK Cultures

Loner (5/6/2008)
Prince Charles, Princess Anne are married twice.
Not at the same time - or to each other!!
^{“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

May 6, 2008 at 7:42 am

#811195
RE: Everyones favourite 'Subquery returned more than 1 value'

Adam Watson (5/6/2008)
Thanks for that suggested alternative, worked a treat - I'll rewrite the whole proc in that format as I totally agree that it is a lot more readable....
^{“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

May 6, 2008 at 7:38 am

#811193
RE: Everyones favourite 'Subquery returned more than 1 value'

Hi Adam
Currently you're doing 20 selects into the same table to get your values. You could consolidate this into a single select and make the first part of your code...
^{“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

May 6, 2008 at 4:32 am

#811092
RE: Table design

Heh heh no worries Grant, you are of course absolutely right. In practice though, about 7 years of it, the table layout I've shown above was far easier to work...
^{“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

May 2, 2008 at 2:36 pm

#810349
RE: Table design

The two fashion systems I've written in the past worked like this:
Column Name
StyleID
ColourID
Size06
Size08
Size10
Size12
.
.
.
Size 24
Unsized
The meaning of the sizing columns is a property of the Style master, so Size06 to Size18...
^{“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

May 2, 2008 at 9:34 am

#810169
RE: How to Update duplicate records in table

svhanda (4/30/2008)
steveb (4/29/2008)
do you want Education column to be updated to the same value?
can you post the table structure and some matching sample data for the other table ?
Hi...
Thanks for...
^{“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

May 2, 2008 at 9:10 am

#810146

Viewing 15 posts - 9,841 through 9,855 (of 10,143 total)