March 5, 2011 at 1:13 pm
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.
March 5, 2011 at 1:20 pm
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
Change is inevitable... Change for the better is not.
March 5, 2011 at 1:26 pm
I can research how to do this. In essence, just replace my current view by creating an indexed view?
March 5, 2011 at 1:30 pm
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.
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
March 5, 2011 at 1:37 pm
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)
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
March 5, 2011 at 4:56 pm
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
Change is inevitable... Change for the better is not.
March 7, 2011 at 9:46 am
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.
March 7, 2011 at 10:38 am
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.
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
March 7, 2011 at 11:37 am
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]
March 7, 2011 at 11:45 am
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.
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
March 7, 2011 at 11:52 am
"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.
March 7, 2011 at 12:08 pm
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.
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
March 7, 2011 at 12:35 pm
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.
March 7, 2011 at 1:52 pm
You only get one row when doing a select * from master..sysprocesses? You are SA on the system, right?
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
March 7, 2011 at 1:54 pm
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