-
Mar 16th, 2017, 06:35 AM
#1
issue converting datetime to varchar breaks table indexes!
Hello.
I'm fixing a query that results in a table scan and I'm trying to trigger the index seek that it has.
I'm having a very strange problem.
Code:
---- etc
LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
SUM(TransI_curValueEach) AS TransI_curValueEach,
SUM(TransI_curNetTotal) AS TransI_curNetTotal,
SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
FROM tblTrans_Inventory
GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I
ON I.TransI_lgnNumber = T.TransT_lgnNumber
AND I.TransI_intPackageGroupNo = T.TransT_intPackageGroupNo
CROSS JOIN tblCinema
WHERE B.BookingH_dtmDateBooked>= '2016-12-12'
AND Session_dtmRealShow BETWEEN .......... etc
B.BookingH_dtmDateBooked is DATETIME
Result:
Now when I do this:
Code:
declare @dateTo as date
set @dateTo = '20161212'
set @dateTo = CONVERT(DATE,CONVERT(NVARCHAR,@DateTo,112))
---- etc
LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
SUM(TransI_curValueEach) AS TransI_curValueEach,
SUM(TransI_curNetTotal) AS TransI_curNetTotal,
SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
FROM tblTrans_Inventory
GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I
ON I.TransI_lgnNumber = T.TransT_lgnNumber
AND I.TransI_intPackageGroupNo = T.TransT_intPackageGroupNo
CROSS JOIN tblCinema
WHERE B.BookingH_dtmDateBooked>= @dateTo
AND Session_dtmRealShow BETWEEN .......... etc
The plain changes entirely and I get a table scan and a HASH match (!!)
How is that possible?! I have tried various conversions on the @DateTo . Is there a possibility that @dateTo is treated differently than the '2016-12-12' ?
What can i try?
I can post the entire code if required btw the hash match comes from this: tblTrans_Inventory
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 16th, 2017, 07:14 AM
#2
Re: issue converting datetime to varchar breaks table indexes!
What happens if you convert it to a DATETIME rather than a DATE?
Do you need to store the column as a full DATETIME or would a DATE suffice? One issue you can run into is that a DATETIME stores the time down to the more or less millisecond range - this will create a lot of different values for every seconds worth of difference and can cause the effectiveness of the index to suffer.
If you can't store these as just dates then it might be worth considering adding an extra column of type date and calculating it based on the date part alone and use this column for querying.
-
Mar 16th, 2017, 07:44 AM
#3
Re: issue converting datetime to varchar breaks table indexes!
I've tried all combinations of convert. Datetime, date, to varchar to nvarchar to, to char '' + @dateto + '' etc etc.
Nothing works.
I also tried Option (Recompile) suggestion that the value is not known at compilation time. Does not work either.
Last edited by sapator; Mar 16th, 2017 at 08:13 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 17th, 2017, 02:32 PM
#4
Hyperactive Member
Re: issue converting datetime to varchar breaks table indexes!
Hello sapator,
Code:
declare @mydate varchar(10)
, @realdate datetime
, @strDate varchar(8)
, @wrongdate varchar
set @realdate = getdate()
set @strDate = convert(varchar(8), @realdate, 112)
set @mydate = convert(varchar(10), @realdate, 112)
set @wrongdate = CONVERT(varchar, @realdate, 112)
select @realdate as RealDate, @strDate as Stringdate, @mydate as Mydate, @wrongdate as OMG
Code:
Output
RealDate Stringdate Mydate OMG
2017-03-17 19:24:23.257 20170317 20170317 2
When converting dates into a varchar format you have to be extremely particular in ensuring that the varchar length suits the requirements of the date that is being converted.
Kind regards
Steve
-
Mar 17th, 2017, 03:48 PM
#5
Re: issue converting datetime to varchar breaks table indexes!
You should not be doing a CONVERT at all.
Your variable needs to be a DATETIME variable - since your field/index is that data type.
You set a DATETIME variable to a using a quoted-string - but that is certainly not a varchar() value.
I can see no reason why the below code should not work - NO convert or cast.
Code:
declare @dateTo as datetime
set @dateTo = '20161212'
-
Mar 18th, 2017, 04:17 AM
#6
Re: issue converting datetime to varchar breaks table indexes!
have you tried adding an index hint for tblEntryEvent? it could be that this makes sql server prefer the first execution plan. for some reason it now does decide that the second (bad) plan is better to run the second query
pls post the entire query
-
Mar 18th, 2017, 07:19 AM
#7
Re: issue converting datetime to varchar breaks table indexes!
To better isolate cause you should just attack that one table with a simple WHERE clause on just the index.
Use the variable and whatever casting you think you need.
Prove to yourself whether the data type or the casting of the variable is part of the problem or not by checking the execution plan.
-
Mar 20th, 2017, 10:44 AM
#8
Re: issue converting datetime to varchar breaks table indexes!
What I think is happening is that the optimized does not know the variable value while preparing the execution plan.
I was trying to use Option (Recompile) and set statistics profile to get it to have the value with no luck.
I also tried some other "dirty" tricks (LOOP, MERGE) while they worked at some point, the execution plan was changing afterwards.
So I'm not sure what else can I do.
Thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 20th, 2017, 11:50 AM
#9
Re: issue converting datetime to varchar breaks table indexes!
Did you try using a column type of just DATE and seeing if that makes a difference?
-
Mar 20th, 2017, 11:54 AM
#10
Re: issue converting datetime to varchar breaks table indexes!
Usually sapator is dealing with large production tables and trying to fix slow production queries. I'm guessing that a change of the data type from DATETIME to DATE is not something that can be done...
-
Mar 20th, 2017, 12:57 PM
#11
Re: issue converting datetime to varchar breaks table indexes!
Or add a calculated column that is just a date rather than a datetime and index that, if the query is only using a date then indexing values to within tens of milliseconds is going to create a lot of indexes with a broad range of values that aren't suitable for the types of query.
-
Mar 22nd, 2017, 10:30 AM
#12
Re: issue converting datetime to varchar breaks table indexes!
As szlamany said, I cannot alter the table structure.
Also if that was the issue (date) then the problem would also appear on a hard coded date value (as I do in the first example) as the query would also try to match different dates.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 22nd, 2017, 10:40 AM
#13
Re: issue converting datetime to varchar breaks table indexes!
I've never had issues with DATETIME indexes myself. And I do use them a real lot.
-
Mar 23rd, 2017, 03:15 AM
#14
Re: issue converting datetime to varchar breaks table indexes!
So.
Strange thing but today the execution plan seems to work.
So now it understands and gets the correct execution plan.
I've checked this in 8 primary SQL servers and the execution plan is build correctly on every single one of them.
So It may be that we are update statistics and the software does a rebuild indexes every week.
That's what every server has in common, so without being 100% sure, it may as well be (was) the problem.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 23rd, 2017, 03:53 AM
#15
Re: issue converting datetime to varchar breaks table indexes!
No.
Disregard, I was getting another set of data.
Problem remains.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 23rd, 2017, 04:58 AM
#16
Re: issue converting datetime to varchar breaks table indexes!
once more:
Originally Posted by digitalShaman
have you tried adding an index hint for tblEntryEvent? it could be that this makes sql server prefer the first execution plan. for some reason it now does decide that the second (bad) plan is better to run the second query
pls post the entire query
-
Mar 23rd, 2017, 10:41 AM
#17
Re: issue converting datetime to varchar breaks table indexes!
Hi.
I've tried now as you suggested but it does not make any difference just 1% raise on the table scan of tblTrans_Inventory.
What I found is that there is a '%like% clause may be a good candidate.
I have eliminated the line and I get all the indexes.
Here I get a 30% table scan on tblpayment log and I get a hind to use and index. That I cannot, since I cannot add-remove indexes from those tables.
Then, there is another strange thing. Since I cannot get an execution plan with a temp table by just clicking on the execution plan, I am running the query with included execution plan.
I get the indexes. Then I turn the query back, using the "Like" clause and removing the temp table.
I get correct indexes!!
What I see is that I get the correct indexes, only if I run the query + include the execution plan (with '%like%'). If i just click to get an execution plan, I get the table scans.
I guess if I could see the temp table plan without running the query, maybe the execution plan will have a table scan in there(?)
Here is the complete query if anyone interested.
Code:
declare @Date AS DATETIME
select @date = '20161231'
declare @dateTo as dateTIME
--select @dateTo = '20170311'
select @dateTo = (@Date -60)
--AS
--BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--set statistics profile on
--temp table try
--select workstation_strname into #tmpwrks from tblWorkstation
--where workstation_strname LIKE '%UNCOL%'
SELECT CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112)),
Cinema_strName,
F.Film_strTitleAlt,
S.Session_dtmRealShow,
T.TransT_dtmRealTransTime,
S.Screen_bytNum,
T.ScreenD_strPhyRowId + T.ScreenD_strSeatId,
B.BookingH_strCardNo,
B.BookingH_dtmDateBooked,
P.PayLog_strBankTransNumber,
T.TransT_lgnNumber,
SUM(CASE
--WHEN
-- T.STax_strCode in ('S','T','U','V','W','X','Y')
--THEN
-- round(T.TransT_intNoOfSeats * ((T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)/(SELECT Ent_TAX + VAT FROM VISTA.dbo.ZZ_CinemaTax WHERE OperatorCode = T.CinOperator_strCode)+ (T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)),1)
WHEN
T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * (T.TransT_curRedempValueEach),4)
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''--Voucher
THEN
round(T.TransT_intNoOfSeats * (COALESCE(T.TransT_curRedempValueEach,0) + COALESCE(I.TransI_curValueEach,0)),4)
WHEN
T.STax_strCode in ('A')
THEN
round(T.TransT_intNoOfSeats * (T.TransT_curValueEach),4)
ELSE
round(T.TransT_intNoOfSeats * (T.TransT_curValueEach + COALESCE(I.TransI_curValueEach,0)),4)
END) AS Gross,
SUM(CASE
--WHEN
-- T.STax_strCode in ('S','T','U','V','W','X','Y')
--THEN
-- round(T.TransT_intNoOfSeats * ((T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)/(SELECT Ent_TAX + VAT FROM VISTA.dbo.ZZ_CinemaTax WHERE OperatorCode = T.CinOperator_strCode)+ (T.TransT_curRedempTaxEach+T.TransT_curRedempTaxEach2)),2) - T.TransT_intNoOfSeats * (T.TransT_curRedempTaxEach + T.TransT_curRedempTaxEach2)
WHEN
T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * (T.TransT_curRedempValueEach - T.TransT_curRedempTaxEach - T.TransT_curRedempTaxEach2),4)
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''--Voucher
THEN
round(T.TransT_intNoOfSeats * (T.TransT_curRedempValueEach - T.TransT_curRedempTaxEach - T.TransT_curRedempTaxEach2),4)
ELSE
round(T.TransT_intNoOfSeats * (T.TransT_curValueEach - T.TransT_curTaxAmount - T.TransT_curTaxAmount2),4)
END) AS Net,
SUM(CASE
WHEN
T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach2,4)
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> '' OR T.STax_strCode in ('S','T','U','V','W','X','Y')
THEN
round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach2,4)
ELSE
round(T.TransT_intNoOfSeats * T.TransT_curTaxAmount2,4)
END) AS FDTH,
SUM(CASE
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> '' OR COALESCE(T.TransT_strVoucherBarcode,'') <> '' OR T.STax_strCode in ('S','T','U','V','W','X','Y')
THEN
round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach,4)
WHEN
T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * T.TransT_curRedempTaxEach,4)
ELSE
round(T.TransT_intNoOfSeats * T.TransT_curTaxAmount,4)
END) AS FPA,
SUM(CASE
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> ''
OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''
OR T.STax_strCode in ('S','T','U','V','W','X','Y')
OR T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curNetTotal,0)),4)
ELSE
round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curNetTotal,0)),4)
END) AS KRATHSEIS,
SUM(CASE
WHEN
COALESCE(T.TransT_strBarcodeRedemp,'') <> ''
OR COALESCE(T.TransT_strVoucherBarcode,'') <> ''
OR T.STax_strCode in ('S','T','U','V','W','X','Y')
OR T.TickNum_strStyleCode = '|TICK|S|N|N|N|' OR T.TickNum_strStyleCode = '|CONC|S|N|N|T|'
THEN
round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curSTaxEach,0)),4)
ELSE
round(T.TransT_intNoOfSeats * (COALESCE(I.TransI_curSTaxEach,0)),4)
END) AS FPAKRATHSEIS,
TransT_strStatus,
1 AS Scanned --COALESCE(EE.Entered,0) AS Scanned
,BD.Bookingd_strPickupWorkstn
FROM tblTrans_Ticket T
LEFT JOIN tblSession S
ON S.Session_lngSessionID = T.Session_lngSessionID
LEFT JOIN tblFilm F
ON F.Film_strCode = S.Film_strCode
LEFT JOIN tblBooking_Header B
ON B.TransC_lgnNumber = T.TransT_lgnNumber
LEFT JOIN tblPaymentLog P
ON P.TransC_lgnNumber = T.TransT_lgnNumber
LEFT JOIN dbo.tblBooking_Detail BD
ON BD.BookingD_intNextBookingNo = B.BookingH_intNextBookingNo
AND BD.BookingD_intSequence = T.TransT_intSequence
LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
SUM(TransI_curValueEach) AS TransI_curValueEach,
SUM(TransI_curNetTotal) AS TransI_curNetTotal,
SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
FROM tblTrans_Inventory
GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I
ON I.TransI_lgnNumber = T.TransT_lgnNumber
AND I.TransI_intPackageGroupNo = T.TransT_intPackageGroupNo
CROSS JOIN tblCinema
WHERE B.BookingH_dtmDateBooked >= @dateto
AND Session_dtmRealShow BETWEEN DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))) AND DATEADD(hh,24,DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))))
AND (P.PayLog_strAccepted = 'Y' OR B.BookingH_strSource = 'VIVA') --VIVA Transactions are not PayLog
AND TransT_strType = 'P'
AND B.BookingH_strSource IN ('CALL','VIVA','WWW','CELL') --ONLY ETICKETS
AND (EXISTS (SELECT * FROM dbo.tblEntryEvent EE WHERE EE.TransT_lgnNumber = T.TransT_lgnNumber AND EE.TransT_intSequence = T.TransT_intSequence AND EE.Workstation_strCode = BD.Bookingd_strPickupWorkstn) --Scanned at least once
-------- here I remove and add the temp table and the like clause
OR BD.Bookingd_strPickupWorkstn LIKE '%UNCOL%' or BD.Bookingd_strPickupWorkstn ='') --Or Collected by Uncollected Etickets Procedure ('=' for usher issues blank when error)
-- OR BD.Bookingd_strPickupWorkstn in (select workstation_strname from #tmpwrks) or BD.Bookingd_strPickupWorkstn ='')
GROUP BY Cinema_strName,
F.Film_strTitleAlt,
S.Session_dtmRealShow,
T.TransT_dtmRealTransTime,
S.Screen_bytNum,
T.ScreenD_strPhyRowId + T.ScreenD_strSeatId,
B.BookingH_strCardNo,
B.BookingH_dtmDateBooked,
P.PayLog_strBankTransNumber,
T.TransT_lgnNumber,
TransT_strStatus,
BD.Bookingd_strPickupWorkstn
option(recompile)
--drop table #tmpwrks
-- set statistics profile off
--select * from tblWorkstation where workstation_strname LIKE '%UNCOL%'
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 23rd, 2017, 10:48 AM
#18
Re: issue converting datetime to varchar breaks table indexes!
Here is the complete query if anyone interested.
OMG, ist a Monster (again)...
not sure when i find some time to decipher this.
-
Mar 23rd, 2017, 11:02 AM
#19
Re: issue converting datetime to varchar breaks table indexes!
It looks to me like you have this in a stored procedure in production - is that correct?
If so can you use a different method to get your "population of rows"?
-
Mar 23rd, 2017, 12:37 PM
#20
Re: issue converting datetime to varchar breaks table indexes!
Code:
AND Session_dtmRealShow BETWEEN DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))) AND DATEADD(hh,24,DATEADD(hh,6,CONVERT(DATETIME,CONVERT(VARCHAR,@Date,112))))
what the heck?
Code:
AND Session_dtmRealShow BETWEEN DATEADD(hh,6,@Date) AND DATEADD(hh,30,@Date)
wouldn't that do just fine?
Last edited by digitalShaman; Mar 23rd, 2017 at 12:48 PM.
-
Mar 23rd, 2017, 01:06 PM
#21
Re: issue converting datetime to varchar breaks table indexes!
you say you have tried a
Code:
LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
SUM(TransI_curValueEach) AS TransI_curValueEach,
SUM(TransI_curNetTotal) AS TransI_curNetTotal,
SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
FROM tblTrans_Inventory (WITH (INDEX PK_tblTrans_I... whatever name it is that is used in the fast query))
GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I
ON I.TransI_lgnNumber = T.TransT_lgnNumber
AND I.TransI_intPackageGroupNo = T.TransT_intPackageGroupNo
-
Mar 24th, 2017, 03:10 AM
#22
Re: issue converting datetime to varchar breaks table indexes!
Hi szlamany. What do you mean a different method (yes it's an sproc alright)?
Mind you this is supposed to be a production query that was initially design by the 3rd party, so in theory It should work fine (it does not apparently) ,
So I do what I can to fix it but I'm not 100% sure on every sum or round it does.
I have already changed all the Isnull to Coalesce, trying to remove the"like" clause. Can't remove the '|CONC|S|N|N|T|' , I may be able to remove some tax codes as some of them are not used and probably the select * vs a single column select , would like to remove the initial sum on the tbltrans_inventory as it is bugging me a lot to have it like this but I'm not sure where it sums and what (would appreciate a helping hand here). But as I see, the main issue seems to be the "like" condition, that apparently when I execute the #temp table and the use the "%like%" again, it picks up the optimal previous execution plan but not sure for how long, so I think I can put the temp table as a change so I will be sure that it stays that way.
digitalShaman. The datetime convert was there as an older DB had the date columns as varchars. So yes It can probably change but I'm not sure what will they do with the next update
I would prefer to avoid forcing an index to it and let the optimizer do that. As it only worked once on a previous problem I had (in a megathread posted here a couple of months ago). Granted , it works if i force it the cluster index but should I be better of , if removing the like clause is fixing the problem, rather than forcing the index?
Here is the plan as it look now btw.
Last edited by sapator; Mar 24th, 2017 at 03:14 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 27th, 2017, 01:44 AM
#23
Re: issue converting datetime to varchar breaks table indexes!
it works if i force it the cluster index
so with the index hint the query performs well, right? note that you do not force anything. it is just a hint and if the optimizer thinks its a bad hint it will not use it. the fact that it now uses the index Shows that the optimizer does accept the hint.
for me this solution (+ removing the Option recompile) would be good enough and as Long as your data is evenly distributed performance should not degrade over time. If you are not happy with it, then you'd Need to rephrase the query. It is hard to assist you with this without knowing the data model Details.
-
Mar 27th, 2017, 02:01 AM
#24
Re: issue converting datetime to varchar breaks table indexes!
Hi.
Maybe I didn't explain it well.
what I'm saying is that it will work both ways, if I fix the '%like%' situation.
So I would prefer to fix the '%like%' issue and have the optimized select the optimal solution, rather than just forcing a query hint and possibly forget it and have other issues if I come back to this query at some time and change something.
Thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 27th, 2017, 04:48 AM
#25
Re: issue converting datetime to varchar breaks table indexes!
If I was going to tackle something that looked like that I would first remove all the sum's and group by's and do a simple SELECT *.
That would give me a record count that I know I need to come back to.
Then using that simple new query I would start building a temp table that holds some of the PK's of the primary tables involved in all this. In places where you have NOT EXISTS I might try DELETE'ing rows in the TEMP TABLE that do not fit the criteria.
Basically building up in steps - and seeing where if any place the build of this table gets slow or doesn't use an index properly.
Then the production query uses this TEMP TABLE as the FROM and join's in what it needs.
There is nothing wrong in doing several steps to get a result. It's what the query optimizer does with a big old ugly query like that anyway.
-
Mar 27th, 2017, 04:58 AM
#26
Re: issue converting datetime to varchar breaks table indexes!
Hi.
Thanks for the info.
Unfortunately( again ) the Crystal Report Version that is used to show the data is not supporting #temp table (another big topic a couple of months ago here).
So I cannot use a temp table . But I could insert the data to the reporting table. But this will take the extra insert effort?
Also if i use a temp table to hold the '%like%' values (as shown) and do an "in" instead then the execution plan works well.
Last edited by sapator; Mar 27th, 2017 at 05:01 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 27th, 2017, 05:04 AM
#27
Re: issue converting datetime to varchar breaks table indexes!
Wow - brick walls in all directions!
Can you make VIEW's in the reporting database?
Making a real table in your reporting region might cost more time - or not.
You could still try the method I described and then try to bury it into a set of derived queries and sub-queries.
-
Mar 27th, 2017, 05:36 AM
#28
Re: issue converting datetime to varchar breaks table indexes!
Hi.
Yes I might be able to try a view or not. I remember having tons of troubles with views last time (i think you where responding that post too) and again, not sure that CR version accepts that (although not 100% sure for the view(.
Well here is the thing.
Running the Crystal Report will just take one second. This have happened, also with the previous non sargable and slow insert table.
I'm thinking that CR is paging the data else I cannot, for the life of me, explain the fast response.
OK but basically, I have no real issue now, since the temp table with the "in" is working fine and indexing correctly.
So I think, doing a full re-invent of the query will be time consuming for something that is working right now.
I can do that if I find some time, so I can re-write the query correctly but I'm on a very tight schedule.
One thing tho, and I have asked this before but I can't really grasp it.
The sum on tbltrans_inventory. That damn thing.
Can I get rid of it? I don't know who put it there like that. Is there a way to make this more readable? I see that is summing at the beginning. What would be the equivalent for example for one row?
talking about this:
Code:
LEFT JOIN (SELECT TransI_lgnNumber, TransI_intPackageGroupNo,
SUM(TransI_curValueEach) AS TransI_curValueEach,
SUM(TransI_curNetTotal) AS TransI_curNetTotal,
SUM(TransI_curSTaxEach) AS TransI_curSTaxEach
FROM tblTrans_Inventory
GROUP BY TransI_lgnNumber, TransI_intPackageGroupNo) I
and how would I go an make the equivalent on, p.e. this:
Code:
THEN
round(T.TransT_intNoOfSeats * (IsNull(T.TransT_curRedempValueEach,0) + IsNull(I.TransI_curValueEach,0)),4)
thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Mar 27th, 2017, 05:51 AM
#29
Re: issue converting datetime to varchar breaks table indexes!
That LEFT JOIN of a derived table make me think that VIEW's won' t work for you. That looks like a VIEW to me...
If I was building this in a SPROC that would be a TEMP TABLE (or TABLE VARIABLE if a small enough number of rows).
By JOIN'ing to this ENTIRE population of lgnNumber rows should mean this sub-query is built once and nicely applied to each main query row.
If it is expensive to build this SUM() data AND you are building it for TOO MANY rows then I would consider trying something different.
The other method that quickly come to mind is a sub-query right where the IsNull(I.TransI...) business is.
But that would require TransI_lgnNumber and TransI_intPackageGroupNo be part of a compound index.
Why is it a problem for you?
-
Mar 27th, 2017, 07:51 AM
#30
Re: issue converting datetime to varchar breaks table indexes!
It may seem like a problem may because I haven't grasp the mechanism entirely.
should have to look further to it to understand that summing.
Thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|