Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

SQL Server 2012 r2 Make faster Temp Table

Hi Experts,

I have this query without temp table it takes 2.24 sec to render
With Temp Table it take 4.02 sec
 to render is there a way to index the temp table inside the temp table?

-- Adobe Category
IF OBJECT_ID('TempDB..##tmpAdobe') IS NOT NULL
        DROP TABLE ##tmpAdobe;
 
CREATE TABLE ##tmpAdobe(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int PRIMARY KEY CLUSTERED WITH (IGNORE_DUP_KEY = ON),
IPLong Bigint NULL, 
)
 
Insert into ##tmpAdobe (Scandate, Category, Plugin, IPLong)
 
SELECT 
                                Scandate
                                ,'Adobe' as Category 
                                ,rs.[Plugin]
                                ,rs.IPLong
 
FROM [FODW_PVT].[Dflt].[Systems] as  rs 
                                INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin 
                                INNER JOIN (SELECT distinct Plugin, Category
                                FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
                                Where Category like '%Adobe%'
                                ) as Cat On Cat.Plugin = rs.Plugin
  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
                                and vn.Severity in ('Critical', 'High') 
Group By ScanDate, rs.Plugin, rs.IPLong
Order By Scandate desc
 
  -- Temp Table 
Select
                                AB.Scandate, 
                                AB.Category, 
                                AB.Plugin, 
                                AB.IPLong 
From ##tmpAdobe AB -- Adobe Category
 
-- Adobe Category
-- 6 month
-- without temp table
-- 4,830,682 records 
-- 2.24 sec 
-- Temp table
-- 335 records
-- 4.02 sec
-- End Adobe Category

Open in new window


Please just trying to make this faster

Thank you for helping me...
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Have you considered using a table variable instead, or creating a table based Function?
I'm not sure on the relative speeds of each. A variable will only exist for the duration of the batch, and so you don't need to check for existence and drop.

The tables function would be a good option as long as the SQL isn't changing - although you can pass parameters to them.

Kelvin
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Amour22015
Amour22015

ASKER

Hi and thanks for helping,

I should point out that it looks like the problem is within the:
INNER JOIN (SELECT distinct Plugin, Category
                                FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
                                Where Category like '%Adobe%'
                                ) as Cat On Cat.Plugin = rs.Plugin

Open in new window


Section.  

The reason, I noticed that when I do a Where Category = 'Adobe' it will run much faster.  There happens to be 7 different 'Adobe' in the table CVA_App_Catalog.  Thus the reason: Where Category like '%Adobe%'.  

Is there a way to do maybe a:
Cross Apply?

rs= Very large table
vn= medium table
cat= small table
Scandates = small table


I did take out the order by that brought the time from: 2.24 sec
 to: 2.08 sec
But
On the temp table:
From: 4.02 sec
To: 3.50 sec
And
When I took out the:
 Plugin int PRIMARY KEY CLUSTERED WITH (IGNORE_DUP_KEY = ON),
changed to:
Plugin int,
the time was:
>4.20 sec giving it the worst on slow time.

Please help and thanks...
Hi,
I have optimized your query. Also note that sometimes when we use the temp table the cost of inserting data and getting data from temp table has more cost then the advantage temp table provides.
Never use functions(They cannot retain their execution plan, use Stored procs instead.), and table variables(They dont have stats-always bad for performance in case of large data), Also dont use loops or cursors.

Please try below-

--

;WITH CTE AS 
(
    SELECT DISTINCT Plugin, Category
    FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
    Where Category LIKE '%Adobe%'
)
,CTE1 AS 
(
	SELECT TOP 6 Id from dflt.scandates ORDER BY ID DESC
)
SELECT 
	 Scandate
	,MAX('Adobe') as Category 
	,rs.[Plugin]
	,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs 
CROSS APPLY (SELECT * FROM Dflt.Vulnerabilities vn WHERE (vn.Severity = 'Critical' OR vn.Severity = 'High') AND vn.Plugin = rs.Plugin ) k0
CROSS APPLY (SELECT * FROM CTE Cat WHERE Cat.Plugin = rs.Plugin ) k1
CROSS APPLY (SELECT * FROM CTE1) k2 WHERE k2.Id = rs.ScandateID
GROUP BY ScanDate, rs.Plugin, rs.IPLong

--

Open in new window


Please let me know in case of any issue.

Hope it helps!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Amour22015

Note - Temp table in this case is USELESS. The reason is we are not doing any calculations/operation on the temp table. We are just inserting data in the temp table and Selecting data from the temp table.

Instead of this we can easily use data from SELECT itself. We are not getting any benefit from it. So whether is local or global temp table it does not matter.

I have optimized the query for you in my last comment. Please try that and let me know if case you face any issues.

Hope it helps!
Hi Experts and thanks for all help,

The main reason for the temp table is that this small query is one of many.  When I get all the records from each of the queries (temp tables) I will be doing some "calculations/operation " on like:

select *
From ##tmpAdobe AB
Inner Join ##tmpJava JA On AB.plugin = JA.Plugin Where AB.Total - JA.Total
Inner Join ##tmpMicrosoft MS On AB.plugin = MS.Plugin Where ((AB.Total - JA.Total) - MS.Total))
Inner join ##ETC.........
I know the calculations is not correct and that is not part of this post, but just giving reason why there is temp tables, they will be needed.  CTE would be way too slow for these calculations don't you think?

Thank you for helping...
Hi Experts,

Jim Horn
first points out:
>Plugin int PRIMARY KEY CLUSTERED WITH (IGNORE_DUP_KEY = ON),
>Order By Scandate desc

To be removed within the temp table.
when I did this the query increased to >4.20

Nakul Vachhrajani
mentions:
1.) a single # instead of ##
2.) sort through 335 records than 4830682 records   -- Note turns out I will be needing 4830682 records inside of the temp table.  Because of the "calculations/operation on many Temp Tables" I will be doing later when I have the "faster temp table(s)" problem at least brought   to  the best possible performance.  I can get all records inside the temp table by removing "PRIMARY KEY CLUSTERED WITH (IGNORE_DUP_KEY = ON"
3.) parallelism =  A MAXDOP = 1, thus: "OPTION (MAXDOP 1)".  SQL Server edition? = 2012 r2 does that make a difference?  Should I use this?

So far I have:

-- Adobe Category
IF OBJECT_ID('TempDB..#tmpAdobe') IS NOT NULL
        DROP TABLE #tmpAdobe;
 
CREATE TABLE #tmpAdobe(
Scandate Date NULL,
Category [varchar](255) NULL,
Plugin int ,
IPLong Bigint NULL, 
)
 
Insert into #tmpAdobe (Scandate, Category, Plugin, IPLong)
 
SELECT 
                                Scandate
                                ,'Adobe' as Category 
                                ,rs.[Plugin]
                                ,rs.IPLong
 
FROM [FODW_PVT].[Dflt].[Systems] as  rs 
                                INNER JOIN Dflt.Vulnerabilities as vn on vn.Plugin = rs.Plugin 
                                INNER JOIN (SELECT distinct Plugin, Category
                                FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
                                Where Category like '%Adobe%'
                                ) as Cat On Cat.Plugin = rs.Plugin
  
WHERE rs.ScandateID in (Select top (6 ) id from dflt.scandates order by id desc)
                                and vn.Severity in ('Critical', 'High') 
Group By ScanDate, rs.Plugin, rs.IPLong

 
  -- Temp Table 
Select
                                AB.Scandate, 
                                AB.Category, 
                                AB.Plugin, 
                                AB.IPLong 
From #tmpAdobe AB -- Adobe Category
Order By Scandate desc

Open in new window



Or should I use something like:
;WITH CTE AS 
(
    SELECT DISTINCT Plugin, Category
    FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
    Where Category LIKE '%Adobe%'
)
,CTE1 AS 
(
	SELECT TOP 6 Id from dflt.scandates ORDER BY ID DESC
)
SELECT 
	 Scandate
	,MAX('Adobe') as Category 
	,rs.[Plugin]
	,rs.IPLong
FROM [FODW_PVT].[Dflt].[Systems] as rs 
CROSS APPLY (SELECT * FROM Dflt.Vulnerabilities vn WHERE (vn.Severity = 'Critical' OR vn.Severity = 'High') AND vn.Plugin = rs.Plugin ) k0
CROSS APPLY (SELECT * FROM CTE Cat WHERE Cat.Plugin = rs.Plugin ) k1
CROSS APPLY (SELECT * FROM CTE1) k2 WHERE k2.Id = rs.ScandateID
GROUP BY ScanDate, rs.Plugin, rs.IPLong

Open in new window


Only inserting into a temp table?

Please Help and thanks
Hi,
How much time my query is taking ? What are you doing next after this ?
Hi Experts,

Pawan Kumar
I posted that information on ID: 41984273

After I get each of the many queries to the best performance one can get I will be doing calculations based on these temp tables.  Thus the reason why I can not use CTE instead of temp tables.  But can use the CTE on the Select queries for each temp table if that gives a better performance (makes it faster for the temp table on inserting into temp table).

Final result will be a SSRS report (Summarized Chart) see attached excel spreadsheet for an example.
Chart.xlsx
Hi,

In that case I would say it depends. I think we should first wrote the entire query with all the calculations and get the timings.

Now if the timings are on a higher side then we should check the execution plan and see what all physical operators are being used. After that we can decide on the what all performance techniques we can use.

Can you please post the entire query ? Note temp tables are usually very fast in nature and we can even create indexes on the same.

Hope it helps !
Hi Experts,

I have not written the whole Stored Procedure yet.  I am working on each temp table and have not gotten that far.  Will be asking for building query in later post like: "SQL Server 2012 r2 - calculations/operation on many Temp Tables"

Presidencies of order:  Remember each temp tables has to select from a very large table like (1,000,000,000,000) and growing.  No they did not make a table for each month example Maintable20161201 or Maintable20161101 And each month this very large table grows.

##tmpBaseline BL = 100,000 Records
##tmpSpecial SP = 250,000
##Adobe AB = 500,000
##tmpJava JA = 1,000,000
##tmpMicrosoft MS = 600,000
##tmpetc......Etc


All record numbers indicated are examples

See Chart

But will be using like:
Cross Apply or NOT EXISTS
to eliminate records


But for now lets not get ahead, this post is just for making the temp tables faster.  
As for an Execute reports don't think one is needed when I have already mentioned that at least one of the tables is very large causing a lot of slow process.  I am just looking to make the query faster - best performance I can get out of each query.  It just may well be that I am going to have to deal with >4.20 sec for each one of the many temp tables.  But once the temp tables are processed within the Stored Procedure then the calculations should be faster..


Please help and thanks...
Hi Armour22015,

I suggests for this you first completely write the functionality with SELECT,JOINs and Cross apply. Then check if the query is taking time.

If the query is taking time then decide what is the threshold. Lets say if my query is taking 15 seconds then I am fine. After that get the actual execution plan and check what all physical operators are taking time.

After that rebuild indexes and update stats. This might be one of the reasons for the query is working slow.

Now after doing these we need to look for the places where we can optimize the query, can we use temp table, can we use cross apply, how we can avoid order by, how we can avoid large table scans, Are we using SELECT * or temp tables , etc, etc ?

We may also need to consider if we need to add any indexes. This you will get when you will get the execution plan. There SQL Server engine will show an index in green color and how much % it will improve your query.

Note once we decide to go with temp tables , we can also consider indexes for them. Note once we decide to go with temp tables , we can also consider indexes for them.

Also note that we may also consider the Horizontal partitioning where we will partition the data based on dates. E.g Month wise data in each partition for large tables.
For details please refer - https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/


Hope it helps!
Hi Experts,

Because of the time it takes to run this temp table > 8 mins.

yes I have taken out:
order By
PRIMARY KEY CLUSTERED WITH (IGNORE_DUP_KEY = ON)

Have changed to
#tmpAdobe
Added:
OPTION (MAXDOP 1) -- increased the time

I might have to create a rollup table(s) for each of the temp tables using SSIS to run over night. Instead of using temp tables each will have a table.

Seems there is no other way to get around this problem....

Thanks
Hi Experts,

I had mentioned that it looks like the problem is:
INNER JOIN (SELECT distinct Plugin, Category
                                FROM [FODW_PVT].[Dflt].[CVA_App_Catalog]
                                Where Category like '%Adobe%'
                                ) as Cat On Cat.Plugin = rs.Plugin

Open in new window


And:
The reason, I noticed that when I do a Where Category = 'Adobe' it will run much faster.  There happens to be 7 different 'Adobe' in the table CVA_App_Catalog.  Thus the reason: Where Category like '%Adobe%'.  

What I should be doing is instead of Like do a In ('','Name all 7','so on')

this made the temp table go from >8mins to < 2 mins.  It was a simple solution...

Thanks you all for helping...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great, thank you all for helping