[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
330 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

656 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