May 12, 2008 at 2:18 pm
Below is a simple query that is taking 2 hour 30 min to run. I created clustered index on rowid and Non Clustered Index on BillingType and CobrandCode columns. But there is no luck.
In the execution plan before the indexes it's a full table scan and after I created the indexes it is giving Clustered index seek. Please give me suggestion to optimize this.
Select
RowID,
DID,
AccountDID,
ContractNumber,
InsertionOrder,
CreateDT,
ActivateDT,
CreatedByID,
ModifyDT,
ModifiedByID,
Comment,
CompanyName,
StatusCode,
TotalRetailAmount,
TotalActualAmount,
DeletedRetailAmount,
DeletedActualAmount,
BillingType,
BillingCycle,
InvoiceDeliveryMethod,
ValidateStatus,
ValidateDT,
ValidatedByID,
CancelStatus,
CancelDT,
CancelledByID,
CancelReasonCode,
AllowRepChange,
TotalCreditedAmount,
SystemModifiedDT,
RenewableFlg,
RenewalDuration,
RenewalChangedByID,
RenewalChangeDT,
RenewedFlg,
DiscountPct,
CoBrandCode,
TrackingCode,
TrialDealFlg,
BizOwnerCode,
CustomerRenewalFlg,
CCAddress,
CCCity,
CCCountry,
CCExpiration,
CCName,
CCNumber,
CCState,
CCType,
CCZip,
ContactName,
ContactPhone,
PaymentMethod,
BillTrackingCode,
BillEmail,
BillPhone,
BillName,
BillAddress1,
BillAddress2,
BillCity,
BillState,
BillZip5,
BillZip4,
BillCountry,
BillProvince,
BillPostalCode,
AccountLevel,
MasterAccountDID,
InvoicingFlg,
ExternalAcctID,
BillingInterval,
ProductStartDT,
ProductEndDT,
PackageDID,
ContactAddress1,
ContactAddress2,
ContactCity,
ContactState,
ContactZip,
ContactCountry,
ContactEmail,
ContactFax
From ARBkup.dbo.HHContract200804
where BillingType <> 'KNEE' and CobrandCode <> 'CBNH_KNEE'
May 12, 2008 at 2:24 pm
Is this selecting from a really wide table, or is "HHContract200804" a view?
How many rows is it dealing with (how many in the table/tables, and how many are actually returned)?
Is it running into locking issues? (Does anything else access this same data at the same time?)
Those are the questions I'd start with.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2008 at 2:32 pm
In addition to GSquared's questions/comments:
Do you have to return all the columns?
I am actually surprised you are getting a clustered index seek since you are using "<>" which many times will at best allow for a scan. Is there any way to change to an "="?
Depending on the other queries you have against this table you would probably be better off with the clustered index on BillingType and CoBrandCode. Neither of the indexes you have currently is really doing anything for you. Having a non-clustered index on BillingType and CoBrandCode won't help with the "<>" and the number of columns you are returning limits it as well since it is not a covering index.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2008 at 2:54 pm
HHContract200804 is a table and the total no of rows in this table are 2230050..
May 12, 2008 at 4:10 pm
check to see if the select is being blocked.
as a quick test add WITH (NOLOCK) hint on the Table
Then, when the query runs, look at IO Performance in PerfMon
I would guess that Disk Queue is just pegged.
the IO subsystem is probably just having a hard time serving up the data.
Do you know how your disks are laid out for this DB ?
RAID LEVEL?
Number of Physical Disks?
File locations, etc etc etc
Greg J
Gregory A Jackson MBA, CSM
May 12, 2008 at 9:22 pm
My guess would be that returning nearly 2 million rows of a really wide table to the client has driven the client into the "swap file" mode, even if the client is QA. Either that or someone fogot to turn on "full duplex" on the routers on the network. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 10:13 pm
You aren't telling us something. Before the indexes were added, it performed a table scan (which is to be expected, it has to read every row to know if it meets the criteria). After the indexes are added you say it does a clustered index seek - but the clustered index is on rowid which isn't in the query, so it would never do a seek unless it was using the other index as well.
Unless the vast majority of records are excluded by your nonclustered index, it won't get used - I suspect it is not very selective at all. You can look at the statistics for selectivity and cardinality information.
May 14, 2008 at 7:28 am
1) Several people here have a mis-conception about what a clustered index scan is. It IS a table scan in this case.
2) Jeff is probably right (as usual) - a big part of this problem is the return of 2M FAT rows (I think that was the number) back to the client. HUGE amounts of network traffic, memory use on client, etc. OP - set your client (SSMS??) to Discard Results (query options dialog) to see how long it takes to send results without actually bothering to display them. If you actually want to KEEP the results, put your query into a text file and use osql to pipe the results to a disk file. See BOL. It will be something like this: osql -E -i input.sql -o output.txt
3) As others stated, unless the values in the where clause are appropriately distributed (unlikely but possible - haven't seen the data spread) and index (or indexes) will not help this query.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 14, 2008 at 7:44 am
TheSQLGuru (5/14/2008)
1) Several people here have a mis-conception about what a clustered index scan is. It IS a table scan in this case.
I agree with your other 2 points, but in this case the OP stated it was a Clustered Index Seek, I also think all of us know that the OP probably meant scan as I mentioned in my first post.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 15, 2008 at 10:55 am
...Non Clustered Index on BillingType and CobrandCode columns...
Sounds like MOST rows are being returned. An Index usually only helps if rows are ~95+% unique.
Anytime you're returning a ton of rows on a query like this, you're just gonna have to suck it up or narrow it down more.
2+hours sounds like a lot of time for just 2mil rows; Memory swapping on server/client?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply