Help me optimise this query

Question

Post reply

Help me optimise this query

Gaja

Right there with Babe

Points: 768
More actions
March 24, 2014 at 7:53 am

#396471

SELECT
a.claim_id,
a.claim_created,
"order" = (
select min(order_id)
from dbo."order" b with (nolock)
where b.claim_id = a.claim_id
),
"order_created" = (
select min(created_dtm) from dbo."order" b with (nolock)
where b.claim_id = a.claim_id
)
from #claim a

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

SELECT

c.claim_id,

c.claim_created,

x.[order],

x.[order_created]

FROM #claim c

CROSS APPLY (

SELECT

[order] = MIN(order_id),

[order_created] = MIN(created_dtm)

FROM dbo.[order] o

WHERE o.claim_id = c.claim_id

) x

^{“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

Gaja Right there with Babe Points: 768 More actions · Answer 2

Thanks for replay still its taking lot of time. Is there any other way around ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please post table definitions, index definitions and execution plan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You need to provide more information. To find out what could be wrong, people need DDL for tables and indexes, as well as actual execution plan as described in this article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

Without seeing the indexes, I can't even guess at what to do.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101100 More actions · Answer 6

Best guess with what's known so far:

Create an index on "order":

key = ( claim_id ); include = ( order_id, created_dtm )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".