Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GreatInca
Posting Yak Master
102 Posts |
 Posted - 2002-12-05 : 17:16:31
|
| Any way to create statistics on table variables?. Query optimizer making very bad query plan (assuming it returns only 1 row) in query with a lot of rows coming from 2 table variables. I know this from experience - no statistics mean highly underestmiated row counts on queries that crunch lots of rows (3,000,000-10,000,000 to be reduced to a few thousand by aggragates in this query). |
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2002-12-05 : 17:21:11
|
| Oh yeah the two table variables contain about 40,000 rows, and 20,000 rows. Both have 4 ints and a datetime (24 bytes) in their rows. The lack of statistics and the under estimation of rows is causing the optimizer to use loop joins instead of hash joins and it likes to ignore hints when table vars / temp tables are involved.Edited by - GreatInca on 12/05/2002 17:24:52 |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-05 : 17:22:34
|
| No, you'd have to use temp tables to create indexes or statistics. |
|
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2002-12-05 : 17:25:39
|
| ok, thanks. |
|
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2002-12-05 : 17:46:09
|
| Done changing code. Statistics helps a lot - query runs 3 times faster now. Guess table variables aren't always faster. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-12-07 : 01:49:05
|
IMHO, the primary reason for extra speed using Table variables is coz they reside in memory but if the data gets huge and memory cant accomodate it then it results in paging thereby speed suffers.quote:
Guess table variables aren't always faster.
-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo EmersonEdited by - Nazim on 12/07/2002 01:50:14 |
|
|
|
|
|
|
Statistics for table varaibles