August 21, 2012 at 8:56 am
Hi all
Can anyone tell me why when I try to right click and select analyse in DTA I get an error of "Object must implement IConvertible. (mscorlib)"
The query is taken from a stored proc I am trying to tune, and is below
DECLARE@centralCompanyvarchar(10),
@salesCompanyvarchar(10),
@postcodenvarchar(50),
@townnvarchar(50),
@countynvarchar(50),
@tradingOffernvarchar(10),
@Itemnvarchar(12),
@SearchKeynvarchar(50),
@fromDatedatetime
SELECT DISTINCT
ct.NAME,
St.SALESID,
ct.CITY,
ct.PHONE,
st.DELIVERYADDRESS,
St.MARAPORDERSTATUS,
si.LastInvoiceDate,
sl.itemid,
sl.NAME as itemDesc,
sl.QTYORDERED,
st.MARTRADINGOFFERID
FROM DynamicsV5Realtime.dbo.SALESLINE sl
INNER JOIN DynamicsV5Realtime.dbo.SalesTable st on sl.SALESID = st.SALESID
INNER JOIN DynamicsV5Realtime.dbo.CUSTTABLE ct on
(st.CUSTACCOUNT = ct.ACCOUNTNUM)
INNER JOIN
(SELECT MAX(InvoiceDate) as LastInvoiceDate, ORIGSALESID
FROM DynamicsV5Realtime.dbo.CUSTINVOICESALESLINK
GROUP BY ORIGSALESID) as si ON st.SALESID = si.ORIGSALESID
INNER JOIN (SELECT element FROM dbo.udf_R0000_ParameterStringToTable(@postcode, ',')) as post
on (st.DELIVERYZIPCODE like element + '%' or @postcode = '')
INNER JOIN DynamicsV5Realtime.dbo.INVENTTABLE it ON sl.ITEMID = it.ITEMID
INNER JOIN DynamicsV5Realtime.dbo.ADDRESSCOUNTY ad on sl.DELIVERYCOUNTY = ad.COUNTYID
WHERE
st.SALESTYPE = 3
and ct.DATAAREAID IN (@salesCompany,@centralCompany)
and (st.DELIVERYCITY like '%' + @town + '%' or @town = '')
and (ad.NAME like '%' + @county + '%' or @county = '')
and (st.MARTRADINGOFFERID = @tradingOffer or @tradingOffer = 'All')
and (sl.ITEMID like '%' + @Item + '%' or @Item = '')
and (it.NAMEALIAS like '%' + @SearchKey + '%' or @SearchKey = '')
and (si.LastInvoiceDate >= @fromDate or @fromDate is null)
August 21, 2012 at 9:34 am
I don't really use DTA, so I cannot comment on that. However, I can tell you that this blog post by Gail Shaw may help you to increase performance on this query:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Jared
CE - Microsoft
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply