ChrisM@Work

Forum Replies Created

Viewing 15 posts - 7,501 through 7,515 (of 10,143 total)

RE: Feeding different parameters to a variable

richard.kirby (8/17/2010)
Hi Chris
I've attached some data and I will read through the suggested articles.
Thanks for such a rapid response.
Hi Richard
Thanks for providing the data, however, for anybody to be...
^{“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 17, 2010 at 6:54 am

#1207845
RE: How to tell difference betwwen empty string and one or more blanks

sgambale (8/17/2010)
Thanks for the replies. It makes sense that 1, 2, or 3 blanks is treated the same (with the entire column filled with blanks), but the empty string...
^{“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 17, 2010 at 6:33 am

#1207812
RE: Delete statement taking more time

ningaraju.n (8/13/2010)
Hi Chris,
There are 7K records and has index on column AssessmentId and this is an single statement
Do you run the same statement many times with different @assessmentID?
How long does...
^{“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 17, 2010 at 5:47 am

#1207760
RE: Feeding different parameters to a variable

Hi Richard
Can you give us some sample data please? This will get you started, and there are also notes in the link in my sig.
Cheers-- Create some sample data
CREATE 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

August 17, 2010 at 5:38 am

#1207753
RE: replacing long running cursor

What is the maximum number of rows for any orderno?
^{“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 17, 2010 at 4:00 am

#1207721
RE: Create Query or View based on 5 tables

-----------------------------------------------------------
-- The Customer table is at the top of the hierarchy of
-- objects Customer, CustPhone, CustEmail, AddrDetail, AddrHeader
-- so use Customer as the driving table i.e. first in 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

August 17, 2010 at 2:42 am

#1207697
RE: I need a better way to do this

Ok, starting at the beginning, does this query return the same as your second query:
SELECT DISTINCT mh.LocationID
FROM tblManifest mh
-- the reference to l.Route in the WHERE clause turns...
^{“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 16, 2010 at 5:57 am

#1207284
RE: I need a better way to do this

dennisv (8/16/2010)
Thank you.
Trying the last example I get these:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'LocationID'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'LocationID'.
Msg 207,...
^{“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 16, 2010 at 3:41 am

#1207236
RE: I need a better way to do this

Hi Dennis, thanks for posting the feedback.
I think there's room for performance improvement here. The second query can be rewritten slightly like this - reasons are in the code:
SELECT DISTINCT...
^{“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 16, 2010 at 2:39 am

#1207207
RE: Read column values character by character in SQL query

deepak.khandelwal (8/13/2010)
If you have got the solution/query. Please share.
Thanks,
Deepak
Yep; if you take the ears off a rabbit, you got a draught excluder.
None of us here are psychic:-P
How about starting...
^{“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 13, 2010 at 6:24 am

#1206557
RE: Delete statement taking more time

ningaraju.n (8/13/2010)
Hi All,
I am executing delete statement in production server and its taking more time . can any one please explain the reason
my delete statement looks like
: Delete 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

August 13, 2010 at 5:28 am

#1206521
RE: Discovering the Performance of a Query

The reason I'm asking is this:
roelofsleroux (8/13/2010)
This function is used in a procedure where we step through about 6000+ LineUp records and use it to calculate what has been allocated...
^{“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 13, 2010 at 5:22 am

#1206519
RE: Discovering the Performance of a Query

roelofsleroux (8/13/2010)
It a really big procedure...
Can you post the part of the procedure which utilises the function?
^{“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 13, 2010 at 5:16 am

#1206512
RE: How do I get one row in a result set from 2 rows in parent table without using cursors.

DROP TABLE #tb1
CREATE TABLE #tb1 (
[trade] [varchar](10) NULL,
[leg] [varchar](10) NULL, -----say it is my Unique key
[ccy] [varchar](10) NULL,
[idex] [varchar](10) NULL
)
INSERT INTO #tb1 (trade, leg, ccy, idex)
SELECT 'a1', 'b1', 'usd',...
^{“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 13, 2010 at 5:12 am

#1206511
RE: Discovering the Performance of a Query

Can you post the whole procedure?
^{“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 13, 2010 at 4:54 am

#1206502

Viewing 15 posts - 7,501 through 7,515 (of 10,143 total)