SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Performance - Advice needed on small query -- plan attached


Query Performance - Advice needed on small query -- plan attached

Author
Message
Michael T2
Michael T2
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 90
I'm not sure why it is using index scans and not seeks. I have indexes on all the tables.


SELECT T.TXID, T.TXAMAZONORDERNUM,T.TXSENDTOAMAZON,TSN.TSNTRACKINGNUMBER,
'CARRIER' = CASE
WHEN ts.ship_typeid = 15 then 'UPS' else ST.CARRIER end,
'DESCRIPTION' = case
when ts.ship_typeid = 15 then 'Mail Innovations' else ST.DESCRIPTION end
FROM TRANSACTIONS T with (nolock)
INNER JOIN TRANSACTION_SHIPPING_NOTES TSN with (nolock) ON T.TXID = TSN.TXID
INNER JOIN TRANSACTION_SHIPPING TS with (nolock) ON T.TXID = TS.TXID
INNER JOIN SHIP_TYPE ST with (nolock) ON TS.SHIP_TYPEID = ST.SHIP_TYPEID
inner join transaction_notes tn with (nolock) on t.txid = tn.txid
WHERE (T.TXSALESCHANNEL = 'Amazon' or txpmtmethod = 'CBA')
and txsendtoamazon = 1 and
tn.tnStage='Shipped' and
txamazonordernum is not null
AND coalesce(txSubtype,'') <> 'BACKORDER'



With this there is a index scan on transaction_shipping & transaction_shipping_notes.
Also this takes alot of cpu usage and it seems like it shouldn't for such a simple query.
It only returns around 200 records.

I have attached the execution plan.
Thanks for all the help
Attachments
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63588 Visits: 17966
Can you post the actual table and index definitions? Both of those index scans look like your stats might be stale. They both have an estimated row count of about 1.1M and actual row counts of about 16K.

As a side note...why all the NOLOCK hints?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Michael T2
Michael T2
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 90
how do i upload the table definition, it says does not allow .sql as attachments.

Is that not good to use nolocks? I use them alot on queries that i do not need total accuracy.
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12454 Visits: 7444
Simply post the DDL into your reply, and if you want to get all fancy place it between the [ code ="sql"] INSERT TSQL HERE [/ code]

Of course remove the spaces between the [ Code and / code

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Michael T2
Michael T2
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 90
CREATE TABLE [dbo].[transactions](
[txID] [int] IDENTITY(1,1) NOT NULL,
[storeNumber] [int] NULL,
[txType] [varchar](10) NULL,
[txStartDate] [datetime] NULL,
[txEndDate] [datetime] NULL,
[txReferrer] [varchar](50) NULL,
[txStatus] [varchar](50) NULL,
[cuID] [int] NULL,
[adrIDShip] [int] NULL,
[adrIDBill] [int] NULL,
[returnfor] [int] NULL,
[txParentID] [int] NULL,
[txRef] [int] NULL,
[txBackorder] [int] NULL,
[txExchange] [int] NULL,
[txExchangeParentID] [int] NULL,
[txHighRiskCheckoutStatus] [varchar](10) NULL,
[QuoteID] [int] NULL,
[ToBePrinted] [tinyint] NULL,
[txEbayCheckout] [tinyint] NULL,
[txStorePickup] [int] NULL,
[txSalesChannel] [varchar](10) NULL,
[txSendToAmazon] [tinyint] NULL,
[txAmazonOrderNum] [varchar](100) NULL,
[txPCMagSentDate] [datetime] NULL,
[txPmtMethod] [varchar](6) NULL,
[txDatePmtReceived] [datetime] NULL,
[txDatePmtFailed] [datetime] NULL,
[txDatePmtCleared] [datetime] NULL,
[PayPalTranID] [varchar](20) NULL,
[HoldOrderShipment] [datetime] NULL,
[HoldOrderShipmentReason] [varchar](500) NULL,
[txNotes] [varchar](2000) NULL,
[PayPalPending] [varchar](50) NULL,
[ResetDate] [datetime] NULL,
[ResetPmtInfoAction] [tinyint] NULL,
[ResetPmtTypesToOffer] [varchar](100) NULL,
[ResetShippingAddressAction] [tinyint] NULL,
[txPaypalReminder1SentDate] [datetime] NULL,
[txPaypalReminder2SentDate] [datetime] NULL,
[NetDollarsPaid] [money] NULL,
[PayPalPaymentType] [varchar](50) NULL,
[checkout_adrID] [int] NULL,
[paypal_adrid] [int] NULL,
[txInvoiceDate] [datetime] NULL,
[txAmazonFeedbackReminderSentDate] [datetime] NULL,
[txAmazonDoNotSendFeedbackFlag] [tinyint] NULL,
[txClarkPhotoSentDate] [datetime] NULL,
[storeQuoteID] [int] NULL,
[txAmazonPaidDate] [datetime] NULL,
[txAmazonCommissionPaid] [money] NULL,
[CGIRemoteAddress] [varchar](100) NULL,
[ebay_adrid] [int] NULL,
[txSubType] [varchar](15) NULL,
[txRefundReason] [varchar](10) NULL,
[PickerMfrGroup] [varchar](15) NULL,
[PickerItemID] [int] NULL,
[InUseByUserID] [int] NULL,
[BingCashBackOrder] [tinyint] NULL,
[BingCashBackProcessDate] [datetime] NULL,
[BingCashBackAmount] [money] NULL,
[EbayMarkedPaidDate] [datetime] NULL,
[txSendToExternal] [tinyint] NULL,
[txExternalOrderNum] [varchar](50) NULL,
[FBA] [tinyint] NULL,
[WebsiteOrderNum] [int] NULL,
[PickerLocGroup] [varchar](10) NULL,
[PickerLocSort] [varchar](3) NULL,
[pickerloc] [varchar](5) NULL,
[EligForSIQ] [tinyint] NULL,
[OrigPayPalTranID] [varchar](20) NULL,
[txCJCancelSentDate] [datetime] NULL,
CONSTRAINT [PK_transactions] PRIMARY KEY CLUSTERED
(
[txID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[transaction_shipping_notes](
[txID] [int] NOT NULL,
[tsnTrackingNumber] [varchar](50) NULL,
[tsnNotes] [varchar](4000) NULL,
[tsnShippingMailSentDate] [datetime] NULL,
[tsnSendShippingMailFlag] [bit] NULL
) ON [PRIMARY]

GO



CREATE TABLE [dbo].[transaction_shipping](
[txID] [int] NOT NULL,
[liID] [int] NOT NULL,
[stID] [int] NULL,
[srID] [int] NULL,
[shippingID] [int] NOT NULL,
[shippingCost] [money] NULL,
[shippingNoticeParsed] [bit] NULL,
[shippingNotice] [varchar](2000) NULL,
[ship_typeid] [int] NULL,
[ActualShippingCost] [money] NULL,
[PostmarkDate] [datetime] NULL,
[InsuranceFee] [money] NULL,
[shippingOverrideFlag] [tinyint] NULL,
[AmazonShipType] [varchar](10) NULL,
[ActualDimWt] [int] NULL,
[ActualShippingWeight] [int] NULL,
[HighPriorityFlag] [tinyint] NULL,
[ItemValueOverrideForInsurance] [money] NULL,
[MailInnovationsFlag] [tinyint] NULL,
[ship_cost] [money] NULL,
[insur_cost] [money] NULL,
[EbayMarkedShippedDate] [datetime] NULL,
[ExternalShipType] [varchar](35) NULL,
[ReqSigFlag] [tinyint] NULL,
[ShippingComputer] [tinyint] NULL,
[LateShipment] [datetime] NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[transaction_notes](
[tnID] [int] IDENTITY(1,1) NOT NULL,
[txID] [int] NULL,
[txNotes] [varchar](4000) NULL,
[tnStage] [varchar](20) NULL,
[adminID] [int] NULL,
[tnDate] [datetime] NULL,
[tnExported] [datetime] NULL,
CONSTRAINT [PK_transaction_notes] PRIMARY KEY NONCLUSTERED
(
[tnID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[ship_type](
[ship_typeid] [int] NOT NULL,
[carrier] [varchar](10) NULL,
[description] [varchar](50) NOT NULL,
[carrier_sort] [int] NULL,
[ship_type_sort] [int] NULL,
[ratetable] [varchar](50) NULL,
[source] [varchar](50) NULL,
[active] [tinyint] NULL,
[created_datetime] [datetime] NOT NULL,
[created_by] [varchar](75) NOT NULL,
[updated_datetime] [datetime] NOT NULL,
[updated_by] [varchar](75) NOT NULL,
[pickerShipSort] [int] NULL,
[pickerShipName] [varchar](40) NULL,
[RequireSignature] [tinyint] NULL,
[internal_only] [tinyint] NOT NULL,
[discount_pct] [float] NOT NULL,
[base_min] [money] NULL,
[insurance_multiplier] [float] NOT NULL,
[sig_reqd_possible] [tinyint] NOT NULL,
[sig_reqd_for_fraud] [tinyint] NOT NULL,
[isGeneric] [tinyint] NULL,
[CustomerDisplay] [varchar](50) NULL,
CONSTRAINT [IX_ship_type] UNIQUE NONCLUSTERED
(
[ship_typeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO


MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12454 Visits: 7444
The reason nearly everyone on here will chine in on the use of NOLOCK (locking hints in particular) is primary because of the byproduct of using it altogether (dirty reads/potentially bad). The most common misconception with this is using it makes things run faster (and this isn't the case). It's typically best to let SQL manage it's own locking/escalations.

In looking at your attached execution plan, it looks like you used the Tuning Advisor already (IMHO, take what that thing provides you with a grain of salt). I agree with Sean, the differences in your estimate/actual may be due to out of date statistics. Consider, updating them before running your query or completely rebuild the following indexes:
- MT_20121207_Transactions_forselectforamazon_1
- _dta_index_transaction_shipping_9_464772763__K9
- _dta_index_transaction_shipping_notes_7_660197402__K1_2

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Michael T2
Michael T2
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 90
i updated the stats, and rebuilt the indexes. Still the same large difference between the estimated and actual, and still have the index scans. Very strange
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63588 Visits: 17966
Michael T2 (12/12/2012)

Is that not good to use nolocks? I use them alot on queries that i do not need total accuracy.


Actually NOLOCK is far worse than simply dirty reads. You can get duplicate or even missing data. It can cause bugs that are impossible to reproduce and happen sporadically.

Here are a few articles that explain this hint in detail.

http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

Now I am not saying that there is not a use for this but it is very important that you understand exactly what it is doing. Often times using an isolation level (snapshot isolation) is preferred because you don't have the consistency issues that you do with NOLOCK.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Michael L John
Michael L John
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6045 Visits: 8228
Remove this from the where clause and see if you still get a scan:

AND coalesce(txSubtype,'') <> 'BACKORDER'

Then, do a search on this site for SARGABLE.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63588 Visits: 17966
Michael L John (12/13/2012)
Remove this from the where clause and see if you still get a scan:

AND coalesce(txSubtype,'') <> 'BACKORDER'

Then, do a search on this site for SARGABLE.



You can still include this check but you need to do it a little differently. First of all there is no point in using coalesce here. NULL can't be equal to anything so just remove it.


txSubType > 'BACKORDER' OR txSubType < 'BACKORDER'



That should keep it sargable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search