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
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

In this article I will describe the Copy Database Wizard 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 article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

724 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