February 3, 2010 at 2:38 pm
EdA ROC (2/3/2010)
We haven't been on the same page for a while. I was using this table as a simple example and you've been looking at it too literally.
I've been looking at exactly what you've been posting, confused as it has been leaving me. I do not have time to guess what someone really means or to guess whether they're being literal, figurative, metaphorical or something else.
the indexes are of no benefit to the ORDER clause?
They can be, but it's not always easy, not always possible
Would your recommendation be to create indexes:
1. RowID - Clustered <-- we covered this before
2. Activity - Non-clustered <-- because most frequently used
Might this be of value if the table were very large?:
3. UserID - Non-clustered <-- because it's often used
Those'll do
There are 22 indexes, they are all NOT clustered. If I understand your article correctly I would have expected to see 1 clustered index on Order_No. Is my thinking correct?
Maybe. I can't tell you where the cluster should be though.
I'd honestly be more concerned about 22 indexes. Assuming the order table is requently inserted into (and one would hope that it is), then those indexes may be hindering insert/update/delete.
Are they all necessary?
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
February 4, 2010 at 8:35 am
In case you missed this note - This database is for an ERP app created by a 3rd party vendor, not us. So, we are stuck with their creation. Outside of petitioning them to clean things up.
Maybe. I can't tell you where the cluster should be though.
I'd honestly be more concerned about 22 indexes. Assuming the order table is requently inserted into (and one would hope that it is), then those indexes may be hindering insert/update/delete.
Are they all necessary?
FYI - Frequent inserts? There were almost 7,000 orders (records) entered the past 2 years.
Am I correct to understand, from reading your article, that one index should be clustered? The one column that would be most frequently referenced in WHERE and ORDER? In this table, orders, it would be order_no.
I looked at Manage Indexes and recorded all the indexes and their columns.
They are listed below - not necessarily for you to scrutinize, but, I will make a couple comments:
I don't think this should be: job_number is indexed twice (xie7orders and xie9orders)
The indexes at the bottom look like they were created for reports that have been created, and not reports that would be frequently used - by any customer, much less by many customers using this ERP app. It looks like the report programmer added these so the reports run faster. Probably not a good idea to create the indexes?
All right. Once more, Thank you for your time and patience, I really do appreciate it.
I think this is a good place to end this thread, otherwise, I'd keep picking your brains.
[font="Courier New"]xie0orders.......completion_flg, order_no
xie10orders......cust_po_no.edi_inb_no.edi_po_line_no
xie10_orders.....proj_no
xie1orders.......cscode.item_no
xie2orders.......cscode.order_no
xie3orders.......whsncode
xie4orders.......completion_flg
xie5orders.......due_date
xie6orders.......item_no
xie7orders.......job_number
xie8orders.......order_date
xie9orders.......job_number
xpkorders........order_no
xpltnoorders.....plt_no
xieorders_rowid..rowid
xie11orders......form_no.completion_flg
xie20orders......order_no.plt_no.cscode.cust_po_no.pricing_meth.sqft_per_unit.exp_set_flg.qty_meth.proj_no.ord_price
xie21orders......order_no.item_no.cscode.for_invt_flg.whsncode.plt_no
xie22orders......order_no.due_date.completion_flg.wwhsncode.for_invt_flg
xie24orders......plt_no.order_no.cscode.cust_po_no.pricing_meth.sqft_per_unit.exp_set_flg.qty_meth.proj_no.ord_price.due_date_cd.special_des...
xie25orders......completion_flg.cscode.item_no.for_invt_flg.pending_rso
xie26orders......order_no.completion_flg.for_invt_flg.plt_no.pending_rso.order_date[/font]
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply