Speed up - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Speed up

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-06-26 : 13:11:04

Any ideas how to speed up this monster?

declare @ExportSettingID AS INT = -1
declare @CompanyID AS VARCHAR (50) = 'CT20031121105711533'
declare @CategoryID AS INT = 1
-- Declare Section
DECLARE @Return AS INT
DECLARE @MAXRowID AS INT

-- select count(*) from t_advehicle -- 1 667 052
-- select top 1 status, av_type, av_companyid, siteid from t_advehicle where av_companyid = 'CT20031121105711533'
-- update t_advehicle set siteid = 2 where av_companyid = 'CT20031121105711533'
-- select top 1 * from RawXmlAdExport
-- Set Section
SET @Return = 0

-- Code Section

SELECT * INTO #temp FROM dbo.t_advehicle WITH (READUNCOMMITTED)
WHERE
t_advehicle.av_companyid = @CompanyID
AND t_advehicle.status=1
AND t_advehicle.av_type = @CategoryID
AND (t_advehicle.siteid & 2 = 2 or t_advehicle.siteid & 4 = 4)

--SELECT t_advehicle.av_id,
-- t_advehicle.av_type,
-- t_advehicle.av_companyid,
-- t_advehicle.av_price,
-- t_advehicle.av_hidePrice,
-- t_advehicle.av_wprice,
-- t_advehicle.av_hidewprice,
-- t_advehicle.av_year,
-- t_advehicle.av_make,
-- t_advehicle.av_model,
-- t_advehicle.av_trim,
-- t_advehicle.av_stocknumber,
-- t_advehicle.av_inStockDate,
-- t_advehicle.av_description,
-- t_advehicle.av_adText,
-- t_advehicle.av_adTextFr,
-- t_advehicle.av_adTextOptions,
-- t_advehicle.av_adTextOptionsFr,
-- t_advehicle.av_status,
-- t_advehicle.av_transmission,
-- t_advehicle.av_kms,
-- t_advehicle.av_doors,
-- t_advehicle.av_passengerno,
-- t_advehicle.av_exteriorcolor,
-- t_advehicle.av_extcolordesc,
-- t_advehicle.av_interiorcolor,
-- t_advehicle.av_drive,
-- t_advehicle.av_engine,
-- t_advehicle.av_cylinders,
-- t_advehicle.av_fueltype,
-- t_advehicle.av_warranty,
-- t_advehicle.av_warrantyDesc,
-- t_advehicle.av_comprogram,
-- t_advehicle.av_body,
-- t_advehicle.av_region,
-- t_advehicle.av_imported,
-- t_advehicle.av_modified,
-- t_advehicle.av_ws,
-- t_advehicle.av_Insert,
-- t_advehicle.av_Update,
-- t_advehicle.av_BCClassID,
-- t_advehicle.av_StratComID,
-- t_advehicle.av_ForeignID,
-- t_advehicle.av_ExtraTextFR,
-- t_advehicle.av_ExtraTextEN,
-- t_advehicle.StarBurstURL,
-- t_advehicle.SiteID,
-- t_advehicle.Status,
-- t_advehicle.Title INTO #temp FROM (SELECT advhc.av_id,
-- advhc.av_type,
-- advhc.av_companyid,
-- advhc.av_price,
-- advhc.av_hidePrice,
-- advhc.av_wprice,
-- advhc.av_hidewprice,
-- advhc.av_year,
-- advhc.av_make,
-- advhc.av_model,
-- advhc.av_trim,
-- advhc.av_stocknumber,
-- advhc.av_inStockDate,
-- advhc.av_description,
-- advhc.av_adText,
-- advhc.av_adTextFr,
-- advhc.av_adTextOptions,
-- advhc.av_adTextOptionsFr,
-- advhc.av_status,
-- advhc.av_transmission,
-- advhc.av_kms,
-- advhc.av_doors,
-- advhc.av_passengerno,
-- advhc.av_exteriorcolor,
-- advhc.av_extcolordesc,
-- advhc.av_interiorcolor,
-- advhc.av_drive,
-- advhc.av_engine,
-- advhc.av_cylinders,
-- advhc.av_fueltype,
-- advhc.av_warranty,
-- advhc.av_warrantyDesc,
-- advhc.av_comprogram,
-- advhc.av_body,
-- advhc.av_region,
-- advhc.av_imported,
-- advhc.av_modified,
-- advhc.av_ws,
-- advhc.av_Insert,
-- advhc.av_Update,
-- advhc.av_BCClassID,
-- advhc.av_StratComID,
-- advhc.av_ForeignID,
-- advhc.av_ExtraTextFR,
-- advhc.av_ExtraTextEN,
-- advhc.StarBurstURL,
-- advhc.SiteID,
-- advhc.Status,
-- advhc.Title
-- FROM dbo.t_advehicle advhc WITH (READUNCOMMITTED)
-- WHERE advhc.av_companyid = @CompanyID) t_advehicle
-- WHERE t_advehicle.av_type = @CategoryID
-- AND t_advehicle.status = 1
-- AND (t_advehicle.siteid & 2 = 2 or t_advehicle.siteid & 4 = 4)

--MAX ROW ID FOR THE Category
SELECT @MAXRowID = ISNULL(MAX(CategoryRowID), 0)
--ExportSettingID
FROM RawXmlAdExport WITH (READUNCOMMITTED)
WHERE CategoryID = @CategoryID
--select @MAXRowID
AND ExportSettingID = @ExportSettingID
--select @MAXRowID

--INSERT INTO RawXmlAdExport (CategoryRowID, ExportSettingID, CompanyID, CategoryID, XmlString)
SELECT
@MAXRowID + 1,
@ExportSettingID,
@CompanyID,
@CategoryID,
(
SELECT
1 AS Tag,
NULL AS Parent,
advehicle.av_id as [SyndicationAdExport!1!ADID!CDATA], --'ADID',
advehicle.av_companyid AS [SyndicationAdExport!1!CompanyID!CDATA], --''CompanyID',
t_Company.co_name AS [SyndicationAdExport!1!CompnayName!CDATA], --''CompnayName',
t_Company.co_address AS [SyndicationAdExport!1!Address!CDATA], --''Address',
t_Company.co_city AS [SyndicationAdExport!1!City!CDATA], --''City',
dbo.PhraseValue(t_region.r_name, 'en', NULL) AS [SyndicationAdExport!1!Province!CDATA], --''Province',
t_Company.co_postalcode AS [SyndicationAdExport!1!PostalCode!CDATA], --''PostalCode',
t_Company.co_phone AS [SyndicationAdExport!1!Phont!CDATA], --''Phont',
NULL AS [SyndicationAdExport!1!Phone2!CDATA], --''Phone2',
NULL AS [SyndicationAdExport!1!Contact!CDATA], --''Contact',
t_Company.co_email AS [SyndicationAdExport!1!CompanyEmail!CDATA], --''CompanyEmail',
CompanyLogo.LogoURL AS [SyndicationAdExport!1!CompanyLogo!CDATA], --''CompanyLogo',
t_Company.co_url AS [SyndicationAdExport!1!CompanyURL!CDATA], --''CompanyURL',
t_Company.co_Insert AS [SyndicationAdExport!1!CompanyInsert!CDATA], --''CompanyInsert' ,
t_Company.LastUpdateDateTime AS [SyndicationAdExport!1!CompanyModified!CDATA], --''CompanyModified',
ISNULL(dbo.PhraseValue(t_Category.ca_Phrase, 'en', NULL), '') AS [SyndicationAdExport!1!Category!CDATA], --'Category,
ISNULL(advehicle.av_make , '') AS [SyndicationAdExport!1!Make!CDATA], --''Make',
ISNULL(advehicle.av_model , '') AS [SyndicationAdExport!1!Model!CDATA], --''Model',
advehicle.av_trim AS [SyndicationAdExport!1!Trim!CDATA], --''Trim',
advehicle.av_year AS [SyndicationAdExport!1!Yrs!CDATA], --''Yrs',
ISNULL(advehicle.av_price, 0) AS [SyndicationAdExport!1!Price!CDATA], --''Price',
ISNULL(advehicle.av_hidePrice, 0) AS [SyndicationAdExport!1!HidePrice!CDATA], --''HidePrice',
ISNULL(dbo.PhraseListValue('en', NULL, 'Status', advehicle.av_status), '') AS [SyndicationAdExport!1!Status!CDATA], --''Status',
advehicle.av_stockNumber AS [SyndicationAdExport!1!StockNumber!CDATA], --''StockNumber',
ISNULL(AdVin.Vin, AdVin.InvalidVin) AS [SyndicationAdExport!1!Vin!CDATA], --''Vin',
adoption.csven as [SyndicationAdExport!1!Options!CDATA], --''Options',
AdExtraXMLField.ExtraFieldEN as [SyndicationAdExport!1!ExtraField], --''ExtraField',
ISNULL(Financing.IsAvailable, 0) AS [SyndicationAdExport!1!FinancingIsAvailable!CDATA], --''FinancingIsAvailable',
dbo.PhraseListValue('en', NULL, 'Warranty', advehicle.av_warranty) AS [SyndicationAdExport!1!Warranty!CDATA], --''Warranty',
advehicle.av_warrantyDesc AS [SyndicationAdExport!1!WarrantyDescription!CDATA], --''WarrantyDescription',
advehicle.av_description AS [SyndicationAdExport!1!AdDescription!CDATA], --''AdDescription',
ISNULL(advehicle.av_Imported, '') AS [SyndicationAdExport!1!CreatedDate!CDATA], --''CreatedDate',
advehicle.av_Modified AS [SyndicationAdExport!1!ModifiedDate!CDATA], --''ModifiedDate',
advehicle.siteid AS [SyndicationAdExport!1!siteid!CDATA],
(SELECT
Photo.Origional AS 'MainPhotoOriginalURL',
Photo.photo AS 'MainPhotoURL',
Photo.Thumbnail AS 'MainPhotoThumbnailURL',
CONVERT(VARCHAR(12), Photo.LastModification, 101)+' '+ CONVERT(VARCHAR(8), Photo.LastModification, 108)AS 'MainPhotoVersionDate',
Photo.PhotoOrder AS MainPhotoOrder,
ISNULL(Photo.SpinOrder ,'') AS 'MainPhotoSpinOrder'
FROM dbo.Photo WITH (READUNCOMMITTED)
WHERE dbo.Photo.ADID = advehicle.av_id
AND
dbo.Photo.IsMainPhoto = 1
FOR XML PATH (''), TYPE) AS [SyndicationAdExport!1!MainPhoto],

(SELECT x.OtherPhotoOriginalURL,
x.OtherPhotoURL,
x.OtherPhotoThumbnailURL,
x.OtherPhotoVersionDate,
x.OtherPhotoOrder,
x.OtherPhotoSpinOrder
FROM
(SELECT 1 AS OrderBy,
Photo.PhotoOrder AS 'OtherPhotoOrder',
ISNULL(Photo.SpinOrder ,'') AS 'OtherPhotoSpinOrder',
CONVERT(VARCHAR(12), Photo.LastModification, 101)+' '+ CONVERT(VARCHAR(8), Photo.LastModification, 108)AS 'OtherPhotoVersionDate',
Photo.Origional AS 'OtherPhotoOriginalURL',
Photo.Photo AS 'OtherPhotoURL',
Photo.Thumbnail AS 'OtherPhotoThumbnailURL'
FROM dbo.Photo WITH (READUNCOMMITTED)
WHERE dbo.Photo.ADID = advehicle.av_id
AND dbo.Photo.IsMainPhoto = 0
AND dbo.Photo.SpinOrder IS NULL

UNION
SELECT 2 AS OrderBY, --Spin Photos
Photo.PhotoOrder AS 'OtherPhotoOrder',
ISNULL(Photo.SpinOrder ,'') AS 'OtherPhotoSpinOrder',
Photo.LastModification AS 'OtherVersionDate',
Photo.Origional AS 'OtherPhotoOriginalURL',
Photo.Photo AS 'OtherPhotoURL',
Photo.Thumbnail AS 'OtherPhotoThumbnailURL'
FROM dbo.Photo WITH (READUNCOMMITTED)
WHERE dbo.Photo.ADID = advehicle.av_id
AND dbo.Photo.IsMainPhoto = 0
AND dbo.Photo.SpinOrder > 0

)AS x
ORDER BY x.OrderBY
FOR XML PATH (''), TYPE) AS [SyndicationAdExport!1!OtherPhoto],

CASE
WHEN advehicle.av_comprogram >0 THEN 'True'
ELSE 'False'
END as [SyndicationAdExport!1!IsCertified!CDATA],
'http://www.autotrader.ca/result/detailinfo.aspx?PPGID=' + convert(varchar(50),advehicle.av_id) as [SyndicationAdExport!1!ADURL!CDATA]
FROM #temp as advehicle WITH (READUNCOMMITTED)
JOIN dbo.t_Company WITH (READUNCOMMITTED)
ON advehicle.av_CompanyID = t_Company.co_id
JOIN dbo.t_Category WITH (READUNCOMMITTED)
ON advehicle.av_type = t_Category.ca_id
LEFT JOIN ADOPTION WITH (READUNCOMMITTED)
ON advehicle.av_id = adoption.adid
LEFT JOIN AdExtraXMLField WITH (READUNCOMMITTED)
ON advehicle.av_id = AdExtraXMLField.adid
LEFT JOIN dbo.CompanyLogo WITH (READUNCOMMITTED)
ON CompanyLogo.CompanyID = t_Company.co_id
AND CompanyLogo.LogoType = 1
LEFT JOIN dbo.Financing WITH (READUNCOMMITTED)
ON advehicle.av_ID = Financing.ADID
LEFT JOIN dbo.AdVin WITH (READUNCOMMITTED)
ON advehicle.av_ID = AdVin.ADID
JOIN dbo.t_Region WITH (READUNCOMMITTED)
ON t_Region.r_id = dbo.t_Company.co_region

FOR XML EXPLICIT
) XmlString

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 13:14:52

Start by figuring out where it's slow. Add SET STATISTICS TIME ON and SET STATISTICS IO ON to the top and run it. Look for the tables with the highest reads. Reads in the thousands typically indicates you can fix it with an index. Also examine the execution plan.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 13:15:28

And READUNCOMMITTED is not the turbo button. It's the bad data button. Proceed with caution.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-06-26 : 14:23:39

Thanks Tkizer

I clear the cache and turned on IO and statistics. Rewrote the query and still same logical/phsycal reads. What should I do next?

Very first msg:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 't_advehicle'. Scan count 1, logical reads 60, physical reads 6, read-ahead reads 0, lob logical reads 16, lob physical reads 2, lob read-ahead reads 0.

SELECT * INTO #temp FROM dbo.t_advehicle WITH (READUNCOMMITTED)
WHERE
t_advehicle.av_companyid = @CompanyID
AND t_advehicle.status=1
AND t_advehicle.av_type = @CategoryID
AND (t_advehicle.siteid & 2 = 2 or t_advehicle.siteid & 4 = 4)

----------------------------------------------------------------------------------------------------------

I rewrote the query below and It shows the follow msg (Same logical/physical reads):
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 't_advehicle'. Scan count 1, logical reads 60, physical reads 6, read-ahead reads 0, lob logical reads 16, lob physical reads 2, lob read-ahead reads 0.

SELECT t_advehicle.av_id,
t_advehicle.av_type,
t_advehicle.av_companyid,
t_advehicle.av_price,
t_advehicle.av_hidePrice,
t_advehicle.av_wprice,
t_advehicle.av_hidewprice,
t_advehicle.av_year,
t_advehicle.av_make,
t_advehicle.av_model,
t_advehicle.av_trim,
t_advehicle.av_stocknumber,
t_advehicle.av_inStockDate,
t_advehicle.av_description,
t_advehicle.av_adText,
t_advehicle.av_adTextFr,
t_advehicle.av_adTextOptions,
t_advehicle.av_adTextOptionsFr,
t_advehicle.av_status,
t_advehicle.av_transmission,
t_advehicle.av_kms,
t_advehicle.av_doors,
t_advehicle.av_passengerno,
t_advehicle.av_exteriorcolor,
t_advehicle.av_extcolordesc,
t_advehicle.av_interiorcolor,
t_advehicle.av_drive,
t_advehicle.av_engine,
t_advehicle.av_cylinders,
t_advehicle.av_fueltype,
t_advehicle.av_warranty,
t_advehicle.av_warrantyDesc,
t_advehicle.av_comprogram,
t_advehicle.av_body,
t_advehicle.av_region,
t_advehicle.av_imported,
t_advehicle.av_modified,
t_advehicle.av_ws,
t_advehicle.av_Insert,
t_advehicle.av_Update,
t_advehicle.av_BCClassID,
t_advehicle.av_StratComID,
t_advehicle.av_ForeignID,
t_advehicle.av_ExtraTextFR,
t_advehicle.av_ExtraTextEN,
t_advehicle.StarBurstURL,
t_advehicle.SiteID,
t_advehicle.Status,
t_advehicle.Title INTO #temp FROM (SELECT advhc.av_id,
advhc.av_type,
advhc.av_companyid,
advhc.av_price,
advhc.av_hidePrice,
advhc.av_wprice,
advhc.av_hidewprice,
advhc.av_year,
advhc.av_make,
advhc.av_model,
advhc.av_trim,
advhc.av_stocknumber,
advhc.av_inStockDate,
advhc.av_description,
advhc.av_adText,
advhc.av_adTextFr,
advhc.av_adTextOptions,
advhc.av_adTextOptionsFr,
advhc.av_status,
advhc.av_transmission,
advhc.av_kms,
advhc.av_doors,
advhc.av_passengerno,
advhc.av_exteriorcolor,
advhc.av_extcolordesc,
advhc.av_interiorcolor,
advhc.av_drive,
advhc.av_engine,
advhc.av_cylinders,
advhc.av_fueltype,
advhc.av_warranty,
advhc.av_warrantyDesc,
advhc.av_comprogram,
advhc.av_body,
advhc.av_region,
advhc.av_imported,
advhc.av_modified,
advhc.av_ws,
advhc.av_Insert,
advhc.av_Update,
advhc.av_BCClassID,
advhc.av_StratComID,
advhc.av_ForeignID,
advhc.av_ExtraTextFR,
advhc.av_ExtraTextEN,
advhc.StarBurstURL,
advhc.SiteID,
advhc.Status,
advhc.Title
FROM dbo.t_advehicle advhc WITH (READUNCOMMITTED)
WHERE advhc.av_companyid = @CompanyID) t_advehicle
WHERE t_advehicle.av_type = @CategoryID
AND t_advehicle.status = 1
AND (t_advehicle.siteid & 2 = 2 or t_advehicle.siteid & 4 = 4)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 14:36:45

We need to see the entire output of statistics time/io.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-06-26 : 14:39:28

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 't_advehicle'. Scan count 1, logical reads 60, physical reads 6, read-ahead reads 0, lob logical reads 16, lob physical reads 2, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 76 ms.
Table 'RawXmlAdExport'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 19 ms.
Table 't_region'. Scan count 0, logical reads 32, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 32, logical reads 125, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Photo'. Scan count 48, logical reads 338, physical reads 13, read-ahead reads 4, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AdVin'. Scan count 16, logical reads 48, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Financing'. Scan count 16, logical reads 51, physical reads 6, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CompanyLogo'. Scan count 16, logical reads 48, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AdExtraXMLField'. Scan count 0, logical reads 64, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AdOption'. Scan count 0, logical reads 64, physical reads 6, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't_company'. Scan count 0, logical reads 48, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't_Category'. Scan count 0, logical reads 32, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp_______________________________________________________________________________________________________________000000027A56'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 32, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 681 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

drop table #temp
go

declare @ExportSettingID AS INT = -1
declare @CompanyID AS VARCHAR (50) = 'CT20031121105711533'
declare @CategoryID AS INT = 1
-- Declare Section
DECLARE @Return AS INT
DECLARE @MAXRowID AS INT

-- select count(*) from t_advehicle -- 1 667 052
-- select top 1 status, av_type, av_companyid, siteid from t_advehicle where av_companyid = 'CT20031121105711533'
-- update t_advehicle set siteid = 2 where av_companyid = 'CT20031121105711533'
-- select top 1 * from RawXmlAdExport
-- Set Section
SET @Return = 0

SET NOCOUNT ON

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
DBCC FreeProccache

SET STATISTICS IO ON
SET statistics time ON

-- Code Section

--SELECT * INTO #temp FROM dbo.t_advehicle WITH (READUNCOMMITTED)
--WHERE
-- t_advehicle.av_companyid = @CompanyID
-- AND t_advehicle.status=1
-- AND t_advehicle.av_type = @CategoryID
-- AND (t_advehicle.siteid & 2 = 2 or t_advehicle.siteid & 4 = 4)

SELECT t_advehicle.av_id,
t_advehicle.av_type,
t_advehicle.av_companyid,
t_advehicle.av_price,
t_advehicle.av_hidePrice,
t_advehicle.av_wprice,
t_advehicle.av_hidewprice,
t_advehicle.av_year,
t_advehicle.av_make,
t_advehicle.av_model,
t_advehicle.av_trim,
t_advehicle.av_stocknumber,
t_advehicle.av_description,
t_advehicle.av_warranty,
t_advehicle.av_warrantyDesc,
t_advehicle.av_comprogram,
t_advehicle.av_region,
t_advehicle.av_imported,
t_advehicle.av_modified,
t_advehicle.SiteID,
t_advehicle.av_status,
t_advehicle.Status
INTO #temp FROM (SELECT advhc.av_id,
advhc.av_type,
advhc.av_companyid,
advhc.av_price,
advhc.av_hidePrice,
advhc.av_wprice,
advhc.av_hidewprice,
advhc.av_year,
advhc.av_make,
advhc.av_model,
advhc.av_trim,
advhc.av_stocknumber,
advhc.av_description,
advhc.av_warranty,
advhc.av_warrantyDesc,
advhc.av_comprogram,
advhc.av_region,
advhc.av_imported,
advhc.av_modified,
advhc.SiteID,
advhc.av_status,
advhc.Status
FROM dbo.t_advehicle advhc WITH (READUNCOMMITTED)
WHERE advhc.av_companyid = @CompanyID) t_advehicle
WHERE t_advehicle.av_type = @CategoryID
AND t_advehicle.status = 1
AND (t_advehicle.siteid & 2 = 2 or t_advehicle.siteid & 4 = 4)

--MAX ROW ID FOR THE Category
SELECT @MAXRowID = ISNULL(MAX(CategoryRowID), 0)
--ExportSettingID
FROM RawXmlAdExport WITH (READUNCOMMITTED)
WHERE CategoryID = @CategoryID
--select @MAXRowID
AND ExportSettingID = @ExportSettingID
--select @MAXRowID

--INSERT INTO RawXmlAdExport (CategoryRowID, ExportSettingID, CompanyID, CategoryID, XmlString)
SELECT
@MAXRowID + 1,
@ExportSettingID,
@CompanyID,
@CategoryID,
(
SELECT
1 AS Tag,
NULL AS Parent,
advehicle.av_id as [SyndicationAdExport!1!ADID!CDATA], --'ADID',
advehicle.av_companyid AS [SyndicationAdExport!1!CompanyID!CDATA], --''CompanyID',
t_Company.co_name AS [SyndicationAdExport!1!CompnayName!CDATA], --''CompnayName',
t_Company.co_address AS [SyndicationAdExport!1!Address!CDATA], --''Address',
t_Company.co_city AS [SyndicationAdExport!1!City!CDATA], --''City',
dbo.PhraseValue(t_region.r_name, 'en', NULL) AS [SyndicationAdExport!1!Province!CDATA], --''Province',
t_Company.co_postalcode AS [SyndicationAdExport!1!PostalCode!CDATA], --''PostalCode',
t_Company.co_phone AS [SyndicationAdExport!1!Phont!CDATA], --''Phont',
NULL AS [SyndicationAdExport!1!Phone2!CDATA], --''Phone2',
NULL AS [SyndicationAdExport!1!Contact!CDATA], --''Contact',
t_Company.co_email AS [SyndicationAdExport!1!CompanyEmail!CDATA], --''CompanyEmail',
CompanyLogo.LogoURL AS [SyndicationAdExport!1!CompanyLogo!CDATA], --''CompanyLogo',
t_Company.co_url AS [SyndicationAdExport!1!CompanyURL!CDATA], --''CompanyURL',
t_Company.co_Insert AS [SyndicationAdExport!1!CompanyInsert!CDATA], --''CompanyInsert' ,
t_Company.LastUpdateDateTime AS [SyndicationAdExport!1!CompanyModified!CDATA], --''CompanyModified',
ISNULL(dbo.PhraseValue(t_Category.ca_Phrase, 'en', NULL), '') AS [SyndicationAdExport!1!Category!CDATA], --'Category,
ISNULL(advehicle.av_make , '') AS [SyndicationAdExport!1!Make!CDATA], --''Make',
ISNULL(advehicle.av_model , '') AS [SyndicationAdExport!1!Model!CDATA], --''Model',
advehicle.av_trim AS [SyndicationAdExport!1!Trim!CDATA], --''Trim',
advehicle.av_year AS [SyndicationAdExport!1!Yrs!CDATA], --''Yrs',
ISNULL(advehicle.av_price, 0) AS [SyndicationAdExport!1!Price!CDATA], --''Price',
ISNULL(advehicle.av_hidePrice, 0) AS [SyndicationAdExport!1!HidePrice!CDATA], --''HidePrice',
ISNULL(dbo.PhraseListValue('en', NULL, 'Status', advehicle.av_status), '') AS [SyndicationAdExport!1!Status!CDATA], --''Status',
advehicle.av_stockNumber AS [SyndicationAdExport!1!StockNumber!CDATA], --''StockNumber',
ISNULL(AdVin.Vin, AdVin.InvalidVin) AS [SyndicationAdExport!1!Vin!CDATA], --''Vin',
adoption.csven as [SyndicationAdExport!1!Options!CDATA], --''Options',
AdExtraXMLField.ExtraFieldEN as [SyndicationAdExport!1!ExtraField], --''ExtraField',
ISNULL(Financing.IsAvailable, 0) AS [SyndicationAdExport!1!FinancingIsAvailable!CDATA], --''FinancingIsAvailable',
dbo.PhraseListValue('en', NULL, 'Warranty', advehicle.av_warranty) AS [SyndicationAdExport!1!Warranty!CDATA], --''Warranty',
advehicle.av_warrantyDesc AS [SyndicationAdExport!1!WarrantyDescription!CDATA], --''WarrantyDescription',
advehicle.av_description AS [SyndicationAdExport!1!AdDescription!CDATA], --''AdDescription',
ISNULL(advehicle.av_Imported, '') AS [SyndicationAdExport!1!CreatedDate!CDATA], --''CreatedDate',
advehicle.av_Modified AS [SyndicationAdExport!1!ModifiedDate!CDATA], --''ModifiedDate',
advehicle.siteid AS [SyndicationAdExport!1!siteid!CDATA],
(SELECT
Photo.Origional AS 'MainPhotoOriginalURL',
Photo.photo AS 'MainPhotoURL',
Photo.Thumbnail AS 'MainPhotoThumbnailURL',
CONVERT(VARCHAR(12), Photo.LastModification, 101)+' '+ CONVERT(VARCHAR(8), Photo.LastModification, 108)AS 'MainPhotoVersionDate',
Photo.PhotoOrder AS MainPhotoOrder,
ISNULL(Photo.SpinOrder ,'') AS 'MainPhotoSpinOrder'
FROM dbo.Photo WITH (READUNCOMMITTED)
WHERE dbo.Photo.ADID = advehicle.av_id
AND
dbo.Photo.IsMainPhoto = 1
FOR XML PATH (''), TYPE) AS [SyndicationAdExport!1!MainPhoto],

(SELECT x.OtherPhotoOriginalURL,
x.OtherPhotoURL,
x.OtherPhotoThumbnailURL,
x.OtherPhotoVersionDate,
x.OtherPhotoOrder,
x.OtherPhotoSpinOrder
FROM
(SELECT 1 AS OrderBy,
Photo.PhotoOrder AS 'OtherPhotoOrder',
ISNULL(Photo.SpinOrder ,'') AS 'OtherPhotoSpinOrder',
CONVERT(VARCHAR(12), Photo.LastModification, 101)+' '+ CONVERT(VARCHAR(8), Photo.LastModification, 108)AS 'OtherPhotoVersionDate',
Photo.Origional AS 'OtherPhotoOriginalURL',
Photo.Photo AS 'OtherPhotoURL',
Photo.Thumbnail AS 'OtherPhotoThumbnailURL'
FROM dbo.Photo WITH (READUNCOMMITTED)
WHERE dbo.Photo.ADID = advehicle.av_id
AND dbo.Photo.IsMainPhoto = 0
AND dbo.Photo.SpinOrder IS NULL

UNION
SELECT 2 AS OrderBY, --Spin Photos
Photo.PhotoOrder AS 'OtherPhotoOrder',
ISNULL(Photo.SpinOrder ,'') AS 'OtherPhotoSpinOrder',
Photo.LastModification AS 'OtherVersionDate',
Photo.Origional AS 'OtherPhotoOriginalURL',
Photo.Photo AS 'OtherPhotoURL',
Photo.Thumbnail AS 'OtherPhotoThumbnailURL'
FROM dbo.Photo WITH (READUNCOMMITTED)
WHERE dbo.Photo.ADID = advehicle.av_id
AND dbo.Photo.IsMainPhoto = 0
AND dbo.Photo.SpinOrder > 0

)AS x
ORDER BY x.OrderBY
FOR XML PATH (''), TYPE) AS [SyndicationAdExport!1!OtherPhoto],

CASE
WHEN advehicle.av_comprogram >0 THEN 'True'
ELSE 'False'
END as [SyndicationAdExport!1!IsCertified!CDATA],
'http://www.autotrader.ca/result/detailinfo.aspx?PPGID=' + convert(varchar(50),advehicle.av_id) as [SyndicationAdExport!1!ADURL!CDATA]
FROM #temp as advehicle WITH (READUNCOMMITTED)
JOIN dbo.t_Company WITH (READUNCOMMITTED)
ON advehicle.av_CompanyID = t_Company.co_id
JOIN dbo.t_Category WITH (READUNCOMMITTED)
ON advehicle.av_type = t_Category.ca_id
LEFT JOIN ADOPTION WITH (READUNCOMMITTED)
ON advehicle.av_id = adoption.adid
LEFT JOIN AdExtraXMLField WITH (READUNCOMMITTED)
ON advehicle.av_id = AdExtraXMLField.adid
LEFT JOIN dbo.CompanyLogo WITH (READUNCOMMITTED)
ON CompanyLogo.CompanyID = t_Company.co_id
AND CompanyLogo.LogoType = 1
LEFT JOIN dbo.Financing WITH (READUNCOMMITTED)
ON advehicle.av_ID = Financing.ADID
LEFT JOIN dbo.AdVin WITH (READUNCOMMITTED)
ON advehicle.av_ID = AdVin.ADID
JOIN dbo.t_Region WITH (READUNCOMMITTED)
ON t_Region.r_id = dbo.t_Company.co_region

FOR XML EXPLICIT
) XmlString

SET STATISTICS IO OFF
SET statistics time OFF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 14:54:56

The output looks very good and appears that the query is pretty fast already at less than a second.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-06-26 : 16:26:42

Yeah, the output is fast. But somehow. it's slow when it runs in prudction. it takes 2hours. I have most data from production in my local.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 16:30:17

Then run it in production and post the results here. We need to compare apples to apples.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-06-26 : 16:53:39

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't_advehicle'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'RawXmlAdExport'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp_______________________________________________________________________________________________________________000000086891'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 17:03:09

If that's the entire output from production, then the issue isn't with your query. You are almost certainly being blocked or waiting on something.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-06-26 : 17:09:10

How can I tell its blocked or waiting? How I fix it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-26 : 17:31:05

Check sysprocesses/sp_who/sp_who2/sp_whoisactive...

Find the spid in sysprocesses and show us the output for that row.

select * from sysprocesses where spid = SomeNo

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-07-03 : 14:16:06

I profiled (TSQL_LOCKS) the server and filtered by loginame. This stored procedure is running often and it takes from 1-7 seconds. I still don't undestand why the front end dev says it takes 2hours to run.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-07-03 : 14:18:18

sp_who gives me:
89 0 suspended ApplExportFramer_user ONBSDSSDMSPR1 0 DSS INSERT 0

---------------------------------------------------------------------------------------------------------
select * from master..sysprocesses where spid = 89 gives me:

spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd nt_domain nt_username net_address net_library loginame context_info sql_handle stmt_start stmt_end request_id
------ ------ ------- -------- -------------------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ----------- -------------------- ----------- ----------------------- ----------------------- ------ --------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------ ----------- ----------- -----------
89 6120 0 0x0042 121 PAGEIOLATCH_SH 5:1:22801414 5 38 488081 1312358 6 2012-07-03 10:55:26.977 2012-07-03 14:16:58.520 0 2 suspended 0x2A76DCB29C94024B84A6D5D12746AAA200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 ONBSDSSDMSPR1 .Net SqlClient Data Provider 7872 INSERT 812A7C1607AB TCP/IP ApplExportFramer_user 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x030005005F09CD1A0CE2CB004C9F000001000000 1962 15960 0

(1 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-03 : 14:33:42

I would talk to the front end dev then as it sounds like the issue is not within SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-07-03 : 15:31:47

How could I make sure it's not a DB issue?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-03 : 15:46:25

Well you said profiler shows it's taking 1-7 seconds, and the developer is saying 2 hours. That developer needs to prove to you now that the problem is with SQL Server. Profiler doesn't lie.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-07-03 : 20:28:48

Thanks for your help tkizer.

Yeah, I could see from SQL profiler the stored procedure is running very often and it takes 1-7 seconds to finish. I'm not sure why developer told me it takes 2 hours and before it took only 1 hour. Could be that the process use to run multiple times less than 1 second?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-05 : 11:35:19

You will need to ask the developer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources