Solved

SQL 2012 Index Question

Posted on 2014-01-06
13
271 Views
Last Modified: 2014-03-17
Hi,

I have a large SQL database table - in excess of 8923411 rows.

I am trying to run the following SQL statement.

SELECT        Display_Name, Product_ID, Version,COUNT(*) as Number
FROM            vp_machine_inventory_list
GROUP BY Display_Name, Product_ID, Version
ORDER BY Number Desc

On this table there are the following indexes I have created:

CREATE INDEX IX_Machine_Name ON VP.dbo.vp_machine_inventory_list (Machine_Name)
CREATE INDEX IX_Display_Name ON VP.dbo.vp_machine_inventory_list (Display_Name)
CREATE INDEX IX_Product_ID ON VP.dbo.vp_machine_inventory_list (Product_ID)
CREATE INDEX IX_Publisher ON VP.dbo.vp_machine_inventory_list (Publisher)
CREATE INDEX IX_Version ON VP.dbo.vp_machine_inventory_list (Version)

I created these indexes to try to speed up the query however it still takes 3 mins / 7 seconds to run
and return back 14,329 rows.

Now given the indexes I created I was expecting the query to run a lot faster then this?

Is there something I have missed here? I was hoping the way I designed the indexes would bump up the execution speed.

What I am missing here?

Also would these indexes be used if I am making an ODBC connection to the database - and using MS Access to do the same select statement?

Thanks,

Ward.
0
Comment
Question by:whorsfall
  • 4
  • 4
  • 2
  • +3
13 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39758793
Execute the query in Management Studio and take a look at the execution plan, that will tell you which, if any, indexes are being used.

You might find that an include index will help:

CREATE INDEX IX_Product_ID_Include ON VP.dbo.vp_machine_inventory_list (Product_ID) INCLUDE (Display_Name, Version)

Open in new window

0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 39758834
Hi Whorsfall,
The first thing that I would do in this situation is I would manually run the SQL statement on the server, using SSMS and display the Execution Plan.

This will help give a better idea of the execution of the query and if any of the indexes are being used and if scans as opposed to seeks are being done.
If possible, please post up a print screen of the execution plan and we can assist further.

Also statistics on the table may need to be updated.
The following command can help determine this:

DBCC SHOW_STATISTICS('dbo.vp_machine_inventory_list'
                     IX_Display_Name)


One thing, at a high level, to keep in mind is that a tweak to optimize performance for one query may not necessarily help or may even have a negative impact on another query.

The following is also another useful command to enter before executing a query as it gives some extra information about the execution of a query.
SET STATISTICS IO ON
0
 

Author Comment

by:whorsfall
ID: 39758845
Hi,

Thanks for your response. In regards to the Execution Plan - can you elaborate on what I should be looking for to see if the indexes are being used?

Thanks,

Ward
0
 

Author Comment

by:whorsfall
ID: 39758850
Hi,

Thanks for the info here is the Execution Plan plus the stats.

from: DBCC SHOW_STATISTICS('dbo.vp_machine_inventory_list',
                     IX_Display_Name)


Thanks,

Ward
plan.png
stats.png
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39758852
Maybe it would be easier if you could post a screenshot of your plan.

Generally you want to be looking for any component that refers to either a scan or seek operation. Hovering over the item will show the details of which object it refers to - which hopefully will be the name of one of your indexes.


Edit: Ah, you posted again while i was typing :)

Your execution plan is showing a table scan which means it isn't using any of your indexes. Which kind of makes sense because all of your indexes are on a single column, so are generally not going to be any more selective than the clustered index.
0
 

Author Comment

by:whorsfall
ID: 39758854
Hi,

Thanks for your response. So would the suggestion you posted before make sense to do then
I suppose?

CREATE INDEX IX_Product_ID_Include ON VP.dbo.vp_machine_inventory_list (Product_ID) INCLUDE (Display_Name, Version)

Thanks,

Ward
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39758862
You could try it, but it probably won't make any difference. The query you are running isn't doing any filtering so SQL needs to scan the whole table anyway in order to produce the results you need.

You might get a small benefit because the INCLUDE index will contain all of the data you need, and therefore require less page reads. But if you have enough memory available for SQL to cache the whole table then that won't provide much benefit either.
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 39758888
Hi Whorsfall,
Another idea may be to implement a 'summary table' build:
So you would create a stored procedure which takes data from the 'vp_machine_inventory_list' table and populates it into the summary table.
So the structure of the summary table in actual fact may be similar to the output of your initial SQL statement above, but if you can keep the summary table even more generic, so as other queries can also use it, then better again.
You would then schedule this stored procedure in a job which run in off- peak hours(at night) and so your 8 million rows would summarized to about 14,000 rows and your queries could then run against this 'summary' table the next day, and would have a smaller number of rows to interrogate.
0
 

Author Comment

by:whorsfall
ID: 39758977
Thanks guys great advice.

Here are the stats which proved very interesting:

Original Indexes:                   3:07
Original Indexes + Suggested Index: 1:23
Suggested Index Only:               1:26
No Indexes:                         3:09

If I extended the idea of a stored procedure and created a view. Since the new index seems to make a lot of difference.

Extending this if I created a view based on that SQL statement. Would it return instantly or will it have re-evaluate things and take the 1min 30 secs etc to process.

So the view would be based on the previous that was suggested.

Thanks,

Ward
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39758998
A standard view would be evaluated each time it is accessed. An indexed view on the other hand would only be re-evaluated when the underlying data changes - so may be an option if the underlying data is changed fairly infrequently.
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 39759736
If this query is run often, then you are better off using an index like this:

CREATE INDEX IX_DisplayName_ProductID_Version ON VP.dbo.vp_machine_inventory_list (Display_Name, Product_ID, Version)

If you are not going to have this query run very often, there is a certain amount of overhead associated, but the reason this would be good for this query is that you are using all the fields in a group by so these are ordered.  The INCLUDE index will have all the columns, but only ProductID is Ordered.

Try this index to give you a comparison of speed and efficiency, and look at the query plan to compare the difference and then finally see the STATISTICS IO counters for the number of reads it does to execute your query.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39761447
no points pl.

regarding execution plans - the very best way of supplying us with those details is to attach the complete .sqlplan file (which is now an EE allowed file extension by the way)

Images of the graphic output don't reveal everything :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39762996
And if you have not defined a CLUSTERED index, then make sure to create your index as a CLUSTERED index.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article will help you understand what HashTables are and how to use them in PowerShell.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now