February 19, 2014 at 3:18 pm
I have a BETWEEN statement that is excruciatingly slow. I'm linking tables by seeing if one date is between the two dates of the other table.
Base table:
RecKey
PartNumber
StartDate
EndDate
Linked Table:
PartNumber
EventDate
I would prefer to generate this table as an intermediate table, where the RecKey is from the Base Table above, and the RecordDate is StartDate, next record is StartDate + 1, StartDate + n, and ending at the EndDate:
Desired Table:
RecKey
PartNumber
RecordDate
Is there a slick way to do this without resorting to cursors or looping?
David
February 19, 2014 at 3:27 pm
Need to see some sample data here. It's not quite clear. Are you saying that if you join the base and linked tables on the part number that checking on the EventDate being between the Start and End dates is slow? It shouldn't be.
If you're trying to generate some table that covers every date as an intermediate table to speed things up, I think you're attacking this the wrong way.
February 19, 2014 at 3:28 pm
Tally Table is perfect for this. Hit the link in my signature on it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 19, 2014 at 4:06 pm
Steve Jones - SSC Editor (2/19/2014)
Need to see some sample data here. It's not quite clear. Are you saying that if you join the base and linked tables on the part number that checking on the EventDate being between the Start and End dates is slow? It shouldn't be.If you're trying to generate some table that covers every date as an intermediate table to speed things up, I think you're attacking this the wrong way.
BaseTable:
RecKey | Part | LT | StartDate | EndDate
1 | AB | 5 | 1/1/2014 | 1/5/2014
2 | AB | 7 | 1/6/2014 | 1/8/2014
3 | AC | 3 | 1/5/2014 | 1/6/2014
Resulting table would be:
RecKey | Part | LT | EventDate
1 | AB | 5 | 1/1/2014
1 | AB | 5 | 1/2/2014
1 | AB | 5 | 1/3/2014
1 | AB | 5 | 1/4/2014
1 | AB | 5 | 1/5/2014
2 | AB | 7 | 1/6/2014
2 | AB | 7 | 1/7/2014
2 | AB | 7 | 1/8/2014
3 | AC | 3 | 1/5/2014
3 | AC | 3 | 1/6/2014
I may be attacking it the wrong way.
I'm trying to match records of a certain date to another table which has date rangers for a certain Lead Time, and the resulting query will have fields from both tables. So, I'm attempting to match on the date/Part, and then connect to the base table on the RecKey.
Not sure how else to do it.
I'm trying to improve the speed over an implementation in QlikView, as I despise the data analysis tools in QlikView.
February 19, 2014 at 4:47 pm
Can we see the DDL for these tables, and if you have one available, the actual execution plan of what you've written up so far that's running poorly?
Do you have the option to adjust indexes at the schema level, or is this in a third part database for the vendor?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 19, 2014 at 5:09 pm
Is this close? Note, as Craig mentioned, you need a Tally table to do this.
Here's his code for creating one:
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
use TEMPDB;
go
DROP TABLE #Parts;
GO
CREATE TABLE #Parts (
RecKey INT,
Part CHAR(2),
LT INT,
StartDate DATE
CONSTRAINT pkParts PRIMARY KEY (RecKey));
INSERT INTO #Parts(RecKey,Part,LT,StartDate)
VALUES ('1','AB','5','1/1/2014');
INSERT INTO #Parts(RecKey,Part,LT,StartDate)
VALUES ('2','AB','7','1/6/2014');
INSERT INTO #Parts(RecKey,Part,LT,StartDate)
VALUES ('3','AC','3','1/5/2014');
SELECT RecKey
, Part
, LT
, StartDate
, DateAdd(d,N-1,StartDate) AS EventDate
, N
FROM #Parts INNER JOIN SCRIDB.dbo.Tally t ON #Parts.LT>=t.N
AND t.N>0;
February 19, 2014 at 5:57 pm
It's been a while since I've looked at the execution plan. I'm just getting back into it.
I did look at the tally table, but it seems to be one long one, and I seem to need many shorter ones, but I haven't deciphered your code yet.
I'm still looking at what you just posted for me. I would like to avoid insert statements, as that means some sort of loop to fill the table, in my pea brain anyway. I'll look at it and see if something is escaping me.
David
February 19, 2014 at 10:08 pm
nonghead-webspam (2/19/2014)
I did look at the tally table, but it seems to be one long one, and I seem to need many shorter ones, but I haven't deciphered your code yet.
No... you only need just one long one. The criteria and the implied CROSSJOIN take care of the rest. Please see the following article for how it replaces certain types of loops and how it works in general.
http://www.sqlservercentral.com/articles/T-SQL/62867/
I'm still looking at what you just posted for me. I would like to avoid insert statements, as that means some sort of loop to fill the table, in my pea brain anyway. I'll look at it and see if something is escaping me.
David
Look again. The code with the inserts is just to create a test table from your original data. It's not a part of the solution. Only the last SELECT is the solution although I see that the END DATE wasn't used. LT was used as a "number of days" and that's not right for your case. The following should do it... using a Tally Table, of course.
SELECT bt.RecKey
,bt.Part
,bt.LT
,EventDate = DATEADD(dd,t.N-1,bt.StartDate)
FROM dbo.BaseTable bt
JOIN dbo.Tally t
ON t.N <= DATEDIFF(dd,bt.StartDate,bt.EndDate)+1
;
To be sure, I haven't actually tested it against your data because I frequently don't have the time to convert your "data" into readily consumable data. Please see the first link in my signature line below under "Helpful Links" to see how to do that correctly for future posts.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply