Select TOP 1 is so SLOOOOWWW

Last Post 22 Jan 2009 05:05 AM by SwePeso. 14 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Krypto
New Member
New Member

--
15 Jan 2009 09:41 AM
I'm getting a major slowdown (execution plan) on a Top N sort.

Here's the part that so slow:


UPDATE #events SET userID =

(SELECT TOP 1 userID FROM #events e2

WHERE e2.coCRN = e.coCRN AND e2.deptCRN = e.deptCRN AND e2.county = e.county AND e2.orderNo = e.orderNo

ORDER BY e2.logID)

FROM #events e


I've been searching and searching looking for a different methoed to aviod this slow sort.

Any ideas?
Krypto
New Member
New Member

--
15 Jan 2009 02:29 PM
In the temp table there are 433000

Yes there are indexes on the temp table.

I was hoping to use rowcount 1 then set it back to rowcount 0

But you can't (or I don't know how) to do that within the select subquery.
Krypto
New Member
New Member

--
15 Jan 2009 07:05 PM
Yep my indexes look good in my execution plan.

This is part of a much larger query...my main slowdown is this part of the query

I was really hoping someone knew of a way to speed up the top 1 part..if I can speed up this part I'm golden..


I'm trying switching to a clustered

thanks gunneyk
Krypto
New Member
New Member

--
15 Jan 2009 08:14 PM
I added the clustered, still slow as hell when it gets to the top 1


I'm fine with the row count part but I can't get to work inside my sub query

I'm willing to try anything..my job may depend on me figuring this out..

Was thinking somthing along this line:

declare @query varchar(200)
set @query = 'select top ' + cast(@number as varchar(5))
+ ' <rest of your query> '
exec(@query)
SwePeso
New Member
New Member

--
16 Jan 2009 04:48 AM
UPDATE e
SET e.UserID = d.UserID
FROM #Events AS e
INNER JOIN (
SELECT d.coCRN,
d.deptCRN,
d.county,
d.orderNo,
d.userID
FROM (
SELECT coCRN,
deptCRN,
county,
orderNo,
userID,
ROW_NUMBER() OVER (PARTITION BY coCRN, deptCRN, county, orderNo ORDER BY logID) AS recID
FROM #Events
) AS d
WHERE d.recID = 1
) AS q ON q.coCRN = e.coCRN
AND q.deptCRN = e.deptCRN
AND q.county = e.county
AND q.orderNo = e.orderNo
Krypto
New Member
New Member

--
16 Jan 2009 06:14 AM
Thanks so much Pesomannen!!!! I'm trying now.


Thank you also gunneyk, it takes a very painful 20 minutes!!! just for this part of the proc! It's about 488k records.


THANKS SOOO MUCH GUYS
Krypto
New Member
New Member

--
16 Jan 2009 06:40 AM


Getting an error:


Msg 4104, Level 16, State 1, Procedure rpt_OrderSummary_rob_new, Line 237
The multi-part identifier "d.UserID" could not be bound.
SQLUSA
New Member
New Member

--
16 Jan 2009 07:14 AM
Referencing alias issue. Corrected:

UPDATE e
SET e.UserID = q.UserID
FROM #Events AS e
INNER JOIN (
SELECT d.coCRN,
d.deptCRN,
d.county,
d.orderNo,
d.userID
FROM (
SELECT coCRN,
deptCRN,
county,
orderNo,
userID,
ROW_NUMBER() OVER (PARTITION BY coCRN, deptCRN, county, orderNo ORDER BY logID) AS recID
FROM #Events
) AS d
WHERE d.recID = 1
) AS q ON q.coCRN = e.coCRN
AND q.deptCRN = e.deptCRN
AND q.county = e.county
AND q.orderNo = e.orderNo


Kalman Toth
http://www.sqlusa.com
Krypto
New Member
New Member

--
16 Jan 2009 08:16 AM


Thanks SQLUSA!! trying that now.


Gunneyk, I follow what you are saying. I'll check into that now and report back.'


THANKS GUYS
Krypto
New Member
New Member

--
17 Jan 2009 06:59 AM
SQLUSA,

That worked great, thank you very much.


Thank you also gunneyk.
Krypto
New Member
New Member

--
17 Jan 2009 08:17 AM

Thanks again.
Krypto
New Member
New Member

--
17 Jan 2009 02:32 PM
OH NOOO

I get a different row count if I use the above method.

If I switch back to old way I get 10732

Above way I get 10799 rows
Krypto
New Member
New Member

--
18 Jan 2009 08:48 AM
I thought having the table & sample data might make it easier.

This is in an 'easy load' form.

CREATE TABLE [dbo].[EventItems_rob](
[LogId] [int] NOT NULL,
[Company] [varchar](50) NOT NULL,
[CompanyId] [int] NOT NULL,
[Department] [varchar](50) NOT NULL,
[DepartmentId] [int] NOT NULL,
[CompanyCRN] [varchar](50) NOT NULL,
[DepartmentCRN] [varchar](50) NOT NULL,
[TitleUnit] [varchar](50) NOT NULL,
[TitleUnitId] [int] NOT NULL,
[State] [varchar](50) NOT NULL,
[County] [varchar](100) NOT NULL,
[OrderNo] [varchar](128) NOT NULL,
[OrderId] [int] NOT NULL,
[UserCompany] [varchar](50) NOT NULL,
[UserCompanyId] [int] NOT NULL,
[UserName] [varchar](50) NOT NULL,
[UserId] [int] NOT NULL,
[Source] [varchar](50) NULL,
[Event] [varchar](256) NULL,
[EventTime] [datetime] NOT NULL,
[EventValue] [varchar](150) NULL,
[PI2Status] [varchar](256) NULL,
[PI2LineCount] [varchar](256) NULL,
[PI2RequestTrigger] [varchar](256) NULL,
[PI2PrimaryRequestValue] [varchar](256) NULL,
[Comment] [varchar](4096) NULL
) ON [PRIMARY]



INSERT INTO Eventitems_rob (LogId,Company,CompanyId,Department,DepartmentId,CompanyCRN,DepartmentCRN,
TitleUnit,TitleUnitId,State,County,OrderNo,OrderId,UserCompany,UserCompanyId,UserName,
UserId,Source,Event,EventTime,EventValue,PI2Status,PI2LineCount,PI2RequestTrigger,
PI2PrimaryRequestValue,Comment)

SELECT '418504535','Chicago Title','134','ServiceLink PA','845','00021','00090','01','910','CA','Madera','1734995','33806768','ServiceLink','190','Christina Young','1538','TitlePoint','Image-Recorded','Sep 29 2008 12:53PM','MA:2008 00020606','','','','',''
SELECT '358982648','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553378','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:01AM','MA:2
SELECT '358982677','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553378','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:01AM','MA:2
SELECT '358981973','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553297','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 12:57AM','MA:2
SELECT '358982004','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553297','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 12:58AM','MA:2
SELECT '360906498','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020607','',
SELECT '359837936','PlantMaintenance','268','ManDATA','300','PLADM','00002','ManDATA','338','CA','Madera','[NONE]','29610978','PlantMaintenance','268','Mylene Mabalot','2907','TitlePoint','Image-Recorded','Jul 1 2008 5:37AM','MA:2008 00020608','','','','
SELECT '359837937','PlantMaintenance','268','ManDATA','300','PLADM','00002','ManDATA','338','CA','Madera','[NONE]','29610978','PlantMaintenance','268','Mylene Mabalot','2907','TitlePoint','Image-Recorded','Jul 1 2008 5:37AM','MA:2008 00020608','','','','
SELECT '359837616','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29610949','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-R
SwePeso
New Member
New Member

--
22 Jan 2009 05:05 AM
This is the execution plan you get with my example

  |--Table Update(OBJECT@Sample AS [e]), SET@Sample.[Source] as [e].[Source] = [Expr1008]))
       |--Table Spool
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE[Expr1008]=CONVERT_IMPLICIT(varchar(50),[UserId],0)))
                      |--Stream Aggregate(GROUP BY[Bmk1000]) DEFINE[UserId]=ANY([UserId])))
                           |--Nested Loops(Inner Join, WHERE[CompanyCRN]=@Sample.[CompanyCRN] as [e].[CompanyCRN] AND [DepartmentCRN]=@Sample.[DepartmentCRN] as [e].[DepartmentCRN] AND [County]=@Sample.[County] as [e].[County] AND [OrderNo]=@Sample.[OrderNo] as [e].[OrderNo]))
                                |--Table Scan(OBJECT@Sample AS [e]))
                                |--Filter(WHERE[Expr1007]=(1)))
                                     |--Sequence Project(DEFINE[Expr1007]=row_number))
                                          |--Segment
                                               |--Sort(ORDER BY[CompanyCRN] ASC, [DepartmentCRN] ASC, [County] ASC, [OrderNo] ASC, [LogId] ASC))
                                                    |--Table Scan(OBJECT@Sample))


      logID companyCRN departmentCRN county  orderNo userID Source
----------- ---------- ------------- ------- ------- ------ --------------------------------------------------
  418504535 00021      00090         Madera  1734995   1538 1538
  358982648 PLADM      00009         Madera  [NONE]    9837 3453
  358982677 PLADM      00009         Madera  [NONE]    1234 3453
  358981973 PLADM      00009         Madera  [NONE]    3453 3453
  358982004 PLADM      00009         Madera  [NONE]    3453 3453
  360906498 PLADM      00036         Madera  [NONE]    2650 2650
  359837936 PLADM      00002         Madera  [NONE]    2907 2907
  359837937 PLADM      00002         Madera  [NONE]    2907 2907
  359837616 PLADM      00009         Madera  [NONE]    9837 3453
  359837618 PLADM      00009         Madera  [NONE]    9837 3453
  377315426 00036      00036         Madera  [NONE]    6473 6473
  358982974 PLADM      00009         Madera  [NONE]    9837 3453
SwePeso
New Member
New Member

--
22 Jan 2009 05:17 AM
DECLARE @Sample TABLE
(
[LogId] [int] NOT NULL,
[CompanyCRN] [varchar](10) NOT NULL,
[DepartmentCRN] [varchar](13) NOT NULL,
[County] [varchar](7) NOT NULL,
[OrderNo] [varchar](7) NOT NULL,
[UserId] [smallint] NOT NULL,
[Source] [varchar](50) NULL
)

INSERT @Sample
SELECT '418504535', '00021', '00090', 'Madera', '1734995', '1538', NULL UNION ALL
SELECT '358982648', 'PLADM', '00009', 'Madera', '[NONE]' , '9837', NULL UNION ALL
SELECT '358982677', 'PLADM', '00009', 'Madera', '[NONE]' , '1234', NULL UNION ALL
SELECT '358981973', 'PLADM', '00009', 'Madera', '[NONE]' , '3453', NULL UNION ALL
SELECT '358982004', 'PLADM', '00009', 'Madera', '[NONE]' , '3453', NULL UNION ALL
SELECT '360906498', 'PLADM', '00036', 'Madera', '[NONE]' , '2650', NULL UNION ALL
SELECT '359837936', 'PLADM', '00002', 'Madera', '[NONE]' , '2907', NULL UNION ALL
SELECT '359837937', 'PLADM', '00002', 'Madera', '[NONE]' , '2907', NULL UNION ALL
SELECT '359837616', 'PLADM', '00009', 'Madera', '[NONE]' , '9837', NULL UNION ALL
SELECT '359837618', 'PLADM', '00009', 'Madera', '[NONE]' , '9837', NULL UNION ALL
SELECT '377315426', '00036', '00036', 'Madera', '[NONE]' , '6473', NULL UNION ALL
SELECT '358982974', 'PLADM', '00009', 'Madera', '[NONE]' , '9837', NULL

UPDATE e
SET e.Source = s.UserID
FROM (
SELECT Source,
MIN(logID) OVER (PARTITION BY companyCRN, departmentCRN, county, orderNo) AS recID
FROM @Sample
) AS e
INNER JOIN @Sample AS s ON s.logID = e.recID

SELECT *
FROM @Sample
You are not authorized to post a reply.

Acceptable Use Policy