May 22, 2008 at 9:42 am
I was doing Database Testing by executing Stored procedure with
Parameters. one stored procedure was executing for more than 20 mins.
I looked into Query and found a Table that was called.
When i executed Select statement against the Table, it took more than 25 mins to return the table. This table is having 45678905 Rows and there is a clustered index & Non clustered indexes on it .
Here is the script for that Table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PostedOrders_Summary](
[UploadBatchID] [int] NOT NULL,
[LineID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[OrderNum] [char](10) NOT NULL,
[OrderTypeCode] [char](5) NULL,
[OrderType] [char](20) NULL,
[PONum] [char](20) NULL,
[LineType] [char](2) NULL,
[Account] [int] NULL,
[AccountName] [varchar](40) NULL,
[ShipToAccount] [int] NULL,
[ShipToName] [varchar](40) NULL,
[ProductID] [varchar](15) NULL,
[Title] [varchar](40) NULL,
[Client] [varchar](5) NULL,
[Date] [datetime] NULL,
[SalesAmt] [money] NULL,
[SalesQty] [int] NULL,
[ReturnAmt] [money] NULL,
[ReturnQty] [int] NULL,
[ARRebillAmt] [money] NULL,
[ARRebillQty] [int] NULL,
[BillableXferAmt] [money] NULL,
[BillableXferQty] [int] NULL,
[ARCreditAmt] [money] NULL,
[ARCreditQty] [int] NULL,
[ReviewQty] [int] NULL,
[TransferQty] [int] NULL,
[ListPrice] [money] NULL,
[Discount] [decimal](18, 4) NULL,
[UnitPrice] [money] NULL,
[LineAmt] [money] NULL,
[LineQty] [int] NULL,
[Country] [varchar](5) NULL,
[CurrencyType] [char](3) NULL,
[CurrencyFactor] [decimal](18, 4) NULL,
[PrePk] [char](1) NULL,
[SalesRepID] [char](8) NULL,
[SalesRepDescription] [varchar](40) NULL,
[SalesRepRegion] [varchar](40) NULL,
[MarketSegment] [char](5) NULL,
[MarketSegmentDesc] [varchar](40) NULL,
[ClientProductID] [varchar](15) NULL,
[ClientCost] [money] NULL,
[ProductSeries] [varchar](10) NULL,
[ProductLanguage] [varchar](20) NULL,
[ShipToState] [char](2) NULL,
[BackOrd] [char](1) NULL,
[OrigInv] [varchar](10) NULL,
[Source] [char](5) NULL,
[FreightType] [char](5) NULL,
[ManAdd] [char](1) NULL,
[OrdQty] [int] NULL,
[BOQty] [int] NULL,
[Returnable] [char](1) NULL,
[Freight] [money] NULL,
[WhoPays] [char](1) NULL,
[RtnDsp] [char](5) NULL,
[RequestDate] [datetime] NULL,
[OrderDate] [datetime] NULL,
[GuaranteedOrder] [varchar](1) NULL,
[PaidUnpaid] [char](1) NULL,
[PayDate] [datetime] NULL,
[SalesOrderNum] [varchar](8) NULL,
[ReviewReasonCode] [varchar](5) NULL,
[ManualName] [varchar](40) NULL,
[ManualAttention] [varchar](40) NULL,
[ManualAddress1] [varchar](40) NULL,
[ManualAddress2] [varchar](40) NULL,
[ManualCity] [varchar](35) NULL,
[ManualState] [varchar](2) NULL,
[ManualZipCode] [varchar](9) NULL,
[ManualCountry] [varchar](3) NULL,
[PromoCode] [varchar](255) NULL,
[PubWQty] [int] NULL,
[HurtsQty] [int] NULL,
[HurtsAmt] [money] NULL,
[FulfillQty] [int] NULL,
[FulfillAmt] [money] NULL,
[RemQty] [int] NULL,
[RemAmt] [money] NULL,
CONSTRAINT [PK_PostedOrders_Summary] PRIMARY KEY NONCLUSTERED
(
[LineID] 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
SET ANSI_PADDING OFF
Thanks all for your time.
May 22, 2008 at 10:05 am
I only see a non-clustered index in what you posted. That being said - it's going to be hard to tell what is happening, without some clue as to what is being pulled out of this table.
Regardless - that table is pretty wide, and is holding 45 million rows. Pulling a lot from this table could really slow things down, just due to how big these rows are.
try posting an example of what kind of query might get run against this table, as well as the execution plan being chosen to run that query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 22, 2008 at 10:39 am
Lots of null columns. Lots. Without the query it's hard to comment, but I'll bet it's doing a table scan. Have you looked at the execution plan for the query?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2008 at 11:13 am
aspirant.dba (5/22/2008)
When i executed Select statement against the Table, it took more than 25 mins to return the table.
If you tried to retrieve the entire table it's going to take time. Retrieving 45 million wide rows, sending them across the network and displaying on the client is not a trivial operation in and of itself.
If you post the slow queries and any other indexes you have on the table, we'll be able to help more.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply