0

While trying to dissect a SQL Server stored proc that's been running slow, we found that simply using a temp table instead of a real table had a drastic impact on performance. The table we're swapping out (ds_location) only has 173 rows:

This query will run complete in 1 second:

IF OBJECT_ID('tempdb..#Location') IS NOT NULL DROP TABLE #Location
SELECT * INTO #Location FROM ds_location 

SELECT COUNT(*) 
FROM wip_cubs_hc m
INNER JOIN ds_scenario sc ON sc.Scenario = m.Scenario
INNER JOIN ds_period pe ON pe.Period = m.ReportingPeriod
INNER JOIN #Location l ON l.Location = m.Sh_Location

Compare that to the original, which takes 7 seconds:

SELECT COUNT(*) 
FROM wip_cubs_hc m
INNER JOIN ds_scenario sc ON sc.Scenario = m.Scenario
INNER JOIN ds_period pe ON pe.Period = m.ReportingPeriod
INNER JOIN ds_location l ON l.Location = m.Sh_Location

Here's the definition of wip_cubs_hc. It contains 1.7 million rows:

CREATE TABLE wip_cubs_hc(
    Scenario varchar(16) NOT NULL,
    ReportingPeriod varchar(50) NOT NULL,
    Sh_Location varchar(50) NOT NULL,
    Department varchar(50) NOT NULL,
    ProductName varchar(75) NOT NULL,
    Account varchar(50) NOT NULL,
    Balance varchar(50) NOT NULL,
    Source varchar(50) NOT NULL,
    Data numeric(18, 6) NOT NULL,
    CONSTRAINT PK_wip_cubs_hc PRIMARY KEY CLUSTERED 
    (
        Scenario ASC,
        ReportingPeriod ASC,
        Sh_Location ASC,
        Department ASC,
        ProductName ASC,
        Account ASC,
        Balance ASC,
        Source ASC
    )
)

CREATE NONCLUSTERED INDEX IX_wip_cubs_hc_Balance
ON [dbo].[wip_cubs_hc] ([Scenario],[Sh_Location],[Department],[Balance])
INCLUDE ([ReportingPeriod],[ProductName],[Account],[Source])

I'd love to know HOW to determine what's causing the slowdown, too.

Nathan Bedford
  • 8,924
  • 5
  • 35
  • 29
  • 1
    Please read accepted answer here: http://stackoverflow.com/questions/2825342/why-would-using-a-temp-table-be-faster-than-a-nested-query – David Fleeman Oct 30 '13 at 15:41

1 Answers1

0

I can answer the "How to determine the slowdown" question...

Take a look at the execution plan of both queries. You do this by going to the "Query" menu > "Display Estimated Execution Plan". The default keyboard shortcut is Ctrl+L. You can see the plan for multiple queries at once as well. Look at the type of operation being done. What you want to see are things like Index Seek instead of Index Scan, etc.

This article explains some of the other things to look for.

Without knowing the schema/indexes of all the tables involved, this is where I would suggest starting.

Best of Luck!

Ryan Anderson
  • 154
  • 5