SQL Server Performance

Row_number() slow on large datasets

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Steph2004, May 7, 2009.

  1. Steph2004 New Member

    Hi,

    I have this query going like this:

    select * from (
    select
    id, row_number()over(partition by fk_tbl_visits_id order by id desc) as rn
    from tbl_logs
    where date between '2009-05-06 00:00:00' and '2009-05-06 23:59:00'
    ) x where rn = 1

    I want to get the first page visited by a visitor. This query works fine but gets slower when the dataset gets bigger. The query execution plan shows costly sorts and table spool operations. Is there a way to make this query faster? Is there a way to put an index on the row_number() function? I guess not but we never know...

    Is there another way of doing this while having a better performance?

    Thanks a lot for any idea

    Stephane
  2. Steph2004 New Member

    Here's a more complex example, a table and some data. See the table spools in the query execution plan.
    Thanks for any help,
    IF OBJECT_ID('TempDB..#tbl_logs','U') IS NOT NULL
    DROP TABLE #tbl_logs

    CREATE TABLE #tbl_logs(
    [date] [datetime] NOT NULL DEFAULT (getdate()),
    [id] [bigint] NOT NULL,
    [fk_tbl_visits_id] [bigint] NULL,
    [cookieNumber] [float] NULL DEFAULT ((0)),
    [nbPageViews] [int] NULL DEFAULT ((0)),
    [visitLength] [int] NULL DEFAULT ((0)),
    [isNewVisit] [bit] NULL DEFAULT ((0)),
    [isRebound] [bit] NULL DEFAULT ((1)),
    CONSTRAINT [PK_tbl_logs] PRIMARY KEY CLUSTERED
    (
    [date] ASC,
    [id] ASC
    )
    ) ON [PRIMARY]


    insert into #tbl_logs(date, id, fk_tbl_visits_id, [cookieNumber], [nbPageViews], [visitLength], [isNewVisit], [isRebound])
    SELECT 'Oct 18 2008 12:00AM','86598137','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:00AM','86598138','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:00AM','86598139','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:00AM','86598195','39864033','1.34132e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:00AM','86598140','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:01AM','86598141','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:01AM','86598142','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:05AM','86598143','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:05AM','86598144','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:05AM','86598360','39864096','1.71522e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:06AM','86598367','39864100','2.07625e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:06AM','86598368','39864101','1.81609e+009','1','0','0','1' UNION ALL
    SELECT 'Oct 18 2008 12:06AM','86598369','39864102','5.79382e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:07AM','86598145','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:07AM','86598146','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:07AM','86598147','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:07AM','86598148','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:07AM','86598149','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:08AM','86598427','39864125','1.98e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:08AM','86598429','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:11AM','86598430','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:11AM','86598431','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:11AM','86598150','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:12AM','86598151','39864024','7.60646e+008','15','703','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:12AM','86598432','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:12AM','86598433','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:14AM','86598434','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:15AM','86599083','39864208','1.84229e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:15AM','86598435','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:16AM','86598436','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:16AM','86599059','39864220','1.36913e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:16AM','86598437','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:16AM','86598438','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:16AM','86598439','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:18AM','86599110','39864251','8.18458e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:19AM','86598440','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:19AM','86598441','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:20AM','86599054','39864290','2.14484e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:22AM','86599075','39864312','1.67987e+009','4','473','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:22AM','86599073','39864312','1.67987e+009','4','473','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:23AM','86598442','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:23AM','86599085','39864312','1.67987e+009','4','473','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:23AM','86598443','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:26AM','86598444','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:27AM','86598445','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:27AM','86598446','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:27AM','86598447','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:28AM','86598448','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:28AM','86598449','39864127','1.21004e+009','21','1221','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:29AM','86599092','39864380','1.23927e+009','1','0','0','1' UNION ALL
    SELECT 'Oct 18 2008 12:30AM','86599071','39864312','1.67987e+009','4','473','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:30AM','86599067','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:30AM','86599096','39864392','1.37347e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:30AM','86599057','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:32AM','86599065','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:32AM','86599078','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:33AM','86599053','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:33AM','86599051','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:33AM','86600475','39864415','1.84378e+009','6','288','1','0' UNION ALL
    SELECT 'Oct 18 2008 12:34AM','86600479','39864415','1.84378e+009','6','288','1','0' UNION ALL
    SELECT 'Oct 18 2008 12:34AM','86599061','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:34AM','86600460','39864424','1.61507e+009','1','0','0','1' UNION ALL
    SELECT 'Oct 18 2008 12:35AM','86600458','39864415','1.84378e+009','6','288','1','0' UNION ALL
    SELECT 'Oct 18 2008 12:36AM','86600467','39864438','1.22812e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:37AM','86600471','39864415','1.84378e+009','6','288','1','0' UNION ALL
    SELECT 'Oct 18 2008 12:37AM','86600491','39864415','1.84378e+009','6','288','1','0' UNION ALL
    SELECT 'Oct 18 2008 12:37AM','86600504','39864444','1.14802e+008','1','0','0','1' UNION ALL
    SELECT 'Oct 18 2008 12:38AM','86600473','39864415','1.84378e+009','6','288','1','0' UNION ALL
    SELECT 'Oct 18 2008 12:47AM','86600533','39864537','2.05814e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:49AM','86600509','39864559','1.17807e+009','1','0','0','1' UNION ALL
    SELECT 'Oct 18 2008 12:49AM','86599064','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:51AM','86600462','39864582','1.8943e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:52AM','86600465','39864588','6.66527e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:52AM','86600489','39864590','3.71447e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 12:52AM','86599087','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 12:55AM','86600499','39864618','8.5501e+008','1','0','0','1' UNION ALL
    SELECT 'Oct 18 2008 12:58AM','86599063','39864391','1.21004e+009','10','1648','0','0' UNION ALL
    SELECT 'Oct 18 2008 1:01AM','86600495','39864683','1.47354e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 1:05AM','86600463','39864740','1.93774e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 1:06AM','86600501','39864749','2.1049e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 1:10AM','86600481','39864779','1.07581e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 1:10AM','86600483','39864786','5.25334e+008','3','208','1','0' UNION ALL
    SELECT 'Oct 18 2008 1:12AM','86600485','39864786','5.25334e+008','3','208','1','0' UNION ALL
    SELECT 'Oct 18 2008 1:14AM','86600487','39864786','5.25334e+008','3','208','1','0' UNION ALL
    SELECT 'Oct 18 2008 1:19AM','86600528','39864869','1.39363e+009','2','250','0','0' UNION ALL
    SELECT 'Oct 18 2008 1:23AM','86600497','39864909','2.77185e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 1:23AM','86600506','39864869','1.39363e+009','2','250','0','0' UNION ALL
    SELECT 'Oct 18 2008 1:24AM','86600493','39864924','3.50627e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 1:34AM','86603730','39865029','1.47354e+008','1','0','0','1' UNION ALL
    SELECT 'Oct 18 2008 1:40AM','86603678','39865080','2.11723e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 1:42AM','86603748','39865109','1.86172e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 1:49AM','86603669','39865159','9.05993e+008','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 1:54AM','86603714','39865243','1.08842e+009','1','0','1','1' UNION ALL
    SELECT 'Oct 18 2008 2:01AM','86603762','39865248','1.84313e+009','3','35','1','0' UNION ALL
    SELECT 'Oct 18 2008 2:02AM','86603717','39865248','1.84313e+009','3','35','1','0' UNION ALL
    SELECT 'Oct 18 2008 2:02AM','86603760','39865248','1.84313e+009','3','35','1','0' UNION ALL
    SELECT 'Oct 18 2008 2:04AM','86603704','39865269','1.38991e+009','4','1114','1','0' UNION ALL
    SELECT 'Oct 18 2008 2:08AM','86603673','39865269','1.38991e+009','4','1114','1','0' UNION ALL
    SELECT 'Oct 18 2008 2:08AM','86603702','39865269','1.38991e+009','4','1114','1','0' UNION ALL
    SELECT 'Oct 18 2008 2:22AM','86603700','39865269','1.38991e+009','4','1114','1','0'

    select count(distinct cookieNumber) as nbvisitors,
    count(distinct fk_tbl_visits_id) as nbVisits,
    sum(nbPageViews) as nbPageViews,
    avg(visitLength) as visitLength,
    sum(cast(isNewVisit as tinyint)) as nbNewVisits,
    sum(cast(isRebound as tinyint)) as nbRebounds
    from (
    select
    cookieNumber,
    fk_tbl_visits_id,
    nbPageViews,
    visitLength,
    isNewVisit,
    isRebound,
    row_number() over(partition by fk_tbl_visits_id order by id desc) as rn
    from #tbl_logs
    where date between '2008-10-06 00:00:00' and '2009-05-06 23:59:00'
    ) x where rn = 1


    drop table #tbl_logs

Share This Page