February 28, 2017 at 1:23 pm
Just wondering if there's much I can do to improve a query that has to join about 70K rows to a temp table that has a RangeLow (int) and RangeHigh (int) and a RangeName value that is going to be part of a GROUP BY, and the JOIN has to compare a primary query value to see if it's between a given RangeName's RangeLow and RangeHigh. All my attempts to change the index to be on the RangeLow and RangeHigh instead of RangeName end up costing the query a LOT more time and the query plan drives that portion of the query alone to 46% instead of just 9% and the elapsed time for the query reflects that.
Here's the table:
CREATE TABLE #HIT_RANGE (
RangeName varchar(20) NOT NULL,
RangeLow int NOT NULL,
RangeHigh int NOT NULL
);
CREATE UNIQUE NONCLUSTERED INDEX IX_PK_HIT_RANGE_RangeName_INCLUDES_RangeLow_RangeHigh ON #HIT_RANGE
(
RangeName ASC
)
INCLUDE (RangeLow, RangeHigh);
INSERT INTO #HIT_RANGE (RangeName, RangeLow, RangeHigh)
VALUES ('ANY HIT RANGE', 0, 2147483647),
('0 hits/pres', 0, 0),
('1 hit/pres', 1, 2),
('2 hits/pres', 2, 3),
('3 hits/pres', 3, 4),
('4 hits/pres', 4, 5),
('5 - 9 hits', 5, 10),
('10 - 19 hits', 10, 20),
('20 - 49 hits', 20, 50),
('50 plus hits', 50, 2147483647);
CREATE TABLE #SELECTED_HITS (
RangeName varchar(20) NOT NULL,
RangeLow int NOT NULL,
RangeHigh int NOT NULL
);
INSERT INTO #SELECTED_HITS (RangeName, RangeLow, RangeHigh)
SELECT HR.RangeName, HR.RangeLow, HR.RangeHigh
FROM #HIT_RANGE AS HR
WHERE HR.RangeName IN (${hitrange:-'ANY HIT RANGE'})
ORDER BY HR.RangeName;
IF EXISTS (SELECT 1 FROM #SELECTED_HITS AS H WHERE H.RangeName = 'ANY HIT RANGE')
BEGIN
DELETE S
FROM #SELECTED_HITS AS S
WHERE S.RangeName <> 'ANY HIT RANGE';
END;
CREATE UNIQUE CLUSTERED INDEX IX_PK_SELECTED_HITS_RangeName_INCLUDES_RangeLow_RangeHigh ON #SELECTED_HITS
(
RangeName ASC
);
That ${hitrange:-'ANY HIT RANGE'} text is going to get substituted for by a KPI panel application that will either supply a value for the hitrange parameter, or if not, allow the text value 'ANY HIT RANGE' to remain. Looking for ideas to see if there's a way to improve on what I have, which works fine. As I have to fix some other things in the main query, I figured I'd see if there's anything else I might be able to do, and the plan showed this part to be 9%, so I figured it was a "target of opportunity"...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2017 at 1:48 pm
For the first part, use a clustered index not a nonclustered index.
Presumably you'd want to changce to cluster on RangeLow, RangeHigh for the part of that code that joins to those values, but I can't really say since I don't see that code.
...
CREATE CLUSTERED INDEX CL_HIT_RANGE_RangeName ON #HIT_RANGE
(
RangeName ASC
);
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 28, 2017 at 2:09 pm
ScottPletcher - Tuesday, February 28, 2017 1:48 PMFor the first part, use a clustered index not a nonclustered index.Presumably you'd want to changce to cluster on RangeLow, RangeHigh for the part of that code that joins to those values, but I can't really say since I don't see that code.
...
CREATE CLUSTERED INDEX CL_HIT_RANGE_RangeName ON #HIT_RANGE
(
RangeName ASC
);
...
Yeah, that's a miss on my part, but I probably should have mentioned that it's the #SELECTED_HITS table that actually gets joined as follows:
SELECT
GD.Facility_Code AS facility_codes,
GD.DATE_GROUPER,
GD.GTPStation AS gtpstation,
GD.Operator AS operator,
HR.RangeName AS hitrange,
SUM(Total_Picked) AS Total_Picked,
AVG(CAST(GD.Total_Picked AS decimal(12,6))) AS AveragePickedQtyPerPresentation,
CASE @CYCLE_TIME_MEASURE
WHEN 'MIN' THEN MIN(CAST(GD.Total_Duration AS decimal(16,6))) /
CASE @WORK_COMPLETED
WHEN 'Donor Totes' THEN 1
WHEN 'Eaches Picked' THEN COUNT(DISTINCT GD.PresentationID)
ELSE 1
END
WHEN 'AVG' THEN AVG(CAST(GD.Total_Duration AS decimal(16,6))) /
CASE @WORK_COMPLETED
WHEN 'Donor Totes' THEN 1
WHEN 'Eaches Picked' THEN COUNT(DISTINCT GD.PresentationID)
ELSE 1
END
WHEN 'MAX' THEN MAX(CAST(GD.Total_Duration AS decimal(16,6))) /
CASE @WORK_COMPLETED
WHEN 'Donor Totes' THEN 1
WHEN 'Eaches Picked' THEN COUNT(DISTINCT GD.PresentationID)
ELSE 1
END
END AS CycleTimeValue,
COUNT(DISTINCT GD.PresentationID) AS PresentationCount,
@WORK_COMPLETED AS Work_Completed
FROM GROUPED_DATA AS GD
INNER JOIN #SELECTED_HITS AS SH
ON GD.Total_Picked >= SH.RangeLow
AND GD.Total_Picked < SH.RangeHigh
INNER JOIN #HIT_RANGE AS HR
ON SH.RangeName = HR.RangeName
GROUP BY GD.Facility_Code, GD.DATE_GROUPER, GD.GTPStation, GD.Operator, HR.RangeName
ORDER BY GD.Facility_Code, GD.DATE_GROUPER, GD.GTPStation, GD.Operator, HR.RangeName
OPTION (RECOMPILE);
The OPTION (RECOMPILE) is there because there are so many parameters that feed into this query earlier on (I've omitted all the CTEs for brevity), that it's a bit of a "catch-all" query. and I'm pretty sure that a bad plan is going to be easy to get, given the nature of the KPI panel.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2017 at 2:37 pm
Yeah, straight forward join, for a relatively small temp table, I'd probably go ahead and recluster it:
CREATE CLUSTERED INDEX CL_HIT_RANGE ON #HIT_RANGE
(
RangeName ASC
);
ALTER TABLE #HIT_RANGE ADD CHECK(RangeLow <= RangeHigh);
...
/* delete from table, other processing, etc. */
CREATE UNIQUE CLUSTERED INDEX CL_HIT_RANGE ON #HIT_RANGE
(
RangeLow,
RangeHigh
)
WITH (
DROP_EXISTING = ON,
FILLFACTOR = 100
);
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 28, 2017 at 2:54 pm
sgmunson - Tuesday, February 28, 2017 1:23 PMJust wondering if there's much I can do to improve a query that has to join about 70K rows to a temp table that has a RangeLow (int) and RangeHigh (int) and a RangeName value that is going to be part of a GROUP BY, and the JOIN has to compare a primary query value to see if it's between a given RangeName's RangeLow and RangeHigh. All my attempts to change the index to be on the RangeLow and RangeHigh instead of RangeName end up costing the query a LOT more time and the query plan drives that portion of the query alone to 46% instead of just 9% and the elapsed time for the query reflects that.Here's the table:
CREATE TABLE #HIT_RANGE (
RangeName varchar(20) NOT NULL,
RangeLow int NOT NULL,
RangeHigh int NOT NULL
);
CREATE UNIQUE NONCLUSTERED INDEX IX_PK_HIT_RANGE_RangeName_INCLUDES_RangeLow_RangeHigh ON #HIT_RANGE
(
RangeName ASC
)
INCLUDE (RangeLow, RangeHigh);INSERT INTO #HIT_RANGE (RangeName, RangeLow, RangeHigh)
VALUES ('ANY HIT RANGE', 0, 2147483647),
('0 hits/pres', 0, 0),
('1 hit/pres', 1, 2),
('2 hits/pres', 2, 3),
('3 hits/pres', 3, 4),
('4 hits/pres', 4, 5),
('5 - 9 hits', 5, 10),
('10 - 19 hits', 10, 20),
('20 - 49 hits', 20, 50),
('50 plus hits', 50, 2147483647);CREATE TABLE #SELECTED_HITS (
RangeName varchar(20) NOT NULL,
RangeLow int NOT NULL,
RangeHigh int NOT NULL
);INSERT INTO #SELECTED_HITS (RangeName, RangeLow, RangeHigh)
SELECT HR.RangeName, HR.RangeLow, HR.RangeHigh
FROM #HIT_RANGE AS HR
WHERE HR.RangeName IN (${hitrange:-'ANY HIT RANGE'})
ORDER BY HR.RangeName;IF EXISTS (SELECT 1 FROM #SELECTED_HITS AS H WHERE H.RangeName = 'ANY HIT RANGE')
BEGIN
DELETE S
FROM #SELECTED_HITS AS S
WHERE S.RangeName <> 'ANY HIT RANGE';
END;CREATE UNIQUE CLUSTERED INDEX IX_PK_SELECTED_HITS_RangeName_INCLUDES_RangeLow_RangeHigh ON #SELECTED_HITS
(
RangeName ASC
);That ${hitrange:-'ANY HIT RANGE'} text is going to get substituted for by a KPI panel application that will either supply a value for the hitrange parameter, or if not, allow the text value 'ANY HIT RANGE' to remain. Looking for ideas to see if there's a way to improve on what I have, which works fine. As I have to fix some other things in the main query, I figured I'd see if there's anything else I might be able to do, and the plan showed this part to be 9%, so I figured it was a "target of opportunity"...
The two temp tables are very small, it's the normal table which is costing. You're caught between a rock and a hard place: an index supporting the join to the temp tables won't support the aggregate, and an index on the normal table supporting the aggregate won't support the filter effected by the temp tables. I'd try both, but bet on an index supporting a streaming aggregate (keys as per GROUP BY) over a hash aggregate, which is all you'll get (without a sort) if you favour the filter. Try both, Steve.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 28, 2017 at 2:55 pm
I don't really need the index at all for #HIT_RANGE. It's #SELECTED_HITS that has to get joined based on RangeLow and RangeHigh. Would adding a CHECK to the indexing code do something that might avoid what happens when I change the code to have the clustered index on #SELECTED_HITS (RangeLow, RangeHigh) instead of on RangeName? Making that change to my code seems to destroy the performance. Mind you, there's no other place in my query where #SELECTED_HITS gets joined to anything.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2017 at 3:00 pm
sgmunson - Tuesday, February 28, 2017 1:23 PMJust wondering if there's much I can do to improve a query that has to join about 70K rows to a temp table that has a RangeLow (int) and RangeHigh (int) and a RangeName value that is going to be part of a GROUP BY, and the JOIN has to compare a primary query value to see if it's between a given RangeName's RangeLow and RangeHigh. All my attempts to change the index to be on the RangeLow and RangeHigh instead of RangeName end up costing the query a LOT more time and the query plan drives that portion of the query alone to 46% instead of just 9% and the elapsed time for the query reflects that.Here's the table:
CREATE TABLE #HIT_RANGE (
RangeName varchar(20) NOT NULL,
RangeLow int NOT NULL,
RangeHigh int NOT NULL
);
CREATE UNIQUE NONCLUSTERED INDEX IX_PK_HIT_RANGE_RangeName_INCLUDES_RangeLow_RangeHigh ON #HIT_RANGE
(
RangeName ASC
)
INCLUDE (RangeLow, RangeHigh);INSERT INTO #HIT_RANGE (RangeName, RangeLow, RangeHigh)
VALUES ('ANY HIT RANGE', 0, 2147483647),
('0 hits/pres', 0, 0),
('1 hit/pres', 1, 2),
('2 hits/pres', 2, 3),
('3 hits/pres', 3, 4),
('4 hits/pres', 4, 5),
('5 - 9 hits', 5, 10),
('10 - 19 hits', 10, 20),
('20 - 49 hits', 20, 50),
('50 plus hits', 50, 2147483647);CREATE TABLE #SELECTED_HITS (
RangeName varchar(20) NOT NULL,
RangeLow int NOT NULL,
RangeHigh int NOT NULL
);INSERT INTO #SELECTED_HITS (RangeName, RangeLow, RangeHigh)
SELECT HR.RangeName, HR.RangeLow, HR.RangeHigh
FROM #HIT_RANGE AS HR
WHERE HR.RangeName IN (${hitrange:-'ANY HIT RANGE'})
ORDER BY HR.RangeName;IF EXISTS (SELECT 1 FROM #SELECTED_HITS AS H WHERE H.RangeName = 'ANY HIT RANGE')
BEGIN
DELETE S
FROM #SELECTED_HITS AS S
WHERE S.RangeName <> 'ANY HIT RANGE';
END;CREATE UNIQUE CLUSTERED INDEX IX_PK_SELECTED_HITS_RangeName_INCLUDES_RangeLow_RangeHigh ON #SELECTED_HITS
(
RangeName ASC
);That ${hitrange:-'ANY HIT RANGE'} text is going to get substituted for by a KPI panel application that will either supply a value for the hitrange parameter, or if not, allow the text value 'ANY HIT RANGE' to remain. Looking for ideas to see if there's a way to improve on what I have, which works fine. As I have to fix some other things in the main query, I figured I'd see if there's anything else I might be able to do, and the plan showed this part to be 9%, so I figured it was a "target of opportunity"...
You don't need to use both temp tables in your final query. Choose one of them and adjust as necessary.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 28, 2017 at 3:15 pm
First quick reply:
why this:IF EXISTS (SELECT 1 FROM #SELECTED_HITS AS H WHERE H.RangeName = 'ANY HIT RANGE')
BEGIN
DELETE S
FROM #SELECTED_HITS AS S
WHERE S.RangeName <> 'ANY HIT RANGE';
END;
instead of this:
IF EXISTS (SELECT 1 FROM #SELECTED_HITS AS H WHERE H.RangeName = 'ANY HIT RANGE')
BEGIN
DELETE #SELECTED_HITS
WHERE RangeName <> 'ANY HIT RANGE';
END;
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 28, 2017 at 3:21 pm
sgmunson - Tuesday, February 28, 2017 2:09 PMScottPletcher - Tuesday, February 28, 2017 1:48 PMFor the first part, use a clustered index not a nonclustered index.Presumably you'd want to changce to cluster on RangeLow, RangeHigh for the part of that code that joins to those values, but I can't really say since I don't see that code.
...
CREATE CLUSTERED INDEX CL_HIT_RANGE_RangeName ON #HIT_RANGE
(
RangeName ASC
);
...Yeah, that's a miss on my part, but I probably should have mentioned that it's the #SELECTED_HITS table that actually gets joined as follows:
SELECT
GD.Facility_Code AS facility_codes,
GD.DATE_GROUPER,
GD.GTPStation AS gtpstation,
GD.Operator AS operator,
HR.RangeName AS hitrange,
SUM(Total_Picked) AS Total_Picked,
AVG(CAST(GD.Total_Picked AS decimal(12,6))) AS AveragePickedQtyPerPresentation,
CASE @CYCLE_TIME_MEASURE
WHEN 'MIN' THEN MIN(CAST(GD.Total_Duration AS decimal(16,6))) /
CASE @WORK_COMPLETED
WHEN 'Donor Totes' THEN 1
WHEN 'Eaches Picked' THEN COUNT(DISTINCT GD.PresentationID)
ELSE 1
END
WHEN 'AVG' THEN AVG(CAST(GD.Total_Duration AS decimal(16,6))) /
CASE @WORK_COMPLETED
WHEN 'Donor Totes' THEN 1
WHEN 'Eaches Picked' THEN COUNT(DISTINCT GD.PresentationID)
ELSE 1
END
WHEN 'MAX' THEN MAX(CAST(GD.Total_Duration AS decimal(16,6))) /
CASE @WORK_COMPLETED
WHEN 'Donor Totes' THEN 1
WHEN 'Eaches Picked' THEN COUNT(DISTINCT GD.PresentationID)
ELSE 1
END
END AS CycleTimeValue,
COUNT(DISTINCT GD.PresentationID) AS PresentationCount,
@WORK_COMPLETED AS Work_Completed
FROM GROUPED_DATA AS GD
INNER JOIN #SELECTED_HITS AS SH
ON GD.Total_Picked >= SH.RangeLow
AND GD.Total_Picked < SH.RangeHigh
INNER JOIN #HIT_RANGE AS HR
ON SH.RangeName = HR.RangeName
GROUP BY GD.Facility_Code, GD.DATE_GROUPER, GD.GTPStation, GD.Operator, HR.RangeName
ORDER BY GD.Facility_Code, GD.DATE_GROUPER, GD.GTPStation, GD.Operator, HR.RangeName
OPTION (RECOMPILE);
The OPTION (RECOMPILE) is there because there are so many parameters that feed into this query earlier on (I've omitted all the CTEs for brevity), that it's a bit of a "catch-all" query. and I'm pretty sure that a bad plan is going to be easy to get, given the nature of the KPI panel.
Now the fun stuff:
1) This query just SCREAMS dynamic SQL to me. You can then eliminate every CASE in the query if you do that. No conditionals, no screwy stuff the optimizer has to deal with - just a straight up aggregate query.
2) I cannot figure out for the life of me why you are using two temp tables. Can't they be collapsed into just one table at run time (if not before)?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 28, 2017 at 3:28 pm
Oh, some more thoughts:
A) What happens if you have NO indexes on those tables? I can't count the number of times I have removed indexes from client temp objects to make the overall process faster. Possibly not an issue here since the tables are so small, but still.
B) Thinking outside the box while continuing the dynamic SQL theme - you could build your RangeName field as a CASE statement crafted from your driver table. No join(s) necessary, and that would ABSOLUTELY be the fastest way to solve this one, and I suspect it woudn't even be close if you have any reasonable number of rows to report off of.
CASE
WHEN GD.Total_Picked >= value1start and GD.Total_Picked < value1end THEN value1name
WHEN GD.Total_Picked >= value2start and GD.Total_Picked < value3end THEN value3name
...
ELSE 'BAD DATA FOUND SOMEHOW'
END AS RangeName
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 1, 2017 at 7:54 am
TheSQLGuru - Tuesday, February 28, 2017 3:28 PMOh, some more thoughts:A) What happens if you have NO indexes on those tables? I can't count the number of times I have removed indexes from client temp objects to make the overall process faster. Possibly not an issue here since the tables are so small, but still.
B) Thinking outside the box while continuing the dynamic SQL theme - you could build your RangeName field as a CASE statement crafted from your driver table. No join(s) necessary, and that would ABSOLUTELY be the fastest way to solve this one, and I suspect it woudn't even be close if you have any reasonable number of rows to report off of.
CASE
WHEN GD.Total_Picked >= value1start and GD.Total_Picked < value1end THEN value1name
WHEN GD.Total_Picked >= value2start and GD.Total_Picked < value3end THEN value3name
...
ELSE 'BAD DATA FOUND SOMEHOW'
END AS RangeName
A couple of rather interesting thoughts. I've also considered making the table a lot larger, and finding a reasonable maximum value for TotalPicked that would be impossible to exceed, and then just assigning the right range name as a separate task. Then I can do an equi-join on such a table. I just didn't want to have to do 2 billion records or anything anywhere near that many. I'm guessing it's highly unlikely it could ever be a situation where you're picking more than 1,000 items from a container in a warehouse on any given presentation, but it could be something where the "container" is an entire pallet, and if that something were small batteries, the number could get rather large. That was my initial rationale for choosing a range table... I was afraid that the equi-join table would have to be overly large, and if I have to do a range computation on 50,000 records, how different can that be from doing 70,000 range joins? In other words, six of one, or a half dozen of the other, so to speak.
As it turns out, removing any index from the #SELECTED_HITS table has the same effect as changing the index to be on the range values, except for the elapsed time, which in the case of no index at all, remains consistent with the existing NONCLUSTERED index, at about 4 seconds. I'm going to see if I can compute the range name with that CASE statement structure and I'll let you know results.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 1, 2017 at 8:16 am
Well, if you can have tens of thousands, or perhaps just 1000 or even 100, different ranges to name the CASE construct could become unwieldy and/or result in a long compile time for the dynamic SQL. I'd probably still do it though because I will jump through extraordinary hoops to trade CPU ticks (BILLIONS of operations per second per core) for anything else, ESPECIALLY IO. Well, maybe unless the client is on a very poorly configured VM with insufficient CPU resources, but then we have a different discussion altogether. 😎
Do be sure to put OPTION (RECOMPILE) at the end of your dynamic SQL though to save from caching a potentially large single-use plan. Hmm, unless maybe you WANT the plans reused? Actually looking at them there would only be a handful of permutations (6 by my count - 3 for @CYCLE_TIME_MEASURE CASE and 2 for @WORK_COMPLETED, and there is no WHERE clause), so you may actually WANT plan reuse! Oooooh, I am liking that at first blush, especially if you have lots of range values.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 1, 2017 at 9:20 am
Again, I can imagine very few scenarios where a nonclustered index on a temp table would make real sense, typically that just wastes disk space. Just cluster the table that way before loading the table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 2, 2017 at 11:20 am
TheSQLGuru - Wednesday, March 1, 2017 8:16 AMWell, if you can have tens of thousands, or perhaps just 1000 or even 100, different ranges to name the CASE construct could become unwieldy and/or result in a long compile time for the dynamic SQL. I'd probably still do it though because I will jump through extraordinary hoops to trade CPU ticks (BILLIONS of operations per second per core) for anything else, ESPECIALLY IO. Well, maybe unless the client is on a very poorly configured VM with insufficient CPU resources, but then we have a different discussion altogether. 😎Do be sure to put OPTION (RECOMPILE) at the end of your dynamic SQL though to save from caching a potentially large single-use plan. Hmm, unless maybe you WANT the plans reused? Actually looking at them there would only be a handful of permutations (6 by my count - 3 for @CYCLE_TIME_MEASURE CASE and 2 for @WORK_COMPLETED, and there is no WHERE clause), so you may actually WANT plan reuse! Oooooh, I am liking that at first blush, especially if you have lots of range values.
Kevin,
That query is only a small fraction of the larger query, and I do have OPTION (RECOMPILE) at the end, because there are so many parameters, that's it's definitely in the category of "catch-all query". The SQL code I specify sits in a .sql file, and doesn't have any of it's own dynamic SQL. Instead, a Java-based service opens the file and does dynamic parameter substitution for recognized constructs in the format of:
${parametername}
or
${parametername:-sqlexpression}
In the latter, you can supply a valid SQL expression to substitute in when the parameter has no value. That also means it's not going to substitute in the word NULL if you haven't provided the 2nd form of the parameter.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 2, 2017 at 11:29 am
Also, what I ended up doing was changing the #SELECTED_HITS table to end up having a row for each possible value from 0 to 49, based on the RangeName values being passed in. Thus, if I had:
'1 hit/pres'
'2 hits/pres'
'3 hits/pres'
'5 - 9 hits'
I'd have a records in #SELECTED_HITS with the right RangeLow value for the numbers 1 through 9. I can also end up with 'ANY HIT RANGE', which would then be the only record in the table, and I do an equi-join on RangeName against:ISNULL(NULLIF(SH.RangeName, 'ANY HIT RANGE'), GD.RangeName)
It's pretty much as good as it will likely get.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply