display estimated execution plan

saloj
saloj used Ask the Experts™
on
I have created DB on local as we have on server and we have the following query.
SELECT buyitem.dataid, SUM(buyitem.cost_point) AS total,itemName.itemName_C,buyitem.cost_point FROM buyitem
 LEFT OUTER JOIN itemName ON buyitem.dataid = itemName.itemID WHERE (buyitem.create_time > '2010/06/01') and
 (buyitem.create_time < '2010/06/29') GROUP BY  buyitem.dataid, itemName.itemName_C,buyitem.cost_point ORDER BY  total DESC

And also attached display estimated execution plan as following.
1.server.bmp
2.local.bmp
why both looks different on server and local
server.bmp
local.bmp
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Also we web application on .net2.0 and  get error when we searched for large scale date from the web. how could it be resolved.

Thanks
Do you have a different set of data on the two databases? The SQL optimiser could choose different plans if there are different amounts of data in the tables. Also if you have a different number of CPU cores between the two machines you may see a different plan.

Author

Commented:
Both databases have same data. how could it be different though both have same table design and same data. does different machine display different execution plan as i uploaded pics from server and local pc?
Do you have exactly the same build of SQL Server on both machines? You can get the version by executing SELECT @@VERSION. Different builds of SQL can yield different plans for the same query.

Also have you created and updated the statistics relevant to the tables in the query? If the statistics are inaccurate on one database, a different plan may be generated.
Top Expert 2012

Commented:
>>how could it be different though both have same table design and same data.<<
But the question was do you have the same number of rows?  And more importantly have you updated statisitics in both databases.  Both of these can make a difference to the execution plan, as they should.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial