September 22, 2010 at 2:33 am
Hi All,
I'm struggling with a badly performing query which has me at my wits end and I was hoping for a bit of free advice from the experts 😉
The situation is I have a small 2008R2 Express database for a simple data capture application. My main data table contains stock items for an inventory system, it's approaching 100000 rows, so really not big. The data table has about 6 foreign keys columns which reference lookup tables, Product, Supplier, Unit of measure and so on.
I created (what I would call) a de-normalised view (InventoryView) which joins on all the 6 lookup tables and gives me a 'flat' view of the relational data.
Now, if I 'SELECT TOP 50 * from InventoryView', the results return in under a second, yet if I 'SELECT TOP 50 * FROM InventoryView ORDER BY id;' It takes over 20 second to return results, this is on a table with < 100K rows !! (id is the Primary key of the Stock table)
Before you ask, Yes, I have triple-checked my indexes, I have dropped and recreated indexes. I have created indexes on the View, I have followed all the recommended advice, but no change, still 20 seconds to select 50 rows from a small table 🙁
Last night I sat and executed each of the joins individually to see it it could be isolated to a particular join and sure enough when I join my Stock table to a StockStatus table it takes noticibly longer that on any of the other joins, about 5-6 seconds on the StockStatus join, under a seconds for the other 5 joins.
Again, there is nothing special about the StockStatus table, it's a little table with 11 rows, Indexes and foreign keys are the same at the other lookup tables.
The server started off as 2005 Express and has been through various Service Packs and Releases since then, so my next plan of action is try get rid of the legacy by re-creating the database from scripts and importing the across database from the old to the new database.
So, any more advice you think might help me?
September 22, 2010 at 2:43 am
Please post table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2010 at 3:31 am
The data table, Stock.Register:
CREATE TABLE [Stock].[Register](
[id] [int] IDENTITY(1,1) NOT NULL,
[Stock_Product_id] [int] NOT NULL,
[Stock_Status_id] [int] NOT NULL,
[Company_Supplier_id] [int] NOT NULL,
[Description] [nvarchar](200) NOT NULL,
[ReelNumber] [nvarchar](50) NOT NULL,
[Quantity] [decimal](10, 4) NOT NULL,
[Length] [decimal](10, 4) NOT NULL,
[Width] [decimal](10, 4) NOT NULL,
[Thickness] [smallint] NOT NULL,
[Weight] [decimal](10, 4) NOT NULL,
[ReelDiameter] [decimal](10, 4) NOT NULL,
[Reference] [nvarchar](50) NULL,
[SupplierReelNumber] [nvarchar](16) NULL,
[SupplierInvoice] [nvarchar](50) NULL,
[Price] [money] NOT NULL,
[isEnabled] [bit] NOT NULL,
[ReceivedDate] [datetime] NULL,
[DispatchDate] [datetime] NULL,
[Stock_Location_id] [int] NOT NULL,
[Condition] [varchar](10) NULL,
CONSTRAINT [PK_Stock.Register] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 65) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Stock].[Register] WITH CHECK ADD CONSTRAINT [FK_Register_Status] FOREIGN KEY([Stock_Status_id])
REFERENCES [Stock].[Status] ([id])
GO
ALTER TABLE [Stock].[Register] CHECK CONSTRAINT [FK_Register_Status]
GO
(I've removed the foreign keys to the other lookup tables)
The lookup table, Stock.Status
CREATE TABLE [Stock].[Status](
[id] [int] NOT NULL,
[name] [nchar](20) NOT NULL,
[canSell] [bit] NOT NULL,
[canMake] [bit] NULL,
CONSTRAINT [PK_Stock.Status] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The fast query:
SELECT TOP 10 * FROM Stock.Register INNER JOIN Stock.Status ON Stock.Register.stock_status_id = Stock.Status.id
The slow query
SELECT TOP 10 * FROM Stock.Register INNER JOIN Stock.Status ON Stock.Register.stock_status_id = Stock.Status.id ORDER BY Stock.Register.id
September 22, 2010 at 3:42 am
Can you post the actual execution plans for both fast and slow queries please?
From your initial post I assume there are indexes other than the cluster on those tables? If so, please post the index definitions as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2010 at 6:05 am
Like Gail said, you really need to see the whole picture including execution plans to understand what is going on. But while you are doing that, could you possbily try an experiment and send the execution plan for it as well?
;with cte as (SELECT TOP 10 * FROM Stock.Register INNER JOIN Stock.Status ON Stock.Register.stock_status_id = Stock.Status.id)
SELECT * from cte
ORDER BY Stock.Register.id
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 22, 2010 at 6:12 am
Thanks, I'll see if the CTE makes a difference. I will post the execution plans when I get a chance. Don't have direct access to the server a.t.m
September 22, 2010 at 6:51 am
One other variation:
;with cte as (SELECT TOP 10 * FROM Stock.Register ORDER BY Stock.Register.id)
SELECT *
FROM cte
INNER JOIN Stock.Status ON cte.stock_status_id = Stock.Status.id
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 22, 2010 at 6:55 am
The Dixie Flatline (9/22/2010)
One other variation:;with cte as (SELECT TOP 10 * FROM Stock.Register ORDER BY Stock.Register.id)
SELECT *
FROM cte
INNER JOIN Stock.Status ON cte.stock_status_id = Stock.Status.id
Careful with that. With the top and order by on the inner query and then a join in the outer, there are two possible changes from the original query
1) More than 10 rows may be returned if the join to StockStatus returns more than 1 row for each in StockRegister
2) The order of the output rows is no longer guaranteed as the order by is not on the outermost query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2010 at 7:04 am
You're right, Gail, I was a bit sloppy this morning. May I blame it on lack of caffeine?
Since Stock.Status.ID was a primary key, I assumed there would be no multiplier effect from the outer join, but the ORDER BY point was just oversight. To be certain, the ORDER BY clause would have to appear in the final query as well as the cte. Now, I need to find coffee.
;with cte as (SELECT TOP 10 * FROM Stock.Register ORDER BY Stock.Register.id)
SELECT *
FROM cte
INNER JOIN Stock.Status ON cte.stock_status_id = Stock.Status.id
ORDER BY Stock.Register.id
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply