[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

what is the best way to index etc.

Posted on 2009-12-31
13
Medium Priority
?
205 Views
Last Modified: 2013-11-30
what is the best way to index etc.
i use msSql 2005 i need to join 2 table with some filtering can some one help me how i need to index in order i should get back my information fast
here is the sql
SELECT [Transaction].TransactionNo,
           [Transaction].TransactionType,
           [Transaction].TransactionID,
           [Transaction].StartSaleTime,
           TransactionEntry.ItemStoreID,
           TransactionEntry.UOMQty AS Qty,
           TransactionEntry.Status,
           TransactionEntry.Total,
           TransactionEntry.UOMPrice,
            (SELECT     CASE WHEN [TRANSACTION].TransactionType = 0 THEN 'Sale' WHEN dbo.[TRANSACTION].transactionType = 3 THEN 'Return' END AS Expr1)
            AS Type
FROM         [Transaction] INNER JOIN
                      TransactionEntry ON [Transaction].TransactionID = TransactionEntry.TransactionID  WHERE (dbo.TransactionEntry.ItemStoreID ='06521644-00a1-44f8-96c4-63f6d4c46436')
       AND (dbo.[Transaction].StartSaleTime >='2009-12-01') and (dbo.[Transaction].StartSaleTime <'2010-01-01')
AND [TRansaction].Status>0 AND TransactionEntry.Status>0

the Transaction Table has 1,139,296 Records and the TransactionEntry Table has 10,723,676 Records. The Primary key On Transaction is TransactionID which is a GUID(uniqueidentifier)  now it take me about 5 Min to search for this report. What do i need to do to speed up the process
thanks for you're reply
0
Comment
Question by:JustAskMe
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 26154165
  assuming that transactionID is the Primary key of [transaction] table
for transactionEntry ->   TransactionID   INCLUDE (ItemStoreID , Status)
0
 

Author Comment

by:JustAskMe
ID: 26154187
TransactionEntry Table is the PrimaryKey TransactionEntryID which don't include in the search
0
 

Author Comment

by:JustAskMe
ID: 26154211

CREATE TABLE [dbo].[Transaction](
	[TransactionID] [uniqueidentifier] NOT NULL,
	[TransactionNo] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
	[TransactionType] [int] NOT NULL,
	[RegisterTransaction] [bit] NULL,
	[BatchID] [uniqueidentifier] NULL,
	[StoreID] [uniqueidentifier] NULL,
	[CustomerID] [uniqueidentifier] NULL,
	[Debit] [money] NULL,
	[Credit] [money] NULL,
	[StartSaleTime] [datetime] NULL,
	[EndSaleTime] [datetime] NULL,
	[DueDate] [datetime] NULL,
	[CurrBalance] [money] NULL,
	[LeftDebit] [money] NULL,
	[Freight] [money] NULL,
	[Tax] [money] NULL,
	[TaxType] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[TaxRate] [decimal](19, 4) NULL,
	[TaxID] [uniqueidentifier] NULL,
	[Rounding] [money] NULL CONSTRAINT [DF_Transaction_Rounding]  DEFAULT ((0)),
	[ShipTo] [uniqueidentifier] NULL,
	[ShipVia] [uniqueidentifier] NULL,
	[PONo] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[RepID] [uniqueidentifier] NULL,
	[TermsID] [uniqueidentifier] NULL,
	[PhoneOrder] [bit] NULL,
	[ToPrint] [bit] NULL,
	[ToEmail] [bit] NULL,
	[CustomerMessage] [uniqueidentifier] NULL,
	[RegisterID] [uniqueidentifier] NULL,
	[RecieptTxt] [ntext] COLLATE Latin1_General_CI_AS NULL,
	[Note] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
	[VoidReason] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL,
	[ResellerID] [uniqueidentifier] NULL,
	[DeliveryDate] [datetime] NULL,
	[TrackNo] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[Status] [smallint] NULL,
	[DateCreated] [datetime] NULL,
	[UserCreated] [uniqueidentifier] NULL,
	[DateModified] [datetime] NULL,
	[UserModified] [uniqueidentifier] NULL,
	[TransferedToBookkeeping] [bit] NULL CONSTRAINT [DF_Transaction_TransferedToBookkeeping]  DEFAULT ((0)),
 CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED 
(
	[TransactionID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Statistic [_dta_stat_133575514_10_7_8]    Script Date: 12/31/2009 11:06:21 ******/
CREATE STATISTICS [_dta_stat_133575514_10_7_8] ON [dbo].[Transaction]([StartSaleTime], [CustomerID], [Debit])
GO
/****** Object:  Statistic [_dta_stat_133575514_36_10_7_3]    Script Date: 12/31/2009 11:06:21 ******/
CREATE STATISTICS [_dta_stat_133575514_36_10_7_3] ON [dbo].[Transaction]([Status], [StartSaleTime], [CustomerID], [TransactionType])
GO
/****** Object:  Statistic [_dta_stat_133575514_36_3_7]    Script Date: 12/31/2009 11:06:21 ******/
CREATE STATISTICS [_dta_stat_133575514_36_3_7] ON [dbo].[Transaction]([Status], [TransactionType], [CustomerID])
GO
/****** Object:  Statistic [_dta_stat_133575514_7_36]    Script Date: 12/31/2009 11:06:21 ******/
CREATE STATISTICS [_dta_stat_133575514_7_36] ON [dbo].[Transaction]([CustomerID], [Status])
GO
/****** Object:  Statistic [_dta_stat_133575514_8_36]    Script Date: 12/31/2009 11:06:21 ******/
CREATE STATISTICS [_dta_stat_133575514_8_36] ON [dbo].[Transaction]([Debit], [Status])



CREATE TABLE [dbo].[TransactionEntry](
	[TransactionEntryID] [uniqueidentifier] NOT NULL,
	[TransactionID] [uniqueidentifier] NULL,
	[ItemStoreID] [uniqueidentifier] NULL,
	[Sort] [int] NULL,
	[TransactionEntryType] [int] NOT NULL,
	[Taxable] [bit] NULL,
	[Qty] [decimal](19, 3) NULL,
	[UOMPrice] [money] NULL,
	[UOMType] [money] NULL,
	[UOMQty] [decimal](19, 3) NULL,
	[Total] [money] NULL,
	[RegUnitPrice] [money] NULL,
	[DiscountPerc] [decimal](19, 3) NULL,
	[DiscountAmount] [money] NULL,
	[SaleCode] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[PriceExplanation] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[ParentTransactionEntry] [uniqueidentifier] NULL,
	[AVGCost] [money] NULL,
	[Cost] [money] NULL,
	[ReturnReason] [int] NULL,
	[Note] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
	[DepartmentID] [uniqueidentifier] NULL,
	[DiscountOnTotal] [decimal](19, 3) NULL,
	[Status] [smallint] NULL,
	[DateCreated] [datetime] NULL,
	[UserCreated] [uniqueidentifier] NULL,
	[DateModified] [datetime] NULL,
	[UserModified] [uniqueidentifier] NULL,
	[TotalAfterDiscount] [decimal](18, 3) NULL,
 CONSTRAINT [PK_TransactionEntry] PRIMARY KEY CLUSTERED 
(
	[TransactionEntryID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26154215
can you provide the current index structure
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26154217
need a nc on StartSaleTime
0
 

Author Comment

by:JustAskMe
ID: 26154226
can u explain what is "nc"
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 500 total points
ID: 26154235
oh, sorry.  Nonclustered index.

create nonclustered index idx_transaction_StartSaleTime on transaction(startsaletime)
0
 

Author Comment

by:JustAskMe
ID: 26154262
sory for asking... can u explain me what  the nonclustered index do. And do i need some other index on the TransactionEntry Table like ItemStoreID
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26154275
try creating another index for transaction table      transaction INCLUDE (status)
0
 

Author Comment

by:JustAskMe
ID: 26154282
sorry i don't get u
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26154348
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 26154443
create nonclustered index idx__transaction__StartSaleTime_Status on [transaction](startsaletime )include ([status])
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 500 total points
ID: 26154569
For openers, do you really need to use GUIDs for your Identity columns?  If not, that can help a lot because INT has a much smaller footprint in indexes than does GUID.
I have slightly restructured your SQL statement (See SQL_1 in the attached code).  this may help a little.
You might want to make sure that there is an index on the Transaction table on columns StartSalesTime, Status, and TransactionID, in that order.  The TransactionEntry table should probably have an index on the Status and TrasnsactionID columns, again in that order.
I also included code for a stored proc (SQL_2: in the code snippet) that I created recently that will let you determine a Uniqueness Ratio for candidate indexes (it will work for one or more columns on a single table).  TO try multiple columns, enter them as a comma separated list.

SQL_1:
SELECT [Transaction].TransactionNo
  ,[Transaction].TransactionType
  ,[Transaction].TransactionID
  ,[Transaction].StartSaleTime
  ,TransactionEntry.ItemStoreID
  ,TransactionEntry.UOMQty AS Qty
  ,TransactionEntry.Status
  ,TransactionEntry.Total
  ,TransactionEntry.UOMPrice
  ,CASE  WHEN [TRANSACTION].TransactionType = 0 THEN 'Sale'
        WHEN dbo.[TRANSACTION].transactionType = 3 THEN 'Return'
    END AS Type
FROM [Transaction]
INNER JOIN TransactionEntry
   ON [Transaction].TransactionID = TransactionEntry.TransactionID  AND
	  TransactionEntry.Status > 0
WHERE(dbo.TransactionEntry.ItemStoreID = '06521644-00a1-44f8-96c4-63f6d4c46436')
    AND(dbo.[Transaction].StartSaleTime >= '2009-12-01')
    AND(dbo.[Transaction].StartSaleTime < '2010-01-01')
    AND [Transaction].Status > 0;




SQL_2:
-- =============================================
-- Author:		 Ralph D. Wilson II
-- Create date: 2009-12-30
-- Description: This stored procedure is based 
--              upon an article from the SQL 
--              Server Performance Newsletter 
--              regarding determining the  
--              "uniqueness ratio" of a column 
--              that is being considered as a 
--              non-clustered index candidate.
-- =============================================
ALTER PROCEDURE usp_Obtain_Uniqueness_Ratio_of_Table_Column 
	@TableName VarChar(25) = '', 
	@ColumnName VarChar(300) = ''
AS
BEGIN
	
	SET NOCOUNT ON;

	--Finds the Degree of Selectivity for a Specific Column in a Row 
	DECLARE @SQLStatement1	VarChar(4000);
	DECLARE @SQLStatement2	VarChar(4000);
	DECLARE @SQLStatement3	VarChar(4000);

	IF	(@TableName <> '') AND
		(@ColumnName <> '')
	BEGIN
		--Find the Total Number of Unique Rows in the Table 
	 
		SET	@SQLStatement1	=	'( ' +
		                        ' SELECT COUNT(Uniques) AS UniqueRows ' +
		                        ' FROM ' +
		                        ' ( ' +
		                        '  SELECT COUNT(*) AS Uniques ' +
		                        '  FROM [' + @TableName + '] ' +
		                        '  GROUP BY ' + @ColumnName + 
		                        ' ) A ' +
		                        ') D'; 
	
	   PRINT @SQLStatement1;
	   
		--Calculate Total Number of Rows the Table 
		SET	@SQLStatement2	=	'( ' +
		                        ' SELECT COUNT(*) TotalRows ' +
		                        ' FROM [' + @TableName + '] ' +
		                        ') R';

	   PRINT @SQLStatement2;
	   
		SET	@SQLStatement3	=	'SELECT ROUND(((UniqueRows * 100.0)/TotalRows),2,2) AS Selectivity_Ratio ' + 
		                        'FROM ' +
		                        @SQLStatement1 + ', ' +
		                        @SQLStatement2 + ';';
			   
	   PRINT @SQLStatement3;
	   
		--Calculate Selectivity Ratio for a Specific Column 
		EXECUTE (@SQLStatement3); 
	END
	ELSE
	BEGIN
		SELECT		-1 AS Selectivity_Ratio;
	END
	
END

Open in new window

0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question