Solved

T-SQL:  How to Add an Index

Posted on 2014-02-28
2
1,039 Views
Last Modified: 2014-03-01
Hello:

Below is my T-SQL view.  The data in this view was returned a lot more quickly, before I added two lines to it.  I added the following lines to this view:

INNER JOIN GL20000 ON AAG30000.JRNENTRY = GL20000.JRNENTRY
INNER JOIN GL30000 AS GL30000_1 ON AAG40000.JRNENTRY = GL30000.JRNENTRY

I added these lines, because I will need to eventually pull fields from the GL20000 and GL30000 tables.

I'm sure that I can speed up the time for the data that is returned, if I only knew how and where to add indexes.  

I'm not too familiar with the syntax for adding indexes nor do I understand the difference even between a clustered and nonclustered index.  Also, I need to know if there is any harm to the database or to the data, if I add indexes.

Can someone please let me know what syntax I need to use for adding indexes to this view for those two lines, in order to speed up the time for the data that is returned?

Thanks!

TBSupport

--ALTER VIEW [ACCTTRANSREVISED] AS
SELECT DISTINCT
                      AAG30000.JRNENTRY AS [Journal Entry], AAG30000.GLPOSTDT AS [Transaction Date], GL00105.ACTNUMST AS [Account Number],
                      GL00100.ACTDESCR AS [Account Description], GL00102.ACCATDSC AS [Account Category],    AAG30001.DEBITAMT AS [Debit Amount], AAG30001.CRDTAMNT AS [Credit Amount],
                      COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDescr, '') AS [Transaction Dim Code Description], GL00100.ACTINDX as [Account Index],
                      CASE WHEN GL00100.ACCTTYPE = '1' THEN 'Posting Account'
                                          WHEN GL00100.ACCTTYPE = '2' THEN 'Unit Account'
                                          WHEN GL00100.ACCTTYPE = '3' THEN 'Posting Allocation Account'
                                          WHEN GL00100.ACCTTYPE = '4' THEN 'Unit Allocation Account'
                                          ELSE '' END as [Account Type],
                              CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
                              CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
                              CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance]
FROM         AAG30000 INNER JOIN
                      AAG30001 ON AAG30000.aaGLHdrID = AAG30001.aaGLHdrID INNER JOIN
                      GL00105 ON AAG30001.ACTINDX = GL00105.ACTINDX INNER JOIN
                      GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX LEFT OUTER JOIN
                      AAG30003 ON AAG30001.aaGLHdrID = AAG30003.aaGLHdrID AND AAG30001.aaGLDistID = AAG30003.aaGLDistID LEFT OUTER JOIN
                      AAG00401 ON AAG30003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND
                      AAG30003.aaTrxDimID = AAG00401.aaTrxDimID
                              INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
                              INNER JOIN GL20000 ON AAG30000.JRNENTRY = GL20000.JRNENTRY
--where GL20000.DSCRIPTN <> ''
GROUP BY AAG30001.aaGLDistID, AAG30000.JRNENTRY, AAG30001.DEBITAMT, AAG30001.CRDTAMNT, GL00105.ACTNUMST,
                      GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr, AAG30000.GLPOSTDT, GL00102.ACCATDSC, GL00100.ACTINDX, GL00100.ACCTTYPE, GL00100.ACTIVE, GL00100.PSTNGTYP, GL00100.TPCLBLNC
UNION ALL
SELECT DISTINCT
                      AAG40000.JRNENTRY AS [Journal Entry], AAG40000.GLPOSTDT AS [Transaction Date], GL00105.ACTNUMST AS [Account Number],
                      GL00100.ACTDESCR AS [Account Description], GL00102.ACCATDSC as [Account Category], AAG40001.DEBITAMT AS [Debit Amount], AAG40001.CRDTAMNT AS [Credit Amount],
                      COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDescr, '') AS [Transaction Dim Code Description], GL00100.ACTINDX AS [Account Index],
                       CASE WHEN GL00100.ACCTTYPE = '1' THEN 'Posting Account'
                                          WHEN GL00100.ACCTTYPE = '2' THEN 'Unit Account'
                                          WHEN GL00100.ACCTTYPE = '3' THEN 'Posting Allocation Account'
                                          WHEN GL00100.ACCTTYPE = '4' THEN 'Unit Allocation Account'
                                          ELSE '' END as [Account Type],
                                    CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
                                    CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
                                    CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance]
FROM         AAG40000 INNER JOIN
                      AAG40001 ON AAG40000.aaGLHdrID = AAG40001.aaGLHdrID INNER JOIN
                      GL00105 ON AAG40001.ACTINDX = GL00105.ACTINDX INNER JOIN
                      GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX LEFT OUTER JOIN
                      AAG40003 ON AAG40001.aaGLHdrID = AAG40003.aaGLHdrID AND AAG40001.aaGLDistID = AAG40003.aaGLDistID LEFT OUTER JOIN
                      AAG00401 ON AAG40003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND AAG40003.aaTrxDimID = AAG00401.aaTrxDimID LEFT OUTER JOIN
                      GL30000 ON AAG40000.JRNENTRY = GL30000.JRNENTRY
                      INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
                      INNER JOIN GL30000 AS GL30000_1 ON AAG40000.JRNENTRY = GL30000.JRNENTRY
--where GL30000.DSCRIPTN <> ''
GROUP BY AAG40001.aaGLDistID, AAG40000.JRNENTRY, AAG40001.DEBITAMT, AAG40001.CRDTAMNT, GL00105.ACTNUMST,
                      GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr, AAG40000.GLPOSTDT, GL00102.ACCATDSC, GL00100.ACTINDX, GL00100.ACCTTYPE, GL00100.ACTIVE, GL00100.PSTNGTYP, GL00100.TPCLBLNC
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39894751
Without digging into your T-SQL holy cats..), use CREATE INDEX with an INCLUDE(

Replace the below with the correct names, and column1, column2 you'll want to add the columns that participate in the JOIN, and column3, column4 would be the columns involved in a SELECT.  

CREATE INDEX ix_give_me_a_name
    ON table_name (column1, column2)
    INCLUDE (column3, column4)
GO

Open in new window


Keep in mind that indexes add to the overhead of inserting-updating-deleting rows, which will slow down those queries.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39895176
You also asked about the difference between a CLUSTERED and NON CLUSTERED index.

The documentation describes is nicely: http://technet.microsoft.com/en-us/library/ms190457.aspx

However, in brief, a clustered index is the physical ordering of the rows on the disk.  When you fetch data on the clustered index you're actually fetching the physical row.

Non Clustered indexes are stored as a tree of pointers to the physical row location.

There can only be one clustered index per table.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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