How to tune this query

Question

How to tune this query

Viewing 11 posts - 16 through 25 (of 25 total)

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

Judy Why Hall of Fame Points: 3656 More actions · Answer 1

lmu92 (4/1/2010)
Did you try the solution I posted earlier?
SELECT UID, ReferenceNumber, TYPE
FROM #tbl myTable
WHERE ReferenceNumber NOT LIKE 'TR%'
AND EXISTS (SELECT 1 FROM #tbl myTable2 WHERE TYPE = 1 AND myTable.uid = myTable2.uid)
ORDER BY UID

I tried. but the return result is not right.

Judy Why Hall of Fame Points: 3656 More actions · Answer 2

Execution plan please?[/quote]

Because this is production sensitive data and company policy does not allow me to disclose this. I will build test table with full column list and post execution plan later.

Thank you

Judy Why Hall of Fame Points: 3656 More actions · Answer 3

here is execution plan

|--Merge Join(Inner Join, MERGE:([Union1010])=([T].[UID]), RESIDUAL:([SQLAdmin].[dbo].[Mytable].[UID] as [T].[UID]=[Union1010]))

|--Stream Aggregate(GROUP BY:([Union1010]))

| |--Merge Join(Concatenation)

| |--Sort(ORDER BY:([SQLAdmin].[dbo].[Mytable].[UID] ASC))

| | |--Clustered Index Scan(OBJECT:([SQLAdmin].[dbo].[Mytable].[PK_Mytable]), WHERE:([SQLAdmin].[dbo].[Mytable].[Type]=(1)))

| |--Sort(ORDER BY:([O1].[UID] ASC))

| |--Hash Match(Right Anti Semi Join, HASH:([SQLAdmin].[dbo].[Mytable].[ReferenceNumber])=([O1].[ReferenceNumber]), RESIDUAL:([SQLAdmin].[dbo].[Mytable].[ReferenceNumber] as [O1].[ReferenceNumber]=[SQLAdmin].[dbo].[Mytable].[ReferenceNu

| |--Index Scan(OBJECT:([SQLAdmin].[dbo].[Mytable].[IX_ReferenceNumber]), WHERE:([SQLAdmin].[dbo].[Mytable].[Type]=(1)))

| |--Nested Loops(Left Anti Semi Join)

| |--Nested Loops(Left Anti Semi Join, WHERE:([SQLAdmin].[dbo].[Mytable].[ReferenceNumber] as [O1].[ReferenceNumber] IS NULL))

| | |--Clustered Index Scan(OBJECT:([SQLAdmin].[dbo].[Mytable].[PK_Mytable] AS [O1]))

| | |--Top(TOP EXPRESSION:((1)))

| | |--Clustered Index Scan(OBJECT:([SQLAdmin].[dbo].[Mytable].[PK_Mytable]), WHERE:([SQLAdmin].[dbo].[Mytable].[Type]=(1)))

| |--Row Count Spool

| |--Index Seek(OBJECT:([SQLAdmin].[dbo].[Mytable].[IX_ReferenceNumber]), SEEK:([SQLAdmin].[dbo].[Mytable].[ReferenceNumber]=NULL AND [SQLAdmin].[dbo].[Mytable].[Type]=(1)) ORDERED FORWARD)

|--Filter(WHERE:(NOT [SQLAdmin].[dbo].[Mytable].[ReferenceNumber] as [T].[ReferenceNumber] like 'LC%'))

|--Nested Loops(Inner Join, OUTER REFERENCES:([T].[ID], [Expr1011]) WITH ORDERED PREFETCH)

|--Index Scan(OBJECT:([SQLAdmin].[dbo].[Mytable].[IX_UID] AS [T]), ORDERED FORWARD)

|--Clustered Index Seek(OBJECT:([SQLAdmin].[dbo].[Mytable].[PK_Mytable] AS [T]), SEEK:([T].[ID]=[SQLAdmin].[dbo].[Mytable].[ID] as [T].[ID]) LOOKUP ORDERED FORWARD)

Judy Why Hall of Fame Points: 3656 More actions · Answer 4

Ken McKelvey (4/1/2010)
How does this do?
SELECT UID, ReferenceNumber, Type
FROM myTable
WHERE ReferenceNumber NOT LIKE 'TR%'
AND Type = 1
UNION ALL
SELECT T.UID, T.ReferenceNumber, T.Type
FROM myTable T
WHERE T.ReferenceNumber NOT LIKE 'TR%'
AND T.Type <> 1
AND NOT EXISTS
(
SELECT *
FROM myTable T1
WHERE T1.ReferenceNumber = T.ReferenceNumber
AND T1.Type = 1
)

After this query ran over 3 minutes, I stopped it.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 5

If myTable is actually a complicated view, try avoiding subqueries and use a derived table instead:

SELECT T.UID, T.ReferenceNumber, T.Type

FROM myTable T

LEFT JOIN

(

SELECT DISTINCT T1.ReferenceNumber

FROM myTable T1

WHERE T1.ReferenceNumber NOT LIKE 'TR%'

AND T1.Type = 1

) D

ON T.ReferenceNumber = D.ReferenceNumber

WHERE T.ReferenceNumber NOT LIKE 'TR%'

AND

(

T.Type = 1

OR D.ReferenceNumber IS NULL

)

ORDER BY T.UID

LutzM SSC Guru Points: 107049 More actions · Answer 6

Judy-363345 (4/1/2010)
lmu92 (4/1/2010)
Did you try the solution I posted earlier?
SELECT UID, ReferenceNumber, TYPE
FROM #tbl myTable
WHERE ReferenceNumber NOT LIKE 'TR%'
AND EXISTS (SELECT 1 FROM #tbl myTable2 WHERE TYPE = 1 AND myTable.uid = myTable2.uid)
ORDER BY UID
I tried. but the return result is not right.

Well I guess I have to give up then....

I usually prefer to work on issues with sample data provided so I have something to compare against.Saves a lot of "Try this - no - how about this - no" loops...

If you cannot provide actual data please provide some modified sample data that actually describe your scenario.

Btw: I can't see any reason for not providing a unique identifier, a reference number and a value of 0 or 1. Must be a top secret company... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Judy Why Hall of Fame Points: 3656 More actions · Answer 7

Ken McKelvey (4/1/2010)
If myTable is actually a complicated view, try avoiding subqueries and use a derived table instead:
SELECT T.UID, T.ReferenceNumber, T.Type
FROM myTable T
LEFT JOIN
(
SELECT DISTINCT T1.ReferenceNumber
FROM myTable T1
WHERE T1.ReferenceNumber NOT LIKE 'TR%'
AND T1.Type = 1
) D
ON T.ReferenceNumber = D.ReferenceNumber
WHERE T.ReferenceNumber NOT LIKE 'TR%'
AND
(
T.Type = 1
OR D.ReferenceNumber IS NULL
)
ORDER BY T.UID

This is table, not view.

I run this query. running time: 02:17 and logical reads 238814

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Since you are returning 48000 out of 50000 rows, how about just forcing a table scan on everything?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 9

Judy-363345 (3/31/2010)
Can anybody shed a light on how to tune this query?
Mytable has around 50000 rows and return result 48000 rows. There are non-clustered indexes on UID,ReferenceNumber, Type. it runs 100 secs and logical read 408000.

select UID, ReferenceNumber, Type

FROM myTable

WHERE ReferenceNumber NOT LIKE 'TR%' AND

UID in (Select UID from myTable where Type = 1

UNION Select UID from myTable where ReferenceNumber

not in (select ReferenceNumber from myTable where Type = 1))

ORDER BY UID

OPTION (HASH JOIN, MERGE JOIN, MAXDOP 0);

Just my guess.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Chris Souchik SSC-Addicted Points: 429 More actions · Answer 10

Since you are returning 96% of the table rather than build the uid's you need, build the uid's you don't and exclude them. Maybe something like this:

;WITH excludesRef AS

(

SELECT ReferenceNumber FROM myTable

GROUP BY ReferenceNumber

HAVING COUNT(*)>1

),

excludesUid AS

(

SELECT UID from myTable t

JOIN excludesRef e ON t.ReferenceNumber=e.ReferenceNumber

AND TYPE=0

)

SELECT UID, ReferenceNumber, TYPE

FROM myTable

WHERE ReferenceNumber NOT LIKE 'TR%'

AND uid not in(SELECT uid from excludesUid)

ORDER BY UID

Stefan_G SSCertifiable Points: 6609 More actions · Answer 11

Judy-363345 (3/31/2010)
Table structure
CREATE TABLE [dbo].[mytable](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[UID] [uniqueidentifier] NOT NULL,
[ReferenceNumber] [varchar](20) NULL,

I am pretty sure that your problem is caused by allowing NULL in ReferenceNumber.

Try changing the table definition to

[ReferenceNumber] [varchar](20) NOT NULL

To speed up the query a little more you could also create a covering index with type first, like this:

CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[myTable]

( Type )

INCLUDE

( ReferenceNumber, UID, ID )

/SG