Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 04 Nov 2002 @ 18:20:41 GMT

  <Prev Next>   <<First <Prev

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.

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020