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
324 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

726 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