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
321 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach 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.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

14 Experts available now in Live!

Get 1:1 Help Now