Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query optimizing


query optimizing

Author
Message
srathna77
srathna77
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 176
Declare
@A money,
@Picklist varchar(10)
-- for testing
SELECT
@A = 1000,
@Picklist
= 'Exclude' -- 'Include'
IF
(@Picklist = 'Exclude')
BEGIN
select acct_code,
part_no
,location
,description
,sku_no
,list_status
,pub_date
,pub_status
,succession_status
,void
,in_stock
,avg_cost
,std_cost
,((avg_cost - std_cost)/avg_cost)*100 as unit_cost_var
,(avg_cost - std_cost)*in_stock as extended_diff
,in_stock*avg_cost as extended_avg_cost
from dfi_inv_master_list with (NOLOCK)
where ((avg_cost - std_cost)*in_stock >= @A
or (avg_cost - std_cost)*in_stock <= -1* @A)
and avg_cost <> 0
and in_stock <> 0
order by ABS((avg_cost - std_cost)*in_stock) desc
END
ELSE
BEGIN
select acct_code, part_no
, location
, description, sku_no
, list_status
, pub_date
, pub_status
, succession_status
, void
, in_stock
, avg_cost
, std_cost
, ((avg_cost - std_cost)/avg_cost)*100 as unit_cost_var
, (avg_cost - std_cost)*in_stock as extended_diff
, in_stock*avg_cost as extended_avg_cost
from
dfi_inv_master_list with (NOLOCK)
where ((avg_cost - std_cost)*in_stock >= @A
or (avg_cost - std_cost)*in_stock <= -1* @A)
and (avg_cost <> 0 OR avg_cost = 0)
and (in_stock <> 0 OR in_stock = 0)
order by ABS((avg_cost - std_cost)*in_stock) desc
END
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24168 Visits: 37932
Why don't you just delete this part from the second query, it is always true:


and (avg_cost <> 0 OR avg_cost = 0)
and (in_stock <> 0 OR in_stock = 0)



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
Assuming @A will always be a positive number you can change
where ((avg_cost - std_cost)*in_stock >= @A 
or (avg_cost - std_cost)*in_stock <= -1* @A)



to
where (ABS((avg_cost - std_cost)*in_stock) >= @A) 





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
srathna77
srathna77
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 176
Yes you are correct. any more suggestions plz

Thanks
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24168 Visits: 37932
Here is a big one. If these queries are inside a stored procedure, I'd seperate them into their own procedure and use a master stored procedure to call the approriate procedure based on the inputs to the master stored procedure.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Mike McQueen
Mike McQueen
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 841
where ((avg_cost - std_cost)*in_stock >= @A 
or (avg_cost - std_cost)*in_stock <= -1* @A)



will cause a scan every time, as well as the computation.

I don't know that much about computed columns (so please, don't be shy to correct me, gurus- and I wouldn't take my word as the end-all-be-all, OP) but I know you can have a PERSISTED computed column which stores and retains the calculated value. This will at least get rid of having to do the math every single time, plus you might even be able to get a seek out of it (PERSISTED computed columns can be indexed).

Like I said, I don't have all that much experience with these so I can't speak as to how they affect mass INSERTS and UPDATES. Someone please elaborate (or feel free to tell me my idea is terrible :heheSmile
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44977 Visits: 39869
Mike McQueen (10/7/2009)
where ((avg_cost - std_cost)*in_stock >= @A 
or (avg_cost - std_cost)*in_stock <= -1* @A)



will cause a scan every time, as well as the computation.

I don't know that much about computed columns (so please, don't be shy to correct me, gurus- and I wouldn't take my word as the end-all-be-all, OP) but I know you can have a PERSISTED computed column which stores and retains the calculated value. This will at least get rid of having to do the math every single time, plus you might even be able to get a seek out of it (PERSISTED computed columns can be indexed).

Like I said, I don't have all that much experience with these so I can't speak as to how they affect mass INSERTS and UPDATES. Someone please elaborate (or feel free to tell me my idea is terrible :heheSmile


Actually, a computed column that isn't persisted can be indexed. It just needs to be deterministic. But, your basic premise is spot on.

Also, regardless of that suggestion, the following will also force a full table scan or, at best, a full index scan...

order by ABS((avg_cost - std_cost)*in_stock) desc


You need to use the ol' "Divide'n'Conquer" methods to get any performance out of this code... Can you use a stored procedure? Also, can you use temp tables?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mike McQueen
Mike McQueen
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 841
Jeff- Thanks for the follow up, good to know I wasn't too far off Smile

I do have a question though- why would the order by cause a scan on the entire table?
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 72512
It's not the Order by so much as the ABS function in the Order by I'm pretty sure.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Mike McQueen
Mike McQueen
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 841
mtassin (10/8/2009)
It's not the Order by so much as the ABS function in the Order by I'm pretty sure.


But wouldn't it only need to ABS() the rows that meet the WHERE criteria?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search