OK -- it's the CAST(ROUND that is causing the scalar calc I think. May be harmless
What are the estimated row counts in those tables? Are the estimates correct?
When was the last time you updated the statistics?
If the tables are small enough and the statics are up to date, SQL may just think the table scans are the best way to go.
Hi.
tbltrans_inventory table has about 10 million rows.
tblitem has about 12000 rows.
Running the view and including the inner joins from other tables (as shown in the first post)
I get 1369 rows on the test site
declare @DateFrom DATETIME
declare @DateTo DATETIME
declare @CollectedUserCodeV nvarchar(50)
set @CollectedUserCodeV = '-1'
declare @CollectedWorkstationCodeV nvarchar(50)
set @CollectedWorkstationCodeV = '-1'
set @DateFrom = '20160101'
set @DateTo = '20170120'
Select @DateFrom = DateAdd(Hour, 6, @DateFrom), @DateTo = DateAdd(Hour, 6, @DateTo)
-- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @CollectedUserCode int
declare @CollectedWorkstationCode nvarchar(50)
if (@CollectedUserCodeV = '-1' or @CollectedUserCodeV = 'UserNo')
Begin
set @CollectedUserCode = -1
end
else
begin
set @CollectedUserCode = cast(@CollectedUserCodeV as int)
end
if (@CollectedWorkstationCodeV = '-1' or @CollectedWorkstationCodeV = 'WrkstnCode')
begin
set @CollectedWorkstationCode ='-1'
end
else
begin
set @CollectedWorkstationCode = @CollectedWorkstationCodeV
end
SELECT CollectedDate,
CollectedUserCode,
User_strLastName,
User_strFirstName,
CollectedWorkStationCode, tblWorkstation.Workstation_strName AS CollectedWorkStationName,
ItemCode, ItemDescription,
SUM(Quantity) AS Quantity,
SUM(GrossValue) AS GrossValue,
SUM(VATValue) AS VATValue,
SUM(NetValue) AS NetValue
FROM [dbo].[ZZ_vwConsCollectedDTL] With (Index(indCollectedTranDate,ind_TransI_lngSessionId,indDateTime,indItemLocCollectedDate,indItemLocDate,indKPI_InvSummarise
,indPOSSessionID,indTransI_SummarisedFlag,zz_ind_tblTrans_Inventory,PK_tblTrans_Inventory))
LEFT OUTER JOIN dbo.tblWorkstation WITH (NOLOCK) ON tblWorkstation.Workstation_strCode = CollectedWorkStationCode
LEFT OUTER JOIN dbo.tblUser WITH (NOLOCK) ON tblUser.User_intUserNo = CollectedUserCode
WHERE collectedDate between @DateFrom and @DateTo
and ( ( @CollectedUserCode = -1 ) OR ( ZZ_vwConsCollectedDTL.CollectedUserCode = @CollectedUserCode))
and ( ( @CollectedWorkstationCode = '-1' ) OR ( ZZ_vwConsCollectedDTL.CollectedWorkStationCode = @CollectedWorkstationCode))
GROUP by CollectedDate ,
CollectedUserCode,
User_strLastName,
User_strFirstName,
CollectedWorkStationCode, tblWorkstation.Workstation_strName,
ItemCode, ItemDescriptionI'm not sure about the estimate on the tables. Not sure what estimates you want but from the tbltrans_inventory i get an estimate of 152655 rows (show in the execution plan)
update statistics is run on a weekly basis so up until we have started this topic should have run at some point.
At least on the last week post it has definitely run.
thanks.
OK -- so 12000 rows on the small table. not sure how wide those rows are but I can believe that SQL would just do a table scan. also the hash match makes sense. Doing a Seek on the large table would mean 12000 seeks then scans since the index is not unique on that table.
At this point, I'm not sure you can do much better.
BTW, it looks like there's no CI on these tables. Is that correct? If so, why? Especially the larger table. It could be fragmented. Heaps can get that way if the table is volatile (many inserts and deletes). Something to check
If you have to deal with heaps forwarded records can slow the db a lot. It may be worth seeing if something like the following produces any results:
SELECT
N'[' + SCHEMA_NAME(O.[schema_id]) + N'].[' + OBJECT_NAME(S.[object_id]) + N']' AS TableName
,S.forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID('agresso'), DEFAULT, DEFAULT, DEFAULT, DEFAULT) S
JOIN sys.objects O
ON S.[object_id] = O.[object_id]
WHERE S.index_type_desc = 'HEAP'
AND S.forwarded_record_count > 5;Also as this seems to be a third party DB, I would look at creating some sort of Reporting DB, Repository DB or Cube on another server to run these types of queries against. eg Having a Reporting DB will stop your queries interfering with the production server and you can alter the indexes, produce summary tables etc as you see fit. Doing this requires effort to setup but may produce long term benefits.
Hi. one row is about 150 columns but the widest must be the item decription column that is about 50-60 characters.
What exactly is CI? Columnstore indexes?
This is an 2005 sql server.
CI = Clustered Index
Oh.
Yeah i have no idea why there are no cluster index there.Can't do anything as this is the third party db.
Thanks for the info, I guess we have expanded this as much as it goes. Don't think I we can do more on that.
This is running for about 5 minutes on the test db( that is a lot smaller) so if i run this on the live db I think we are going to have problems. Also now it stopped to a offline db (we have many on the live servers also).
The reason that we are not copying the records to a reporting DB is that these results need to be fetched on live situations. So we can't run copies.
We have a reporting - Cube - Repository DB that gets the previous day data but this is what we need to do here as we need a live query running.
thanks.
I have just noticed [quote="sapator, post:27, topic:8986"]
This is running for about 5 minutes
[/quote]
Opps. You had better replace the DB name with the name of your DB.
Note sure what to replace and where:
SELECT
N'[' + SCHEMA_NAME(O.[schema_id]) + N'].[' + OBJECT_NAME(S.[object_id]) + N']' AS TableName
,S.forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID('mytestserver'), DEFAULT, DEFAULT, DEFAULT, DEFAULT) S
JOIN sys.objects O
ON S.[object_id] = O.[object_id]
WHERE S.index_type_desc = 'HEAP'
AND S.forwarded_record_count > 5;And what to do with the end result?
mytestserver should be myDB
If you get any results you have forwarded records. If the count is high it will give performance problems and you should rebuild the effected heaps.
Hi.
I get no rows in the result
OK!
Got something here!
If is use a with index to the tbltrans_inventory table everything works in 2 seconds and I get indexded execution plan !! ( FROM VISTA.dbo.tblTrans_Inventory WITH (INDEX = indCollectedTranDate) )
!I think this is it!
P.S. If i use it on the view it slows down significantly!
declare @DateFrom DATETIME
declare @DateTo DATETIME
declare @CollectedUserCodeV nvarchar(50)
set @CollectedUserCodeV = '-1'
declare @CollectedWorkstationCodeV nvarchar(50)
set @CollectedWorkstationCodeV = '-1'
set @DateFrom = '20170101' --- add extra dates so we do not loose some
set @DateTo = '20170105'
declare @DateFromX as datetime
set @dateFromx = DATEADD(d, -1, @DateFrom)
declare @DateToX as datetime
set @dateTox = DATEADD(d, +2, @DateTo)
--AS
--BEGIN
--TRUNCATE TABLE dbo.ZZ_tblRPTConcessionEOrders
-- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @CollectedUserCode int
declare @CollectedWorkstationCode nvarchar(50)
if (@CollectedUserCodeV = '-1' or @CollectedUserCodeV = 'UserNo')
Begin
set @CollectedUserCode = -1
end
else
begin
set @CollectedUserCode = cast(@CollectedUserCodeV as int)
end
if (@CollectedWorkstationCodeV = '-1' or @CollectedWorkstationCodeV = 'WrkstnCode')
begin
set @CollectedWorkstationCode ='-1'
end
else
begin
set @CollectedWorkstationCode = @CollectedWorkstationCodeV
end
SELECT CASE WHEN ISNULL(TransI_decActualNoOfItems,0) < 0 THEN TransI_dtmRealTransTime --Refund
ELSE CASE WHEN tblBooking_Header.BookingH_intNextBookingNo IS NULL THEN TransI_dtmRealTransTime ELSE TransI_dtmDateCollected END
END AS CollectedDate,
CASE WHEN ISNULL(TransI_decActualNoOfItems,0) < 0 THEN User_intUserNo --Refund
ELSE CASE WHEN tblBooking_Header.BookingH_intNextBookingNo IS NULL THEN User_intUserNo ELSE ZZ_tblTrans_Inventory_EXT.TransI_intPickupUser END
END AS CollectedUserCode,
CASE WHEN ISNULL(TransI_decActualNoOfItems,0) < 0 THEN Workstation_strCode --Refund
ELSE CASE WHEN tblBooking_Header.BookingH_intNextBookingNo IS NULL THEN Workstation_strCode ELSE ZZ_tblTrans_Inventory_EXT.TransI_strPickupWorkstn END
END AS CollectedWorkStationCode,
tblItem.HOPK AS ItemCode,
tblItem.Item_strItemDescription AS ItemDescription,
ISNULL(TransI_decActualNoOfItems,0) AS Quantity,
ISNULL(TransI_curValueEach,0) AS GrossUP,
CAST(ROUND(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decActualNoOfItems,0),2) AS MONEY) AS GrossValue,
CAST(ROUND((ISNULL(TransI_curSTaxEach,0)+
ISNULL(TransI_curSTaxEach2,0)+
ISNULL(TransI_curSTaxEach3,0)+
ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decActualNoOfItems,0),2) AS MONEY) AS VATValue,
--NET = GROSS - TAX
CAST(ROUND(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decActualNoOfItems,0),2)-
(ROUND((ISNULL(TransI_curSTaxEach,0)+
ISNULL(TransI_curSTaxEach2,0)+
ISNULL(TransI_curSTaxEach3,0)+
ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decActualNoOfItems,0),2)) AS MONEY) AS NetValue
Into #TempEorder
FROM VISTA.dbo.tblTrans_Inventory WITH (INDEX = indCollectedTranDate)
INNER JOIN VISTA.dbo.tblItem
ON tblItem.Item_strItemId = tblTrans_Inventory.Item_strItemId
LEFT OUTER JOIN VISTA.dbo.ZZ_tblTrans_Inventory_EXT
ON tblTrans_Inventory.TransI_lgnNumber = ZZ_tblTrans_Inventory_EXT.TransI_lgnNumber
AND tblTrans_Inventory.TransI_intSequence = ZZ_tblTrans_Inventory_EXT.TransI_intSequence
LEFT JOIN VISTA.DBO.tblBooking_Header
ON tblBooking_Header.TransC_lgnNumber = tblTrans_Inventory.TransI_lgnNumber
WHERE tblItem.Item_strBookingFee <> 'Y'
AND TransI_dtmDateCollected < '9999-01-01 00:00:00.000'
AND TransI_dtmDateCollected between @DateFromX and @DateToX
-- select * from #TempEorder
CREATE CLUSTERED INDEX Tmp_EticketRPT_IDX_C_Users_UserID ON #TempEorder(CollectedDate)
SELECT CAST(CONVERT(VARCHAR, CASE WHEN Datepart(hh, CollectedDate) < 6 THEN DATEADD(d, -1, CollectedDate) ELSE CollectedDate END, 112) AS DATETIME) AS CollectedDate,
CollectedUserCode, ISNULL(User_strLastName,'') + ' ' + ISNULL(User_strFirstName,'') AS CollectedUserName,
CollectedWorkStationCode, tblWorkstation.Workstation_strName AS CollectedWorkStationName,
ItemCode, ItemDescription,
SUM(Quantity) AS Quantity,
SUM(GrossValue) AS GrossValue,
SUM(VATValue) AS VATValue,
SUM(NetValue) AS NetValue
FROM #TempEorder
LEFT OUTER JOIN dbo.tblWorkstation WITH (NOLOCK) ON tblWorkstation.Workstation_strCode = CollectedWorkStationCode
LEFT OUTER JOIN dbo.tblUser WITH (NOLOCK) ON tblUser.User_intUserNo = CollectedUserCode
WHERE CAST(CONVERT(VARCHAR, CASE WHEN Datepart(hh, CollectedDate) < 6 THEN DATEADD(d, -1, CollectedDate) ELSE CollectedDate END, 112) AS DATETIME) between @dateFrom and @DateTo
and ( ( @CollectedUserCode = -1 ) OR ( #TempEorder.CollectedUserCode = @CollectedUserCode))
and ( ( @CollectedWorkstationCode = '-1' ) OR ( #TempEorder.CollectedWorkStationCode = @CollectedWorkstationCode))
GROUP BY CAST(CONVERT(VARCHAR, CASE WHEN Datepart(hh, CollectedDate) < 6 THEN DATEADD(d, -1, CollectedDate) ELSE CollectedDate END, 112) AS DATETIME),
CollectedUserCode,
ISNULL(User_strLastName,'') + ' ' + ISNULL(User_strFirstName,''),
CollectedWorkStationCode, tblWorkstation.Workstation_strName,
ItemCode, ItemDescription
drop table #TempEorder