index temp tables?

Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
look at the Execution Plan I see about 25 table scans on these guys. The Row
Count and Estimated Row Size are usually only about 3-120 rows though. Do you
guys Index temp tables this size?
TIA,
ChrisR
Depends on the row size to some degree but usually tables that small it
won't matter much. Most if not all the rows are on a single page anyway.
But the only way to know for sure is to test it with and without to see if
the gain is worth the time and effort to create the indexes.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A23705AA-6B95-43E0-877A-05B1E457DE27@.microsoft.com...
> Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
> look at the Execution Plan I see about 25 table scans on these guys. The
> Row
> Count and Estimated Row Size are usually only about 3-120 rows though. Do
> you
> guys Index temp tables this size?
>
> --
> TIA,
> ChrisR

index temp tables?

Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
look at the Execution Plan I see about 25 table scans on these guys. The Row
Count and Estimated Row Size are usually only about 3-120 rows though. Do yo
u
guys Index temp tables this size?
TIA,
ChrisRDepends on the row size to some degree but usually tables that small it
won't matter much. Most if not all the rows are on a single page anyway.
But the only way to know for sure is to test it with and without to see if
the gain is worth the time and effort to create the indexes.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A23705AA-6B95-43E0-877A-05B1E457DE27@.microsoft.com...
> Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
> look at the Execution Plan I see about 25 table scans on these guys. The
> Row
> Count and Estimated Row Size are usually only about 3-120 rows though. Do
> you
> guys Index temp tables this size?
>
> --
> TIA,
> ChrisR

index temp tables?

Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
look at the Execution Plan I see about 25 table scans on these guys. The Row
Count and Estimated Row Size are usually only about 3-120 rows though. Do you
guys Index temp tables this size?
--
TIA,
ChrisRDepends on the row size to some degree but usually tables that small it
won't matter much. Most if not all the rows are on a single page anyway.
But the only way to know for sure is to test it with and without to see if
the gain is worth the time and effort to create the indexes.
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A23705AA-6B95-43E0-877A-05B1E457DE27@.microsoft.com...
> Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
> look at the Execution Plan I see about 25 table scans on these guys. The
> Row
> Count and Estimated Row Size are usually only about 3-120 rows though. Do
> you
> guys Index temp tables this size?
>
> --
> TIA,
> ChrisRsql

Index scans while using PreparedStatements

I have a query that does a 3-table join. The tables involved
are
1. ct_list_item(li_key int, li_code nvarchar(329))
li_key is the primary key
2. ct_list_item_lang(li_key int, lang_code varchar(5),
value nvarchar(64))
li_key and lang_code form a 2-part primary key.
3. ct_list_item_map(list_key int, li_key int, list_level int)
list_key and li_key form a 2-part primary key.
All of these tables have clustered indexes on their primary
keys.
Here's the query:
SELECT map.list_key, map.li_key, lil.value, li.li_code
FROM ct_list_item li
JOIN ct_list_item_map map on map.li_key = li.li_key
JOIN ct_list_item_lang lil on li.li_key = lil.li_key and
lil.lang_code='en'
WHERE map.list_key= 1011
I am finding that when I run the query using a JDBC
PreparedStatement with bind variables (on lang_code and
list_key), the query performs an index scan over the
clustered index on the ct_list_item_lang's primary key.
However if I run the query using a JDBC Statement without
bind variables, it does an clustered index seek. I am
puzzled as to why there is a difference.
Since we hard code values in the query statement, the SQL knows the values
before hand and can use Index seek. For
preparedstatement using parameters SQL has no knowledge about the value for
each
parameter during the preparation hence Index Scan is used. This results in
Preparedstatement running slower than regular Statement with hard coded
query.
If you need to use parameterized query in code, in stead of using ad hoc
query, you can create a stored proc and call it from Java code. This
should generate a plan using Index Seek which results in better performance.
sql

Index Defragmentation

None of the system tables in our databases have a significant number of
pages, and I can't see there would be many scans happening, but in the
interests of completeness should system tables be defragmented if they show
high fragmentation?Hi Ben
I would say yes, they are no different to any other table in that respect
and should be optimized in a similar way. Unfortunately you can't run DBCC
INDEXDEFRAG or DBCC DBREINDEX on system tables, so you will have to hope that
the system has it's own way of keeping these tables in tune.
John
"BenUK" wrote:
> None of the system tables in our databases have a significant number of
> pages, and I can't see there would be many scans happening, but in the
> interests of completeness should system tables be defragmented if they show
> high fragmentation?
>

Index Defragmentation

None of the system tables in our databases have a significant number of
pages, and I can't see there would be many scans happening, but in the
interests of completeness should system tables be defragmented if they show
high fragmentation?Hi Ben
I would say yes, they are no different to any other table in that respect
and should be optimized in a similar way. Unfortunately you can't run DBCC
INDEXDEFRAG or DBCC DBREINDEX on system tables, so you will have to hope tha
t
the system has it's own way of keeping these tables in tune.
John
"BenUK" wrote:

> None of the system tables in our databases have a significant number of
> pages, and I can't see there would be many scans happening, but in the
> interests of completeness should system tables be defragmented if they sho
w
> high fragmentation?
>