March 28, 2007 at 10:27 am
Antares,
Thanks for the thought - I agree that is can be useful - it was run on Sat as part of the normal maintenance and then again yesterday before the tests - it had no impact on the query path in this case.
Does anyone have any idea if the same problem exists in 2005? We are planning a July/aug cutover and it would be nice to know that the optimizer handled the situation in a more appropriate way.
Harley
March 28, 2007 at 10:53 am
Try this as I want to know what happens when you do so then I may have the answer as to what you see that is happening.
DECLARE @x CHAR(8)
SET @x = CONVERT(CHAR(8),DATEADD(DAY,-15,GetDate()),112)
SELECT
TRD......,
CD.......,
PD.......,
Cur......,
EV.......
FROM
dbo.Tbl_SalesTransactions TRD
JOIN dbo.Tbl_CustomerData CD ON TRD.CID = CD.CID
JOIN dbo.Tbl_PaymentData PD ON TRD.PID = PD.PID
JOIN dbo.Tbl_Currencies Cur ON TRD.ISOCurrencyCode = Cur.ISOCurrencyCode
JOIN dbo.Tbl_ExecutingVendor EV ON TRD.EVID = EV.EVID AND EV.Active ='Y'
WHERE
(TRD.XLINK = '' OR TRD.XLINK IS NULL)
AND TRD.SalesType IN (1,11)
AND TRD.TxFlag<>'C'
AND TRD.SalesDate >= @x
--AND TRD.SalesDate >= '20070311'
Tell us how long it takes and the stats you were asked previously about estimated rowcounts.
March 28, 2007 at 11:39 am
Antares,
I declared the variable and moved the date calc out of the select as requested. The change had no impact on the query path selected - table scans are performed.
As an interesting sidenote, if I set the variable to a hardcoded date, the query plan elects full table scans. The number of estimated rows is 4152 for both values of @x - the actual is 0 in both cases.
Harley
March 28, 2007 at 11:57 am
OK that threw my theory a bit so I have a new one please try this and let us know what the query decides to do.
SELECT
TRD......,
CD.......,
PD.......,
Cur......,
EV.......
FROM
dbo.Tbl_SalesTransactions TRD
JOIN dbo.Tbl_CustomerData CD ON TRD.CID = CD.CID
JOIN dbo.Tbl_PaymentData PD ON TRD.PID = PD.PID
JOIN dbo.Tbl_Currencies Cur ON TRD.ISOCurrencyCode = Cur.ISOCurrencyCode
JOIN dbo.Tbl_ExecutingVendor EV ON TRD.EVID = EV.EVID AND EV.Active ='Y'
WHERE
--(TRD.XLINK = '' OR TRD.XLINK IS NULL)
TRD.SalesType IN (1,11)
--AND TRD.TxFlag<>'C'
AND TRD.SalesDate >= CONVERT(CHAR(8),DATEADD(DAY,-15,GetDate()),112)
--AND TRD.SalesDate >= '20070311'
Tell us how long it takes, the stats you get and if it makes and index choice. Unfortunately I don't have any datasets that large where I can test similar myself but I am trying to get my hands on one.
I did comment out the two lines on purpose just to see what the query does when you change.
Also try this as an alternate
SELECT
TRD......,
CD.......,
PD.......,
Cur......,
EV.......
FROM
dbo.Tbl_SalesTransactions TRD
JOIN dbo.Tbl_CustomerData CD ON TRD.CID = CD.CID
AND TRD.SalesDate >= CONVERT(CHAR(8),DATEADD(DAY,-15,GetDate()),112)
JOIN dbo.Tbl_PaymentData PD ON TRD.PID = PD.PID
JOIN dbo.Tbl_Currencies Cur ON TRD.ISOCurrencyCode = Cur.ISOCurrencyCode
JOIN dbo.Tbl_ExecutingVendor EV ON TRD.EVID = EV.EVID AND EV.Active ='Y'
WHERE
(TRD.XLINK = '' OR TRD.XLINK IS NULL)
AND TRD.SalesType IN (1,11)
AND TRD.TxFlag<>'C'
--AND TRD.SalesDate >= '20070311'
March 28, 2007 at 12:25 pm
Antares,
Without sales date, the query runs quite a long time and retunrs thousands of rows.....there are no indexes selected and full clustred index scans are done of all tables.......
Thanks for all your help - I'm just going to have to put it down to an anomaly of the optimizer and move on to other projects.
Harley
March 28, 2007 at 12:30 pm
I have tried the 3 databases I could get access too and just cannot duplicate the issue you have. I have seen this same sort of thing noted in the past on the boards if you want to try searching for but I just have no explination for your issue.
March 28, 2007 at 12:35 pm
Can you post the actual execution plan (text version pls)? I'm sure something obvious is spliping by all.
March 28, 2007 at 5:49 pm
Harley, did you try the approach I suggested on my first posting on page 2?
This additional table will contain as many different date columns as you need in one.
This single date column will be used for clustered index.
Then you need just to filter out action you choose - and you are there.
I guarantee you'll be impressed with performance of select from joined tables.
Believe me, properly designed databases don't have "sniffing parameter" problems.
_____________
Code for TallyGenerator
March 28, 2007 at 5:52 pm
Sergiv,
Thanks for the suggestion - but I'm afriad that I'm on to other projects. Adding the clustered index on SalesDate wont help me though - since there are several dates and I could only add a clustered index on one - even it if worked, the others would still perform poorly........for now, it appears that we will just have to live with the table scans..........
Regards,
Harley
March 28, 2007 at 6:55 pm
Did you actually read that post???
I suggested not to change indexing on existing table, but create another one.
If you ask for suggestions try to concentrate and read them.
_____________
Code for TallyGenerator
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply