March 31, 2010 at 12:42 pm
Hi All,
Can anybody shed a light on how to tune this query?
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
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.
Thanks
Judy
March 31, 2010 at 1:00 pm
select UID, ReferenceNumber, Type
FROM myTable
WHERE ReferenceNumber NOT LIKE 'TR%' AND
Type = 1
Is thi squery giving different results than yours one?
:w00t:
March 31, 2010 at 1:04 pm
vidya_pande (3/31/2010)
select UID, ReferenceNumber, TypeFROM myTable
WHERE ReferenceNumber NOT LIKE 'TR%' AND
Type = 1
Is thi squery giving different results than yours one?
:w00t:
Yes. The result is different.
March 31, 2010 at 1:12 pm
Can you provide some table structures and sample data?
Gethyn Elliswww.gethynellis.com
March 31, 2010 at 1:21 pm
Table structure
CREATE TABLE [dbo].[mytable](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[UID] [uniqueidentifier] NOT NULL,
[ReferenceNumber] [varchar](20) NULL,
[Type] [int] NOT NULL,
[Col4] [varchar](50) NOT NULL,
[Col5] [int] NOT NULL,
[Col6] [int] NULL,
[Col7] [uniqueidentifier] NULL,
[Col8] [int] NOT NULL,
[Col9] [varchar](max) NULL,
[Col10] [int] NULL
....
)
March 31, 2010 at 2:04 pm
Please post complete table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Have you considered normalising that table?
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
March 31, 2010 at 2:41 pm
I'm not sure if I'm totally wrong, but isn't that WHERE condition identical to WHERE ReferenceNumber NOT LIKE 'TR%' ?
Reason:
With your first subquery you include all UIDs with Type = 1 and with your second subquery you add all remaining UIDs.
With the following sample data I get identical result:
DECLARE @tbl TABLE
(
uid INT,
TYPE INT,
ReferenceNumber CHAR(3)
)
INSERT INTO @tbl
SELECT 1,1,'ABC' UNION ALL
SELECT 1,2,'ABC' UNION ALL
SELECT 2,1,'ABC' UNION ALL
SELECT 2,1,'ABC' UNION ALL
SELECT 2,3,'ABC' UNION ALL
SELECT 2,1,'TRA' UNION ALL
SELECT 2,2,'TRA' UNION ALL
SELECT 3,1,'ARA' UNION ALL
SELECT 3,1,'TRA' UNION ALL
SELECT 3,3,'TRA'
SELECT UID, ReferenceNumber, TYPE
FROM @tbl myTable
WHERE ReferenceNumber NOT LIKE 'TR%'
AND
UID
IN
(
SELECT UID
FROM @tbl myTable
WHERE TYPE = 1
UNION
SELECT UID
FROM @tbl myTable
WHERE ReferenceNumber
NOT IN (SELECT ReferenceNumber FROM @tbl myTable WHERE TYPE = 1)
)
ORDER BY UID
SELECT UID, ReferenceNumber, TYPE
FROM @tbl myTable
WHERE ReferenceNumber NOT LIKE 'TR%'
ORDER BY UID
-- Edit: maybe there is a data constellation that requires an EXISTS check:
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
April 1, 2010 at 7:10 am
Do you have an execution plan?
"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
April 1, 2010 at 7:28 am
lmu92 (3/31/2010)
I'm not sure if I'm totally wrong, but isn't that WHERE condition identical to WHERE ReferenceNumber NOT LIKE 'TR%' ?Reason:
With your first subquery you include all UIDs with Type = 1 and with your second subquery you add all remaining UIDs.
[/code]
At the beginning, I have the same thought.
but if Column "ReferenceNumber" duplicate on type=0 and type=1, then the result will only returm type=1.
For example,
ID,UID,ReferenceNumber,Type
1,B57B7BDF-2D13-4F00-AF97-000016834168,TR052950,0
2,EA82B85C-7993-4AC7-9221-000018FCAF44,TR052950,1
3,5749DF81-FC62-4CA8-9AFF-0000AF35F77E,AS053069,1
4,50C32C91-A0CA-4E21-9C2C-0002888315F8,AS053069,0
5,380728E0-A1F8-46C1-9D06-0007924C50AF,SD068972,1
The result will only show ID=2,3,5. not all.
April 1, 2010 at 7:37 am
GilaMonster (3/31/2010)
Please post complete table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Have you considered normalising that table?
CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[myTable]
(
[UID] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ReferenceNumber] ON [dbo].[myTable]
(
[ReferenceNumber] ASC,
[Type] ASC
)
yes, this is a normalisied table.
By the way, Type has only two value 0 and 1
April 1, 2010 at 7:44 am
Try:
SELECT T.UID, T.ReferenceNumber, T.Type
FROM myTable T
WHERE T.ReferenceNumber NOT LIKE 'TR%'
AND
(
T.Type = 1
OR NOT EXISTS
(
SELECT *
FROM myTable T1
WHERE T1.ReferenceNumber = T.ReferenceNumber
AND T1.Type = 1
)
)
ORDER BY UID
April 1, 2010 at 7:59 am
Judy-363345 (4/1/2010)
GilaMonster (3/31/2010)
Please post complete table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Have you considered normalising that table?
CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[myTable]
(
[UID] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_ReferenceNumber] ON [dbo].[myTable]
(
[ReferenceNumber] ASC,
[Type] ASC
)
yes, this is a normalisied table.
By the way, Type has only two value 0 and 1
Execution plan please?
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
April 1, 2010 at 8:05 am
Ken McKelvey (4/1/2010)
Try:
SELECT T.UID, T.ReferenceNumber, T.Type
FROM myTable T
WHERE T.ReferenceNumber NOT LIKE 'TR%'
AND
(
T.Type = 1
OR NOT EXISTS
(
SELECT *
FROM myTable T1
WHERE T1.ReferenceNumber = T.ReferenceNumber
AND T1.Type = 1
)
)
ORDER BY UID
Compared to my original query, The run time is close. but the logical read is much smaller than mine.
Your logical read is 198509. mine is 461382
April 1, 2010 at 8:14 am
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
April 1, 2010 at 8:16 am
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
)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply