Slow Querying Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Slow Querying Time

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-01-27 : 22:53:47

I know this is something to do with the database design. I have this querry from my friend, this will be used for a report.

All the necessarry datas on it, is get from 4 tables. Since the requisition of the report is very complex because this report was not identified on the design phase of the development. So, we put the necessarry querries on a view, and all of the datas are get in there. However he still have to query on that view to specified the necessarry data for the specific column of the report. He is concern now how would he will make this querry a little bit faster?

Any suggestion will be much appreciated... THNX...

CREATE PROCEDURE dbo.spe_StockInventory_Balance
 @xMonthFrom as char(12),
 @xMonthTo as char(12)
AS

BEGIN

select  StockCode, CompleteDescription,                                       --StockCode & CompleteDescription,

(select top 1 isnull(EndingQty,0) from vwStock
where StockCode = AAA.StockCode and DateTimeOrder < cast(@xMonthFrom as DateTime)    --Beginning Quantity,----------------
order by DateTimeOrder desc, TransID desc) as BeginningQty,                                        

(select top 1 isnull(EndingAmount,0) from vwStock
where StockCode = AAA.StockCode and DateTimeOrder < cast(@xMonthFrom as DateTime)     --Beginning Amount--------------
order by DateTimeOrder desc, TransID desc) as BeginningAmount,                                     

(select isnull(sum(Quantity),0) from vwStock
where StockCode = AAA.StockCode 
and DateTimeOrder >= cast(@xMonthFrom as DateTime) 
and DateTimeOrder < cast(@xMonthTo as DateTime) 
and type =0) as SRR_Qty,                           					---S.R.R. Quantity---------------------

(select isnull(sum(Amount),0) from vwStock
where StockCode = AAA.StockCode 
and DateTimeOrder >= cast(@xMonthFrom as DateTime) 
and DateTimeOrder < cast(@xMonthTo as DateTime) 
and type =0) as SRR_Amnt,                         				        ---S.R.R. Amount---------------------

(select isnull(sum(Quantity),0) from vwStock
where StockCode = AAA.StockCode 
and DateTimeOrder >= cast(@xMonthFrom as DateTime) 
and DateTimeOrder < cast(@xMonthTo as DateTime) 
and type =1) as MIS_Qty,                          					----M.I.S. Quntity,------------------------

(select isnull(sum(Amount),0) from vwStock
where StockCode = AAA.StockCode 
and DateTimeOrder >= cast(@xMonthFrom as DateTime) 
and DateTimeOrder < cast(@xMonthTo as DateTime) 
and type =1) as MIS_Amnt,                         					--- M.I.S. Amount----------------------

(select top 1 isnull(EndingQty,0) from vwStock
where StockCode = AAA.StockCode and DateTimeOrder < cast(@xMonthTo as DateTime)    -----Ending Qunatity---------------
order by DateTimeOrder desc,TransID desc) as EndingQty,

(select top 1 isnull(EndingAmount,0) from vwStock
where StockCode = AAA.StockCode and DateTimeOrder < cast(@xMonthTo as DateTime)    -----Ending Amount--------------
order by DateTimeOrder desc, TransID desc) as EndingAmount

from tblMasterProfile as AAA
--where flgSelectedToConsumption = 1 
order by Category asc

END
GO

Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-28 : 02:54:22

Looks to me like you are duplicating a lot of code in subqueries in the select - move it to join clauses so it only gets executed once - suspect the server will execute it multiple times as you have it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources