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
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 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 (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148437 Visits: 18575
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
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26800 Visits: 7488
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; Theyll drag you down to their level and beat you with experience

Michael T2
Michael T2
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 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
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26800 Visits: 7488
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; Theyll drag you down to their level and beat you with experience

Michael T2
Michael T2
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 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 (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148437 Visits: 18575
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
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: 12319 Visits: 9012
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 (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148437 Visits: 18575
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