FindObject:
---在当前Server上找某某object,注意只需修改"要找的object"就可以使用
EXEC sp_MSforeachdb 'use ? ;IF EXISTS(SELECT top 1 1 FROM sys.syscomments WHERE text LIKE ''%要找的object%'')SELECT ''?'' as dbname,object_name(id) as object FROM sys.syscommentsWHERE text LIKE ''%要找的object%'''
XML处理:
INSERT INTO IMK.[dbo].[ClaimAttachment]
([ClaimNumber] ,[AttachmentName] ,[Path] ,[Status] ,[InDate] ,[InUser] ,[CompanyCode] ,[LanguageCode]) SELECT @ClaimNumber ,T.c.value('(AttachmentName/text())[1]', 'nvarchar(300)') AS AttachmentName ,T.c.value('(Path/text())[1]', 'nvarchar(50)') AS [Path] ,T.c.value('(Status/text())[1]', 'char(1)') AS [Status] -- 'O' AS Status ,GETDATE() AS [InDate] ,@IssueUser ,@CompanyCode ,@LanguageCode FROM @AttachmentList.nodes('/ArrayOfClaimAttachmentEntity/ClaimAttachmentEntity') T (c)
XML简单序列化:
XmlSerializer ser = new XmlSerializer(typeof(CreateTaskListRequest));
StringBuilder xml = new StringBuilder(); TextWriter stream = new StringWriter(xml); ser.Serialize(stream, requestList.CreateTaskByTemplateIDRequests[0]); string s = xml.ToString();
SQL Training:
1、建议将:
insert into log_SOInvoice (sonumber, wh ,invoicenumber) select top 5000 sonumber,'50' , 1000 from newsql.newegg.dbo.changeorderloghistory where sonumber in ( select sonumber from act..newegg_invoicemaster(nolock)) and changeordertime> getdate() - 30 and sonumber not in ( select sonumber from log_SOInvoice (nolock) where wh = '50')CPU time = 484 ms, elapsed time = 2230 ms.
修改为:insert into log_SOInvoice (sonumber, wh ,invoicenumber) select top 5000 sonumber,'50' , 1000 from newsql.newegg.dbo.changeorderloghistory where changeordertime> getdate() - 30 and sonumber not in ( select sonumber from log_SOInvoice (nolock) where wh = '50') and sonumber in ( select sonumber from act..newegg_invoicemaster(nolock)) 2、select ReferenceSoNumber,ItemNumber,SerialNumber,ScanDate,ScanUser,LargeItemFlag,WarehouseNumber,ShippingUser,ShippingDate,TransactionNumber from DropShipSerialNumber07 where shippingDate>'12/18/2003' and transactionnumber not in (select transactionnumber from warehouse4..UploadSerialnumberLog07 where datediff(day,indate,getdate())<8) and datediff(day,shippingdate,getdate())<7 执行结果:CPU time = 21437 ms, elapsed time = 33927 ms.修改为:
select ReferenceSoNumber,ItemNumber,SerialNumber,ScanDate,ScanUser,LargeItemFlag,WarehouseNumber,ShippingUser,ShippingDate,TransactionNumber from DropShipSerialNumber07 (nolock index(ShippingDate)) where shippingdate>dateadd(day,-7,getdate()) --shippingDate>'12/18/2003' and transactionnumber not in (select transactionnumber from warehouse4..UploadSerialnumberLog07(nolock) where indate>dateadd(day,-8,getdate()))执行结果: CPU time = 875 ms, elapsed time = 2005 ms. 3.建议将: insert into log_SOInvoice (sonumber, wh ,invoicenumber) select top 5000 a.sonumber,a.warehousenumber ,b.invoicenumber from newegg_sotransaction a(nolock) inner join newegg_somaster b (nolock) on a.sonumber = b.sonumber where b.status <> 'v' and a.warehousenumber is not null and b.invoicenumber is not null and b.invoicenumber <> 0 and a.sonumber not in ( select sonumber from log_SOInvoice (nolock)) and warehousenumber > '' and warehousenumber is not null and warehousenumber <>'00' and warehousenumber <'90' group by a.sonumber,a.warehousenumber,b.invoicenumber CPU time = 18594 ms, elapsed time = 6215 ms.修改为: insert into log_SOInvoice (sonumber, wh ,invoicenumber) select top 5000 a.sonumber,a.warehousenumber ,b.invoicenumber from newegg_sotransaction a(nolock) inner join newegg_somaster b (nolock) on a.sonumber = b.sonumber where not exists( select sonumber from log_SOInvoice (nolock) where a.sonumber = sonumber ) and b.status <> 'v' and a.warehousenumber is not null and b.invoicenumber is not null and b.invoicenumber <> 0 and warehousenumber > '' and warehousenumber is not null and warehousenumber <>'00' and warehousenumber <'90' group by a.sonumber,a.warehousenumber,b.invoicenumber 4.建议将: update GCI set GCI.LeftAmount = GCI.LeftAmount + GD.SOAmount From GiftCertificateItem GCI ,giftredeem GD Where GCI.giftcode = GD.RedeemGiftCode And GD.SONumber = @SONumber And GD.Status ='O' update GCI set GCI.status = Case when GCI.giftunitprice < GCI.leftamount Then 'A' Else 'U' End From GiftCertificateItem GCI ,giftredeem GD Where GCI.giftcode = GD.RedeemGiftCode And GD.SONumber = @SONumber And GD.Status ='O' 合并为:update GCI set GCI.LeftAmount = GCI.LeftAmount + GD.SOAmount, GCI.status = Case when GCI.giftunitprice < GCI.leftamount Then 'A' Else 'U' End From GiftCertificateItem GCI ,giftredeem GD Where GCI.giftcode = GD.RedeemGiftCode And GD.SONumber = @SONumber And GD.Status ='O'
5.建议将SQL语句修改为UNION ALL连接;
SELECT distinct a.ItemNumber, b.itemcatalog, ISNULL(c.ComboID, 0) AS ComboID, c.ItemNumber AS ComboItem, ISNULL(c.ItemCatalog, 0) AS comboItemCatalogFROM ECommerce2005.dbo.IM_ItemBuyAlong a (nolock) INNER JOIN abs.dbo.Arinvt01 b (nolock) ON a.ItemNumber = b.Item LEFT JOIN ECOMMERCE2005.DBO.IM_ComboTransaction c (NOLOCK) ON ((c.ItemNumber = a.itemNumber OR c.ItemCatalog = b.Itemcatalog) AND c.ComboGroupid <> a.comboGroupid) OR c.combogroupid IN (SELECT combogroupid FROM ECOMMERCE2005.DBO.IM_ComboGiftItem(NOLOCK) WHERE itemNumber = a.itemNumber AND comboGroupid <> a.comboGroupid)修改为:SELECT a.ItemNumber, b.itemcatalog, ISNULL(c.ComboID, 0) AS ComboID, c.ItemNumber AS ComboItem, ISNULL(c.ItemCatalog, 0) AS comboItemCatalogFROM ECommerce2005.dbo.IM_ItemBuyAlong a (nolock) INNER JOIN abs.dbo.Arinvt01 b (nolock) ON a.ItemNumber = b.Item LEFT JOIN ECOMMERCE2005.DBO.IM_ComboTransaction c (NOLOCK) ON ((c.ItemNumber = a.itemNumber OR c.ItemCatalog = b.Itemcatalog) AND c.ComboGroupid <> a.comboGroupid)unionSELECT a.ItemNumber,
b.itemcatalog, ISNULL(c.ComboID, 0) AS ComboID, c.ItemNumber AS ComboItem, ISNULL(c.ItemCatalog, 0) AS comboItemCatalogFROM ECommerce2005.dbo.IM_ItemBuyAlong a (nolock) INNER JOIN abs.dbo.Arinvt01 b (nolock) ON a.ItemNumber = b.Item LEFT JOIN ECOMMERCE2005.DBO.IM_ComboTransaction c (NOLOCK) ON c.combogroupid IN (SELECT combogroupid FROM ECOMMERCE2005.DBO.IM_ComboGiftItem(NOLOCK) WHERE itemNumber = a.itemNumber AND comboGroupid <> a.comboGroupid)原SQL语句执行结果: CPU time = 11187 ms, elapsed time = 11264 ms.
修改为UNION ALL后执行结果:CPU time = 3486 ms, elapsed time = 2412 ms.6.建议将SQL语句:
select * from scm..potran01 (nolock) where purno = 912926 order by TransactionNumber修改为:select * from scm..potran01 (nolock) where purno = '912926'
order by TransactionNumber
结果:修改前:CPU time = 1109 ms, elapsed time = 5319 ms. 修改后:CPU time = 0 ms, elapsed time = 40 ms. 7.可以考虑将SQL语句:Select * From CodeCenter..ViewPropertiesInAdvSearch Where CatalogID=22 Order By Priority,ValuePriority,ValueDescription修改为:Select * From CodeCenter..ViewPropertiesInAdvSearch Where CatalogID=22 Order By Priority,ValuePriority,ValueDescriptionoption(maxdop 1)结果:修改前:CPU time = 3284 ms, elapsed time = 14832 ms. 修改后:CPU time = 766 ms, elapsed time = 1183 ms.8.建议将SQL语句:
SELECT ACount = (SELECT COUNT(*) FROM ABS.dbo.arinvt01 a (nolock) INNER JOIN codecenter..itemdescription b (nolock) ON a.item = b.itemnumber WHERE NEWEGGITEMMARK > 0 AND checked = 1), VCount = (SELECT COUNT(*) FROM ABS.dbo.arinvt01 a (nolock) INNER JOIN codecenter..itemdescription b (nolock) ON a.item = b.itemnumber WHERE NEWEGGITEMMARK > 0 AND (ITEM LIKE '%sf' OR ITEMCATALOG = 346 OR ITEM LIKE '50-%') AND checked = 1), InStock = (SELECT COUNT(*) FROM ABS.dbo.arinvt01 A (nolock) INNER JOIN inventory..ItemInventory B (NOLOCK) ON A.ITEM = B.Item INNER JOIN codecenter..itemdescription c (nolock) ON a.item = c.itemnumber WHERE A.NEWEGGITEMMARK > 0 AND B.newegg_avail > 0 AND A.ITEM NOT LIKE '%sf' AND A.ITEMCATALOG <> 346 AND A.ITEM NOT LIKE '50-%' AND checked = 1), VirtualInStock = (SELECT COUNT(*) FROM ABS.dbo.arinvt01 A (nolock) INNER JOIN inventory..ItemInventory B (NOLOCK) ON A.ITEM = B.Item INNER JOIN codecenter..itemdescription c (nolock) ON A.item = c.itemnumber WHERE A.NEWEGGITEMMARK > 0 AND B.newegg_avail > 0 AND (A.ITEMCATALOG = 346 OR A.ITEM LIKE '%SF' OR A.ITEM LIKE '50-%') AND checked = 1), Refurbish = (SELECT COUNT(*) FROM ABS.dbo.arinvt01 A (nolock) INNER JOIN inventory..ItemInventory B (NOLOCK) ON A.ITEM = B.Item INNER JOIN codecenter..itemdescription c (nolock) ON a.item = c.itemnumber WHERE A.NEWEGGITEMMARK > 0 AND B.newegg_avail > 0 AND A.ITEM LIKE '%R' AND checked = 1)修改为:SELECT ACount = SUM(ACount), VCount = SUM(VCount), InStock = SUM(InStock), VirtualInStock = SUM(VirtualInStock), Refurbish = SUM(Refurbish)FROM (SELECT ACount = COUNT(*), VCount = COUNT(CASE WHEN (ITEM LIKE '%sf' OR ITEMCATALOG = 346 OR ITEM LIKE '50-%') THEN 1 END), InStock = 0, VirtualInStock = 0, Refurbish = 0 FROM ABS.dbo.arinvt01 a (nolock) INNER JOIN codecenter..itemdescription b (nolock) ON a.item = b.itemnumber WHERE NEWEGGITEMMARK > 0 AND checked = 1 UNION ALL SELECT ACount = 0, VCount = 0, InStock = COUNT(CASE WHEN (A.ITEM NOT LIKE '%sf' AND A.ITEMCATALOG <> 346 AND A.ITEM NOT LIKE '50-%') THEN 1 END), VirtualInStock = COUNT(CASE WHEN (A.ITEMCATALOG = 346 OR A.ITEM LIKE '%SF' OR A.ITEM LIKE '50-%') THEN 1 END), Refurbish = COUNT(CASE WHEN A.ITEM LIKE '%R' THEN 1 END) FROM ABS.dbo.arinvt01 A (nolock) INNER JOIN inventory..ItemInventory B (NOLOCK) ON A.ITEM = B.Item INNER JOIN codecenter..itemdescription c (nolock) ON A.item = c.itemnumber WHERE A.NEWEGGITEMMARK > 0 AND B.newegg_avail > 0 AND checked = 1) A结果:修改前:CPU time = 13375 ms, elapsed time = 9772 ms. 修改后:CPU time = 8916 ms, elapsed time = 4776 ms.
9.建议将SQL语句:
SELECT SoNumberFROM [CNSLS].[dbo].[SOMaster](NOLOCK)WHERE CompanyCode = 1006 AND Status = 'O' AND CreditCardVerifyMark = 'G' AND status <> 'V' AND (InvoiceNumber IS NULL OR InvoiceNumber = 0) AND AcctPostDate IS NOT NULL AND SoNumber NOT IN (SELECT SoNumber FROM [CNSLS].[dbo].[DownloadSO](NOLOCK))修改为:SELECT SoNumberFROM [CNSLS].[dbo].[SOMaster] a(NOLOCK)WHERE CompanyCode = 1006 AND Status = 'O' AND CreditCardVerifyMark = 'G' -- AND status <> 'V' AND (InvoiceNumber IS NULL OR InvoiceNumber = 0) AND AcctPostDate IS NOT NULL AND not exists (SELECT SoNumber FROM [CNSLS].[dbo].[DownloadSO](NOLOCK) where SoNumber=a.SoNumber)option (maxdop 1)结果:修改前:CPU time = 3200 ms, elapsed time = 4496 ms.
修改后:CPU time = 1297 ms, elapsed time = 1382 ms.10.由于abs..PO_Detail_Query中的potran01表中的ITEM字段的值长度最大为10,因此建议将:
SELECT TOP 200 *FROM abs..PO_Detail_QueryWHERE Item LIKE '[0-9]%' AND Item LIKE '%19-103-759%' AND purdate >= '10/16/2006 7:21:39 AM' AND purdate <= '1/17/2007 7:21:39 AM'修改为:SELECT TOP 200 *FROM abs..PO_Detail_QueryWHERE Item = '19-103-759' AND purdate >= '10/16/2006 7:21:39 AM' AND purdate <= '1/17/2007 7:21:39 AM'结果:修改前:CPU time = 6047 ms, elapsed time = 10633 ms.
修改后:CPU time = 0 ms, elapsed time = 5 ms.2、Personal表后未加NOLOCK;11.建议将SQL语句:
SELECT Item = rtrim(b.ItemNumber) + 'R', RefurbishIn = SUM(b.Quantity), RefurbishOut = 0, RefurbishAdjust = 0 FROM [abs].dbo.RefurbishMaster AS a (NOLOCK) INNER JOIN [abs].dbo.RefurbishTransaction AS b (NOLOCK) ON a.RTNumber = b.RTNumber WHERE a.ReceivingDate >= @pThisMonthBeginDate AND a.ReceivingDate < @pOneDayAfterEffectDate AND b.ItemNumber LIKE '%-%' GROUP BY b.ItemNumber UNION ALL SELECT Item = b.ItemNumber, RefurbishIn = 0, RefurbishOut = SUM(b.Quantity), RefurbishAdjust = 0 FROM abs.dbo.RefurbishMaster AS a (nolock) INNER JOIN [abs].dbo.RefurbishTransaction AS b (nolock) ON a.RTNumber = b.RTNumber WHERE a.ReceivingDate >= @pThisMonthBeginDate AND a.ReceivingDate < @pOneDayAfterEffectDate AND a.Source = '3' AND b.ItemNumber LIKE '%-%' GROUP BY b.ItemNumber修改为:SELECT Item =CASE WHEN a.Source = '3' THEN b.ItemNumber ELSE rtrim(b.ItemNumber) + 'R' END, RefurbishIn = CASE WHEN a.Source = '3' THEN 0 ELSE SUM(b.Quantity) END, RefurbishOut = CASE WHEN a.Source = '3' THEN SUM(b.Quantity) ELSE 0 END, RefurbishAdjust = 0 FROM [abs].dbo.RefurbishMaster AS a (NOLOCK) INNER JOIN [abs].dbo.RefurbishTransaction AS b (NOLOCK) ON a.RTNumber = b.RTNumber WHERE a.ReceivingDate >= '2007-01-01'--@pThisMonthBeginDate AND a.ReceivingDate < '2007-01-18'--@pOneDayAfterEffectDate AND b.ItemNumber LIKE '%-%' GROUP BY b.ItemNumber, a.Source经查,调整后结果是一致的;结果:调整前:CPU time = 846 ms, elapsed time = 2161 ms.
调整后:CPU time = 484 ms, elapsed time = 901 ms.12.由于使用sp_executesql函数时,字符型的参数变量是nchar或nvarchr的,但由于ZipCode,ShippingAddress是char型,因此查询时是不会使用索引的。因此建议将SQL:
exec sp_executesql N' SELECT * FROM imk.dbo.viewDetectFrud (nolock) WHERE ZipCode like rtrim(@ZipCode) + ''%'' and (ShippingAddress like rtrim(@FirstAddress) + ''%'' or ShippingAddress like rtrim(@SecondAddress) + ''%'') ',N'@ZIPCODE nvarchar(10),@FIRSTADDRESS nvarchar(80),@SECONDADDRESS nvarchar(80)', @ZIPCODE = N'97070', @FIRSTADDRESS = N'287', @SECONDADDRESS = N''修改为:declare @ZIPCODE varchar(10),@FIRSTADDRESS varchar(80),@SECONDADDRESS varchar(80)set @ZIPCODE = '97070'set @FIRSTADDRESS = '287'set @SECONDADDRESS = ''--exec sp_executesql N' exec (' SELECT * FROM imk.dbo.viewDetectFrud (nolock) WHERE ZipCode like ''' + @ZipCode + '%''and (ShippingAddress like ''' + @FirstAddress + '%'' or ShippingAddress like '''+ @SecondAddress + '%'')')结果:修改前:CPU time = 15781 ms, elapsed time = 6395 ms.
修改后:CPU time = 13 ms, elapsed time = 13 ms.