Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Temp Tables, Derived Tables, Table Joins, Inline Tables - Performance Question

Posted on 2006-04-20
7
Medium Priority
?
377 Views
Last Modified: 2012-06-27
I have a large table named tbl_df_weekly with about 100 million rows in it...  of which there is a field called "vid" that is an integer representing a vendor id or particular subset of data... so each one of our customers has a VID that identifies that row of data as associated iwth them - so 99% of our queries have where vid = x and etc...  We also use a ton of functions to generate custom tables and lists that we then query against... we're beginning to run into some performance issues, and we've started to rewrite some of our queries...  a lot of our functions return inline tables, and some are multi-line table functions...  whenever we join against an inline table, everything is very fast, if we have a function that returns a table, but is derived from using a multi-line function, the join is painstakingly slow at times...  Also, we've started trying #temp tables at the top of our queries, but our major hold up there is that we can't use temp tables in functions?!?!  Below i've pasted 3 queries, all of which do the same thing, but have different execution plans, and different run times...  Can someone explain the one that *should* run the fastest and why?  Our database isn't big enough for any huge time differences...  I'm also considering horizontal partitioning the large table based on the VID column, would this speed up these queries?  Also, what type of index should i put on the table?  Right now i have a Clustered Index on VID, WMWeek...  however most of our queries also use (in the select statement) fields: pq, ps, and sometimes oh, oo, it  ....   all of which are integer  fields relating to sales data...  

QUERY 1
----------------------------------------------------

declare @stores table
(
      sid int
)
insert into @stores
select sid from func_GenerateStoreList(0,0)

declare @inums table
(
      inum int
)
insert into @inums
select inum from func_GetItemList(2,0,0)

declare @wmweeks table
(
      wmweek int
)
insert into @wmweeks
select wmweek from func_GetWmWeekList(0,200603,0,0)

select top 10
      round(dbo.func_DivByZero(df.ps, df.pq),2) as avg$,
      sum(df.pq) as pq,
      sum(df.ps) as ps,
      count(df.pq) as storecount,
      RANK() over (order by sum(df.pq) desc) as pqrank,
      RANK() over (order by sum(df.ps) desc) as psrank
from
      tbl_df_weekly df,
      @stores s,
      @inums i,
      @wmweeks w
where
      df.vid = 2 and df.wmweek = w.wmweek and df.sid = s.sid  and df.inum = i.inum
group by
      round(dbo.func_DivByZero(df.ps, df.pq),2)
order by
      avg$ desc


----------------------
QUERY 2
---------------------

select top 10
      round(dbo.func_DivByZero(df.ps, df.pq),2) as avg$,
      sum(df.pq) as pq,
      sum(df.ps) as ps,
      count(df.pq) as storecount
from
      tbl_df_weekly df,
      func_GenerateStoreList(0,0) s,
      func_GetItemList(2,0,0) i,
      func_GetWmWeekList(0,200603,0,0) w
where
      df.vid = 2 and df.wmweek = w.wmweek and df.sid = s.sid  and df.inum = i.inum
group by
      round(dbo.func_DivByZero(df.ps, df.pq),2)
order by
      avg$ desc

---------------
QUERY 3
--------------------

select sid into #stores from (select * from func_GenerateStoreList(0,0)) as tbl
select inum into #inums from (Select * from func_GetItemList(2,0,0)) as tbl
select wmweek into #wmweeks from (Select * from func_GetWmWeekList(0,200603,0,0)) as tbl


select top 10
      round(dbo.func_DivByZero(df.ps, df.pq),2) as avg$,
      sum(df.pq) as pq,
      sum(df.ps) as ps,
      count(df.pq) as storecount
from
      tbl_df_weekly df,
      #stores s,
      #inums i,
      #wmweeks w
where
      df.vid = 2 and df.wmweek = w.wmweek and df.sid = s.sid  and df.inum = i.inum
group by
      round(dbo.func_DivByZero(df.ps, df.pq),2)
order by
      avg$ desc



--------------------

of the above 3 queries, query 3 appears to be running the fastest...  Query 2 appears to be the slowest, and Query 1 is all over the board, sometimes fast, sometimes slow...  The slow part appears to be on the JOIN...  tbl_df_weekly in this case returns about 500,000 rows...   func_GetStorelist about 3000 rows, func_getitemlist about 300 rows, func_getwmweeklist about 2 rows ...  

Again, i can't use #temp tables in UDFs, however i can use @temp tables, what is the theoretical perfomance difference/advantage between the # and @ type tables?  aren't they both stored in memory therefor should be pretty fast?  Also, why would the 2nd query that treats the UDFs as basically inline tables take the longest by far???  Shouldn't sql query it once and then do the join on it?  or is it doing it for every row?

SQL Server 2005.

Thanks,
Nick
0
Comment
Question by:NickUA
  • 4
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16504591
NickUA,
>  can't use #temp tables in UDFs, however i can use @temp tables, what  is the theoretical perfomance difference/advantage
>between the # and  @ type tables?

There are several differences,
1. By default if the size of the table variable is less, then this will be allocated to Memory So increased performance..
   If it grows further, this will be moved to tempdb..
   Temp tables will be created and maintained in Tempdb
2. You can't create indexes on table vars
3. While using in Joins, table vars must have an alias

...
read this article, this gives you the performance comparison of these two
http://sqlnerd.blogspot.com 

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16504595
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16504603
one correction
> You can't create indexes on table vars

You can't create non clustured indexes on table variables
0
 
LVL 1

Author Comment

by:NickUA
ID: 16504630
how do i create an index on a table variable on the fly?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16504639
You can create a CI, by specifying the desired column as Primary Key

declare @tab Table (i int Primary Key, j int )
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

564 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