using Inner Join or Join on same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

using Inner Join or Join on same table

Author

Topic

beginnertosql
Starting Member

3 Posts

Posted - 2014-09-10 : 10:49:04

I need to select the rows which are not already in a status called 'Inprogress' and are all in 'Ready' status for a given policy number.
we expect multiple records for a given policy.

Below is my table:
SELECT TOP 100 [DownstreamQuoteRequest_ID]
,[Account_NUMBER],[Policy_NUMBER],[Job_NUMBER]
,[Message_ID],[Quote_PAYLOAD],[Create_DTM]
,[Update_DTM],[Source_SYSTEM]
,[DownstreamEvent_Eventname],[Status_Indicator]
FROM [Batch].[dbo].[UPSTREAM_QUOTE_REQUEST]

[DownstreamQuoteRequest_ID] is the PK

Here is my query which is working fine:
UPDATE Top (25) [Batch].[dbo].[UPSTREAM_QUOTE_REQUEST] SET
Status_Indicator = 'Inprogress' OUTPUT INSERTED.Policy_NUMBER where
Status_Indicator in ('Ready') AND Policy_NUMBER NOT IN
(SELECT Policy_NUMBER FROM [Batch].[dbo].[UPSTREAM_QUOTE_REQUEST]
WHERE Status_Indicator in ('Inprogress') Group by Policy_NUMBER)

The only thing is that the architect insisted to come up with a better query to be more efficient. Is there a better way to write this query?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-10 : 11:01:51

What does the execution plan show? Any scans that can be mitigated through appropriate indexing?

How big is the [dbo].[UPSTREAM_QUOTE_REQUEST] table? If really big, can you remove the GROUP BY (usually causes a SORT)?

You can write it using a CTE (not necessarily faster)

[code]
with uqr as (
select *
from [Batch].[dbo].[UPSTREAM_QUOTE_REQUEST]
where Status_Indicator = 'Ready'
)

Update uqr
set Status_Indicator = 'Inprogress'
OUTPUT INSERTED.Policy_NUMBER

beginnertosql
Starting Member

3 Posts

Posted - 2014-09-10 : 12:37:03

Below is my detail explanation, and sorry If I really making it confusing to understand. But any suggestion on my situation is really appreciated. Thanks In Advance.

Table will be in real-time, will recieve records continuously during working business hours. Expecting 2 records for every second which might approximately accumalte to 50,000 records a day.
Since the data I process in java code with this query, will be needed by other teams as quick as possible for further processing and updating the user.

I tried CTE and architect was not happy and I ended with the query I had in my post...again he insisted to check the rows above my selection by the primary key ID which might be more efficient and I don't know how to do that..

Our intent here is..to select and update (and process in Java) only those records everytime which are not already in 'Inprofress' or 'Error' status.
We will recieve multiple records randomly with same policy number but with different quote_payloads.
Below is the table sample with few fields..

ID Policy_NUMBER (Qoute_Payload) Status_Indicator
1 POL1 XML0001 Inprogress
2 POL1 XML0002 Inprogress
3 POL1 XML0003 Inprogress
4 POL1 XML0004 Inprogress
7 POL1 XML0007 Ready
8 POL1 XML0008 Ready
17 POL1 XML0017 Ready

So now while one of the instance is processing the records (1, 2, 3, 4) which are in 'Inprogress' status, the other instance should not pick up records (7, 8, 17) which are in 'Ready' status.

Also, in my first instance, if suppose record 3 got an error then the record status is updated as 'Error' and will not process record 4, my java code will update its status back to 'Ready'.

So till the time the record 3 is fixed by someone who is responsible, my query should not select those policies to process, as I need to maintain the order they came in to send the sucessful records to other table.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-10 : 14:48:54

"check the rows above my selection by the primary key ID"

Check them for what?

I'm guessing the architect doesn't understand how ctes work. They're just an alternate way of expressing subqueries. Many folks find them neater and easier to read (I know that I do!) What counts is the execution plan. For an OLTP system like this, you don't want to lock the table every time. YOu could do a quick check before the update:


if 0 < (
  select top(n) * from mytable with (readpast)
  where <conditions>
  )

... do your update

Readpast is a bit like NoLock, except you don't run the risk of dirty reads. See: http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/

beginnertosql
Starting Member

3 Posts

Posted - 2014-09-10 : 15:07:54

Thank you so much. I will look into the link you gave me and might find some good reason/explanation to present to architect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources