February 11, 2008 at 3:23 pm
how do I create the String of consultantIDs? This is the proc that the would call the XML proc:
Simple, you do XML the reverse way 😉
DECLARE @ConIDs VARCHAR(4000)
SET @ConIDs =
(SELECT DISTINCT RTRIM(ConsultantID) + '|'
FROM MyTableWithConsultantNames
--WHERE = 'SOMEFILTER'
FOR XML PATH(''))
EXEC uspS_DownlineRepromotions @ConIDs
February 11, 2008 at 3:25 pm
If you ever get into a situation that you need more than 4000 bytes for your string, you can use the nvarchar(max) data type, which holds 2 GB.
February 11, 2008 at 4:00 pm
In the latest repromotion proc something is not quite right. THis part:
INSERT INTO @C
SELECTa.ConsultantID
,a.AchieveLevel
,a.AchieveTitle
,a.PeriodEndDate
FROM volume a
LEFT OUTER JOIN Volume d ON a.ConsultantID = d.ConsultantID
WHERE EXISTS
(SELECT * FROM volume b WHERE
b.consultantid=a.consultantid)
--AND ConsultantID = @ConsultantID)
I think I need to parse it some how with the incoming consultant IDs, Otherwise it looking at the entire 2.7 millon rows in the volume table.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 11, 2008 at 4:07 pm
I tried to add the new proc into my main proc and recieved the following error:
Msg 9400, Level 16, State 1, Procedure uspS_DownlineRepromotions2, Line 7
XML parsing: line 1, character 4000, unexpected end of input
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 11, 2008 at 4:11 pm
You can apply your filter here instead of later. Meaning if you implement the filter here then there is no need to filter in the actual query.
INSERT INTO @C
SELECT a.ConsultantID
,a.AchieveLevel
,a.AchieveTitle
,a.PeriodEndDate
FROM volume a
LEFT OUTER JOIN Volume d ON a.ConsultantID = d.ConsultantID
WHERE EXISTS
(SELECT * FROM volume b WHERE
b.consultantid=a.consultantid)
AND a.ConsultantID IN
(SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i)
)
February 11, 2008 at 5:31 pm
Are you trying to do all the Consultants or just a select few? How many consultants are left after you filter your recordset? The XML is only good for a finite amount of consultants and to be processing thousands of them could cause problems with the length of the string. You can try to use the varchar(max) instead of nvarchar, unless you need unicode characters.
If you are running into problems with the length of the string then you need to filter the recordset using a traditional where clause. You want to code this solution in a way were it will maintain efficiency. More importantly a few years from now you are not recoding it.
You can filter by anything you want in your insert statement. I would suggest you use the same filters you were going to use in the creation of the delimited string.
INSERT INTO @C
SELECT a.ConsultantID,
a.AchieveLevel,
a.AchieveTitle,
a.PeriodEndDate
FROM volume a LEFT OUTER JOIN Volume d ON a.ConsultantID = d.ConsultantID
WHERE EXISTS (SELECT * FROM volume b WHEREb.consultantid=a.consultantid)
AND 'some other filters that will narrow down the data and the number of consultants'
February 12, 2008 at 10:13 am
This is the code as it sits now:
ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions2]
@CONSULTANTID VARCHAR(8000)
AS
SET NOCOUNT ON
DECLARE @x XML
SET @x = ' '
DECLARE @C TABLE(
ConsultantID CHAR(7),
Level CHAR(2),
Title CHAR(25),
PeriodEndDate DATETIME
)
INSERT INTO @C
SELECT a.ConsultantID,a.AchieveLevel,a.AchieveTitle,a.PeriodEndDate
FROM volume a
LEFT OUTER JOIN Volume d ON a.ConsultantID = d.ConsultantID
WHERE EXISTS
(SELECT * FROM volume b WHERE
b.consultantid=a.consultantid)AND a.ConsultantID IN
(SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i) )
SELECT *
FROM(
SELECT DISTINCT ConB.ConsultantID, ConB.Level, ConB.Title, ConB.PeriodEndDate
FROM @C ConA INNER JOIN @C ConB ON ConA.ConsultantID = ConB.ConsultantID
AND ConA.Level = ConB.Level AND ConA.PeriodEndDate < ConB.PeriodEndDate
WHERE ConB.ConsultantID IN (
SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i))
) as CON
WHERE EXISTS (SELECT * FROM @C b
WHERE b.ConsultantID = CON.ConsultantID
AND b.Level < CON.Level
AND b.PeriodEndDate < CON.PeriodEndDate
AND NOT EXISTS (SELECT * FROM @C c
WHERE c.ConsultantID = CON.ConsultantID
AND c.PeriodEndDate > b.PeriodEndDate
AND c.PeriodEndDate < CON.PeriodEndDate))
ORDER BY PeriodEndDate
--EXEC [consultantreports].uspS_DownlineRepromotions2 '0002354|0002617'
But it is not taking too long especially for just two ConsultantiDs.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 10:29 am
So, is the query working how you want it to?
Additionally, you can remove the second filter from the actual query because you are filtering the data in the insert into @C. If you leave it in there you are increasing processing for no reason.
SELECT *
FROM(
SELECT DISTINCT ConB.ConsultantID, ConB.Level, ConB.Title, ConB.PeriodEndDate
FROM @C ConA INNER JOIN @C ConB ON ConA.ConsultantID = ConB.ConsultantID
AND ConA.Level = ConB.Level AND ConA.PeriodEndDate < ConB.PeriodEndDate
WHERE ConB.ConsultantID IN (
SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i))
) as CON
WHERE EXISTS (SELECT * FROM @C b
WHERE b.ConsultantID = CON.ConsultantID
AND b.Level < CON.Level
AND b.PeriodEndDate < CON.PeriodEndDate
AND NOT EXISTS (SELECT * FROM @C c
WHERE c.ConsultantID = CON.ConsultantID
AND c.PeriodEndDate > b.PeriodEndDate
AND c.PeriodEndDate < CON.PeriodEndDate))
ORDER BY PeriodEndDate
--EXEC [consultantreports].uspS_DownlineRepromotions2 '0002354|0002617'
This can be changed to:
SELECT *
FROM(
SELECT DISTINCT ConB.ConsultantID, ConB.Level, ConB.Title, ConB.PeriodEndDate
FROM @C ConA INNER JOIN @C ConB ON ConA.ConsultantID = ConB.ConsultantID
AND ConA.Level = ConB.Level AND ConA.PeriodEndDate < ConB.PeriodEndDate
) as CON
WHERE EXISTS (SELECT * FROM @C b
WHERE b.ConsultantID = CON.ConsultantID
AND b.Level < CON.Level
AND b.PeriodEndDate < CON.PeriodEndDate
AND NOT EXISTS (SELECT * FROM @C c
WHERE c.ConsultantID = CON.ConsultantID
AND c.PeriodEndDate > b.PeriodEndDate
AND c.PeriodEndDate < CON.PeriodEndDate))
ORDER BY PeriodEndDate
--EXEC [consultantreports].uspS_DownlineRepromotions2 '0002354|0002617'
February 12, 2008 at 10:55 am
It looks like its returning what I want but it is way to slow. It took 50 second for one consultantID and 3.45 sec for two ConsultantIDs I can't even imagine if we have over 80 consultantid passing through which can happen when working with the main downline proc.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 11:14 am
It looks like its returning what I want but it is way to slow. It took 50 second for one consultantID and 3.45 sec for two ConsultantIDs I can't even imagine if we have over 80 consultantid passing through which can happen when working with the main downline proc.
It took longer for 1 consultant than 2?
I have a another question:
Why does your insert statement have a left outer join to volumn and then you check for the existance of consultantid against another copy of the volume table?
INSERT INTO @C
SELECT a.ConsultantID,a.AchieveLevel,a.AchieveTitle,a.PeriodEndDate
FROM volume a
LEFT OUTER JOIN Volume d ON a.ConsultantID = d.ConsultantID
WHERE EXISTS
(SELECT * FROM volume b WHERE
b.consultantid=a.consultantid)AND a.ConsultantID IN
(SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i) )
I think the better method here would be to do something like this:
INSERT INTO @C
SELECT a.ConsultantID,
a.Level,a.Title,
a.PeriodEndDate
FROM Volume a
INNER JOIN
(SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i)
) as b
on a.ConsultantID = b.ConsultantID
February 12, 2008 at 11:17 am
Declaring a table variable (@C) may lead to performance problems because all rows you insert will be stored in memory...
You may try with a temp table instead.
CREATE TABLE #C(
ConsultantID CHAR(7),
Level CHAR(2),
Title CHAR(25),
PeriodEndDate DATETIME
)
and change every @C to #C
February 12, 2008 at 11:21 am
Declaring a table variable (@C) may lead to performance problems because all rows you insert will be stored in memory...
Another good point Jonnie. Another thing to do is adjusting your insert script to match the one I just posted. Also, make sure you remove the extra filtering from the main query, as I mentioned before.
February 12, 2008 at 11:58 am
I took two IDs 3 minutes and 30 seconds to run. I need the hook to the volume table because that is where the initial Consultant data is (AChieveLevel, AchieveTitle and PeriodEndDate).
I put your script in for the Insert To statement and now it does not return anything.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 12:08 pm
I also changed to use a temp table instead but still have the previous issue of no data.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 12:11 pm
Make sure that you are using the current version of the insert table.
I edited my post and you may have missed it. All the new query does is join everybody in volume to everybody in the xml string.
INSERT INTO @C
SELECT a.ConsultantID,
a.Level,a.Title,
a.PeriodEndDate
FROM Volume a
INNER JOIN
(SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i)
) as b
on a.ConsultantID = b.ConsultantID
Viewing 15 posts - 46 through 60 (of 124 total)
You must be logged in to reply to this topic. Login to reply