 | |
Re: Performance of SQL2000 queries vs SQL2005
microsoft.public.sqlserver.clustering microsoft.public.sqlserver.clustering
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Performance of SQL2000 queries vs SQL2005 -
05-11-2008
, 12:52 AM
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
#2
| |||
| |||
|
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
#3
| |||
| |||
|
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
#4
| |||
| |||
|
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
#5
| |||
| |||
|
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
#6
| |||
| |||
|
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
#7
| |||
| |||
|
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
#8
| |||
| |||
|
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
#9
| |||
| |||
|
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
#10
| |||
| |||
|
|
3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. |
Server 2000 in milliseconds
Many things have changed such SQL Server Engine, Query Optimization and etc
"DPM" <aa (AT) bb (DOT) cc> wrote
Quote:
|
Hi, I used a SQL2000 DB and executed the following query SELECT TOP 100 Int_Key, TrmNo, Package, Sent, Sent as DT_Sent, Reason, Message, RspnMess, RspnReqd, SenderId, SendCompId, TrmType, PrntDist, CustFields, Cancelled, CancelBy, CancelRsn , Int_Change FROM fTrmSum WHERE sendcompid='QAS' AND sent<'2008-03-13' AND sent>='2003-12-30' AND ackndate>='1900-01-01' AND ackndate<'2008-03-13' ORDER BY Sent DESC From the Profiler of SQL2000 gathered the following information Reads - 1697 Avg. Duration - 16 The execution plans are attached from SQL2000, with and without TOP 100 in the above query Create the same table structure in a SQL2005 DB with the indexes imported the data from SQL2000 Ran the above query From the Profiler of SQL2005 gathered the following information Reads - 1153 Avg. Duration - 101 The execution plans are attached from SQL2005, with and without TOP 100 in the above query Both DBs has the same table structures and data with the following indexes - sendcompid (non-unique, non-clustered) - sent (non-unique, non-clustered) - package (non-unique, non-clustered) - int_key (clustered) - senderid (non-unique, non-clustered) - trmno (non-unique, non-clustered) - trntype (non-unique, non-clustered) My questions are, 1. Eventhough the index are the same in both DBs, there are differences in the execution plans, eg Nested loops with the "TOP 100" clause, Index Seek 2. When the TOP 100 is removed SQL2005 uses only a Sort and Index Scan 3. Eventhough the reads are less in SQL2005 when compared with SQL2000 the durations are more Tried by Reindexing, Updating the Statistics, Creating a new table in SQL2005 but nothing helps to reduce the duration. Please advice on how to optimize these queries in SQL2005 |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.