CODEWITHSUNDEEP
sql-server
Mark Blackburn
Mark Blackburn

Reputation: 105

SQL INDEX for slow select

Still learning SQL, I have the below select that works well, however, I would like to speed it up a bit. From what I understand about Indexing (Very little), I need to have a Unique ID column for an Index to have an effect?

None of my tables have a unique ID, even combinations of the columns would not result in a unique ID.

so my question is can Indexing help me? if so, how do I implement? Or is there a better way of writing the below to execute faster? 

SELECT * FROM (
    SELECT *,
        (SELECT SITE FROM SITES WHERE SITES.SITE_ID = tblJOIN.SITE_ID ) AS SITE, (SELECT ARTICLE FROM ARTICLES  WHERE ARTICLES.ARTICLE_ID = tblJOIN.ARTICLE_ID ) AS ARTICLE
    FROM tblJOIN) a
    INNER JOIN RANGE ON a.SITE=RANGE.SITE_ID AND a.ARTICLE=RANGE.ARTICLE_ID;

Upvotes: 0

Views: 1079

Answers (3)

Eric Brandt
Eric Brandt

Reputation: 8101

Indexing aside, all of the sub-selects and the sub-query are pulling a lot of records into memory that you're just filtering off with your last outer join. This should return the same result set a little faster right away. The execution plan should give you more useful hints about where indexing might be helpful.

SELECT 
  J.*,
  R.*,
  S.[SITE] AS [SITE], 
  A.ARTICLE AS ARTICLE
FROM 
  [tblJOIN] AS J --The two original subselects joined here; good place to start.
JOIN --Rather than a subselect (row by row processing) added two joins...
  [SITES] AS S
    ON
      S.[SITE_ID] = J.[SITE_ID]
JOIN --...because joins are set based. Then SELECTed the needed fields from each.
  [ARTICLES] AS A
    ON
      A.[ARTICLE_ID] = J.[ARTICLE_ID]
 JOIN --Then just added your last join as written.
  [RANGE] AS R
    ON 
      S.[SITE] = R.[SITE_ID]
      AND 
      A.[ARTICLE] = R.[ARTICLE_ID];

Also, if you have anything to say about the table structures, it's not a best practice to have fields called SITE_ID and ARTICLE_ID in two tables that aren't the same data points. From the original query, tblJOIN.SITE_ID <> RANGE.SITE_ID and likewise with ARTICLE_ID. Also, it's not a best practice to have fields called Anything_ID that aren't, in fact, identifier columns.

Upvotes: 1

DaniDev
DaniDev

Reputation: 2631

In addition to creating a (unique) Primary Key (using SSMS). You will also want to make a Clustered Index based on that key. This can be done through the SSMS Design Table or through commands.

Example Code for creating (Clustered) Primary Key for your Table SItes using site_id as a primary key (you will have to make it unique):

USE [yourDBname]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [PK_SITES]    Script Date: 3/5/2018 9:19:11 AM ******/
ALTER TABLE [dbo].[SITES] ADD  CONSTRAINT [PK_SITE_ID] PRIMARY KEY CLUSTERED 
(
    [SITE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ADDENDUM:

None of my tables have a unique ID, even combinations of the columns would not result in a unique ID.

This does not suggest a well thought out design. It does appear that you have fields that are meant to function as unique IDs such as your SITES.SITE_ID. Granted not all tables must have a primary unique ID, but ones that represent an "Entity" should.

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

Orignal 

SELECT * 
FROM ( SELECT *
            , (SELECT SITE FROM SITES WHERE SITES.SITE_ID = tblJOIN.SITE_ID ) AS SITE
            , (SELECT ARTICLE FROM ARTICLES  WHERE ARTICLES.ARTICLE_ID = tblJOIN.ARTICLE_ID ) AS ARTICLE 
        FROM tblJOIN
     ) a
FROM RANGE r 
JION tblJOIN j
  ON a.SITE    = r.SITE_ID 
 AND a.ARTICLE = r.ARTICLE_ID;

Not sure I will come back and look at this

Upvotes: 0

Related Questions