Need some help tweaking SQL select
-
I have a view that has horrible response time, plus I'm getting dups. When I add "Distinct" to the front 9 out of 10 times it'll time out. Some of these tables are huge and hold close to 1 Million records. Is there anything I can do to help the speed of this thing?
SELECT CONVERT(nvarchar, dbo.table_repinfo.co_num) AS co_num, CONVERT(nvarchar, dbo.table_repinfo.ack_hold) AS ack_hold,
dbo.table_repinfo.ack_sent_date, dbo.table_repcodem.board_des, dbo.table_repinfo.ccode, dbo.custaddr.city, dbo.coitem.co_line,
dbo.coitem.co_release, dbo.co.contact, dbo.custaddr.country, dbo.co.cust_num, dbo.co.cust_po, dbo.co.cust_seq,
dbo.custaddr.name AS CustAddrName, dbo.custaddr.addr##1 AS CustAddy1, dbo.custaddr.addr##2 AS CustAddy2, CONVERT(nvarchar,
dbo.coitem.cust_item) AS CustPartNo, CONVERT(nvarchar, dbo.table_repinfo.custno) AS CustRepNo, dbo.coitem.uf_DockDate AS DockDate,
dbo.table_repinfo.est_hrs, CONVERT(nvarchar, dbo.table_repinfo.hold) AS hold, dbo.table_repholds.hold_code, dbo.table_repinfo.indate,
CONVERT(nvarchar, dbo.table_repinfo.inutc) AS inutc, dbo.table_repinfo.labor_rate, dbo.table_repcodel.cat AS LCat,
dbo.table_category.cat_desc AS LCatDesc, dbo.table_repcodel.main AS LMain, dbo.table_repcodel.oper_num AS LOperNum,
dbo.table_repcodel.rcode1 AS LRC1, table_repcodes_4.code_desc AS LCR1Desc, dbo.table_repcodel.rcode2 AS LRC2,
table_repcodes_1.code_desc AS LRC2Desc, dbo.table_repcodel.rcode3 AS LRC3, table_repcodes_2.code_desc AS LRC3Desc,
dbo.table_repcodel.rcode4 AS LRC4, table_repcodes_3.code_desc AS LRC4Desc, dbo.table_repinfo.mat_mult, dbo.table_repcodem.cat AS MatlCat,
dbo.table_repcodem.main AS MatlMain, dbo.table_repcodem.oper_num AS MatlOperNum, dbo.table_repcodem.prime AS MatlPrime,
dbo.item.item AS MatricPartNo, dbo.item.description AS MatricPartNoDesc, CONVERT(nvarchar, dbo.table_mines.name) AS MineName,
dbo.table_mines.serv_center AS MineServCenter, dbo.table_mines.stat AS MineStatus, dbo.table_repcodem.rcode1 AS MRC1,
table_repcodes_5.code_desc AS MRC1Desc, dbo.table_repcodem.rcode2 AS MRC2, table_repcodes_6.code_desc AS MRC2Desc,
dbo.table_repcodem.rcode3 AS MRC3, table_repcodes_7.code_desc AS MRC3Desc, dbo.table_repcodem.rcode4 AS MRC4,
table_repcodes_8.code_desc AS MRC4Desc, dbo.table_repinfo_newdate.new_date, CONVERT(nvarchar, dbo.table_repinfo.newcpn) AS newcpn,
dbo.table_repinfo.newitem, CONVERT(nvarchar, dbo.table_repinfo.newrev) AS newrev, CONVERT(nvarchar, dbo.table_repinfo.notification)
AS notification, dbo.co.order_date, dbo.table_repinfo.outdate, dbo.table_repinfo.oututc, dbo.co.phone, dbo.co.price,
dbo.item.uf_ProductType AS ProductType, dbo.coitem.qty_ordered, dbo.coitem.qty_shipped, dbo.table_repcodem.ref_seq,
dbo.table_repinfo.rep_type, CONVERT(nvarchar, dbo.table_repinfo.rev) AS rev, CONVERT(nvarchar, dbo.table_repinfo.ser_num) AS ser_num,
dbo.co.ship_code, dbo.coitem.ship_date, dbo.shipcode.description AS ShipCodeDesc, dbo.custaddr.state, dbo.co.taken_by, dbo.item.uf_Team AS Team,
dbo.co.terms_code, dbo.terms.description AS TermsDesc, CONVERT(nvarchar, dbo.table_repinfo.war_type) AS war_type, dbo.table_repinfo.wo_num,
dbo.custaddr.zip
FROM dbo.table_mines RIGHT OUTER JOIN
dbo.table_repinfo ON dbo.table_mines.ccode = dbo.table_repinfo.ccode LEFT OUTER JOIN
dbo.table_repcodem LEFT OUTER JOIN
dbo.table_repcodes table_repcodes_8 ON dbo.table_repcodem.rcode4 = table_repcodes_8.rcode LEFT OUTER JOIN
dbo.table_repcodes table_repcodes_7 ON dbo.table_repcodem.rcode3 = table_repcodes_7.rcode LEFT OUTER JOIN
dbo.table_repcodes table_repcodes_6 ON dbo.table_repcodem.rcode2 = table_repcodes_6.rcode LEFT OUTER JOIN
dbo.table_repcodes table_repcodes_5 ON dbo.table_repcodem.rcode1 = table_repcodes_5.rcode ON
dbo.table_repinfo.co_num = dbo.table_repcodem.job LEFT OUTER JOIN
dbo.table_repcodes table_repcodes_1 LEFT OUTER JOIN
dbo.table_repcodel LEFT OUTER JOIN
dbo.table_repcodes table_repcodes_3 ON dbo.table_repcodel.rcode4 = table_repcodes_3.rcode LEFT OUTER JOIN
dbo.table_repcodes table_repcodes_2 ON dbo.table_repcodel.rcode3 = table_repcodes_2.rcode ON
table_repcodes_1.rcode = dbo.table_repcodel.rcode2 LEFT OUTER JOIN
dbo.table_repcodes table_repcodes_4 ON dbo.table_repcodel.rcode1 = table_repcodes_4.rcode LEFT OUTER JOIN
dbo.table_category ON dbo.table_repcodel.cat = dbo.table_category.cat ON dbo.table_repinfo.co_num = dbo.table_repcodel.job LEFT OUTER JOIN
dbo.table_repinfo_newdate ON dbo.table_repinfo.ser_num = dbo.table_repinfo_newdate.ser_num LEFT OUTER JOIN
dbo.table_repholds ON dbo.table_repinfo.co_num = dbo.table_repholds.co_num LEFT OUTER JOIN
dbo.item RIGHT OUTER JOIN
dbo.custaddr RIGHT OUTER JOIN
dbo.coitem RIGHT OUTER JOIN
dbo.co ON dbo.coitem.co_num = dbo.co.co_num LEFT OUTER JOIN
dbo.shipcode ON dbo.co.ship_code = dbo.shipcode.ship_code LEFT OUTER JOIN
dbo.terms ON dbo.co.terms_code = dbo.terms.terms_code ON dbo.custaddr.cust_num = dbo.co.cust_num ON dbo.item.item = dbo.coitem.item ON
dbo.table_repinfo.co_num = dbo.co.co_num
-
Without reading the whole thing in details :
Are all the columns listed in the join statements indexed?
Is there a way to remove some of the left and right joins with inner joins?
can you post the execution plan?
-
Also, getting rid of the in-line CONVERTs would speed things up.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I kind of need the inline converts seeing as the front end I'm feeding this beast requires those fields to be nvarchar.
Execution plan???
-
what if I separate this out into multiple views?
-
Can you change the back-end field types to nvarchar, rather than doing the converts? I'm guessing not.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Probabely won't be faster... might even be slower.
execution plan :
--use the results in text mode (make sure you can show more than 255 characters per line in tools/options/results/max char per column
SET SHOWPLAN_TEXT ON
GO
--Select query here
GO
SET SHOWPLAN_TEXT OFF
-
You sure you want this behaviour ?
dbo.table_repcodem
LEFT OUTER JOIN dbo.table_repcodes table_repcodes_8 ON dbo.table_repcodem.rcode4 = table_repcodes_8.rcode
LEFT OUTER JOIN dbo.table_repcodes table_repcodes_7 ON dbo.table_repcodem.rcode3 = table_repcodes_7.rcode
LEFT OUTER JOIN dbo.table_repcodes table_repcodes_6 ON dbo.table_repcodem.rcode2 = table_repcodes_6.rcode
LEFT OUTER JOIN dbo.table_repcodes table_repcodes_5 ON dbo.table_repcodem.rcode1 = table_repcodes_5.rcode
probably not ... "plus I'm getting dups"
This table appear so many times in joins dbo.table_repcodes
I think will be better to post the tables and the result that you want
Kindest Regards,Vasc
-
Actually yes I need that behaviour and the dups are not coming from those relationships. For each repcodem.rcode1, 2,3,& 4 there are many repcodes. It's a 1 to many relationship coming from the same table, so I need that joined for each rcode to get the relative info from repcodes.
I think the main problem is the inline converts. I'm turning off replication and modifying my tables to be the correct data type and get rid of these inline converts. Hopefully that'll fix a lot of the response time issues.
-
Changing the datatypes and getting rid of the inline converts made the response time go from 30 secs to 1 sec. The dups were coming from a missing link between two tables (customer and address)
Thanks for your help guys/gals!
-
Why is it faster that way?
-
Do you mean why is it faster doing nothing than doing CONVERTs??The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Yes and no...
How can the converts be accountable for 97% of the work in the query (from 30 secs to 1)? I'm wondering if the missing link might not have been the real time saver here. I'm not arguing that doing nothing is faster than doing something
. -
Oh good. Well, if there's a lot of data, that's a lot of CONVERTs - approx 15 per row returned. I reckon it might all add up. Missing links don't help, of course.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
CONVERT Deterministic unless used with datetime, smalldatetime, or sql_variant. The datetime and smalldatetime data types are deterministic if the style parameter is also specified. So if your converts where DETERMINISTIC I don't believe either that the converts saved that much time
Kindest Regards,Vasc
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply