Archives of the TeradataForum
Message Posted: Mon, 04 Nov 2002 @ 18:20:41 GMT
| Subj: | | Re: Does Teradata have anything similar to Procedural caching (supported with SQL Server) |
| |
| From: | | Terry Stover |
You didn't indicate the size of the tables. I wouldn't be surprised if sqlserver was faster in a single user environment, particarly
with a clustered index lookup. I also wouldn't be surprised when it choked on multiuser.
As mentioned before, for sqlserver you need to make sure you have clean buffers before benchmarking, otherwise you'll get cached results.
Run DBCC DROPCLEANBUFFERS to flush the sqlserver buffers between queries.
I don't like any benchmarking based on time / duration. With Teradata use PMON to get the disk and cpu stats. With SQLServer you can
capture the stats from the sysprocess table before and after the query, then calculate the delta: "select getdate(), cpu, physical_io from
master..sysprocesses with (nolock) where spid = @@spid" The cpu is in milliseconds and IO is number of (8K?) pages. You can run the after
running "set showplan_all on" in the same session, but I never had a lot of faith in the numbers.
On the design side, SQLserver seems to work better with subqueries, but they can cause problems Teradata. Derived tables (subqueries
that aren't correlated) go to spool, so you don't leverage your indexes. You also need to seriously look at your Teradata primary indexes
for data distribution.
The sqlserver approach of giving each table a unique primary index, then creating secondaries on the join columns doesn't work all that
well on Teradata. With sqlserver the secondaries can be about 10% selective and still be useful, with teradata it's more like 1-2%. In the
explain if you're seeing "duplicated on all amps", you are redistributing data for the joins, which can really kill you. One big app I
ported in the past had about a 20x performance hit using the sqlserver upi instead of a nupi that was more appropriate for the joins. In my
experience, judicious use of secondaries can make even a poorly designed sqlserver db run well, but there's little hope for a Teradata db
with inappropriate primary indexes.
| 
