3

I have an SQL database with millions of records and when I'm querying the data like

select * from ActCosts where ScenarioID= 456

The tables has 1,323,718 rows and it gives me like 50,000+ rows which is surprisingly taking more than 3 minutes. So I am now thinking of how I could improve this performance. I find one way is to create an index on the column "SomeID". I have created this index but the query takes the same time

enter image description here

Execution plan enter image description here

Index Script

CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] 
(
    [ScenarioID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO


ALTER TABLE [dbo].[ActCost] ADD  CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED 
(
    [ActCostID] 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)
GO
  • If you have a look at the execution plan, you will see whether SQL Server will do a Scan or Seek. (Index seek is what you want him to do). – RayofCommand Dec 22 '14 at 8:49
  • Do you have an index on (ScenarioID) and is it used? – ypercubeᵀᴹ Dec 22 '14 at 9:26
  • Yes i do have an index on ScenarioID and how do i check whether its used or not ? – Vivekh Dec 22 '14 at 9:27
  • check the execution plan of the query. the screenshot shows that you use SQL Server, but it's always good to place the tag SQL-Server to reach more people :) – RayofCommand Dec 22 '14 at 10:07
  • 2
    Do you really need SELECT *? Do you really need 50,000 rows returned by this query? What user is going to do anything useful with 50,000 rows? – Aaron Bertrand Dec 22 '14 at 13:46
8

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed.

As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to return from the query would need to be retrieved from the Clustered Index. On 50,000+ rows, this simply wouldn't be efficient, hence the index is ignored.

Firstly, you need to ask yourself if you really need ALL columns to be returned. I somehow doubt you do (the ID columns for example), but I may be wrong. In any case, you should always define the column list rather than using *.

SELECT
 ActID, 
 ActCostTypeID,
 Description,
 Cost,
 <rest_of_columns...>
FROM dbo.ActCosts 
WHERE ScenarioID = 456;

Notes:
1. Don't avoid the schema prefix
2. Avoid using SELECT *

You then need to include the extra columns returned in IX_ActCost_ScenarioID:

CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] ([ScenarioID] ASC)
INCLUDE ( <list_all_other_columns_returned> );
GO
  • Thanks. i have just realized that i havent used the index in my query at all and just now i should mention the index which i want to use like this select * from ActCost WITH(INDEX(IX_ActCost_ScenarioID)) where ScenarioID = 797 this works good like it takes just 20 sec's. Thanks for guiding me in the right path – Vivekh Dec 22 '14 at 11:10
  • I'd recommend not forcing index hints. 20 seconds still sounds like a long time, suggesting you've still not optimised as much as you can – Mark Sinkinson Dec 22 '14 at 11:13
  • Okay. i have a small query on this your saying that i can still optimize it in terms of time by creating a new Index like you said and by just calling the select statement without even using Index hints ? – Vivekh Dec 22 '14 at 11:17
  • Yes. You can use INCLUDE on the ScenarioID index to include all the extra columns you are returning from the SELECT *. But please stop writing SELECT *, there is no good reason for it in a Production environment. – Mark Sinkinson Dec 22 '14 at 11:20
  • Sure i will follow that. Do i need to alter the existing index or just create the new Index with a different name – Vivekh Dec 22 '14 at 11:32
2

That's a lot of NTEXT columns. You should know NTEXT is deprecated and you should consider refactoring to the newer datatypes, eg NVARCHAR(MAX).

Regarding your specific problem, depending on what version and edition ( eg select @@version ) of SQL Server you are using, you could consider partitioning. Here's a simple demo of this might work for you and my results which show hugely reduced reads (although not particularly duration ) on my 8-core laptop:

USE tempdb
GO

IF OBJECT_ID('dbo.ActCost') IS NOT NULL DROP TABLE dbo.ActCost
IF EXISTS ( SELECT * FROM sys.partition_schemes WHERE name = 'ps_test' ) DROP PARTITION SCHEME ps_test
IF EXISTS ( SELECT * FROM sys.partition_functions WHERE name = 'pf_test' ) DROP PARTITION FUNCTION pf_test
GO

CREATE PARTITION FUNCTION pf_test (INT) AS RANGE LEFT FOR VALUES ( 450, 451, 452, 453, 454, 455, 456, 457, 458, 459, 460, 461, 462, 463, 464, 465, 466, 467, 468, 469, 470, 471, 472, 473, 474, 475 )
GO

-- SELECT * FROM sys.partition_range_values

-- !!TODO don't use ALL TO PRIMARY, instead create individual files and filegroups
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO ( [PRIMARY] )
GO


CREATE TABLE dbo.ActCost
(
    ActCostID           INT NOT NULL,
    ActID               INT NOT NULL,
    ActCostTypeID       INT NOT NULL,
    [Description]       NTEXT NOT NULL,
    Cost                FLOAT NOT NULL,
    CostPerProductUnit  FLOAT NOT NULL,
    OtherValue          FLOAT NOT NULL,
    OtherID             INT NOT NULL,
    Comment1            NTEXT NOT NULL,
    Comment2            NTEXT NOT NULL,
    OPerProductUnit     FLOAT NOT NULL,
    OPerHour            FLOAT NOT NULL,
    OCostPerUnit        FLOAT NOT NULL,
    OCostPerHour        FLOAT NOT NULL,

    PerfEnh_ProcessID   INT NOT NULL,
    PerfEnh_MillID      INT NOT NULL,
    ScenarioID          INT NOT NULL,

    CONSTRAINT PK_ActCost PRIMARY KEY ( ActCostID )
)
GO


-- Create some dummy data
;WITH cte AS (
SELECT TOP 1323718 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.ActCost ( ActCostID, ActID, ActCostTypeID, Description, Cost, CostPerProductUnit, OtherValue, OtherID, Comment1, Comment2, OPerProductUnit, OPerHour, OCostPerUnit, OCostPerHour, PerfEnh_ProcessID, PerfEnh_MillID, ScenarioID )
SELECT
    rn AS ActCostID,
    rn % 333 AS ActID,
    rn % 444 AS ActCostTypeID,
    CAST( NEWID() AS VARCHAR(MAX) ) AS [Description],
    rn % 555 AS Cost,
    rn % 777 AS CostPerProductUnit,
    rn % 888 AS OtherValue,
    rn % 999 AS OtherID,
    CAST( NEWID() AS VARCHAR(MAX) ) AS Comment1,
    CAST( NEWID() AS VARCHAR(MAX) ) AS Comment2,
    rn % 123 AS OPerProductUnit,
    rn % 124 AS OPerHour,
    rn % 125 AS OCostPerUnit,
    rn % 126 AS OCostPerHour,
    rn % 127 AS PerfEnh_ProcessID,
    rn % 128 AS PerfEnh_MillID,
    450 + ( rn % 26 ) AS ScenarioID

FROM cte
GO

CREATE NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] 
(
    [ScenarioID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

/*
SELECT ScenarioID, COUNT(*)
FROM dbo.ActCost
GROUP BY ScenarioID
ORDER BY ScenarioID DESC
GO
*/

PRINT 'Original query ...'
SET STATISTICS IO ON
GO

SELECT *
INTO #tmp1
FROM dbo.ActCost
WHERE ScenarioID = 456
GO

SET STATISTICS IO OFF
GO

-- Rebuild the clustered index on the partition scheme
ALTER TABLE dbo.ActCost DROP CONSTRAINT PK_ActCost
GO
CREATE UNIQUE CLUSTERED INDEX PK_ActCost ON dbo.ActCost( ActCostID, ScenarioID ) 
ON ps_test(ScenarioID);
GO


PRINT 'Query with partitioning enabled ...'
SET STATISTICS IO ON
GO

SELECT * 
INTO #tmp2
FROM dbo.ActCost
WHERE ScenarioID = 456
GO

SET STATISTICS IO OFF
GO

Results: enter image description here

Some of your duration may be to do with the length of time required to pull the data back to the client, which is why I've used SELECT ... INTO in my demo.

SQL Azure Database does not currently support partitioning although there are workarounds, see here. There is also the currently in-preview yet intriguing sharding ( aka Elastic Scale ) option although this is probably a hammer to crack a nut.

HTH

  • My sql version is Microsoft SQL Azure (RTM) - 11.0.9228.34 Dec 15 2014 13:43:56 Copyright (c) Microsoft Corporation – Vivekh Dec 23 '14 at 11:47
  • 1
    Table partitioning is in preview link1 link2 but I doubt it's the right solution here. As you say at the start of your answer, the ntext columns need to go. I doubt they store > 8000 bytes anyway. The OP appears to be quite new to databases. – Paul White Reinstate Monica Dec 23 '14 at 13:46
  • Thanks for clarification @PaulWhite and Merry Christmas to you! – wBob Dec 23 '14 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.