Stored procedure - 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)

Stored procedure

Author

Topic

Deltaur
Starting Member

29 Posts

Posted - 2009-08-06 : 07:44:18

I have created stored procedure and in the previous topic i got the answer and this is working but is this correctly created? seems bit slow on SQL 2000 server though i have lot of order numbers that i query. Just want to check that all is ok because this is my second stored procedure

SELECT ORDER, SALESID,SALESDATE,QUANTITY,DATE
FROM ORDERS2006
WHERE RIGHT(ORDER,6)=@ORDERNUMBER
AND ((CAST (LEFT(ORDER,4) AS INT) BETWEEN 3000 AND 4999) OR (CAST (LEFT(ORDER,4) AS INT) BETWEEN 7000 AND 7999))
UNION SELECT ORDER, SALESID,SALESDATE, QUANTITY,DATE
FROM ORDERS2007
WHERE RIGHT(ORDER,6)=@ORDERNUMBER
AND ((CAST (LEFT(ORDER,4) AS INT) BETWEEN 3000 AND 4999) OR (CAST (LEFT(ORDER,4) AS INT) BETWEEN 7000 AND 7999))
UNION SELECT ORDER, SALESID,SALESDATE, QUANTITY,DATE
FROM ORDERS2008
WHERE RIGHT(ORDER,6)=@ORDERNUMBER
AND ((CAST (LEFT(ORDER,4) AS INT) BETWEEN 3000 AND 4999) OR (CAST (LEFT(ORDER,4) AS INT) BETWEEN 7000 AND 7999))
UNION SELECT ORDER, SALESID,SALESDATE, QUANTITY, DATE
FROM ORDERS2009 WHERE RIGHT(ORDER,6)=@ORDERNUMBER
AND ((CAST (LEFT(ORDER,4) AS INT) BETWEEN 3000 AND 4999) OR (CAST (LEFT(ORDER,4) AS INT) BETWEEN 7000 AND 7999))
ORDER BY ORDER,SALESDATE

Thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 07:47:48

Try change "UNION" to "UNION ALL"

"UNION" detects duplicats and remove then and it's a costly operation.
"UNION ALL" doesn't do more than add to current resultset.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 07:49:40

You can also speed up things by not casting the order number for the range check.
Try this

SELECT	[ORDER],
	SALESID,
	SALESDATE,
	QUANTITY,
	DATE
FROM	ORDERS2006
WHERE	RIGHT([ORDER], 6) = @ORDERNUMBER
	AND (LEFT([ORDER], 4) BETWEEN '3000' AND '4999' OR LEFT([ORDER], 4) BETWEEN '7000' AND '7999')

UNION ALL

SELECT	[ORDER],
	SALESID,
	SALESDATE,
	QUANTITY,
	DATE
FROM	ORDERS2007
WHERE	RIGHT([ORDER], 6) = @ORDERNUMBER
	AND (LEFT([ORDER], 4) BETWEEN '3000' AND '4999' OR LEFT([ORDER], 4) BETWEEN '7000' AND '7999')

UNION ALL

SELECT	[ORDER],
	SALESID,
	SALESDATE,
	QUANTITY,
	DATE
FROM	ORDERS2008
WHERE	RIGHT([ORDER], 6) = @ORDERNUMBER
	AND (LEFT([ORDER], 4) BETWEEN '3000' AND '4999' OR LEFT([ORDER], 4) BETWEEN '7000' AND '7999')

UNION ALL

SELECT	[ORDER],
	SALESID,
	SALESDATE,
	QUANTITY,
	DATE
FROM	ORDERS2009
WHERE	RIGHT([ORDER], 6) = @ORDERNUMBER
	AND (LEFT([ORDER], 4) BETWEEN '3000' AND '4999' OR LEFT([ORDER], 4) BETWEEN '7000' AND '7999')

and compare speed to your original query.

N 56°04'39.26"
E 12°55'05.63"

Deltaur
Starting Member

29 Posts

Posted - 2009-08-06 : 08:01:51

Ok, i try it.

btw, the ORDER field is tricky and reason why i have to use this CAST and LEFT style approaching is because the field data is "1234 123456" nvarchar field so i have to parse from right the account and from left the order number. Those should be in their own fields but i dont know what is the idea keep that data in one field (in my mind it should be like account in own field and order number in own field). I cant change this because database is not "my design" :(

Deltaur
Starting Member

29 Posts

Posted - 2009-08-06 : 08:22:48

I have tried it and didnt notice any massive speed difference. I think i have too much data to query and slow server :(. Have to find another way. I have about 76000 single orders and they have different amount of rows in those tables. So querrying every ordernumber one by one will take long time. Any ideas? Maybe i should replicate that database to faster server maybe SQL2005.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 08:33:41

I've found an edge-condition where my previous suggestion will not work properly.
See my edited response above.

N 56°04'39.26"
E 12°55'05.63"

Deltaur
Starting Member

29 Posts

Posted - 2009-08-06 : 08:38:56

In matter of fact i changed it already like that you edited. But still i am lack of speed :(

i'll try to run copy of that database on faster server and check if that brings any relief, if there arent anything else to try. Kiitos!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-06 : 08:51:23

Try changing

(LEFT([ORDER], 4) BETWEEN '3000' AND '4999' OR LEFT([ORDER], 4) BETWEEN '7000' AND '7999')

to

([ORDER] like '[3-4][0-9][0-9][0-9]%' or data like '[7][0-9][0-9][0-9]%')

and see what happens

Madhivanan

Failing to plan is Planning to fail

Deltaur
Starting Member

29 Posts

Posted - 2009-08-07 : 01:59:11

Thank you, i think it got little faster but not much.

I have now transferred the database to other server which 2005 platform and NOW i can see how much powerful server can speed things up.

I move to the other forum area and start asking how i do the replication relationship only oneway (from 2000 -> 2005 and only the changed data)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources