| Groups | Blog | Home |
sql server programming : 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
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 [quoted text, click to view] "Vinod Kumar" wrote:
> 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 > > "Deepson Thomas" <DeepsonThomas@discussions.microsoft.com> wrote in message > news:A705E419-4CB6-4E13-8090-9DE09BF41A11@microsoft.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 > > > >
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 [quoted text, click to view] "Roji. P. Thomas" wrote:
> 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 > > > "Deepson Thomas" <DeepsonThomas@discussions.microsoft.com> wrote in message > news:A705E419-4CB6-4E13-8090-9DE09BF41A11@microsoft.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 > > > >
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 [quoted text, click to view] Deepson Thomas wrote:
>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 > >
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 [quoted text, click to view] "Deepson Thomas" <DeepsonThomas@discussions.microsoft.com> wrote in message news:A705E419-4CB6-4E13-8090-9DE09BF41A11@microsoft.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 >
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 [quoted text, click to view] Deepson Thomas wrote:
> > 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 > > "Roji. P. Thomas" wrote: > > > 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 > > > > > > "Deepson Thomas" <DeepsonThomas@discussions.microsoft.com> wrote in message > > news:A705E419-4CB6-4E13-8090-9DE09BF41A11@microsoft.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 > > > > > > >
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 [quoted text, click to view] "Deepson Thomas" <DeepsonThomas@discussions.microsoft.com> wrote in message news:A705E419-4CB6-4E13-8090-9DE09BF41A11@microsoft.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 >
Deepson,
[quoted text, click to view] >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 [quoted text, click to view] "Deepson Thomas" <DeepsonThomas@discussions.microsoft.com> wrote in message news:6218FB59-2418-4BE4-A686-6AF5E217B0CE@microsoft.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 > > "Roji. P. Thomas" wrote: > >> 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 >> >> >> "Deepson Thomas" <DeepsonThomas@discussions.microsoft.com> wrote in >> message >> news:A705E419-4CB6-4E13-8090-9DE09BF41A11@microsoft.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 >> > >> >> >>
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |