How to Simplify and speed up this Script

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 2005 Forums

Transact-SQL (2005)

How to Simplify and speed up this Script

Author

Topic

vyx
Starting Member

2 Posts

Posted - 2012-07-07 : 06:29:04

Hello Guys

I just want to view the Transaction of the 3 tables at first the result of query display fast but when it data inserted increase takes too long to display and shows a message that says timeout expired...

How could i simplify and speedup this query to display data very fast..

tblItems=5500 record
tblitemledger=20000 records
tblwarehousing=10000 records

select i.itemid,i.description,
isnull(i.conversion,1)as conversion,
isnull(i.bigunit,'PCS')AS BigUnit,
isnull(i.itemsection,0)as Itemsection,
isnull(i.smallunit,'PCS')as SmallUnit,
isnull(isnull(i.costsmall,0),0) as costsmall,isnull(isnull(i.costBig,0),0) as costBig,
isnull(isnull(i.bulkprice,0),0) as bulkPrice,isnull(isnull(i.sellingprice,0),0) as sellingprice,
[QTYIN] = ISNULL((select SUM(QTYIN*CONVERSION) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' and TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0'),
[QTYOUT] = ISNULL((select SUM(QTYOUT*CONVERSION) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' and TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0'),
[BALANCE]=ISNULL((select SUM(QTYIN*CONVERSION)-SUM(QTYOUT*CONVERSION) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' AND TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0')
[PurchaseCost]=ISNULL((select SUM(ISNULL(TotPurchaseCost,0)) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' AND TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0'),
[SalesCost]=ISNULL((select SUM(ISNULL(TotSalesCost,0)) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' AND TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0'),
[SalesPrice]=ISNULL((select SUM(ISNULL(TotSalesPrice,0)) FROM TBLITEMLEDGER WHERE deptcode='GROCERY' AND TRANSDATE<='7/6/2012' and itemid = I.ITEMID),'0')
FROM tblitems i inner join tblwarehousing w on i.itemid=w.itemid where w.deptcode='GROCERY' order by I.DESCRIPTION

I really appreciate all your help here..

Thank you in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-07 : 08:40:04

Two or three things to look at:

a) What kind of primary keys and indexes do you have on the tables? Specifically, do you have indexes on the itemid columns of each table? If not having those should speed it up.

b) Instead of using the sub-queries to compute the sums, if you can join the table TBLITEMLEDGER to the other 2 tables that should help. I don't know enough about your data to be able to suggest how to do that get the correct grouping.

vyx
Starting Member

2 Posts

Posted - 2012-07-08 : 02:56:35

Thank you for your reply...

The tblitems has a primarykey ITEMID
tblitemledger has no primary key as well as the tblwarehousing but they have a automatic number pkeyno identity auto
the sample database looks like this

TBLITEMS(5000 records)
=========================================================================================================
ITEMID |DESCRIPTION |CONVERSION |BIGUNIT |SMALLUNIT |SECTION |COSTBIG |COSTSMALL |BULKPRICE |SELLINGPRICE
100 A 10 pck pcs 2 50 5 55 7
200 A 10 pck pcs 2 50 5 55 7
300 A 10 pck pcs 2 50 5 55 7
400 A 10 pck pcs 2 50 5 55 7
500 A 10 pck pcs 2 50 5 55 7

TBLWAREHOUSING(10000 Records doubled the tblitems)
ITEMID|DEPTCODE
================
100 DEPTA
200 DEPTA
300 DEPTA
400 DEPTA
500 DEPTA
100 DEPTB
200 DEPTB
300 DEPTB
400 DEPTB
500 DEPTB

TBLITEMLEDGER(20 0000 of records)
================
ITEMID|QTYIN|QTYOUT|TOTSALESCOST|TOTSALESPRICE TRANSDATE
100 1 0 5 5 01/01/2012
100 1 0 5 5 01/01/2012
100 1 0 5 5 02/02/2012
200 1 0 5 5 02/02/2012
200 1 0 5 5 03/03/2012
200 1 0 5 5 03/04/2012
200 1 0 5 5 04/01/2012
300 1 0 5 5 04/01/2012
300 1 0 5 5 05/01/2012
300 1 0 5 5 05/01/2012
400 1 0 5 5 06/01/2012
400 1 0 5 5 06/01/2012
100 1 0 5 5 07/06/2012
100 1 0 5 5 07/06/2012
200 1 0 5 5 07/06/2012

I Just made a sample data that looks like on my records, when i execute the query it takes too long that result for timeout in my application and in sql server 2005 IDE. executing and processing too long that cause me to stop the execution
Hope it helps you figure out my concern.

Thanks again in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources