Solved

How to view how SQL Server would re-organize a SQL Statement in a stored proc to optimize it?

Posted on 2011-03-24
6
319 Views
Last Modified: 2012-06-27
Is there a way to see how a query would be or could be reorganized to perform better but have the same results.  I have a SQL query that is in a stored procedure that takes < 4 seconds to run for a large amount of data via a stored procedure in SQL Server 2008 however when the database is changed to SQL CE DB instance running it takes nearly 3 minutes (no stored procedures are allowed there).  I'm wondering if the stored procedure reorganizes the query in a special way that could be recreated as in-line SQL.
0
Comment
Question by:endrec
6 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35212449
The stored procedure doesn't get "reorganized" - it's possible that either the sql server database has some indexes the sqlce one doesn't, or that the sql server has quite a bit more memory. If you post the stored proc here, maybe we can give some suggestions, but we can't do it without seeing the code.
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35212800
Actually what SQL Server does is it creates a execution plan for each and every SQL Query it runs. Sometimes there is more than one way of retriving data so it creates few execution plans and then it selects the optimum plan based on CPU, IO costs. But thats all happens behind the scene. You can view the actual execution plan through the SSMS.

What is happening with SP's are SQL Server caches the execution plan so it does not have to create it again each time it executes the same query. So that is probably why your SP is faster than the SQL Query. But of course you can do lot of things to optimize your query like introducing indexes, etc. Thats why DBA;s and DB Developers have a jobs.
0
 
LVL 3

Expert Comment

by:kumarnimavat
ID: 35214598
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:endrec
ID: 35214812
Thank you, here is the query.  Please note that if the the subqueries that make up the "s" alias and "y" alias are run on their own in SQL CE, the "s" query takes 11 seconds max and the "y" query takes 1 second max in my scenario...but when the entire query is run all together it just keeps going for minutes and minutes.  When the entire query is run in SQL Server 2008 it runs in less then 3 seconds.

--



SELECT y.PartId, p.PartName, y.WarehouseId, w.Name WarehouseName, y.LocationId, l.Name LocationName, y.Quarter, y.WorkWeek, s.SummedErpOfficialQuantity, sa.Id InventoryAdjustmentId, sa.Quantity InventoryAdjustmentQuantity, y.Ytq, sa.CreatedBy, sa.CreatedDateTime, sa.ModifiedBy, sa.ModifiedDateTime
                FROM
                (
                      SELECT PartId,
                            WarehouseId,
                            LocationId,
                            WorkWeek,
                            Quarter,
                            Sum(Quantity) SummedErpOfficialQuantity
                      FROM SupplyOrders
                      WHERE Quarter >= @Quarter AND WorkWeek >= @WorkWeek
                      AND Type2 = 'Erp Official'
                      GROUP BY PartId,
                            WarehouseId,
                            LocationId,
                            WorkWeek,
                            Quarter ) s            
                INNER JOIN
                (
                      SELECT PartId,
                            WarehouseId,
                            LocationId,
                            WorkWeek,
                            Quarter,
                            Ytq
                      FROM Ytq
                      WHERE Quarter >= @Quarter AND WorkWeek >= @WorkWeek
                      GROUP BY PartId,
                            WarehouseId,
                            LocationId,
                            WorkWeek,
                            Quarter,
                            Ytq       ) y
                ON s.PartId = y.PartId AND
                s.WarehouseId = y.WarehouseId AND
                s.LocationId = y.LocationId AND
                s.WorkWeek = y.WorkWeek AND
                s.Quarter = y.Quarter
                INNER JOIN Part p ON p.Id = y.PartId
                INNER JOIN Warehouse w ON w.Id = y.WarehouseId
                INNER JOIN Location l ON l.Id = y.LocationId
                LEFT JOIN InventoryAdjustments sa
                ON sa.PartId = y.PartId AND
                sa.WarehouseId = y.WarehouseId AND
                sa.LocationId = y.LocationId AND
                sa.WorkWeek = y.WorkWeek AND
                sa.Quarter = y.Quarter
                ORDER By y.Quarter, y.WorkWeek, p.PartName, w.Name, l.Name
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 35214838
>>I have a SQL query that is in a stored procedure that takes < 4 seconds to run for a large amount of data via a stored procedure in SQL Server 2008 however when the database is changed to SQL CE DB instance running it takes nearly 3 minutes <<
If you cleared the cache I suspect you would not see much of a difference.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35214846
In other words, assuming everything else is the same they would both take nearly 3 minutes.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now