February 16, 2010 at 10:46 am
Hi All,
I have machine with 64bit SQL 2005 Standard and two quad core processors.
When i'm running below query from query window in managment studion, query is taking maximally 2 seconds.
SELECT PURCHASE_REQ_MST.C_USER_ID, USERINFO.USERNAME, USERINFO.USERID, PURCHASE_REQ_MST.C_DATETIME, PURCHASE_REQ_MST.PURCHASE_VALUE, PURCHASE_REQ_MST.ORDER_NO, PURCHASE_REQ_MST.REQ_STATUS, PURCHASE_REQ_MST.PURCHASE_REQ_ID, PURCHASE_REQ_MST.SUPPLIER_NAME, PURCHASE_REQ_MST.JUSTIFICATION, PURCHASE_REQ_MST.PURCHASE_REQ_NO FROM PURCHASE_REQ_MST INNER JOIN USERINFO ON PURCHASE_REQ_MST.C_USER_ID = USERINFO.USERID WHERE (USERINFO.USERNAME LIKE '%NTT%') OR (PURCHASE_REQ_MST.JUSTIFICATION LIKE '%NTT%') OR (PURCHASE_REQ_MST.SUPPLIER_NAME LIKE '%NTT%')
When i created below stored procedure and ran it, time was 8 seconds.
CREATE PROCEDURE [dbo].[testquery]
@mystring varchar(10)
AS
BEGIN
DECLARE @mystring2 varchar(10)
SET @mystring2 = @mystring
SELECT PURCHASE_REQ_MST.C_USER_ID, USERINFO.USERNAME, USERINFO.USERID, PURCHASE_REQ_MST.C_DATETIME, PURCHASE_REQ_MST.PURCHASE_VALUE, PURCHASE_REQ_MST.ORDER_NO, PURCHASE_REQ_MST.REQ_STATUS, PURCHASE_REQ_MST.PURCHASE_REQ_ID, PURCHASE_REQ_MST.SUPPLIER_NAME, PURCHASE_REQ_MST.JUSTIFICATION, PURCHASE_REQ_MST.PURCHASE_REQ_NO FROM PURCHASE_REQ_MST INNER JOIN USERINFO ON PURCHASE_REQ_MST.C_USER_ID = USERINFO.USERID WHERE (USERINFO.USERNAME LIKE @mystring2 ) OR (PURCHASE_REQ_MST.JUSTIFICATION LIKE @mystring2) OR (PURCHASE_REQ_MST.SUPPLIER_NAME LIKE @mystring2) OPTION (MAXDOP 1)
END
Summary i noticed that my sql 2005 64bit is ignoring cost threshold for parallelism sql server parameter in case of run stored procedures. When i was running select command, sql had in use all cores. In case of run stored procedure only two cores were working hard.
Is any logical explanation how it is possible ? Can i setup cost threshold for parallelism parameter for whole stored procedure. Like in exaple i already tryed to use maxdop option and without it but no difference 🙁
My sql version is 9.00.4035.00 (X64) (Build 3790: Service Pack 2)
Thanks a lot for any help
Pawel
February 19, 2010 at 7:06 am
Was the intent of the query and the procedure to have the same body, because your stored procedure is different than the query. Your query has LIKE '%NTT%' which requires a table scan, while your stored procedure has LIKE @mystring2 which could potentially use an index if the field being searched is indexed. Either way you don't have parity because the stored procedure is not using a wildcard at the beginning of the statement. This doesn't answer the question about MAXDOP but perhaps it will help in getting your two queries to behave similarly.
Try:
LIKE '%' + @mystring2 + '%' .
In your procedure. Of course this will have an adverse effect if occasionally you don't want that wildcard in the front, I am just trying to help you understand what is going on and think that you should either try to get rid of a wildcarded LIKE or try to find other ways to make your query more sargable.
My conclusion without knowing your schema:
It appears to me that if you are passing your parameter in as '%NTT%', you may be confusing the query analyzer into thinking that an using an index is appropriate. The one field that stands out is the username field which, by name, is often an indexed field in many databases (especially if this is an authentication field). The query analyzer will need to assume an arbitrary non-wildcarded value. If the field is indexed it will think it can use the index at design time, but at run-time apply the wildcard (causing the inefficiency), requiring a tablescan of the index (bad) and adding a lookup to the clustered index of the table. A single index on any of the fields that you are searching can change your query plan in this way.
This will show you this behavior:
CREATE TABLE dbo.tmp
(
id int NOT NULL IDENTITY (1, 1),
username varchar(50) NULL,
blank bit NULL,
primary key (id)
)
GO
CREATE NONCLUSTERED INDEX IX_tmp ON dbo.tmp
(
username
)
GO
-- fill the table with enough data to use the index
declare @iter int
set @iter = 1
while @iter <= 100000
begin
insert into dbo.tmp (username)
select 'user' + CONVERT(varchar(10), @iter)
select @iter = @iter + 1
end
-- make the wildcar part of the procedure
create procedure test
( @var varchar(10) )
as
select * from tmp where username like '%' + @var + '%'
GO
now show the estimated query plan of
exec dbo.test 'ser'
alter the procedure to : select * from tmp where username like @var
now show the estimated query plan of
exec dbo.test '%ser%'
In the first case, the analyzer knows about the wildcard and pulls directly from the clustered index.
In the second case the index is used and then a bookmark lookup is executed. This should be a better plan if the index would help to find the records, but in this case the wildcard makes the index useless.
Tim Januario
February 19, 2010 at 7:10 am
And I forgot the important part regarding your question. If the query analyzer thinks it has given you an efficient plan, you will NOT get it to use a parallel query plan. WHich is what you are seeing.
Tim Januario
February 20, 2010 at 10:04 am
Hello Tim,
Thanks for the answer.
Solution presented by you was a beginning of my issue. Some day one person came to me with complies. How it is possible that my page in aspx (VB) is generating so slowly
report when exactly the same query in report written in java (tomcat) is doing it so fast?
I started diagnosing and found in SQL Profiler what is aspx sending to SQL. There is only difference that visual studio is generating temporary query like a procedure. The same schema like in your
Example
( @var varchar(10) )
as
select * from tmp where username like '%' + @var + '%'
GO
In case of your suggestion and my sample stored procedure problem is the same. They are not running in parallelism. and i don't know why 🙁
Thanks
Pawel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply