SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query optimizing


query optimizing

Author
Message
srathna77
srathna77
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40492 Visits: 38567
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10569 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
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

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

Thanks
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40492 Visits: 38567
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 (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 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 Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88704 Visits: 41132
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 315 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.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4878 Visits: 72519
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 (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 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