Query Speed - How to make faster

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 2005 Forums

Transact-SQL (2005)

Query Speed - How to make faster

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2009-09-10 : 09:14:07

I have a query which works. However, it seems slow when I ran it on production environment. Some of the tables have 500,000 to 1,000,000 records......

Here's the query...........

select
POP10110.PONUMBER as POP1010_PONUMBER,
POP10110.Vendorid as Vendor,
POP10100.docdate as PODate,
POP10100.user2ent as POUser,
POP10110.ord,
POP10110.ORD/16384 as POLineItemNumber,
POP10110.ITEMNMBR as PO_itemNumber,
POP10110.itemdesc as PO_itemdesc,
POP10110.Qtyorder as POQty,
POP10110.QtyCance as POQty_cancelled,
POP10110.umqtyinb as UOFM_baseQty,
POP10110.uofm as Purchase_UOM,
POP10110.unitcost as POUnitCost,
POP10110.extdcost as POExtdcost,
POP10110.locncode as POLocation,
t.ivivindx,
GL00105.ACTNUMST,
t.itemtype,
sop60100.sopnumbe as SalesNumber,
(CASE WHEN SOP60100.SOPNUMBE in (select SOP30200.ORIGNUMB from SOP30200) then sop30200.docdate else sop10100.docdate end)
as SODate,
(CASE WHEN SOP60100.SOPNUMBE in (select SOP30200.ORIGNUMB from SOP30200) then 'Invoiced' else 'NotInvoiced' end)
as SalesInvoiced,
(CASE WHEN SOP60100.SOPNUMBE in (select SOP30200.ORIGNUMB from SOP30200) then sop30200.custnmbr else sop10100.custnmbr end)
as CustID,
sop60100.lnitmseq,
sop60100.lnitmseq/16384 as SOLineItemNumber,
(CASE WHEN SOP60100.SOPNUMBE in (select SOP30200.ORIGNUMB from SOP30200) then sop30300.unitprce else sop10200.unitprce end)
as SOUnitPrice,
sop60100.rcptcost as SOP60100ReceiptCost,
sop60100.qtyonpo as QtyRemaining,
sop60100.qtyrecvd as TotalQtyReceived,
sop60100.locncode as SalesLocation
from POP10110
outer apply
(select IVIVINDX, ITEMTYPE from IV00101 where POP10110.ITEMNMBR = IV00101.ITEMNMBR
)T
left outer join POP10100 on POP10100.PONUMBER = POP10110.PONUMBER
left outer join SOP60100 on SOP60100.PONUMBER = POP10110.PONUMBER and SOP60100.ORD = POP10110.ORD
left outer join GL00105 on GL00105.ACTINDX = POP10110.INVINDX
left outer join sop10100 on POP10110.ponumber = sop60100.ponumber and sop10100.sopnumbe = sop60100.sopnumbe
left outer join sop10200 on POP10110.ponumber = sop60100.ponumber and sop60100.ord = pop10110.ord
and sop10200.sopnumbe = sop60100.sopnumbe and sop60100.lnitmseq = sop10200.lnitmseq
left outer join sop30200 on POP10110.ponumber = sop60100.ponumber and sop30200.sopnumbe = sop60100.sopnumbe
left outer join sop30300 on POP10110.ponumber = sop60100.ponumber and sop60100.ord = pop10110.ord
and sop30300.sopnumbe = sop60100.sopnumbe and sop60100.lnitmseq = sop30300.lnitmseq
where t.itemtype = '6' and SOP60100.PONUMBER = POP10110.PONUMBER

I'm fairly new to the SQL world - as I worked on the query it seemed to grow - I'm now joining 7 tables and using several case statements.
I need to add several more case statements and I need to add 2 additional tables or join the additional tables in SRS.

Since the query will be used in an SRS report. My questions are:

1. How does one decide where to stop the query and use SRS to make additional table joins? Is there a simple rule to be learned?
2. I needed the case statements because our erp system stores posted sales documents in different tables than if sales documents are not posted...in our case sales price is going to come from 1 of 2 tables - the case statement works but I read that case statements can slow a query.......I researched where exists......but i couldn't make the conversion from case syntax to where exists....
3. What's the difference between outer apply and traditional joins? I liked the outer apply it seemed easier to code than complex outer joins......in my example i don't think I gain anything with the outer apply - it was something I did after researching on this forum (which is fabulous by the way)......
4. Should I have considered using User defined Functions? Is there any advantage? I've never created any and really don't know the basis/main purpose of them......any guidance would be appreciated.
5. This query will eventually be a SQL view - should I have considered a stored procedure? What advantage would a stored procedure have? Are they faster? They seem more difficult to code, any thoughts?

Thanks in advance for any advice.....

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-10 : 09:54:40

Try following the second link in my signature. It will tell you how to give us the information needed to answer your question. Having the full query (like you gave us)is a good start. Now we just need additional info.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources