Re: Multiple of 4? Better performance?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 11/01/04

Next message: Hugo Kornelis: "Re: Strange Error"
Previous message: Hugo Kornelis: "Re: Why data could not be committed into table?"
In reply to: Alpha: "Re: Multiple of 4? Better performance?"
Messages sorted by: [ date ] [ thread ]

Date: Mon, 01 Nov 2004 11:13:30 +0100

On Mon, 1 Nov 2004 17:19:44 +0800, Alpha wrote:

(snip)
>So, to find out the answer, the only way is to measure and compare the
>performance using a large amount of rows with different set of column width.
>
>Anyone have carried out such kind of experiment?

Hi Alpha,

Have you?

drop table Normal
go
CREATE TABLE Normal (Col1 CHAR(1) NOT NULL,
                     Col2 CHAR(1) NOT NULL,
                     Col3 CHAR(1) NOT NULL,
                     Col4 CHAR(1) NOT NULL,
                     Col5 CHAR(1) NOT NULL,
                     PRIMARY KEY (Col1, Col2, Col3, Col4, Col5))
go
drop table Mult_4
go
CREATE TABLE Mult_4 (Col1 CHAR(4) NOT NULL
                          CHECK(Col1 LIKE '_ '),
                     Col2 CHAR(4) NOT NULL
                          CHECK(Col2 LIKE '_ '),
                     Col3 CHAR(4) NOT NULL
                          CHECK(Col3 LIKE '_ '),
                     Col4 CHAR(4) NOT NULL
                          CHECK(Col4 LIKE '_ '),
                     Col5 CHAR(4) NOT NULL
                          CHECK(Col5 LIKE '_ '),
                     PRIMARY KEY (Col1, Col2, Col3, Col4, Col5))
go
insert Normal
select char(a.n), char(b.n), char(c.n), char(d.n), char(e.n)
from numbers a, numbers b, numbers c, numbers d, numbers e
where a.n between 66 and 75
and b.n between 66 and 75
and c.n between 66 and 75
and d.n between 66 and 75
and e.n between 66 and 75
insert Mult_4 select * from Normal
go
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics io on
set statistics time on
select * from Normal where Col2 = 'C' and Col3 = 'D' and Col4 = 'E' and
Col5 = 'F'
set statistics time off
set statistics io off
go
dbcc dropcleanbuffers
dbcc freeproccache
go
set statistics io on
set statistics time on
select * from Mult_4 where Col2 = 'C' and Col3 = 'D' and Col4 = 'E' and
Col5 = 'F'
set statistics time off
set statistics io off
go

Results (snipped down to relevant part):

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Col1 Col2 Col3 Col4 Col5
---- ---- ---- ---- ----
B C D E F
C C D E F
D C D E F
E C D E F
F C D E F
G C D E F
H C D E F
I C D E F
J C D E F
K C D E F

Table 'Normal'. Scan count 1, logical reads 175, physical reads 0,
read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 25 ms.

Table 'Mult_4'. Scan count 1, logical reads 361, physical reads 0,
read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 31 ms.

Best, Hugo

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Next message: Hugo Kornelis: "Re: Strange Error"
Previous message: Hugo Kornelis: "Re: Why data could not be committed into table?"
In reply to: Alpha: "Re: Multiple of 4? Better performance?"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: Monitoring Physical Reads of a Query on sql 2005
... Are you saying dbcc dropcleanbuffers doesn't flush buffer pool? ... 1) I am sole user of the machine 2) The machine has 2gig of ram 3) The default memory allocation on sql server 2005 is dynamic from 0 to the limit or almost 2gig. ... set statistics io off ...
(microsoft.public.sqlserver.server)
Re: Data File
... Thanks for reminding me that Jet isn't the only text driver ... >set statistics time on ... >>better plan, your solution gets the data quicker in the test I ran!) ... >>set statistics time on ...
(microsoft.public.sqlserver.programming)
Re: Monitoring Statistics inside a DTS package
... Set statistics Time on ... Wayne Snyder, MCDBA, SQL Server MVP ... The DTS package takes a long time to ...
(microsoft.public.sqlserver.server)
Re: Query execution time
... Look at the SET STATISTICS TIME statement. ... > select getdate() ...
(microsoft.public.sqlserver.server)
Re: Usage of Views
... Look up SET STATISTICS IO, SET STATISTICS IO an SET STATISTICS TIME ... being consumed to resolve the query. ... I support the Professional Association for SQL Server ... Would there be any chance of optimizer applying the ...
(microsoft.public.sqlserver.server)