Query Execution and Performance |
|
Author |
Message |
daverinda
|
Posted: Tue Dec 14 23:55:50 CST 2004 |
Top |
SQL Server Developer >> Query Execution and Performance
There are three tables and all tables contains 100000 records and contains
exactly same data., u can say each table is a carbon copy of other one.
i executed 4 queries as follows
1) SELECT * FROM tbl1
2) SELECT * FROM tbl2
3) SELECT * FROM tbl3
4) SELECT * FROM tbl4
what i want to know is which query will execute faster and why. now may be u
can ask y dont i try this myself...i tried in my PC but the results are
same..may b coz itz a new fast machine or may be even the db and the
application is in the same machine... whatever i didnt got the exact result i
want ... hope anybody can help me ... the table structures and the constrains
are listed below
##############################
Table 1 - tbl1
##############################
id int NOT NULL identity(1,1)
ProdCode VarChar(10) NOT NULL
ProdName varcChar(200) NOT NULL
##############################
Table 2 - tbl2
##############################
id int NOT NULL identity(1,1)
ProdCode VarChar(10) NOT NULL PK
ProdName varcChar(200) NOT NULL
##############################
Table 3 - tbl3
##############################
id int NOT NULL identity(1,1)
ProdCode VarChar(10) NOT NULL Index - Clustered
ProdName varcChar(200) NOT NULL
##############################
Table 4 - tbl4
##############################
id int NOT NULL identity(1,1)
ProdCode VarChar(10) NOT NULL Index - NON Clustered
ProdName varcChar(200) NOT NULL
Thanks in advance
Deepson Thomas
SQL Server305
|
|
|
|
|
Vinod
|
Posted: Tue Dec 14 23:55:50 CST 2004 |
Top |
SQL Server Developer >> Query Execution and Performance
I *personally* dont think it makes a difference for the scenario described.
Anyway if you have a clustered index on the Identity column and we are
selecting all the rows we are going to make a clusteres index scan only.
Else this will be a table scan. Unless you have more clauses added to the
queries. I dont see much of a difference in the outputs.
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> There are three tables and all tables contains 100000 records and contains
> exactly same data., u can say each table is a carbon copy of other one.
>
> i executed 4 queries as follows
> 1) SELECT * FROM tbl1
> 2) SELECT * FROM tbl2
> 3) SELECT * FROM tbl3
> 4) SELECT * FROM tbl4
>
> what i want to know is which query will execute faster and why. now may be
u
> can ask y dont i try this myself...i tried in my PC but the results are
> same..may b coz itz a new fast machine or may be even the db and the
> application is in the same machine... whatever i didnt got the exact
result i
> want ... hope anybody can help me ... the table structures and the
constrains
> are listed below
>
> ##############################
> Table 1 - tbl1
> ##############################
> id int NOT NULL identity(1,1)
> ProdCode VarChar(10) NOT NULL
> ProdName varcChar(200) NOT NULL
>
>
> ##############################
> Table 2 - tbl2
> ##############################
> id int NOT NULL identity(1,1)
> ProdCode VarChar(10) NOT NULL PK
> ProdName varcChar(200) NOT NULL
>
>
> ##############################
> Table 3 - tbl3
> ##############################
> id int NOT NULL identity(1,1)
> ProdCode VarChar(10) NOT NULL Index - Clustered
> ProdName varcChar(200) NOT NULL
>
>
> ##############################
> Table 4 - tbl4
> ##############################
> id int NOT NULL identity(1,1)
> ProdCode VarChar(10) NOT NULL Index - NON Clustered
> ProdName varcChar(200) NOT NULL
>
>
> Thanks in advance
> Deepson Thomas
>
|
|
|
|
|
Steve
|
Posted: Wed Dec 15 00:06:10 CST 2004 |
Top |
SQL Server Developer >> Query Execution and Performance
Deepson,
If the data has been inserted into each table and there have been no
data modification statements (delete, update, insert), there may be
little difference between the four tables. The first table is a heap, the
second and third consist only of a clustered index (unique in one
case and not the other, but if the data is unique, they are almost
identical in size and structure), and the fourth has two components,
a heap and a separate nonclustered index, but the nonclustered index
will not be used by the query. There is little difference in size between
a heap and the leaf level of a clustered index, so the I/O, which dominates
the cost of SELECT * (assuming no calculated columns), is very close to
equal in each case. The order in which data is inserted doesn't matter to
the heap, but could have a slightly detrimental effect on the clustered
indexes.
If there have been numerous data modification statements, however, many
other factors come into play, and any of these tables can become fragmented,
increasing the time it takes to return the results. Without knowing
anything
at all beside what you say here, I would choose the table with the PK, but
not with any particularly good reason - just because tables should have
primary keys, and because for a typical workload, if there is such a think,
a clustered index is usually a good thing to have.
It's rare in my experience to have these kinds of choices - most of the
time there are other design considerations that make one or another
more suitable.
Steve Kass
Drew University
>There are three tables and all tables contains 100000 records and contains
>exactly same data., u can say each table is a carbon copy of other one.
>
>i executed 4 queries as follows
> 1) SELECT * FROM tbl1
> 2) SELECT * FROM tbl2
> 3) SELECT * FROM tbl3
> 4) SELECT * FROM tbl4
>
>what i want to know is which query will execute faster and why. now may be u
>can ask y dont i try this myself...i tried in my PC but the results are
>same..may b coz itz a new fast machine or may be even the db and the
>application is in the same machine... whatever i didnt got the exact result i
>want ... hope anybody can help me ... the table structures and the constrains
>are listed below
>
>##############################
> Table 1 - tbl1
>##############################
>id int NOT NULL identity(1,1)
>ProdCode VarChar(10) NOT NULL
>ProdName varcChar(200) NOT NULL
>
>
>##############################
> Table 2 - tbl2
>##############################
>id int NOT NULL identity(1,1)
>ProdCode VarChar(10) NOT NULL PK
>ProdName varcChar(200) NOT NULL
>
>
>##############################
> Table 3 - tbl3
>##############################
>id int NOT NULL identity(1,1)
>ProdCode VarChar(10) NOT NULL Index - Clustered
>ProdName varcChar(200) NOT NULL
>
>
>##############################
> Table 4 - tbl4
>##############################
>id int NOT NULL identity(1,1)
>ProdCode VarChar(10) NOT NULL Index - NON Clustered
>ProdName varcChar(200) NOT NULL
>
>
>Thanks in advance
>Deepson Thomas
>
>
>
|
|
|
|
|
Roji
|
Posted: Wed Dec 15 00:14:40 CST 2004 |
Top |
SQL Server Developer >> Query Execution and Performance
Deepson,
Instead of doing SELECT * FROM tbl1,
add a WHERE clause on the ProdCode and
you can see the difference.
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
> There are three tables and all tables contains 100000 records and contains
> exactly same data., u can say each table is a carbon copy of other one.
>
> i executed 4 queries as follows
> 1) SELECT * FROM tbl1
> 2) SELECT * FROM tbl2
> 3) SELECT * FROM tbl3
> 4) SELECT * FROM tbl4
>
> what i want to know is which query will execute faster and why. now may be
> u
> can ask y dont i try this myself...i tried in my PC but the results are
> same..may b coz itz a new fast machine or may be even the db and the
> application is in the same machine... whatever i didnt got the exact
> result i
> want ... hope anybody can help me ... the table structures and the
> constrains
> are listed below
>
> ##############################
> Table 1 - tbl1
> ##############################
> id int NOT NULL identity(1,1)
> ProdCode VarChar(10) NOT NULL
> ProdName varcChar(200) NOT NULL
>
>
> ##############################
> Table 2 - tbl2
> ##############################
> id int NOT NULL identity(1,1)
> ProdCode VarChar(10) NOT NULL PK
> ProdName varcChar(200) NOT NULL
>
>
> ##############################
> Table 3 - tbl3
> ##############################
> id int NOT NULL identity(1,1)
> ProdCode VarChar(10) NOT NULL Index - Clustered
> ProdName varcChar(200) NOT NULL
>
>
> ##############################
> Table 4 - tbl4
> ##############################
> id int NOT NULL identity(1,1)
> ProdCode VarChar(10) NOT NULL Index - NON Clustered
> ProdName varcChar(200) NOT NULL
>
>
> Thanks in advance
> Deepson Thomas
>
|
|
|
|
|
DeepsonThomas
|
Posted: Wed Dec 15 00:19:06 CST 2004 |
Top |
SQL Server Developer >> Query Execution and Performance
Hi Vinod,
iam 100% sure that there is execution time difference and that difference
may be in milliseconds. just like u said if the table have clustered index
in identity column then there will be only index scan otherwise table scan
right ?? ... what if the clustered index is in a column which is not
identity and varchar.
Deepson
> I *personally* dont think it makes a difference for the scenario described.
> Anyway if you have a clustered index on the Identity column and we are
> selecting all the rows we are going to make a clusteres index scan only.
> Else this will be a table scan. Unless you have more clauses added to the
> queries. I dont see much of a difference in the outputs.
>
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> > There are three tables and all tables contains 100000 records and contains
> > exactly same data., u can say each table is a carbon copy of other one.
> >
> > i executed 4 queries as follows
> > 1) SELECT * FROM tbl1
> > 2) SELECT * FROM tbl2
> > 3) SELECT * FROM tbl3
> > 4) SELECT * FROM tbl4
> >
> > what i want to know is which query will execute faster and why. now may be
> u
> > can ask y dont i try this myself...i tried in my PC but the results are
> > same..may b coz itz a new fast machine or may be even the db and the
> > application is in the same machine... whatever i didnt got the exact
> result i
> > want ... hope anybody can help me ... the table structures and the
> constrains
> > are listed below
> >
> > ##############################
> > Table 1 - tbl1
> > ##############################
> > id int NOT NULL identity(1,1)
> > ProdCode VarChar(10) NOT NULL
> > ProdName varcChar(200) NOT NULL
> >
> >
> > ##############################
> > Table 2 - tbl2
> > ##############################
> > id int NOT NULL identity(1,1)
> > ProdCode VarChar(10) NOT NULL PK
> > ProdName varcChar(200) NOT NULL
> >
> >
> > ##############################
> > Table 3 - tbl3
> > ##############################
> > id int NOT NULL identity(1,1)
> > ProdCode VarChar(10) NOT NULL Index - Clustered
> > ProdName varcChar(200) NOT NULL
> >
> >
> > ##############################
> > Table 4 - tbl4
> > ##############################
> > id int NOT NULL identity(1,1)
> > ProdCode VarChar(10) NOT NULL Index - NON Clustered
> > ProdName varcChar(200) NOT NULL
> >
> >
> > Thanks in advance
> > Deepson Thomas
> >
>
>
>
|
|
|
|
|
DeepsonThomas
|
Posted: Wed Dec 15 00:51:01 CST 2004 |
Top |
SQL Server Developer >> Query Execution and Performance
Hi Roji,
i got ur point, like u want to increase the load on query so that i can
visually view the time diff. but actually seeing the time diff is not my
need. what i want to know is about the behaviour of sql server in executing
queries when ther table / colums have different constraints like clustered /
Non Clustered index,PK etc... hope u understand my need
Regards
Deepson
> Deepson,
>
> Instead of doing SELECT * FROM tbl1,
> add a WHERE clause on the ProdCode and
> you can see the difference.
>
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
>
> > There are three tables and all tables contains 100000 records and contains
> > exactly same data., u can say each table is a carbon copy of other one.
> >
> > i executed 4 queries as follows
> > 1) SELECT * FROM tbl1
> > 2) SELECT * FROM tbl2
> > 3) SELECT * FROM tbl3
> > 4) SELECT * FROM tbl4
> >
> > what i want to know is which query will execute faster and why. now may be
> > u
> > can ask y dont i try this myself...i tried in my PC but the results are
> > same..may b coz itz a new fast machine or may be even the db and the
> > application is in the same machine... whatever i didnt got the exact
> > result i
> > want ... hope anybody can help me ... the table structures and the
> > constrains
> > are listed below
> >
> > ##############################
> > Table 1 - tbl1
> > ##############################
> > id int NOT NULL identity(1,1)
> > ProdCode VarChar(10) NOT NULL
> > ProdName varcChar(200) NOT NULL
> >
> >
> > ##############################
> > Table 2 - tbl2
> > ##############################
> > id int NOT NULL identity(1,1)
> > ProdCode VarChar(10) NOT NULL PK
> > ProdName varcChar(200) NOT NULL
> >
> >
> > ##############################
> > Table 3 - tbl3
> > ##############################
> > id int NOT NULL identity(1,1)
> > ProdCode VarChar(10) NOT NULL Index - Clustered
> > ProdName varcChar(200) NOT NULL
> >
> >
> > ##############################
> > Table 4 - tbl4
> > ##############################
> > id int NOT NULL identity(1,1)
> > ProdCode VarChar(10) NOT NULL Index - NON Clustered
> > ProdName varcChar(200) NOT NULL
> >
> >
> > Thanks in advance
> > Deepson Thomas
> >
>
>
>
|
|
|
|
|
Roji
|
Posted: Wed Dec 15 01:14:14 CST 2004 |
Top |
SQL Server Developer >> Query Execution and Performance
Deepson,
>what i want to know is about the behaviour of sql server in executing
>queries when ther table / colums have different constraints like clustered
>/
>Non Clustered index,PK etc
You can analyse the execution plan to study the differences
in the query execution, based on the (non) availability of the indexes.
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
>
> Hi Roji,
> i got ur point, like u want to increase the load on query so that i can
> visually view the time diff. but actually seeing the time diff is not my
> need. what i want to know is about the behaviour of sql server in
> executing
> queries when ther table / colums have different constraints like clustered
> /
> Non Clustered index,PK etc... hope u understand my need
>
> Regards
> Deepson
>
>
>> Deepson,
>>
>> Instead of doing SELECT * FROM tbl1,
>> add a WHERE clause on the ProdCode and
>> you can see the difference.
>>
>>
>> --
>> Roji. P. Thomas
>> Net Asset Management
>> https://www.netassetmanagement.com
>>
>>
>> message
>> > There are three tables and all tables contains 100000 records and
>> > contains
>> > exactly same data., u can say each table is a carbon copy of other one.
>> >
>> > i executed 4 queries as follows
>> > 1) SELECT * FROM tbl1
>> > 2) SELECT * FROM tbl2
>> > 3) SELECT * FROM tbl3
>> > 4) SELECT * FROM tbl4
>> >
>> > what i want to know is which query will execute faster and why. now may
>> > be
>> > u
>> > can ask y dont i try this myself...i tried in my PC but the results are
>> > same..may b coz itz a new fast machine or may be even the db and the
>> > application is in the same machine... whatever i didnt got the exact
>> > result i
>> > want ... hope anybody can help me ... the table structures and the
>> > constrains
>> > are listed below
>> >
>> > ##############################
>> > Table 1 - tbl1
>> > ##############################
>> > id int NOT NULL identity(1,1)
>> > ProdCode VarChar(10) NOT NULL
>> > ProdName varcChar(200) NOT NULL
>> >
>> >
>> > ##############################
>> > Table 2 - tbl2
>> > ##############################
>> > id int NOT NULL identity(1,1)
>> > ProdCode VarChar(10) NOT NULL PK
>> > ProdName varcChar(200) NOT NULL
>> >
>> >
>> > ##############################
>> > Table 3 - tbl3
>> > ##############################
>> > id int NOT NULL identity(1,1)
>> > ProdCode VarChar(10) NOT NULL Index - Clustered
>> > ProdName varcChar(200) NOT NULL
>> >
>> >
>> > ##############################
>> > Table 4 - tbl4
>> > ##############################
>> > id int NOT NULL identity(1,1)
>> > ProdCode VarChar(10) NOT NULL Index - NON Clustered
>> > ProdName varcChar(200) NOT NULL
>> >
>> >
>> > Thanks in advance
>> > Deepson Thomas
>> >
>>
>>
>>
|
|
|
|
|
Gert-Jan
|
Posted: Wed Dec 15 04:39:41 CST 2004 |
Top |
SQL Server Developer >> Query Execution and Performance
This completely depends on the query you use. The query SELECT * FROM
tbl1 is a very poor example, because it is unlikely to be performed very
often, and each query plan will use a table scan/clustered index scan,
simply because there is no viable alternative.
It is when you start joining table, start applying WHERE clauses and
start using just a selection of all columns that you may start to see
significant differences.
Hope this helps,
Gert-Jan
>
> Hi Roji,
> i got ur point, like u want to increase the load on query so that i can
> visually view the time diff. but actually seeing the time diff is not my
> need. what i want to know is about the behaviour of sql server in executing
> queries when ther table / colums have different constraints like clustered /
> Non Clustered index,PK etc... hope u understand my need
>
> Regards
> Deepson
>
>
> > Deepson,
> >
> > Instead of doing SELECT * FROM tbl1,
> > add a WHERE clause on the ProdCode and
> > you can see the difference.
> >
> >
> > --
> > Roji. P. Thomas
> > Net Asset Management
> > https://www.netassetmanagement.com
> >
> >
> > > There are three tables and all tables contains 100000 records and contains
> > > exactly same data., u can say each table is a carbon copy of other one.
> > >
> > > i executed 4 queries as follows
> > > 1) SELECT * FROM tbl1
> > > 2) SELECT * FROM tbl2
> > > 3) SELECT * FROM tbl3
> > > 4) SELECT * FROM tbl4
> > >
> > > what i want to know is which query will execute faster and why. now may be
> > > u
> > > can ask y dont i try this myself...i tried in my PC but the results are
> > > same..may b coz itz a new fast machine or may be even the db and the
> > > application is in the same machine... whatever i didnt got the exact
> > > result i
> > > want ... hope anybody can help me ... the table structures and the
> > > constrains
> > > are listed below
> > >
> > > ##############################
> > > Table 1 - tbl1
> > > ##############################
> > > id int NOT NULL identity(1,1)
> > > ProdCode VarChar(10) NOT NULL
> > > ProdName varcChar(200) NOT NULL
> > >
> > >
> > > ##############################
> > > Table 2 - tbl2
> > > ##############################
> > > id int NOT NULL identity(1,1)
> > > ProdCode VarChar(10) NOT NULL PK
> > > ProdName varcChar(200) NOT NULL
> > >
> > >
> > > ##############################
> > > Table 3 - tbl3
> > > ##############################
> > > id int NOT NULL identity(1,1)
> > > ProdCode VarChar(10) NOT NULL Index - Clustered
> > > ProdName varcChar(200) NOT NULL
> > >
> > >
> > > ##############################
> > > Table 4 - tbl4
> > > ##############################
> > > id int NOT NULL identity(1,1)
> > > ProdCode VarChar(10) NOT NULL Index - NON Clustered
> > > ProdName varcChar(200) NOT NULL
> > >
> > >
> > > Thanks in advance
> > > Deepson Thomas
> > >
> >
> >
> >
|
|
|
|
|
|
|