Table Scan

  • I tried to create an index on the view but it wouldn't let me - I can't remember the msg I got. Is this what you are recommending? If so, I'll try it again on MOnday and report the message.

  • No... I'm not recommending trying to add an index to a view. I'm recommending that you build an "indexed view" which, yes, has an index on it, but it's basically a "materialized" view. There are certain rules which must be followed to build one. Look for it in Books Online.

    I'll also warn that they're not always appropriate but think it may be of some help here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I can research how to do this. In essence, just replace my current view by creating an indexed view?

  • Rog, to help you understand what Jeff is seeing, if you look at the graphical execution plan, the first upper right componenets are an Index Scan on RT_TRIP and the Stream Aggregate. These components are your View computing the necessary totals (twice, look again below the 64% cost Table Scan, for some reason it decided a Table Spool wasn't a good idea) to do your join limitations.

    If these are pre-processed and stored as Jeff mentioned, you'd simply have an Index Scan off the view and would only be dealing with the 131k rows that are the result of that view, instead of the 986k that come off the table first.

    From a cost perspective, you'd take probably around 8-9% off the query price. From a time perspective it's worth the experiment. Just remember, it'll slow updates/inserts down to the main RT_TRIP table because the view needs to be updated alongside it.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Rog Saber (3/5/2011)


    I can research how to do this. In essence, just replace my current view by creating an indexed view?

    Yes. See this page for the necessary rules:

    http://msdn.microsoft.com/en-us/library/aa933148(v=sql.80).aspx

    You'd essentially create your view with the following change:

    CREATE VIEW [dbo].[RTV_CURRENT_TRIP] (car_init, car_no, maxMYTABLEshipdatetime)

    WITH SCHEMABINDING

    AS

    SELECT RT_TRIP.car_init, RT_TRIP.car_no, max(RT_TRIP.ship_date_time) maxMYTABLEshipdatetime

    FROM RT_TRIP

    GROUP BY RT_TRIP.car_init,

    RT_TRIP.car_no

    and then create a unique clustered index on it:

    CREATE UNIQUE CLUSTERED INDEX idx_rtv_current_trip ON rtv_current_trip ( car_init, car_no, MaxMyTableShipDateTime)


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Rog Saber (3/5/2011)


    I can research how to do this. In essence, just replace my current view by creating an indexed view?

    Yes. And Craig did a good job of explaining why.

    Here's another link that explains the "why's" and "how's" for performance reasons in 2008.

    http://msdn.microsoft.com/en-us/library/ms187864.aspx

    And yet another which very clearly explains the "how's" and requirements.

    I'll also tell you that if you can't write and indexed view that meets the requirements or that the indexed view slows down your inserts way too much, then you can still improve performance by materializing the view as a Temp Table which would also bring "Divide'n'Conquer" methods into play which have performance advantages themselves.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Here's where I created the view OK:

    CREATE VIEW [dbo].[RTV_CURRENT_TRIP] (car_init, car_no, maxRTTRIPshipdatetime)

    WITH SCHEMABINDING

    AS

    SELECT RT_TRIP.car_init, RT_TRIP.car_no, max(RT_TRIP.ship_date_time) maxRTTRIPshipdatetime

    FROM dbo.RT_TRIP

    GROUP BY RT_TRIP.car_init,

    RT_TRIP.car_no

    But I get an error when trying to create a view on the index:

    CREATE UNIQUE CLUSTERED INDEX idx_rtv_CURRENT_TRIP ON RTV_CURRENT_TRIP ( car_init, car_no, maxRTTRIPshipdatetime)

    Cannot create index on view "RTV_CURRENT_TRIP" because it uses aggregate "MAX". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

  • Rog Saber (3/7/2011)


    Here's where I created the view OK:

    CREATE VIEW [dbo].[RTV_CURRENT_TRIP] (car_init, car_no, maxRTTRIPshipdatetime)

    WITH SCHEMABINDING

    AS

    SELECT RT_TRIP.car_init, RT_TRIP.car_no, max(RT_TRIP.ship_date_time) maxRTTRIPshipdatetime

    FROM dbo.RT_TRIP

    GROUP BY RT_TRIP.car_init,

    RT_TRIP.car_no

    But I get an error when trying to create a view on the index:

    CREATE UNIQUE CLUSTERED INDEX idx_rtv_CURRENT_TRIP ON RTV_CURRENT_TRIP ( car_init, car_no, maxRTTRIPshipdatetime)

    Cannot create index on view "RTV_CURRENT_TRIP" because it uses aggregate "MAX". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

    Well now, that's highly annoying, I may have found a difference in SQL 2k5 Express and a for-pay properly CU'd installation. Apply doesn't allow an index either, so that shoots my workaround in the foot.

    Hopefully Jeff can come back with a recommendation on that, I'm not sure if there is a workaround to the MIN/MAX/APPLY restrictions. I'll keep thinking on it myself.

    In the meanwhile, have you had a chance to get a script of the indexes? This is only 10% of your query and we should also take a look at the rest of the build that was being discussed above. I don't want us to get horribly sidetracked in something that's merely a piece of the puzzle.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Here are the indexes:

    However, just running the primary select statment just then took me 1:17 to return 14,765 rows. BUt, again, am I barking up the wrong tree if I am at the mercy of how slow the server is? If I run this query at night (no users on the DB) it takes less than 10 seconds.

    SELECT DISTINCT RT_TRIP.car_init,

    RT_TRIP.car_no,

    RT_TRIP.ship_date_time,

    RT_TRIP.cust_no,

    RT_TRIP.route_code,

    RT_TRIP.fleet_id,

    RT_TRIP.business_group,

    RT_TRIP.orig_city,

    RT_TRIP.orig_state,

    RT_TRIP.dest_city,

    RT_TRIP.dest_state,

    RT_TRIP.car_status,

    RT_TRIP.bol_no,

    RT_TRIP.trip_no,

    RT_TRIP.cust_po_no,

    RT_TRIP.csr_no,

    RT_TRIP.sales_rep,

    RT_TRIP.hot_list_flag,

    RT_TRIP.trip_ref1,

    RT_TRIP.trip_ref2,

    RT_TRIP.trip_ref3,

    RT_TRIP.trip_ref4,

    RT_TRIP.dest_arr_date_time,

    RT_TRIP.dest_arr_type,

    RT_TRIP.cust_dlv_date_time,

    RT_TRIP.cust_dlv_type,

    RT_TRIP.cust_rel_date_time,

    RT_TRIP.cust_rel_type,

    RT_TRIP.trip_cls_date_time,

    RT_TRIP.trip_cls_type,

    RT_TRIP.orig_eda_eta_cust,

    RT_TRIP.orig_eda_eta_origin,

    RT_TRIP.trip_ref5,

    RT_TRIP.trip_ref6,

    RT_TRIP.trouble_yorn,

    RT_TRIP.free_runner as free_runner,

    RT_TRIP.divert_yorn,

    RT_TRIP.shop_move,

    RT_TRIP.trip_status,

    RT_TRIP.rqst_dlv_date_time,

    datediff(day,RT_TRIP.trip_cls_date_time, getdate()),

    datediff(dd,getdate(),RT_TRIP.orig_eda_eta_origin),

    datediff(dd, getdate(),RT_TRIP.orig_eda_eta_cust)

    FROM RT_TRIP,

    RTV_CURRENT_TRIP

    WHERE

    ( RT_TRIP.car_init = RTV_CURRENT_TRIP.car_init ) and

    ( RT_TRIP.car_no = RTV_CURRENT_TRIP.car_no ) and

    ( RT_TRIP.ship_date_time = RTV_CURRENT_TRIP.maxRTTRIPshipdatetime ) and

    (( RT_TRIP.free_runner = 'Y') and

    ( RT_TRIP.trip_status = 'O') OR ( RT_TRIP.free_runner = 'N'))

    CREATE NONCLUSTERED INDEX [RT_TRIP_FLEET_PROC_IDX3] ON [dbo].[RT_TRIP]

    (

    [trip_status] ASC,

    [trip_cls_type] ASC,

    [bad_order_days] ASC,

    [divert_yorn] ASC,

    [trip_cls_date_time] ASC,

    [fleet_id] ASC,

    [business_group] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE UNIQUE NONCLUSTERED INDEX [RT_TRIP_IDX] ON [dbo].[RT_TRIP]

    (

    [car_init] ASC,

    [car_no] ASC,

    [ship_date_time] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_ORDER_NO_IDX] ON [dbo].[RT_TRIP]

    (

    [trip_no] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_ROUTE_CODE_IDX] ON [dbo].[RT_TRIP]

    (

    [route_code] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_STATUS_IDX1] ON [dbo].[RT_TRIP]

    (

    [trip_status] ASC,

    [sight_code] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_STATUS_IDX2] ON [dbo].[RT_TRIP]

    (

    [trip_status] ASC,

    [car_status] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_STATUS_IDX3] ON [dbo].[RT_TRIP]

    (

    [car_init] ASC,

    [car_no] ASC,

    [trip_status] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_FLEET_PROC_IDX2] ON [dbo].[RT_TRIP]

    (

    [trip_status] ASC,

    [trip_cls_type] ASC,

    [bad_order_days] ASC,

    [divert_yorn] ASC,

    [trip_cls_date_time] ASC,

    [business_group] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_FLEET_PROC_IDX1] ON [dbo].[RT_TRIP]

    (

    [trip_status] ASC,

    [trip_cls_type] ASC,

    [bad_order_days] ASC,

    [divert_yorn] ASC,

    [trip_cls_date_time] ASC,

    [fleet_id] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_ETA_PROC_STATUS_IDX] ON [dbo].[RT_TRIP]

    (

    [trip_status] ASC,

    [eta_proc_date] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_CUST_NO_IDX] ON [dbo].[RT_TRIP]

    (

    [cust_no] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_CLS_STATUS_ROUTE_IDX] ON [dbo].[RT_TRIP]

    (

    [route_code] ASC,

    [trip_status] ASC,

    [trip_cls_date_time] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_TRIP_BOL_NO_IDX] ON [dbo].[RT_TRIP]

    (

    [bol_no] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_FLEET_ID_IDX] ON [dbo].[RT_TRIP]

    (

    [fleet_id] ASC,

    [sight_code] ASC,

    [trip_status] ASC,

    [car_status] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [RT_BUSINESS_GROUP_IDX] ON [dbo].[RT_TRIP]

    (

    [business_group] ASC,

    [trip_status] ASC,

    [sight_code] ASC,

    [car_status] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

  • Rog Saber (3/7/2011)


    Here are the indexes:

    However, just running the primary select statment just then took me 1:17 to return 14,765 rows. BUt, again, am I barking up the wrong tree if I am at the mercy of how slow the server is? If I run this query at night (no users on the DB) it takes less than 10 seconds.

    Glad you were able to get the indexes, that should help. You could be at the mercy of a lot of things, including drive concurrency. Is your Data SAN stored, and if so, can you doublecheck with your storage admins what you're sharing physical space with? You might be competing with something completely out of your control just for Disk I/O.

    Also, during the day, run this query and check the wait_state for the SPID and see what you get. It'd help to know why it thinks it's stuck.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • "Also, during the day, run this query and check the wait_state for the SPID and see what you get. It'd help to know why it thinks it's stuck. "

    I apologize but I do not know how to do this. I searched for a "howto" and couldn't find what I think I need.

    Thank you for all of your input.

  • Rog Saber (3/7/2011)


    "Also, during the day, run this query and check the wait_state for the SPID and see what you get. It'd help to know why it thinks it's stuck. "

    I apologize but I do not know how to do this. I searched for a "howto" and couldn't find what I think I need.

    Thank you for all of your input.

    Ah, not a problem.

    Alright, in SSMS (Management Studio), on the status bar on the bottom you'll see a section with your login name (most likely domain\login), and then after that in parenthesis you'll see a number. That's your SPID. System somethingorother ID. It's basically your connection #.

    Now, do fast select * from master..sysprocesses.

    You'll see a column called SPID... the one that matches your # matches what you're doing.

    What you'll want to do (for a quick test, this isn't comprehensive) is setup the query in one window, and kick it off. Then switch to another window that does a

    SELECT spid, blocked, waittime, lastwaittype FROM master..sysprocesses where SPID = <yourSPIDHere>

    Hit F5 every second or two and see if anything shows up. My guess is you're primarily dealing with PAGEIOLATCH waits, which are waits for the Physical Drive I/O, mostly because of the data volume.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • When I do this....

    select * from master..sysprocesses

    I just get one row.

    When I do this:

    SELECT spid, blocked, waittime, lastwaittype FROM master..sysprocesses

    where SPID = 81

    I get:

    8100MISCELLANEOUS

    I'm 'pretty' sure their aren't many (if any) users hitting that DB (the test DB I am using). However, the server has many more DB's that are on it that are taking the resources - that is my vest guess.

  • You only get one row when doing a select * from master..sysprocesses? You are SA on the system, right?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • No, I am just a developer w/ dbo. Maybe that makes a difference?

Viewing 15 posts - 16 through 30 (of 38 total)

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