ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,461 through 8,475 (of 10,143 total)

RE: CASE statement kills performance

Hi David
I'd try it with the following values first:
@vendor_name = '(All)'
@search_type = 'Parent Company'
and if this fails, then deconstruct the two new operations in the WHERE clause, perhaps...
^{“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

April 17, 2009 at 5:27 am

#979548
RE: Use Exist

mithun.gite (4/17/2009)
GilaMonster (4/17/2009)
mithun.gite (4/17/2009)
See, anyone who sees the query can guess that its not the actual query to optimize,,
Then what was the point of posting it?
its just a matter...
^{“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

April 17, 2009 at 4:56 am

#979538
RE: CASE statement kills performance

Hi David, give the following a try:
SELECT
vrd.request_id,
vrd.wk_ref,
convert(varchar,vrd.[date],103) as date_recorded,
vrq.request_type,
vrd.add_ref,
vpc.parent_company,
vrd.vendor_name,
vrd.cca,
vrd.litigation,
vr.reason_required,
convert(varchar, NULLIF(vrd.date_received, '1900-01-01'), 103) AS date_received,
datediff(dd,vrd.date_requested,case vrd.date_received when '1900-01-01' then vrd.date_requested else vrd.date_received end) as days_to_receipt,
convert(varchar, NULLIF(vrd.deadline_date, '1900-01-01'), 103) AS deadline_date,
convert(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

April 17, 2009 at 4:44 am

#979534
RE: Use Exist

mithun.gite (4/17/2009)
GilaMonster (4/17/2009)
mithun.gite (4/17/2009)
Actually I have been asked to Optimize the query and do the performance tunning to the query.
select * from emp where dept_no in(10,20,30)
As in this query only...
^{“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

April 17, 2009 at 4:22 am

#979524
RE: Use Exist

mithun.gite (4/17/2009)
There is a big performnace problem with using Exists in this example,, so can i say that if we are passing static values in where clause then its better...
^{“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

April 17, 2009 at 3:27 am

#979500
RE: How to simplify a "negative a zero" formula

SELECT CASE WHEN @aFrom > @aCount THEN 0 ELSE CEILING((@aCount-@aFrom+0.0001)/@aRatio) END
^{“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

April 17, 2009 at 3:17 am

#979495
RE: Are the posted questions getting worse?

RBarryYoung (4/16/2009)
john.arnott (4/16/2009)
Am I so obsessed with big round numbers that I would ...
Coincidentally, that's also how I got my first date... :w00t:
C'mon Barry ya can't just leave this...
^{“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

April 17, 2009 at 2:53 am

#979480
RE: Use Exist

mithun.gite (4/17/2009)
Dear Friends,
I have one query like
select * from emp where dept_no in(10,20,30)
Can anyone help me in using EXISTS instead of IN ?
Pls.....
Thanks in Advance
Mithun
SELECT *
FROM emp a
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

April 17, 2009 at 2:43 am

#979474
RE: Querying table with varchar datatype column

sacheen.nemade (4/17/2009)
Hi
Agentid - Integer
Chain - Varchar(2000)
Chain , it contains all sub-agents(Agentid) list seprated by '-'
For eg
agentid 100
chain would be 100-101-102
Chain gets longer as new...
^{“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

April 17, 2009 at 2:35 am

#979469
RE: Querying table with varchar datatype column

Please can you provide a script for table arr2, and some sample data. The link below will show you how to provide sample data in a format which is convenient.
Is...
^{“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

April 17, 2009 at 1:35 am

#979448
RE: Retrieve AD account's group using OpenQuery

Shayne.Gui (4/16/2009)
I want to retrieve a AD account's group which means its [memberof] field in AD,
but I don't know how to get it using Openquery,a user may have multivalue 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

April 17, 2009 at 1:15 am

#979441
RE: Are the posted questions getting worse?

Well-known films by Stanley Thread:
Fear and The Thread
Thread's Kiss
Threads of Glory
Marriage to The Thread
The Thread moves to England
Dr. Strangethread
2009: A Thread Odyssey
A Clockwork Thread
R Barry Thread
The Threading
Full Metal Thread
Eyes Sewn...
^{“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

April 16, 2009 at 10:55 am

#979060
RE: CASE statement kills performance

Hello
First thing I'd do with this is take the switch out of the join:
@search_type = 'Parent Company', tv.vendor_name = '(All)'
- inner join @vendor_names_tab tv ON vpc.parent_company = vpc.parent_company --...
^{“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

April 16, 2009 at 10:31 am

#979032
RE: Results in one row when using an INNER JOIN that returns multiple rows

Check out this excellent article[/url] by Jeff Moden.
Cheers
ChrisM
^{“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

April 16, 2009 at 9:49 am

#978981
RE: Are the posted questions getting worse?

GSquared (4/16/2009)
Alvin Ramard (4/16/2009)
Bob Hovious (4/16/2009)
How could I have forgotten "Bride of the Thread?"
I LOVED Kate Beckinsale in the last remake. 😛
She was in "Bride of 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

April 16, 2009 at 8:20 am

#978891

Viewing 15 posts - 8,461 through 8,475 (of 10,143 total)