Query Performance

Hi Guys,

I have a very big Database...
And This query below is taking up to 2 minutes to complete.

Some suggestions about how to make it better?

Select
s.InvcNbr Numero_nota,
v.cpnyid Cod_CRP,
v.cpnyname Nome_CRP,
i.invtid Cod_Produto,
i.descr Nome_Produto,
p.classid Cod_Classe,
p.descr Nome_Classe,
t.drcr Natureza,
t.acct Cod_Conta,
c.descr Nome_Conta,
t.sub Cod_SubConta,
su.descr Nome_SubConta,
s.custid Cod_Cliente,
s.billname Nome_Cliente,
sl.QtyPick Quantidade,
sl.CurySlsPrice Preco,
sl.curytotinvc Total,
s.OrdDate Data
from soshipline sl
left outer join soshipheader s
on ( s.shipperid = sl.shipperid and s.cpnyId = sl.cpnyId )
inner join vs_company v on s.cpnyid = v.cpnyid
inner join artran t on t.batnbr = s.arbatnbr and
t.cpnyid = s.cpnyid and
t.custid = s.custid and
t.invtid = sl.invtid
inner join inventory i on i.invtid = sl.invtid
inner join productclass p on p.classid = i.classid
inner join account c on c.acct = t.acct
inner join subacct su on su.sub = t.sub
where
t.acct like '3%'
and t.rlsed = 1
and s.User7 <> 'CANC'
and( rtrim( sl.shipperId ) + rtrim( sl.CpnyId ) + rtrim( sl.LineRef )
not In ( select rtrim( a.Origshipperid ) + rtrim( a.CpnyId ) + rTrim( a.LineRef )
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid
and a.cpnyId = b.CpnyId where b.user7 = 'CANC' ) )first of all, see your indexes

2-you have to many joins, look for if some tables you can make a subquery insted of a join, but only the tables that the where clause you can assure will seek by the PK, clustered index and will return only 1 row back.

3-you must have in mind that the principal select must limit your range of rows at maximun, so the subqueries will have less rows to look for.

4-try using set force plan if the principal query is not using the index u want to be used.

5-still try set showplan_all on for a better check of how the query is going to run.

hope i have helped you...

regards !!!

Originally posted by Diogo
Hi Guys,

I have a very big Database...
And This query below is taking up to 2 minutes to complete.

Some suggestions about how to make it better?

Select
s.InvcNbr Numero_nota,
v.cpnyid Cod_CRP,
v.cpnyname Nome_CRP,
i.invtid Cod_Produto,
i.descr Nome_Produto,
p.classid Cod_Classe,
p.descr Nome_Classe,
t.drcr Natureza,
t.acct Cod_Conta,
c.descr Nome_Conta,
t.sub Cod_SubConta,
su.descr Nome_SubConta,
s.custid Cod_Cliente,
s.billname Nome_Cliente,
sl.QtyPick Quantidade,
sl.CurySlsPrice Preco,
sl.curytotinvc Total,
s.OrdDate Data
from soshipline sl
left outer join soshipheader s
on ( s.shipperid = sl.shipperid and s.cpnyId = sl.cpnyId )
inner join vs_company v on s.cpnyid = v.cpnyid
inner join artran t on t.batnbr = s.arbatnbr and
t.cpnyid = s.cpnyid and
t.custid = s.custid and
t.invtid = sl.invtid
inner join inventory i on i.invtid = sl.invtid
inner join productclass p on p.classid = i.classid
inner join account c on c.acct = t.acct
inner join subacct su on su.sub = t.sub
where
t.acct like '3%'
and t.rlsed = 1
and s.User7 <> 'CANC'
and( rtrim( sl.shipperId ) + rtrim( sl.CpnyId ) + rtrim( sl.LineRef )
not In ( select rtrim( a.Origshipperid ) + rtrim( a.CpnyId ) + rTrim( a.LineRef )
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid
and a.cpnyId = b.CpnyId where b.user7 = 'CANC' ) )|||After you check all the indexes and the execution plan...

One area that may be slowing you down is this:

and( rtrim( sl.shipperId ) + rtrim( sl.CpnyId ) + rtrim( sl.LineRef )
not In ( select rtrim( a.Origshipperid ) + rtrim( a.CpnyId ) + rTrim( a.LineRef )
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid
and a.cpnyId = b.CpnyId where b.user7 = 'CANC' ) )

Try changing it to a NOT EXISTS test as follows:

and NOT EXISTS ( select *
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid and a.cpnyId = b.CpnyId and b.user7 = 'CANC'
where a.Origshipperid = sl.shipperId and a.CpnyId = sl.CpnyId
and a.LineRef = sl.LineRef )

btw... what version of SQL Server are you on?|||Hey Guys.
Thanx all!

I replace NOT IN with NOT EXISTS, and looke all steps you have suggested me.

Now,
The quey dont`t take 18 seconds!!

Thank Leandro and HueyStLoui!sql

Query output as text rather than a temp table..

Hi Guys,

I'm trying to figure out how to output a query as text instead of a tempory table...

I thought perhaps i could use this:

SELECT PRINT CustomerID FROM ORDER_TABLE

But that doesn't work

PRINT CustomerID FROM ORDER_TABLE

Doesn't work either...

I need this so i can print a customised invoice in SQL. That is, unless their is a better way of tackling this problem?

thx for reading :)

--PhilkillsWhere does "SELECT CustomerID FROM ORDER_TABLE" write its output to?|||That generates a tempory table with a heading of 'CustomerID' and row numbers at the left hand side....

Im talking about just outputing the data in the customerID column... and nothing else :p

The same way you would do:

PRINT 'BLAH'

etc...|||That generates a tempory table with a heading of 'CustomerID' and row numbers at the left hand side....you can be assuered that it isn't a temporary table and it doesn't have line numbers -- your front end app is doing that

what database is this? (notice you have posted in the standard sql forum)|||Its for MSSQL

and i found 1 potential way...

storing the output in a buffer:

DECLARE @.ordNo INT
DECLARE @.outBuff VARCHAR(1000)
DECLARE @.newLine CHAR(1)
SET @.ordNo = 1
SET @.outBuff = ''
SET @.newLine = '
'

SELECT @.outBuff = @.outBuff + 'PRICE: ' + CAST(ItemPrice AS VARCHAR) + @.newLine
FROM ORDER_ITEM, ITEM
WHERE ORDER_ITEM.ItemID = ITEM.ItemID

PRINT @.outBuff

But is there a better way?|||moved to SQL Server forum

Query optimization in query that uses CTE.

Hi Guys,

I want to optimize one query which uses the CTE (server 2005 feature).
I am sending you the abstract query.

currently this query take 4-5 seconds to execute.
but i want to reduce it to 1 sec.
Plz, do help me, if someone know how to do it.

--
DECLARE @.X INT
DECLARE @.LowerGradeRange INT
DECLARE @.UpperGradeRange INT
DECLARE @.Keyword NVARCHAR(500)

SET @.X = 11500001
SET @.LowerGradeRange = NULL
SET @.UpperGradeRange = NULL
SET @.Keyword = ''

IF ISNULL(@.Keyword,'')=''
SET @.Keyword='';

WITH SelPath (path_id,x,y,z,r)
AS
(

-- Anchor member definition (returns base result set)
SELECT path_id,x,y,z,r

FROM tab1 a
INNER JOIN tab2 b ON a.x= b.x

WHERE
a.x = @.X
-- AND (a.parent IS NULL OR a.parent = 0)
AND
CASE
WHEN ISNULL(@.LowerGradeRange,'')='' THEN 1
WHEN ISNULL(@.LowerGradeRange,'')<>'' AND b.lgr >= @.LowerGradeRange THEN 1
END=1
AND
CASE
WHEN ISNULL(@.UpperGradeRange,'')='' THEN 1
WHEN ISNULL(@.UpperGradeRange,'')<>'' AND b.ugr <= @.UpperGradeRange THEN 1

END=1
AND
CASE
WHEN @.Keyword <>'' AND b.y LIKE @.Keyword THEN 1
ELSE 1
END =1

UNION ALL

-- Recursive member definition
-- (returns the direct subordinate(s) of the activity in the anchor member result set)

SELECT path_id,x,y,z,r
FROM SelPath b
INNER JOIN tab1 a ON a.parent = b.path_id
INNER JOIN tab2 c ON a.x = c.x
WHERE
CASE
WHEN ISNULL(@.LowerGradeRange,'')='' THEN 1
WHEN ISNULL(@.LowerGradeRange,'')<>'' AND c.lgr >= @.LowerGradeRange THEN 1
END=1
AND
CASE
WHEN ISNULL(@.UpperGradeRange,'')='' THEN 1
WHEN ISNULL(@.UpperGradeRange,'')<>'' AND c.ugr <= @.UpperGradeRange THEN 1

END=1
AND
CASE
WHEN @.Keyword <>'' AND c.y LIKE @.Keyword THEN 1
ELSE 1
END =1

)

-- Statement that executes the CTE
SELECT path_id,x,y,z,r

FROM SelPath a
INNER JOIN pce.qq c ON a.r = c.r

ORDER BY x

--

Reply soon...
bye
take care

Regards,
-Surendra

Go to the T-SQL forum. You'll get more help there.

-Jamie

Query Notification - Setup Problems

Hi Guys,

I'm having some problems setting up a SQL Server 2005 Query Notification application. I've only recently moved from SQL Server 2000 so 2005 is still a little alien to me!

I've followed example found on the internet in the following steps:

1.

ALTER DATABASE <dbname> SET ENABLE_BROKER

2.

GRANT CREATE PROCEDURE TO <user>

GRANT CREATE QUEUE TO <user>

GRANT CREATE SERVICE TO <user>

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <user>

GRANT RECEIVE ON QueryNotificationErrorsQueue TO <user>

3.

ALTER DATABASE <dbname> SET TRUSTWORTHY ON

The application developers have also followed the same example setting up SQL cache and the connection code. When they start the application I can see the connection in the activity monitor but it has a status of SUSPENDED. I've also checked the view:

SELECT * FROM sys.dm_qn_subscriptions

There are no subscriptions setup I can however see that there is a new SP created. I tried to creating master keys as well but I'm not sure what the reasoning behind this is.

Is there anything else I need to take into consideration or check? When I change the data which they use for their select statement SQL profiler doesn't register anything (except the update). I have however seen this:

exec sp_executesql N'END CONVERSATION @.p1; BEGIN CONVERSATION TIMER (''69e2c786-9d33-dc11-a751-0050568146e8'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-5fb25d04-6ef6-47e6-9406-854f15e16eb7]), TIMEOUT @.p2;',N'@.p2 int,@.p1
uniqueidentifier',@.p2=60000,@.p1='BD98F6B4-A133-DC11-A751-0050568146E8'

This occurs when they refresh the page.

Any ideas?

Cheers!

Ian

You should place the SqlDependency.Start in the appdomain load event. From your description it seems to be placed on the page load.