SQL runs slow but... - 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)

SQL runs slow but...

Author

Topic

ElenaSTL1
Starting Member

9 Posts

Posted - 2012-03-15 : 09:40:46

My Stored Procedure runs for 8 minutes. It has SQL like this: SELECT * FROM tblCustomers
However it runs just 3 seconds when I divide it to 2:
SELECT * FROM tblCustomers WHERE CustomerID<='500000'
UNION ALL
SELECT * FROM tblCustomers WHERE CustomerID > '500000'
This table tblCustomer has about 1 million records.
Table located on SQL Server 2008. I think it is not enough memory.
When I worked with ORACLE database I had a message:"increase segment".
How can I increase memory space on SQL Server? Or is that another reason?
(actual SQL is more complex and returns just 20 records)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-15 : 09:51:57

Without seeing the query, and correlated DDL, it's hard to give any advice.

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

ElenaSTL1
Starting Member

9 Posts

Posted - 2012-03-15 : 10:37:18

ALTER PROCEDURE [dbo].[sp_1]
@Division varchar(3) = null
AS BEGIN

DECLARE @MultipleDeliveryOrders table (
OrderNumber varchar(55)
)

DECLARE @MultipleDivision as varchar(3)
SET @MultipleDivision = @Division

INSERT INTO @MultipleDeliveryOrders
SELECT OAORNO
FROM OrderHead
INNER JOIN OrderDelivery
ON OrderDelivery.UAORNO = OrderHead.OAORNO
GROUP BY OAORNO
HAVING
COUNT(OrderDelivery.UADLIX) > 1 --MORE THEN 1 DELIVERY PER ORDER

--two identical select statements with UNION ALL with the only difference that in the first Customer.OKCUNO < '00000MW550'
--and in the second Customer.OKCUNO >= '00000MW550' - this is done for making SQL run faster.
SELECT
OrderHead.OAORNO as OrderNumber,
CAST(OrderDelivery.UADLIX AS INT) as DeliveryNumber,
OrderHead.OACUNO as CustomerID,
Customer.OKCUNM as CustomerName,
Customer.OKECAR as [State],
OrderHead.OADIVI as Division,
OrderHead.OAPYCD as PaymentMethod,
OrderHead.OAWHLO as WHS,
SUM(OrderLine.OBNEPR * OrderLine.OBDLQT) as TotalDeliveryAmount
FROM @MultipleDeliveryOrders as MultipleDeliveries
INNER JOIN OrderHead
ON MultipleDeliveries.OrderNumber = OrderHead.OAORNO collate Latin1_General_BIN
INNER JOIN OrderDelivery WITH (NOLOCK)
ON OrderDelivery.UAORNO = OrderHead.OAORNO
INNER JOIN OrderDeliveryLine WITH (NOLOCK)
ON OrderDeliveryLine.UBDLIX = OrderDelivery.UADLIX
INNER JOIN OrderLine WITH (NOLOCK)
ON OrderLine.OBORNO = OrderHead.OAORNO
AND OrderLine.OBPONR = OrderDeliveryLine.UBPONR
INNER JOIN Customer WITH (NOLOCK)
ON OrderHead.OACUNO = Customer.OKCUNO AND OrderHead.OACONO = Customer.OKCONO
WHERE OrderDelivery.UAORST = '60' AND
Customer.OKCUNO < '00000MW550' AND
CASE
WHEN NullIf(@MultipleDivision, '') IS NULL THEN 1
WHEN OrderHead.OADIVI = @MultipleDivision THEN 1
ELSE 0
END = 1
GROUP BY
OrderHead.OAORNO,
OrderDelivery.UADLIX,
OrderHead.OACUNO,
Customer.OKCUNM,
Customer.OKECAR,
OrderHead.OADIVI,
OrderHead.OAPYCD,
OrderHead.OAWHLO

UNION ALL

SELECT
OrderHead.OAORNO as OrderNumber,
CAST(OrderDelivery.UADLIX AS INT) as DeliveryNumber,
OrderHead.OACUNO as CustomerID,
Customer.OKCUNM as CustomerName,
Customer.OKECAR as [State],
OrderHead.OADIVI as Division,
OrderHead.OAPYCD as PaymentMethod,
OrderHead.OAWHLO as WHS,
SUM(OrderLine.OBNEPR * OrderLine.OBDLQT) as TotalDeliveryAmount
FROM @MultipleDeliveryOrders as MultipleDeliveries
INNER JOIN OrderHead
ON MultipleDeliveries.OrderNumber = OrderHead.OAORNO collate Latin1_General_BIN
INNER JOIN OrderDelivery WITH (NOLOCK)
ON OrderDelivery.UAORNO = OrderHead.OAORNO
INNER JOIN OrderDeliveryLine WITH (NOLOCK)
ON OrderDeliveryLine.UBDLIX = OrderDelivery.UADLIX
INNER JOIN OrderLine WITH (NOLOCK)
ON OrderLine.OBORNO = OrderHead.OAORNO
AND OrderLine.OBPONR = OrderDeliveryLine.UBPONR
INNER JOIN Customer WITH (NOLOCK)
ON OrderHead.OACUNO = Customer.OKCUNO AND OrderHead.OACONO = Customer.OKCONO
WHERE OrderDelivery.UAORST = '60' AND
Customer.OKCUNO >= '00000MW550' AND
CASE
WHEN NullIf(@MultipleDivision, '') IS NULL THEN 1
WHEN OrderHead.OADIVI = @MultipleDivision THEN 1
ELSE 0
END = 1
GROUP BY
OrderHead.OAORNO,
OrderDelivery.UADLIX,
OrderHead.OACUNO,
Customer.OKCUNM,
Customer.OKECAR,
OrderHead.OADIVI,
OrderHead.OAPYCD,
OrderHead.OAWHLO

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:45:42

sorry why do you need to split them up to two different select statements? I cant see anything different between them
Am I missing something??

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 15:57:52

>> Customer.OKCUNO >= '00000MW550'

My bigger problem is WHERE do you think the data is going to when this gets run

A million rows you say...should crush the memory, buffer, ect

WHY are you trying to return a million rows?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 16:00:23

Why does this make sense?

DECLARE @MultipleDivision as varchar(3)
SET @MultipleDivision = @Division

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 16:10:22

What does this give you?


	SELECT COUNT(*)
	FROM (	   SELECT OAORNO FROM OrderHead
			INNER JOIN OrderDelivery
					ON OD.UAORNO = O.OAORNO
			  GROUP BY OAORNO
			    HAVING COUNT(*) > 1) AS M
        INNER JOIN OrderHead			O
		ON M.OrderNumber		= O.OAORNO collate Latin1_General_BIN 
        INNER JOIN OrderDelivery		OD 
		ON OD.UAORNO			= O.OAORNO
        INNER JOIN OrderDeliveryLine	        ODL 
		ON ODL.UBDLIX			= OD.UADLIX
        INNER JOIN OrderLine			OL 
		ON OL.OBORNO			= O.OAORNO 
  	       AND OL.OBPONR			= ODL.UBPONR
        INNER JOIN Customer			C 
		ON O.OACUNO			= C.OKCUNO AND O.OACONO = C.OKCONO
	 WHERE OD.UAORST			= '60' AND
	   AND
		 CASE
				WHEN NullIf(@MultipleDivision, '') IS NULL THEN 1
				WHEN O.OADIVI = @MultipleDivision THEN 1
				ELSE 0 
			END = 1
	GROUP BY
		O.OAORNO,
		OD.UADLIX,
		O.OACUNO,
		C.OKCUNM,
		C.OKECAR,
		O.OADIVI,
		O.OAPYCD,
		O.OAWHLO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ElenaSTL1
Starting Member

9 Posts

Posted - 2012-03-16 : 10:21:12

to VISAKH16: I split in 2 Select statements because it runs just 3 seconds. With one Select statement it runs 8 minutes. And this is my question: Why it that?

ElenaSTL1
Starting Member

9 Posts

Posted - 2012-03-16 : 10:33:40

to Brett: I created a local variables and assign the input parameter to the local variables and use the local variables in the query to avoid parameter sniffing:
http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html
this method helped me to change run time significantly in another stored procedure, but in this one did not help.
Brett, you sent me a lot of links with help from you but you can not read the question carefully: I did not extract 1 million rows. I extract about 20 rows, but use a table with 1 million rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 11:06:35

quote:
Originally posted by ElenaSTL1

to VISAKH16: I split in 2 Select statements because it runs just 3 seconds. With one Select statement it runs 8 minutes. And this is my question: Why it that?

what are indexes you've on tables? can you check execution plan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ElenaSTL1
Starting Member

9 Posts

Posted - 2012-03-16 : 14:05:59

We have on Customer table clustered index OKCONO and OKCUNO.I guess that I'm using this index by joining this table with another by these 2 fields. Execution plan looks strange for me: Query1: Query cost (relative to the batch): 100%; set ANSI_NULLS ON; T-SQL SET ON/OFF COST: 0%

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 14:26:20

I don't know if I buy that...I have never seen this behavior..I log ever sproc executions...and I only ever seen super sub second times...so...it might be a practice when all else fails

I go with bad indexing as your problem

Did you do a SHOWPLAN?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-16 : 14:27:35

And BTW did you try the query I rewrote for you? It didn't take hours, but it would be nice to know how long it takes and what the plan

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ElenaSTL1
Starting Member

9 Posts

Posted - 2012-03-16 : 14:30:56

And another question. Yesterday I reduced execution time of another Stored Procedure from 40 minutes to 4 minutes.
I just declared joining field in table variable exactly the same type as type in joining table. Before I had field in table variable varchar(50) and field in joining table nchar(15), so I used collate database_default statement to compile my Stored Proc. When I changed varchar(50) to nchar(15) I could compile SP without used collate database_default statement. I made it in 2 places in SP and now it runs 10 times faster! The problem is that I cannot do the same with 2 other fields. I declare them the exact type as in database, but it doesn’t allow me to remove collate database_default statement.
I also tried to do the same with SP I originally posted in this topic:
DECLARE @MultipleDeliveryOrders table (
OrderNumber nchar(10)
)
( Order Number in OrderHead has type nchar(10)
Instead of
DECLARE @MultipleDeliveryOrders table (
OrderNumber varchar(55)
)

but I could not compile this SP without collate Latin1_General_BIN
Why is that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-16 : 14:51:55

seems like your tempdb collation is different from your actual db collation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ElenaSTL1
Starting Member

9 Posts

Posted - 2012-03-16 : 15:02:33

Brett, I can not make a clear experiment with your code because this problem doesn't exist any more. We had this problem for several months before and I solved it for several SPs with splitting SQL. Now it works fast without splitting. I don't know what happened: may be our DBA changed some settings, may be they just restarted SQL Server.
I just wonder why it happened and how to fix this problem in the future.

ElenaSTL1
Starting Member

9 Posts

Posted - 2012-03-16 : 15:05:12

visakh16, I also thought about it. How can I see collation of my tempdb and change it? Why it worked in 2 cases and doesn't work in 2 similar cases?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources