Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query Performance - Advice needed on small query -- plan attached Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 3:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:51 AM
Points: 22, Visits: 84
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



  Post Attachments 
select upload for amazon query.sqlplan (11 views, 70.79 KB)
Post #1395896
Posted Wednesday, December 12, 2012 3:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1395898
Posted Wednesday, December 12, 2012 3:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:51 AM
Points: 22, Visits: 84
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.
Post #1395902
Posted Wednesday, December 12, 2012 3:51 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,900, Visits: 7,138
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"
Post #1395909
Posted Wednesday, December 12, 2012 3:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:51 AM
Points: 22, Visits: 84
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

Post #1395911
Posted Wednesday, December 12, 2012 4:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,900, Visits: 7,138
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"
Post #1395914
Posted Wednesday, December 12, 2012 4:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 15, 2013 10:51 AM
Points: 22, Visits: 84
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
Post #1395921
Posted Thursday, December 13, 2012 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1396191
Posted Thursday, December 13, 2012 7:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:28 AM
Points: 946, Visits: 2,975
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
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1396197
Posted Thursday, December 13, 2012 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1396203
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse