what is the best way to index etc.

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
JustAskMeAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
  assuming that transactionID is the Primary key of [transaction] table
for transactionEntry ->   TransactionID   INCLUDE (ItemStoreID , Status)
0
 
JustAskMeAuthor Commented:
TransactionEntry Table is the PrimaryKey TransactionEntryID which don't include in the search
0
 
JustAskMeAuthor Commented:

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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Aneesh RetnakaranDatabase AdministratorCommented:
can you provide the current index structure
0
 
chapmandewCommented:
need a nc on StartSaleTime
0
 
JustAskMeAuthor Commented:
can u explain what is "nc"
0
 
chapmandewCommented:
oh, sorry.  Nonclustered index.

create nonclustered index idx_transaction_StartSaleTime on transaction(startsaletime)
0
 
JustAskMeAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
try creating another index for transaction table      transaction INCLUDE (status)
0
 
JustAskMeAuthor Commented:
sorry i don't get u
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
create nonclustered index idx__transaction__StartSaleTime_Status on [transaction](startsaletime )include ([status])
0
 
8080_DiverCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.