The query I'm running is very simple as follows:
select TOP 26 * from vSearchListOpportunityItem WHERE OpIt_OpportunityId=2495 ORDER BY Prod_Name, OpIt_OpportunityItemId
The view it is pulling data from only contains only 1890 lines, which in turn pulls data from 3 tables with 821, 2560, and 1957 lines of data. In other words it's small. I have noticed that if I try and open the smallest of these tables while on a 'go slow' period it also takes around 15 minutes to return the data.
The database was originally on SQL 2000. It is the only database on this powerful quad core server.
The SQL Server CPU usage never goes above 40%, and always has free memory.
No sign of locks.
I can't figure out why such a small database is going so slow with such a simple query. Any ideas?
Depends on the view definition, one mistake in the view definition and the results is a cartesian product resulting in e.g. 821x2560x1957 (or any other variation) of the data leading to a non-wanted data explosion. If opening one table also slows down the process, make sure that there is no *sophisticated* computed column on the table which could slow down the operation.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Perhaps this 'secret' dba trick will help.
Use the following database option:
SET OPTION RUN_FASTER ON
And then, I have a bridge to sell if you are interested...
(Please post your query and the VIEW definition, as well as the unlaying table DDL, and we 'might' be able to give you more directed guidance.)
|||
> SET OPTION RUN_FASTER ON
No I tried that already.
Here is the View:
USE [CRM]
GO
/****** Object: View [dbo].[vSearchListOpportunityItem] Script Date: 08/03/2007 09:41:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vSearchListOpportunityItem] AS SELECT Prod_ProductId, Prod_Name, Prod_Description, Prod_ListPrice, Prod_ListPrice_CID, OpportunityItem.*,Opportunity.* FROM Opportunity,OpportunityItem LEFT OUTER JOIN Products ON OpIt_ProductId = Prod_ProductId WHERE OpIt_OpportunityId=Oppo_OpportunityId and OpIt_Deleted IS NULL
And the table Products (this is the table that also runs slow when returning its 821 rows):
USE [CRM]
GO
/****** Object: Table [dbo].[Products] Script Date: 08/03/2007 09:43:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[Prod_ProductId] [int] NOT NULL,
[Prod_Name] [nchar](64) NULL,
[Prod_Description] [nchar](60) NULL,
[Prod_CreatedBy] [int] NULL,
[Prod_CreatedDate] [datetime] NULL,
[Prod_UpdatedBy] [int] NULL,
[Prod_UpdatedDate] [datetime] NULL,
[Prod_Deleted] [tinyint] NULL,
[Prod_ListPrice] [numeric](24, 6) NULL,
[Prod_TimeStamp] [datetime] NULL,
[Prod_SegmentID] [int] NULL,
[Prod_ChannelID] [int] NULL,
[Prod_ListPrice_CID] [int] NULL,
[Prod_ICITEM] [nchar](24) NULL,
[Prod_ICType] [nchar](10) NULL,
[prod_nsrcategory] [nchar](40) NULL,
[prod_nsrtype] [nchar](40) NULL
) ON [PRIMARY]
Table Opportunity 2561 rows:
USE [CRM]
GO
/****** Object: Table [dbo].[Opportunity] Script Date: 08/03/2007 09:43:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Opportunity](
[Oppo_OpportunityId] [int] NOT NULL,
[Oppo_PrimaryCompanyId] [int] NULL,
[Oppo_PrimaryPersonId] [int] NULL,
[Oppo_AssignedUserId] [int] NULL,
[Oppo_ChannelId] [int] NULL,
[Oppo_Description] [nchar](40) NULL,
[Oppo_Type] [nchar](40) NULL,
[Oppo_Product] [nchar](30) NULL,
[Oppo_Source] [nchar](40) NULL,
[Oppo_Note] [ntext] NULL,
[Oppo_CustomerRef] [nchar](30) NULL,
[Oppo_Opened] [datetime] NULL,
[Oppo_Closed] [datetime] NULL,
[Oppo_Status] [nchar](40) NULL,
[Oppo_Stage] [nchar](40) NULL,
[Oppo_Forecast] [numeric](24, 6) NULL,
[Oppo_Certainty] [int] NULL,
[Oppo_Priority] [nchar](40) NULL,
[Oppo_TargetClose] [datetime] NULL,
[Oppo_CreatedBy] [int] NULL,
[Oppo_CreatedDate] [datetime] NULL,
[Oppo_UpdatedBy] [int] NULL,
[Oppo_UpdatedDate] [datetime] NULL,
[Oppo_TimeStamp] [datetime] NULL,
[Oppo_Deleted] [tinyint] NULL,
[Oppo_Total] [numeric](24, 6) NULL,
[Oppo_NotifyTime] [datetime] NULL,
[Oppo_SMSSent] [nchar](20) NULL,
[Oppo_WaveItemId] [int] NULL,
[Oppo_SegmentID] [int] NULL,
[Oppo_SecTerr] [int] NULL,
[Oppo_WorkflowId] [int] NULL,
[Oppo_LeadID] [int] NULL,
[Oppo_Forecast_CID] [int] NULL,
[Oppo_Total_CID] [int] NULL,
[oppo_scenario] [nchar](40) NULL,
[oppo_decisiontimeframe] [nchar](40) NULL,
[oppo_Currency] [int] NULL,
[oppo_TotalOrders_CID] [int] NULL,
[oppo_TotalOrders] [numeric](24, 6) NULL,
[oppo_totalQuotes_CID] [int] NULL,
[oppo_totalQuotes] [numeric](24, 6) NULL,
[oppo_NoDiscAmtSum] [numeric](24, 6) NULL,
[oppo_NoDiscAmtSum_CID] [int] NULL,
[Oppo_OrderNumber] [nchar](100) NULL,
[oppo_CurrentSystem] [nchar](15) NULL,
[oppo_ProductInterest] [nchar](40) NULL,
[oppo_deposit_received] [nchar](1) NULL,
[oppo_Install_Complete] [nchar](1) NULL,
[oppo_NewExisting] [nchar](40) NULL,
[oppo_InstallDate] [datetime] NULL,
[oppo_invoicenumber] [nchar](20) NULL,
[oppo_Soldby] [int] NULL,
[oppo_SaleDate] [datetime] NULL,
[oppo_Creditnoteno] [nchar](10) NULL,
[oppo_creditnoteamount] [numeric](24, 6) NULL,
[oppo_creditnoteamount_CID] [int] NULL,
[oppo_invoiceamount] [numeric](24, 6) NULL,
[oppo_invoiceamount_CID] [int] NULL,
[oppo_invoicedate] [datetime] NULL,
[oppo_Depositamoun] [numeric](24, 6) NULL,
[oppo_Depositamoun_CID] [int] NULL,
[oppo_SuppThisSale] [numeric](24, 6) NULL,
[oppo_SuppThisSale_CID] [int] NULL,
[oppo_SuppFreq] [nchar](40) NULL,
[oppo_SupportInAccpac] [nchar](1) NULL,
[oppo_SupportAddedBy] [int] NULL,
[oppo_supportAddedDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Table OpportunityItem 1957 rows:
USE [CRM]
GO
/****** Object: Table [dbo].[OpportunityItem] Script Date: 08/03/2007 09:44:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OpportunityItem](
[OpIt_OpportunityItemId] [int] NOT NULL,
[OpIt_OpportunityId] [int] NOT NULL,
[OpIt_ProductId] [int] NOT NULL,
[OpIt_Quantity] [numeric](24, 6) NULL,
[OpIt_OriginalListPrice] [numeric](24, 6) NULL,
[OpIt_QuotedPrice] [numeric](24, 6) NULL,
[OpIt_QuotedPriceTotal] [numeric](24, 6) NULL,
[OpIt_Discount] [numeric](24, 6) NULL,
[OpIt_CreatedBy] [int] NULL,
[OpIt_CreatedDate] [datetime] NULL,
[OpIt_UpdatedBy] [int] NULL,
[OpIt_UpdatedDate] [datetime] NULL,
[OpIt_TimeStamp] [datetime] NULL,
[OpIt_Deleted] [tinyint] NULL,
[OpIt_SegmentID] [int] NULL,
[OpIt_ChannelID] [int] NULL,
[OpIt_Discount_CID] [int] NULL,
[OpIt_OriginalListPrice_CID] [int] NULL,
[OpIt_QuotedPrice_CID] [int] NULL,
[OpIt_QuotedPriceTotal_CID] [int] NULL,
[OpIt_Ordered] [nchar](1) NULL
) ON [PRIMARY]
> SET OPTION RUN_FASTER ON No I tried that already.
Since that didn't work for you, how about trying a RESTORE with the 'secret' DBA RESTORE option:
RESTORE DATEBASE MyDatabase
WITH FAST_QUERY_MODE_DWIM
*( DWIM = Do What I Mean )
I'll offer the following comments, some of which 'may' help with this issue.
First, change your datatype for Oppo_Note to varchar(max). Text, ntext, and image are on the deprecation list.
Second, do you really need ALL columns from Opportunity and OpportunityItem? -You are retrieving approximately 94 columns per row, and that is consuming a lot of memory in order to create the resultset. From a cursory look, it appears that each row needs a couple thousand bytes.
Then I would -re-organize the VIEW a bit to optimize the JOIN. (There is a chance that your VIEW is doing a cartesian JOIN between Opportunity and OpportunityItem (the resultset is over 5 million rows -check the execution plan. A cartesian JOIN would require in excess of 10GB just to hold the intermediate JOIN resultset.) The bottom line is that there is a lot of I/O happening here, perhaps some of which is unnecessary.
ALTER VIEW [dbo].[vSearchListOpportunityItem]
AS
SELECT
p.Prod_ProductId,
p.Prod_Name,
p.Prod_Description,
p.Prod_ListPrice,
p.Prod_ListPrice_CID,
o.*, --List Actual Columns Needed
oi.* --List Actual Columns Needed
FROM Opportunity o,
JOIN OpportunityItem oi
ON ( o.OpIt_OpportunityId = oi.Oppo_OpportunityId
AND o.OpIt_Deleted IS NULL
)
LEFT OUTER JOIN Products p
ON o.OpIt_ProductId = p.Prod_ProductId
Then, verify the indexing
Opportunity(OpIt_OpportunityId),
OpportunityItem(Oppo_OpportunityId)
OpportunityItem(OpIt_ProductId)
Products(Prod_ProductId)
You may also find value is using the Database Tuning Wizard to determine if additional indexing can be useful for this operation.
And lastly, the naming convention used is 'awkward' to be charitable. Prefixing a column name with the table indicator is quite 'out of favor'. You are wasting keystrokes EVERY time you refer to a column. Anytime a JOIN occurs, you 'should' be using an table ALIAS (as above, [ o, oi, p ] -and it actually makes the code more readible. A good 'rule of thumb' is that if the keystroke does not add value, it is wasted. With the column names above, [Prod_], [OpIt_], [Oppo_] are wasted keystokes, they don't add any value to distinguish the columns. The table alias clearly identifies where the column comes from AND also clarifies the JOIN conditions.
|||Unfortunately the query and the views are created by a 3rd party application. I won't embarrass the company involved other than to say they are one of the bigger players in mid-range CRM software. The way their application uses the SQL database often leaves a lot to be desired, and I really can't mess with their choice of naming convention.
This application often contains lazy views which refer to 'table.*' rather than pick out the fields that are required. Unfortunately it is difficult for me to know which are needed or not.
I have made changes to the view as you have suggested, but it appears to have made little if any difference. I'm not really surprised however as if a view was slow, wouldn't you expect it it always be slow if run on exactly the same set of data? How can I explain that sometimes it runs in a second, while other times 14 minutes?|||Hi!
To answer your question about why fast then slow... Caching...
Once the data has been cached it no longer has to go to the data pages to resolve it.
So as long as the data has not changed (or cache expired) it will resolve from cache.
Can you send me the showplan on this... maybe we can solve this with some simple indexing.
chas@.hyman.com
Charles Hyman
Senior Consultant
MCTS SQL Server 2005
MCTS Biztalk Server
MCTS Vista Config
MCITP Database Administration
MCITP Database Developer
TALLAN Inc.
www.tallan.com
|||My initial guess would be from what you describe the table or index is locked. That is where you time difference is coming from.Look at the locks and see when it takes long, if there is a lock on the table by someone.
|||
Please post the execution plan. Without that, everything else is just a WAG*.
* Wild-Assumptions Guess
No comments:
Post a Comment