Need tips for faster queries - 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 2008 Forums

Transact-SQL (2008)

Need tips for faster queries

Author

Topic

indralang
Starting Member

7 Posts

Posted - 2014-06-17 : 04:16:19

[code]
DECLARE CSR CURSOR FOR
SELECT
A.ID,
B.ITEM,
B.SALES_ID
FROM TABLE1 A
INNER JOIN TABLE 2 B ON A.ID = B.ID
WHERE A.DATE BETWEEN @DATE_FROM AND @DATE_TO
OPEN CSR
FETCH NEXT FROM CSR
INTO
@ID,
@ITEM
@SALES_ID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @PURCHASE_PRICE = PURCHASE_PRICE
FROM TABLE3 WHERE ITEM_CODE = @ITEM_CODE

SELECT @SALES_PRICE = SALES_PRICE
FROM TABLE4 WHERE SALES_ID = @SALES_ID

SET @ENDING_BALANCE = @PURCHASE_PRICE - @SALES_PRICE

INSERT INTO TABLE5
VALUES
(
@ID,
@ITEM,
@PURCHASE_PRICE,
@SALES_PRICE,
@ENDING_BALANCE
)
FETCH NEXT FROM CSR
@ID,
@ITEM,
@SALES_ID

END
CLOSE CSR
DEALLOCATED CSR
[/code]
In table1 and table2 have about 5000 data.

This is sample for my code. I have a 9 or 10 queries in 1 stored procedure using cursor like this. And the result really take a lot of times (about 15-20 minutes).
I need help to make my queries faster.
Thank you for your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-06-17 : 04:56:37

See if this works

INSERT INTO TABLE5
SELECT 
    t1.ID,
    t1.ITEM,
    t3.PURCHASE_PRICE,
    t4.SALES_PRICE,
    t3.PURCHASE_PRICE-t4.SALES_PRICE,
   
(
SELECT 
A.ID,
B.ITEM,
B.SALES_ID
FROM TABLE1 A
INNER JOIN TABLE 2 B ON A.ID = B.ID
WHERE A.DATE BETWEEN @DATE_FROM AND @DATE_TO
) as t1 
left join TABLE3 as t3 on t2.ITEM_CODE=t3.ITEM_CODE
left join TABLE4 as t4 on t2.SALES_ID =t4.SALES_ID

Madhivanan

Failing to plan is Planning to fail

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-06-17 : 04:56:39

[code]

INSERT INTO TABLE5
SELECT
A.ID,
B.ITEM,
C.PURCHASE_PRICE,
D.SALES_PRICE,
C.PURCHASE_PRICE - D.SALES_PRICE AS ENDING_BALANCE

FROM TABLE1 A
INNER JOIN TABLE 2 B ON A.ID = B.ID

INNER JOIN TABLE3 C ON C.ITEM_CODE = B.ITEM
INNER JOIN TABLE4 D ON D.SALES_ID = B.SALES_ID

WHERE A.DATE BETWEEN @DATE_FROM AND @DATE_TO
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources