Solved

SQL Server 2000 update records using function

Posted on 2011-09-19
65
472 Views
Last Modified: 2012-05-12
Hi.  I am trying to get an update statement to work in a SQL Server 2000 table using a function.

Table name:  DSXref
Function name:  GetRepsPixel

GetRepsPixel gets passed a string like ‘6-123’ and returns a larger string like ‘6-123-d1’.

The statement below shows the data I want from the function when I manually pass the string to the function.

select sampleid from GetRepsPixel('6-123') correctly returns ‘6-123-d1’

But I want to program the logic so I can pass a value  like ‘6-123’ to the function in a variable that contains that value.

For example, I’d want to create working code for the pseudo code line below to work:

Update DataSummaryCouponXref set RepPixel = (select sampleid from GetRepsPixel(DataSummaryCouponXref field called CouponForDS))

In other words, I want to loop through the DataSummaryCouponXref table and update a field called RepPixel and set it to the value returned from GetRepsPixel when I pass a field called CouponForDS in that record to the function.

I hope this makes sense.  It seems so simple but I can’t get it.

Thanks,
Alexis
0
Comment
Question by:alexisbr
  • 28
  • 28
  • 4
  • +2
65 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
alexisbr,

First, it appears you want to use this function in a scalar way; therefore, WHY is the function designed as a table-valued function? If that was a mistake, the easy route would be to make the function a scalar-valued function and change your update to:

Update DataSummaryCouponXref 
Set RepPixel = dbo.GetRepsPixel(CouponForDS);

Open in new window


If it must stay as a table valued function, then you will need to treat it as a table. You can use CROSS APPLY to join it to your original table and do an UPDATE with JOIN.

Update tto set tto.RepPixel = tfr.sampleid
From DataSummaryCouponXref tto
Cross Apply dbo.GetRepsPixel(tto.CouponForDS) tfr
;

Open in new window


You will want to make sure the function only ever returns one row, though, so maybe a better way:

Update tto set tto.RepPixel = tfr.sampleid
From DataSummaryCouponXref tto
Cross Apply (
   Select Top 1 sampleid
   From dbo.GetRepsPixel(tto.CouponForDS)
) tfr
;

Open in new window


Hope that helps!

Best regards and happy coding,

Kevin
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
Seems too easy, what have I missed?

Update DataSummaryCouponXref
set RepPixel = GetRepsPixel(CouponForDS)
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
And it just sunk in you are using SQL 2000; therefore, CROSS APPLY is not an option. *sigh*
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
Oh, it's a TBF--missed that.  Good catch Kevin.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
So, I would stick with converting this function or creating another function that returns a scalar value representing a singular sampleid per CouponForDS value.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
@dqmq: no worries. I think with SQL 2000, there will be no choice, but to reduce it to that more simple case OR just join the table from function with the table being updated...therefore, it should be that easy as you said in the end. :)
0
 

Author Comment

by:alexisbr
Comment Utility
Thanks, everyone.  Yes, that's why I said SQL Server 2000.   I wish we had a later version so I would have other options but we don't.  I continued working on this issue once I posted this question and got it working using a SQL loop.  However, even with only 18,000 records, it's way too slow.

I am an Access/VB programmer who is trying to get acquainted with SQL coding.  

Mwvisa1, you said I could join the table to the function.  Can you please give me an example?

Thanks,
Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
That is what my additional comment is for. You cannot in SQL 2000. Please see my comment about the function type. If you are only getting back one value, then why not use a SCALAR function?
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
0
 

Author Comment

by:alexisbr
Comment Utility
Thanks, mwvisa1.  I think I understand what you are saying.  I did not write this function but was trying to use it the way it was.  It returns a table.  I only need the one value and no additional rows.  I am trying to rewrite it now.  I will post back with any questions.

Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
And if rewrite doesn't work, you can try my TOP 1 suggestion:
Update DataSummaryCouponXref 
Set RepPixel = (
   Select Top 1 sampleid 
   From GetRepsPixel(DataSummaryCouponXref.CouponForDS)
)
;

Open in new window

0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 70 total points
Comment Utility
Given how the function works, it can potentially return more than one value for each one supplied.  That begs the question, which one do you want?

Can you front-end the tvf with a function of your own that reduces the table to a single value?

Create Function YourFunction ( @StringIn Varchar(20))
returns varchar (30)
as
begin
declare @stringout varchar(30)
declare @Yourtab table (sampleid varchar(30))
insert into @yourtab
   select sampleid from GetRepsPixel(@StringIn)
select @stringout = sampleid from @yourtab
return (@stringout)
end
go


Update DataSummaryCouponXref
set RepPixel = YourFunction(CouponForDS)



0
 

Author Comment

by:alexisbr
Comment Utility
Hi dqmq,
The scenario you described is what I'm trying to get working.  I turned this logic into an SP that returns the one value I need and then I'm doing an update.  But it's very slow and therefore unusable.  Let me see if I can get it to work as a function and I will post back.

Thanks,
Alexis
0
 

Author Comment

by:alexisbr
Comment Utility
I got the code working as a function that returns one value.  Now I have to loop through the table and update the records.  It's very slow. The function itself is somewhat slow due to the data is looking through and I don't think there's much we can do about that.

Is there a way to update the records in my cross reference table without looping sequentially through the records?  I set up a view to only return ID's for records that have not been updated but that didn't help much.

The entire table only has about 18,000 records.

I pasted my current code below.

Thanks,
Alexis
set nocount on
declare @thedot varchar(20)
declare @id int, @couponForDS varchar(20)
select @id = min(id) from vwMincouponForDS
update DataSummaryCouponXref set RepPixel = ''
while @id is not null
begin 
  --vwMincouponForDS looks at distinct DataSummaryCouponXref records where repPixel is blank and 
  --couponForDataSummary not blank so we're only looking at records that haven't been updated yet
   SELECT @couponForDS = couponForDataSummary from vwMincouponForDS where id = @id
   SET @thedot = dbo.getDot(@couponForDS)  --my new function
  -- print cast(@id as varchar(4)) + ' ' + @couponForDS + ' ' + @thedot
   UPDATE DataSummaryCouponXref set RepPixel = @thedot where couponForDataSummary = @couponForDS

  --gets next id that's not the id we just used and that hasn't already been updated (RepPixel <> '')
   select @id = min(id) from vwMincouponForDS where id > @id
end

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
What exactly is this trying to do? The process looks like an over complication, but maybe I am not understanding the end game.
0
 

Author Comment

by:alexisbr
Comment Utility
I have a cross ref table for test results.  Coupon is the test name. RepPixel is a test value that links to the Coupon.  The function returns RepPixel so I can put it in the Xref table tied to its proper Coupon.  When I get to the point to use the function, the other values in the cross ref table are already there.  So I need to loop through the cross ref table and, based on the couponForDataSummary value in each row, update another field in that same row, called RepPixel, with the result of the function.

Therefore, I pass couponForDataSummary to the function and it returns RepPixel, which I then have to put back in the same row.

I have to somehow loop through the entire xref table (18000) records and update RepPixel based on CouponforDataSummary.  I used the view to limit some of the records to loop through, since many records in the Xref table will have the same CouponForDataSummary.  I don't need to update one by one.  If I could find all the distinct CouponForDataSummary values and then update the table using the function that way, that would be idea.  

The current code yields the correct results but is so slow that it's not usable.

I hope this makes sense.

Thanks,
Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
So there is a one-to-one relationship between the records based on Coupon? If so, why not use a simple UPDATE with JOIN?
0
 

Author Comment

by:alexisbr
Comment Utility
I didn't know you could write an update statement with a join that uses a function's return value as the update value.  Can you please show me how to do that?

Thanks,
Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
First I am saying why use the function at all. It seems like in your code, you are updating all the values one at a time in your loop anyway, so why can't you just join to the other table? If that other table has to come from the function, I showed the basic principal here: http:#36562158
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
And I see I missed a piece of your code above is actually retrieving a value from function dbo.getDot(). The comment I linked above does show that concept also, but I will post here again so it is clear:

Update DataSummaryCouponXref 
Set RepPixel = dbo.getDot(CouponForDS);

Open in new window

0
 

Author Comment

by:alexisbr
Comment Utility
I tried the line below but it doesn't update anything.  I get no error messages or anything displayed.  It just keeps running without changing any data.

Update DataSummaryCouponXref Set RepPixel = dbo.getDot(CouponForDataSummary) where CouponForDataSummary is not Null

Then for the heck of it, I ran the line below and it worked perfectly in 0 seconds and updated all 18000 records.

Update DataSummaryCouponXref Set RepPixel = 'xxx' where CouponForDataSummary is not Null

I also confirmed the function works below.  It returns the correct value in 2 seconds.  We know the function is slow but we don't really have any way to get the data we need in the xref table.

DECLARE @coupon varchar(20), @RepPixel varchar(20)
SET @coupon = '6-090111-4-5'
SET @RepPixel = dbo.getDot(@coupon)
PRINT @RepPixel  --correctly returns 6-090111-4-5d1 in 2 seconds - slow for a return like this.

Any ideas?

Thanks,
Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
If you can share the code of the function, then I can take a look when I get out of training tonight. Otherwise, I may be out of ideas if joining the two tables is not an option for you. I still have not seen a response to that, so having to assume.
0
 

Author Comment

by:alexisbr
Comment Utility
Thank you. I've attached the function code.

The guy who wrote the original function that returned multiple values helped change it to return only one value.  It's looking at values in one table and then comparing those values to values in another table before landing at the final return value.  That's why it's so slow.  We have lots of data in these tables.  It's a data warehouse.


Alexis
CREATE function [dbo].[getDot] (@coupon VARCHAR(20) )  
Returns varchar(20)
 AS
BEGIN 
DECLARE @Dot varchar(20)
DECLARE @BadTbl TABLE (SampleID VARCHAR(50), Voltage  FLOAT, AverageV FLOAT, StDevV FLOAT, Deviation  FLOAT);
DECLARE @AvgStdDev TABLE (SampleID VARCHAR(50), AvgVoltage FLOAT, StDevVoltage FLOAT);
--set @instring = '6-090111-4-5'

--Samples Outside of Acceptable Deviation --
---------------------------------------------
INSERT INTO @BadTbl
SELECT     vwLogTABLE_1.SampleID , vwLogTABLE_1.Voltage, derivedtbl_1_1.AverageV, derivedtbl_1_1.StDevV, 
                      ABS(vwLogTABLE_1.Voltage - derivedtbl_1_1.AverageV) AS Deviation
FROM         dbo.vwLogTABLE AS vwLogTABLE_1 INNER JOIN
                          (SELECT     TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
                            FROM          dbo.vwLogTABLE AS vwLogTABLE_2
                            GROUP BY LEFT(SampleID, 12)) AS derivedtbl_1_1 ON LEFT(vwLogTABLE_1.SampleID, 12) = derivedtbl_1_1.RepPixel
WHERE     (vwLogTABLE_1.LogDate > CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) 
                  AND left(SampleID,12) =  @coupon  
                  AND (ABS(vwLogTABLE_1.Voltage - derivedtbl_1_1.AverageV) > .1)
--------------------------------------------
--select * from @badtbl
--Samples Averaged That are acceptable--
--------------------------------------------
INSERT INTO @AvgStdDev
SELECT     TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
FROM          dbo.vwLogTABLE AS vwLogTABLE_2
WHERE sampleID not in (SELECT SampleID FROM @BadTbl) AND left(sampleID,12) = @coupon
GROUP BY LEFT(SampleID, 12)

--select * from @AvgStdDev

--SELECT     TOP 1 dbo.vwLogTABLE.SampleID, Voltage, ABS(Voltage - t1.AvgVoltage) AS Deviation
--FROM         dbo.vwLogTABLE INNER JOIN @AvgStdDev t1 on left(vwLogTABLE.SampleID,12) = t1.SampleID
--ORDER BY  ABS(Voltage - t1.AvgVoltage) 

SET @DOT = (SELECT     TOP 1 dbo.vwLogTABLE.SampleID
FROM         dbo.vwLogTABLE INNER JOIN @AvgStdDev t1 on left(vwLogTABLE.SampleID,12) = t1.SampleID 
ORDER BY  ABS(Voltage - t1.AvgVoltage) )

RETURN (@DOT)

END

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
>It's looking at values in one table and then comparing those values to values in another table before landing at the final return value.  That's why it's so slow.  We have lots of data in these tables.


What's slowing it down are lack of appropriate indexes and/or expressions like these:

INNER JOIN @AvgStdDev t1 on left(vwLogTABLE.SampleID,12) = t1.SampleID

ON LEFT(vwLogTABLE_1.SampleID, 12) = derivedtbl_1_1.RepPixel


When you join large tables, you need indexes on the columns that are joined.   But  then, you need to avoid  functions in the join clause which preclude use of the indexes.   When you use LEFT() in the join clause, then the optimizer cannot use the index on the column that is being shortened.


Also, I've eliminated the table variables from your query.






CREATE function [dbo].[getDot] (@coupon VARCHAR(20) )  
Returns varchar(20)
 AS
BEGIN 
DECLARE @Dot varchar(20)
--set @instring = '6-090111-4-5'

SET @DOT = (SELECT TOP 1 dbo.vwLogTABLE.SampleID
FROM dbo.vwLogTABLE INNER JOIN 
(
SELECT TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
FROM  dbo.vwLogTABLE AS vwLogTABLE_2
WHERE sampleID not in
   (
--Samples Outside of Acceptable Deviation --
SELECT     vwLogTABLE_1.SampleID 
FROM         dbo.vwLogTABLE AS vwLogTABLE_1 INNER JOIN
                          (SELECT     TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
                            FROM          dbo.vwLogTABLE AS vwLogTABLE_2
                            GROUP BY LEFT(SampleID, 12)) AS derivedtbl_1_1 ON LEFT(vwLogTABLE_1.SampleID, 12) = derivedtbl_1_1.RepPixel
WHERE     (vwLogTABLE_1.LogDate > CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) 
                  AND left(SampleID,12) =  @coupon  
                  AND (ABS(vwLogTABLE_1.Voltage - derivedtbl_1_1.AverageV) > .1)
   )
AND sampleID like left(@coupon + space(12),12) + '%'
) t1 on left(vwLogTABLE.SampleID,12) = t1.SampleID 
ORDER BY  ABS(Voltage - t1.AvgVoltage) )

RETURN (@DOT)

END

Open in new window

0
 

Author Comment

by:alexisbr
Comment Utility
Thanks, dqmq.  When I run this code, I get an error:  "Error 207: Invaoid column name sampleid,invalid column name AvgVoltage".  The title of the error message is "Microsoft SQL-DMO (ODBC SQL STATE: 42522) These 2 fields were in one of the table vars.  The error did not give a line number.  This must have happened due to the removal of the table vars that held the values.

I will look at this more in the morning to see if I can figure out what happened.

Thanks,
Alexis
0
 

Author Comment

by:alexisbr
Comment Utility
It appears as though the problem is here:
AND sampleID like left(@coupon + space(12),12) + '%'
) t1 on left(vwLogTABLE.SampleID,12) = t1.SampleID
ORDER BY  ABS(Voltage - t1.AvgVoltage) )

In the old function, t1 was the table variable @AvgStdDev.  I tried to put t1 in the select that replaces the table variable but I kept getting "Incorrect syntax" errors.

Thanks,
Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
I agree with dgmg. There are a lot of functions on columns which removes the ability to utilize indexing on those columns. I finally have a chance to take a look at the code, I will see if I spot anything although dgmg may have you on the right track despite a few type-o's you are noting.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
See if something like this is faster:
ALTER function [dbo].[getDot] (@coupon VARCHAR(20) )  
Returns varchar(20)
 AS
BEGIN 
DECLARE @Dot varchar(20)
DECLARE @BadTbl TABLE (SampleID VARCHAR(50), Voltage  FLOAT, AverageV FLOAT, StDevV FLOAT, Deviation  FLOAT);
DECLARE @AvgStdDev TABLE (SampleID VARCHAR(50), AvgVoltage FLOAT, StDevVoltage FLOAT);
--set @instring = '6-090111-4-5'

--Samples Outside of Acceptable Deviation --
---------------------------------------------
INSERT INTO @BadTbl
SELECT l.SampleID, l.Voltage, d.AverageV, d.StDevV
     , ABS(l.Voltage - d.AverageV) AS Deviation
FROM dbo.vwLogTABLE AS l
CROSS JOIN (
   SELECT AVG(Voltage) AS AverageV
        , STDEV(Voltage) AS StDevV
   FROM dbo.vwLogTABLE
   WHERE SampleID LIKE (@coupon + '%')
) AS d
WHERE l.LogDate > '20110101'
AND l.SampleID LIKE (@coupon + '%')

--------------------------------------------
--select * from @badtbl
--Samples Averaged That are acceptable--
--------------------------------------------
INSERT INTO @AvgStdDev
SELECT @coupon, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
FROM dbo.vwLogTABLE AS l
WHERE NOT EXISTS (
   SELECT 1
   FROM @BadTbl AS b
   WHERE b.SampleID = l.SampleID
   AND b.Deviation > 0.1
)
AND SampleID LIKE (@coupon + '%')

SET @DOT = (
   SELECT TOP 1 l.SampleID
   FROM dbo.vwLogTABLE AS l
   JOIN @AvgStdDev AS a ON l.SampleID LIKE (a.SampleID  + '%')
   ORDER BY ABS(l.Voltage - a.AvgVoltage) 
)

RETURN (@DOT)

END

Open in new window

0
 

Author Comment

by:alexisbr
Comment Utility
Thanks, mwvisa1.  I replaced the existing function with your code and it is much faster.

When I run the code below in the QA, it runs in 0 seconds instead of 2.  That is awesome!

DECLARE @THECOUPON varchar(20)
DECLARE @THEDOT varchar(20)
SET @THECOUPON = '6-090111-4-5'
SET @THEDOT = dbo.getDot(@THECOUPON)
PRINT @THEDOT

Then I tried to use the code below in the QA.  It doesn't give me an error but it's not updating any records.  It just keeps running.  Did I miss something?  I can get it to run with my looping code but it's still slow that way.  Is there a way to get the update statement below working?

Update DataSummaryCouponXref
Set RepPixel = dbo.getDot(CouponForDataSummary), datelastupdated = getdate() where CouponForDataSummary is not null;

Thanks,
Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Try this and see if there is anything odd in the results:

SELECT RepPixel, dbo.getDot(CouponForDataSummary) AS NewRepPixel
FROM DataSummaryCouponXref 
WHERE CouponForDataSummary IS NOT NULL;

Open in new window

0
 

Author Comment

by:alexisbr
Comment Utility
I ran this code in the QA:
SELECT RepPixel, dbo.getDot(CouponForDataSummary) AS NewRepPixel
FROM DataSummaryCouponXref
WHERE CouponForDataSummary IS NOT NULL

After 5 minutes, it was still running.  I then stopped it and it displayed what it had done so far.  The data was correct but it had only gone through 2000 of the 18000 records.

Thanks,
Alexis

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Okay, so you are getting the correct NewRepPixel, but it is slow. But if you change to an UPDATE, then it doesn't update anything. Hmm.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
What data type is CouponForDataSummary? Does it have an index on it, i.e., is this fast?

SELECT RepPixel, CouponForDataSummary AS NewRepPixel
FROM DataSummaryCouponXref 
WHERE CouponForDataSummary IS NOT NULL
;

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
And what data type is RepPixel? Just checking to see if any implicit conversions are going on.
0
 

Author Comment

by:alexisbr
Comment Utility
Yes, there's an index on CouponForDataSummary. The field is varchar(30).  I could make it varchar(20) if that helps at all.  I ran the latest select you suggested and it returns all the rows in 0 seconds which, when excluding nulls, is 12565.
0
 

Author Comment

by:alexisbr
Comment Utility
RepPixel is also varchar(30).
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Okay. Thanks. Everywhere, you compare the @coupon, you use the LEFT(..., 12) characters; therefore, let's try it like this.

ALTER function [dbo].[getDot] (@coupon VARCHAR(12))  
RETURNS VARCHAR(30)
 AS
BEGIN 
DECLARE @Dot VARCHAR(30)
DECLARE @BadTbl TABLE (SampleID VARCHAR(50), Voltage  FLOAT, AverageV FLOAT, StDevV FLOAT, Deviation  FLOAT);
DECLARE @AvgStdDev TABLE (SampleID VARCHAR(50), AvgVoltage FLOAT, StDevVoltage FLOAT);
--set @instring = '6-090111-4-5'

--Samples Outside of Acceptable Deviation --
---------------------------------------------
INSERT INTO @BadTbl
SELECT l.SampleID, l.Voltage, d.AverageV, d.StDevV
     , ABS(l.Voltage - d.AverageV) AS Deviation
FROM dbo.vwLogTABLE AS l
CROSS JOIN (
   SELECT AVG(Voltage) AS AverageV
        , STDEV(Voltage) AS StDevV
   FROM dbo.vwLogTABLE
   WHERE SampleID LIKE (@coupon + '%')
) AS d
WHERE l.LogDate > '20110101'
AND l.SampleID LIKE (@coupon + '%')

--------------------------------------------
--select * from @badtbl
--Samples Averaged That are acceptable--
--------------------------------------------
INSERT INTO @AvgStdDev
SELECT @coupon, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
FROM dbo.vwLogTABLE AS l
WHERE NOT EXISTS (
   SELECT 1
   FROM @BadTbl AS b
   WHERE b.SampleID = l.SampleID
   AND b.Deviation > 0.1
)
AND SampleID LIKE (@coupon + '%')

SET @DOT = (
   SELECT TOP 1 l.SampleID
   FROM dbo.vwLogTABLE AS l
   JOIN @AvgStdDev AS a ON l.SampleID LIKE (a.SampleID  + '%')
   ORDER BY ABS(l.Voltage - a.AvgVoltage) 
)

RETURN (@DOT)

END

Open in new window


Then run the update like this:
UPDATE DataSummaryCouponXref 
SET RepPixel = dbo.getDot(LEFT(CouponForDataSummary, 12))
   , datelastupdated = getdate() 
WHERE CouponForDataSummary IS NOT NULL;

Open in new window

0
 

Author Comment

by:alexisbr
Comment Utility
I made the changes but the update is still not updating anything.  No errors, no messages - it just keeps running but does nothing.  Thanks.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
OK, might be a late comer and might be unwelcomed or create confusion, so I do apologies in advance...

Way back in http:#36568895 alexisbr said it was working. Just slow and had to loop through every row. Well, can fix that up with the following (I think)...

By tracing back thought the data variables and using the *real* column in place of variables, then I think it is more like :

   UPDATE DataSummaryCouponXref set RepPixel = dbo.getDot(v.couponForDataSummary)
   FROM DataSummaryCouponXref
   INNER JOIN vwMincouponForDS V on V.couponForDataSummary = DataSummaryCouponXref.couponfordatasummary
   WHERE isnull(DataSummaryCouponXref.RepPixel,'') = ''   -- I think the comments suggests that only unupdated reppixels.


-- BUT

-- if that is the case, then why do we need anything from vwMincouponForDS ? 

-- after all, the "join" or criteria to link the two tables is couponForDataSummary, and thats what we need for the function...

-- So, all that linking is not needed - right ?

-- Except that vwMincouponForDS does provide a list of those couponForDataSummary that do need updating (maybe)

-- So, we could also consider :

   UPDATE DataSummaryCouponXref set RepPixel = dbo.getDot(couponForDataSummary)
   WHERE isnull(DataSummaryCouponXref.RepPixel,'') = ''   -- I think the comments suggests that only unupdated reppixels.
   AND EXISTS ( select NULL from vwMincouponForDS V where V.couponForDataSummary = DataSummaryCouponXref.couponfordatasummary)

-- Or, is that "exists" actually needed at all ?

Open in new window


As for the function... It is currently hurting my head *laughing*

It would seem there are a few views involved (just guessing based on names) and that might be *interesting* in terms of performance.

But based on the original function, it might be worth checking the above update statement first. Looping ID by ID will be slow compared to "set" updates. Also, might want to double check vwMincouponForDS - it also sounds like it might be a view and there might be some contention as a result there too.

And please take backups / precautions before doing any of these big updates.

So, a few interesting questions about some of those data sources (especially starting with "VW") and to get to the best solution we might need to see the definitions for :

1) vwMincouponForDS
2) DataSummaryCouponXref
3) vwLogTABLE
4) Maybe some "sample" or test data for the above ?

If you feel so inclined to consider the above and provide any additional information, then I think it can be solved reasonably quickly.
0
 

Author Comment

by:alexisbr
Comment Utility
Hi Mark,
Thanks for your help.  
1.  vwMinCouponForDs was used in my old looping logic to only display records in DataSummaryCouponXref that need to be updated.  I was trying to limit the number of records that had to be looped through by only displaying the ID of records that hadn't been updated yet.  Since multiple records have the same CouponForDataSummary, each update statement could update more one record through the loop.  This table is truncated before the process starts. "Select TOP 1000 Percent CouponForDataSummary, MIN(ID) as ID from dbo.DataSummaryCouponXref where (CouponForDataSummary <> '') AND (RepPixel = '') group by CouponForDataSummary Order by CoupnForDataSummary
2.  DataSummaryCouponXref
ID bigint    (PK)
Coupon  varchar(30)
CouponForDataSummary varchar(30)
RepPIxel varchar(30)
DateLastUpdated datetime
Additional indexes on coupon and CouponForDataSummary.
3.  VwLogTable
select dbo.LogTable.*,Substring(sampleid,10) as sampleID10 from dbo.LogTable
I have attached 2 screenshots that describe LogTable below.
4.  Unfortunately, I can't show you sample data for LogTable as it is company internal data.  I am pasting a screenshot of how the DataSummaryCouponXref table looks when it's updated.

Please let me know if you need additional info.
Thanks,
Alexis
 LogTable fields LogTable indexes CouponForDataSummary Sample Data
0
 

Author Comment

by:alexisbr
Comment Utility
FYI After Mark's comments about maybe trying to use my original looping logic, I ran it again after the fixes to the function and it ran in about 30 minutes.  Before mwvisa1's improvement to the function, it was taking several hours to run.  So the speed is improved but I need to run this process during the day every 10 minutes while others are on the system.  When I just ran it, I was one of only a few people on the network.

Thanks,
Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Thanks for jumping in Mark!

Alexis, how did Mark's statement with EXISTS clause stack up speed-wise? also, since multiple items can have the same CouponForDS, it would seem you do need an additional filter to ensure you are not wasting effort. If you can, maybe set all the RepPixel columns to be processed to NULL, so you don't have to wrap it with ISNULL and check for both NULL and empty string. Without an index on that column it may not matter, but maybe it will help ad it appears you definitely want to have some filter that marks a row as already processed. Possibly, you can use the DateLastUpdated field if you cannot reset the RepPixel.
0
 

Author Comment

by:alexisbr
Comment Utility
Hi everyone,
Both of Mark's update statements also do not update anything.  The update works if I update a different field and don't use the function. But when I use the function in the update statement, the process keep going but never updates any RepPixel values.  I see no errors and no indication that there is a problem.

At the point I want to run this step (there are several previous steps that run fine), the DataSummaryCouponXref  table has just been truncated and the records have been recreated, leaving RepPixel NULL.  

And, yes, vwMinCouponForDS was my attempt to not waste time updating records that were already updated, so I check for Null/blank RepPixel and those records don't get picked up again in my looping logic.  If we could get the Update statement to work, we wouldn't need to loop.  But you are right, I should have included that same "exclusion" logic from my view in the update statement.

Thanks,
Alexis
0
 

Author Comment

by:alexisbr
Comment Utility
OK I just ran my looping code by putting it in an SP and then running it through SQL Agent.  The update process ran in 23:59 even with everyone else on the server now.

I've pasted the code below.

Thanks,
Alexis
CREATE PROCEDURE [dbo].[spTemp092111] AS
set nocount on
declare @thedot varchar(20)
declare @id int, @couponForDS varchar(20)
select @id = min(id) from vwMincouponForDS
while @id is not null
begin 
  --vwMincouponForDS looks at distinct DataSummaryCouponXref records where repPixel is blank and 
  --couponForDataSummary not blank so we're only looking at records that haven't been updated yet
   SELECT @couponForDS = couponForDataSummary from vwMincouponForDS where id = @id
   SET @thedot = dbo.getDot(@couponForDS)  --new function
   UPDATE DataSummaryCouponXref set RepPixel = @thedot, datelastupdated = getdate() where couponForDataSummary = @couponForDS
  --gets next id that's not the id we just used and that hasn't already been updated (RepPixel <> '')
   select @id = min(id) from vwMincouponForDS where id > @id 
end
GO

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
If all the view is doing is filtering for the RepPixel values that are NULL, see what the estimated execution plan comes out for this:

UPDATE DataSummaryCouponXref 
SET RepPixel = dbo.getDot(@couponForDS)
  , DateLastUpdated = GETDATE() 
WHERE RepPix IS NULL
;

Open in new window


In particular, see if it is recommending adding a missing index and the cost associated.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Replace @couponForDS with CouponForDataSummary.

By the way, is this the definition of the view?
"Select TOP 1000 Percent CouponForDataSummary, MIN(ID) as ID from dbo.DataSummaryCouponXref where (CouponForDataSummary <> '') AND (RepPixel = '') group by CouponForDataSummary Order by CoupnForDataSummary"

If so, it would appear that RepPixel is set to '' and not NULL.

It would be interesting to check the execution of that query by itself also.

I would try:
Select CouponForDataSummary, ID
From dbo.DataSummaryCouponXref
Where (CouponForDataSummary <> '') AND (RepPixel = '');

See if that makes a difference, but really you can just add that criteria to the update.

UPDATE DataSummaryCouponXref 
SET RepPixel = dbo.getDot(@couponForDS)
  , DateLastUpdated = GETDATE() 
WHERE (CouponForDataSummary <> '') AND (RepPixel = '')
;

Open in new window


Kevin
0
 

Author Comment

by:alexisbr
Comment Utility
Thanks, Kevin.  I appreciate your sticking with this.    I've pasted the view below, and also the estimated exec plan for:
UPDATE DataSummaryCouponXref
SET RepPixel = dbo.getDot(@couponForDS)
  , DateLastUpdated = GETDATE()
WHERE RepPix IS NULL
;

I had never heard of estimated exec plan before so I'm not sure what to make of the results.

One other thing - am I supposed to be trying these update statements inside my looping code or on their own?  If they go inside my looping code, I need to tie the update to the current value in CouponforDataSummary.  If not, well, the update statements are still not working at all.  They keep running but doing nothing.  I keep stopping the execution of the updates and am running it currently using my looping code, which works but it still too slow.   I don't know why the update statements aren't working on their own.

And RepPixel is definitely NULL when this update process starts.  At the point this update runs, the rest of the cross reference table has been created and this is the last piece.

Thanks,
Alexis
Exec plan for Update statement
vwMinCouponforDS
SELECT     TOP 100 PERCENT CouponForDataSummary, MIN(ID) AS ID
FROM         dbo.DataSummaryCouponXref
WHERE     (CouponForDataSummary <> '') AND (CouponForDataSummary IS NOT NULL) AND (RepPixel = '' OR
                      RepPixel IS NULL)
GROUP BY CouponForDataSummary
ORDER BY MIN(ID)

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
OK, we dont need that view... It is a view on the very same rows that we are trying to update. It also does grouping and order by so it will be a bit slow to resolve and if doing the update we dont really have to worry about groups or order by.

So, lets get rid of it, but use the same criteria in the update.


   UPDATE DataSummaryCouponXref set RepPixel = dbo.getDot(couponForDataSummary)
   WHERE isnull(RepPixel,'') = ''
   AND  isnull(couponForDataSummary,'') <> ''

Open in new window


Then we can work on the function itself now that we have resolved one potential locking conflict.

So, using the above approach we might want to first test it by changing the "update" to a "select" and it will first show any applicable rows, and also give an indicator as to speed :

   select dbo.getDot(couponForDataSummary),* from DataSummaryCouponXref 
   WHERE isnull(RepPixel,'') = ''
   AND  isnull(couponForDataSummary,'') <> ''

Open in new window


0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Alexis, I was hoping you would run it outside the loop and without the variable @couponForDS. I made a type-o and so followed up with a comment to replace that with couponForDataSummary as Mark just showed. If RepPixel is definitely NULL, I would think RepPixel IS NULL would be more efficient; however, I trust Mark's expertise there. Similarly, if we only want couponForDataSummary that are valid values then I would think we could just filter couponForDataSummary <> '' to eliminate NULLs without putting a function around that column.

Mark, this is how far I got on optimizing the function -- http:#36576367
I am sure there is more to do, at the time it is what my brain could handle. It executes in less than a second by itself according to Alexis IIRC.

Kevin
0
 

Author Comment

by:alexisbr
Comment Utility
Thanks but I'm still unclear as to which Update we are talking about.  Are you saying this update statement goes in my looping SP?  In that code, I am using vwMinCouponForDs to get the next ID that has not been updated yet.  It's not in the update statement.  I can't tell you anything about performance with just the update statement way because it has never worked.  The update statement by itself just runs without updating anything.

Thanks,
Alexis
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 215 total points
Comment Utility
Alexis, my apologies. I have been searching for coffee with no success. *smile* Both Mark and I are suggesting that the view is unnecessary since the filter used there can just be applied on the table we are updating. Both of our comments have the UPDATE statements we are wanting run. I should have put it in context of the SP.

ALTER PROCEDURE [dbo].[spTemp092111] 
AS
BEGIN
UPDATE DataSummaryCouponXref 
SET RepPixel = dbo.getDot(couponForDataSummary)
  , DateLastUpdated = GETDATE() 
WHERE couponForDataSummary <> ''
AND RepPixel IS NULL
;
END
GO

Open in new window


What Mark is suggesting was to run this as a SELECT outside of the stored procedure to get a feel for rows we are dealing with and time.

That looks like:
SELECT dbo.getDot(couponForDataSummary), RepPixel
FROM DataSummaryCouponXref 
WHERE couponForDataSummary <> ''
AND RepPixel IS NULL
;

Open in new window


If that still takes a long time, you can do the estimated execution plan and post that as a .XML attachment. We can then open that on our end in SQL Management Studio versus just an image.
0
 

Author Comment

by:alexisbr
Comment Utility
I'm sorry that I am getting confused.  SQL coding is not my area of expertise (I'm a VB programmer) but I am really learning a lot.

Kevin - our last 2 posts were posted at the same time so I didn't see your comments when I posted mine.

I just started again with the dataset and confirmed that RepPixel is null.  I then ran the following code from the QA.  Just like all the other times, it is still running but it is not updating any records.  I don't understand why it is not doing anything except running.

 UPDATE DataSummaryCouponXref set RepPixel = dbo.getDot(couponForDataSummary)
   WHERE isnull(RepPixel,'') = ''
   AND  isnull(couponForDataSummary,'') <> ''

However, if I run the code below without using the function, it runs in 1 second.

 UPDATE DataSummaryCouponXref set RepPixel = 'xx'  --dbo.getDot(couponForDataSummary)
   WHERE isnull(RepPixel,'') = ''
   AND  isnull(couponForDataSummary,'') <> ''

Thanks,
Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Thanks for the update and no need to be sorry. BTW, it is probably 5:30 a.m. for Mark now. So if he does not respond until tomorrow or later tonight you will know why. But we'll be here; no worries. :)

Back to the question, for giggles, please try my version http:#36583108 and attach plan, although I am not really sure the ISNULL() is doing anything to performance, but I am just curious. Even though the function is running faster, as Mark said we may need to head back there for optimization. One thing that keeps coming up in my mind is having to compare just the 12 characters of SAMPLEID. Are these really the Coupon + 'some additional text'? Or does SAMPLEID = Coupon? If so, that may be an improvement we can make right off the bat, then we need to see if we can use one statement versus multiple. If SAMPLEID is longer than Coupon, is there a chance to have a calculated column that represents the coupon that we could then index? Additionally, if we can figure out how to do this so that you get all coupons back at once, as a table then you might be able to forego the function or at least make the end operation a little more efficient.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Dont worry about getting confused - that is our job (*laughing* hopefully not to confuse but to help alleviate confusion and hopefully educate along the way)

OK, we have some good information from the last run...

But first...

the View "vwMinCouponforDS" contains the same rows that we want to update, but it has some interesting things in there like groupings. So, it has to resolve the view first, but might contain some locks and checks on the data. That means that when we update the raw data, it has problems and potential conflicts. So, we are better off avoiding that if we dont need to use it. If all it is really doing is providing a trimmed down view of "candidate" rows for update then we can very simply use the same criteria in our update.

that was the first problem to resolve, and seems to be resolved - ie we dont really need that view.

now the second problem to resolve is the function not returning anything. Thats why I included the second "select" example above.

   select dbo.getDot(couponForDataSummary),* from DataSummaryCouponXref 
   WHERE isnull(RepPixel,'') = ''
   AND  isnull(couponForDataSummary,'') <> '' 

Open in new window


and would love to hear back about that... We could even run that with your original getdot() function if the information being shown is not the results you expect.

Kevin, I not as worried about using the isnull() function in the where clause, and yes, I have seen the updated function and will work through that as soon as we see it extracting the correct data.

Trying to do small single steps at a time :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
"Trying to do small single steps at a time :)" I understand and I agree. Judging by the length of your comment, you probably did not see http:#36583108 ... my previous comment on function was in response to your "Then we can work on the function itself now that we have resolved one potential locking conflict." I agree with your direction and now that I know you are not catching some rest, I will let you drive. :) Thought I had to keep the bus moving until you woke back up. *laughing*
0
 

Author Comment

by:alexisbr
Comment Utility
Thanks, Kevin and Mark.  

I ran this below:
SELECT dbo.getDot(couponForDataSummary), RepPixel
FROM DataSummaryCouponXref
WHERE couponForDataSummary <> ''
AND RepPixel IS NULL
;

But it is still running after 7 minutes with no return of values.

Then I ran the one below.  It was running for 2 minutes with no data returned when I stopped it.
 select dbo.getDot(couponForDataSummary),* from DataSummaryCouponXref
   WHERE isnull(RepPixel,'') = ''
   AND  isnull(couponForDataSummary,'') <> ''

As I said, I am not an expert SQL programmer like you guys so I don't understand why the function works fine in my looping SP but not with the update statement by itself.  Why does SQL need to be looping in that way for the function to work?  It just seems so strange to me.

I have to leave work now but I will be logged on later tonight (it's 4:10pm where I am).  I will keep working on this tonight based on what you have shown me and I will let you know where we stand by the morning.

Thanks again.
Alexis
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Okay. Either there is data in CouponForDataSummary that does not yield results or an inefficiency of the function is being magnified when running row-by-row against 12-18K rows. Speak with you later on tonight.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 215 total points
Comment Utility
Alexis, We will catch up with you later :)

Kevin, I am happy being co-pilot, not really the driver :)

The "loop" quite possibly works because it is processing a single row at a time and the amount of time required to resolve the new values could be causing time outs or other conflicts when processing data as a "set".

But we do need to see the results of that select - even if it does take several minutes - maybe put in some additional clauses to reduce the number of rows - e.g. "AND couponForDataSummary like '1234%'   or a date range, or something so that we are dealing with a few hundred rows for testing and not the full set.

But we are getting very close to now knowing what we need to deal with and seems to be the function.

Would love to see the original table valued function as well... Scaler functions can be notoriuosly slow and the reason why we sometimes see bad press about about functions. But they still most definitely have their place.

One of the hardest concepts in mastering SQL for programmers is getting to grips with "set" based queries, functions, procedures and we often see row by row processing, or, extensive use of views to contain selection criteria etc. Not that there is anything particularly wrong, just that need to first explore the full requirement and then make the judgement call as to best approach.



0
 

Author Comment

by:alexisbr
Comment Utility
Thanks to you both.  I ran the query with a select to reduce the number of records returned.   I've attached a screenshot of the results.

Mark - I'm not sure what you mean by the "original table valued function".

Regards,
Alexis select query stats
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Alexis, you started the question with:

Update DataSummaryCouponXref
Set RepPixel = (select sampleid from GetRepsPixel(DataSummaryCouponXref.CouponForDataSummary))

When used in the FROM, that function must be a table-valued function. We are curious as to the original code of that function before you rewrote it to the scalar-valued one.
0
 

Author Comment

by:alexisbr
Comment Utility
Here is the original function, written by another programmer, before I touched it.

Thanks,
Alexis
DECLARE @ListOfSamples TABLE (SampleID VARCHAR(50));
DECLARE @BadTbl TABLE (SampleID VARCHAR(50), Voltage  FLOAT, AverageV FLOAT, StDevV FLOAT, Deviation  FLOAT);
DECLARE @AvgStdDev TABLE (SampleID VARCHAR(50), AvgVoltage FLOAT, StDevVoltage FLOAT);
DECLARE @instring varchar (20)
set @instring = '6-090111-4-5'

--Samples Outside of Acceptable Deviation --
---------------------------------------------
INSERT INTO @BadTbl
SELECT     vwLogTABLE_1.SampleID , vwLogTABLE_1.Voltage, derivedtbl_1_1.AverageV, derivedtbl_1_1.StDevV, 
                      ABS(vwLogTABLE_1.Voltage - derivedtbl_1_1.AverageV) AS Deviation
FROM         dbo.vwLogTABLE AS vwLogTABLE_1 INNER JOIN
                          (SELECT     TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
                            FROM          dbo.vwLogTABLE AS vwLogTABLE_2
                            GROUP BY LEFT(SampleID, 12)) AS derivedtbl_1_1 ON LEFT(vwLogTABLE_1.SampleID, 12) = derivedtbl_1_1.RepPixel
WHERE     (vwLogTABLE_1.LogDate > CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) 
                  AND left(SampleID,12) =  @instring  
                  AND (ABS(vwLogTABLE_1.Voltage - derivedtbl_1_1.AverageV) > .1)
--------------------------------------------
--select * from @badtbl
--Samples Averaged That are acceptable--
--------------------------------------------
INSERT INTO @AvgStdDev
SELECT     TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
FROM          dbo.vwLogTABLE AS vwLogTABLE_2
WHERE sampleID not in (SELECT SampleID FROM @BadTbl) AND left(sampleID,12) = @inString
GROUP BY LEFT(SampleID, 12)

--select * from @AvgStdDev

SELECT     TOP 1 dbo.vwLogTABLE.SampleID, Voltage, ABS(Voltage - t1.AvgVoltage) AS Deviation
FROM         dbo.vwLogTABLE INNER JOIN @AvgStdDev t1 on left(vwLogTABLE.SampleID,12) = t1.SampleID
ORDER BY  ABS(Voltage - t1.AvgVoltage)

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>Here is the original function, written by another programmer<<
That is not a function at least not a SQL Server UDF.
0
 

Author Comment

by:alexisbr
Comment Utility
Sorry, I posted the wrong version.  I will get it and post the function.
Thanks,
Alexis.
0
 

Author Comment

by:alexisbr
Comment Utility
Hi everyone,
I think I found one of the earlier versions of this function. I just tested this function and it takes 2 seconds to return the value.  The newest rewrite takes 0 seconds.

Thanks,
Alexis
CREATE function [dbo].[getDot] (@coupon VARCHAR(20) )  
Returns varchar(20)
 AS
BEGIN 
DECLARE @Dot varchar(20)
DECLARE @BadTbl TABLE (SampleID VARCHAR(50), Voltage  FLOAT, AverageV FLOAT, StDevV FLOAT, Deviation  FLOAT);
DECLARE @AvgStdDev TABLE (SampleID VARCHAR(50), AvgVoltage FLOAT, StDevVoltage FLOAT);
--set @instring = '6-090111-4-5'

--Samples Outside of Acceptable Deviation --
---------------------------------------------
INSERT INTO @BadTbl
SELECT     vwLogTABLE_1.SampleID , vwLogTABLE_1.Voltage, derivedtbl_1_1.AverageV, derivedtbl_1_1.StDevV, 
                      ABS(vwLogTABLE_1.Voltage - derivedtbl_1_1.AverageV) AS Deviation
FROM         dbo.vwLogTABLE AS vwLogTABLE_1 INNER JOIN
                          (SELECT     TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
                            FROM          dbo.vwLogTABLE AS vwLogTABLE_2
                            GROUP BY LEFT(SampleID, 12)) AS derivedtbl_1_1 ON LEFT(vwLogTABLE_1.SampleID, 12) = derivedtbl_1_1.RepPixel
WHERE     (vwLogTABLE_1.LogDate > CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) 
                  AND left(SampleID,12) =  @coupon  
                  AND (ABS(vwLogTABLE_1.Voltage - derivedtbl_1_1.AverageV) > .1)
--------------------------------------------
--select * from @badtbl
--Samples Averaged That are acceptable--
--------------------------------------------
INSERT INTO @AvgStdDev
SELECT     TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
FROM          dbo.vwLogTABLE AS vwLogTABLE_2
WHERE sampleID not in (SELECT SampleID FROM @BadTbl) AND left(sampleID,12) = @coupon
GROUP BY LEFT(SampleID, 12)

--select * from @AvgStdDev

--SELECT     TOP 1 dbo.vwLogTABLE.SampleID, Voltage, ABS(Voltage - t1.AvgVoltage) AS Deviation
--FROM         dbo.vwLogTABLE INNER JOIN @AvgStdDev t1 on left(vwLogTABLE.SampleID,12) = t1.SampleID
--ORDER BY  ABS(Voltage - t1.AvgVoltage) 

SET @DOT = (SELECT     TOP 1 dbo.vwLogTABLE.SampleID
FROM         dbo.vwLogTABLE INNER JOIN @AvgStdDev t1 on left(vwLogTABLE.SampleID,12) = t1.SampleID 
ORDER BY  ABS(Voltage - t1.AvgVoltage) )

RETURN (@DOT)

END

Open in new window

0
 

Author Comment

by:alexisbr
Comment Utility
Hi everyone,
I want to thank you for trying to help me.  I found another way to get this process running fast.  It's not a pretty solution but it appears to work (I still have to validate the results) and the entire process runs in 18 seconds.  I used real tables and I plan to add code to delete those tables and recreate them as needed so as to not waste disk space.  But this project had to be completed so this is a work around until I have time to do it the way you suggested.  I am splitting points and, again, thanks for your help.

If something else comes up, I will post a new question.

Regards,
Alexis
set nocount on
truncate table BadTbl
truncate table AvgStdDev

--badTbl can have null results so need to handle that
--Warning: Null value is eliminated by an aggregate or other SET operation.
INSERT INTO BadTbl
SELECT     vwLogTABLE_1.SampleID , vwLogTABLE_1.Voltage, derivedtbl_1_1.AverageV, derivedtbl_1_1.StDevV, 
                      ABS(vwLogTABLE_1.Voltage - derivedtbl_1_1.AverageV) AS Deviation,getdate()
FROM         dbo.vwLogTABLE AS vwLogTABLE_1 INNER JOIN
                          (SELECT     TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV
                            FROM          dbo.vwLogTABLE AS vwLogTABLE_2
                            GROUP BY LEFT(SampleID, 12)) AS derivedtbl_1_1 ON LEFT(vwLogTABLE_1.SampleID, 12) = derivedtbl_1_1.RepPixel
WHERE     (vwLogTABLE_1.LogDate > CONVERT(DATETIME, '2011-01-01 00:00:00', 102)) 
                  AND (ABS(vwLogTABLE_1.Voltage - derivedtbl_1_1.AverageV) > .1)

--this next insert appears to be fine
INSERT INTO AvgStdDev
SELECT     TOP 100 PERCENT LEFT(SampleID, 12) AS RepPixel, AVG(Voltage) AS AverageV, STDEV(Voltage) AS StDevV, getdate()
FROM          dbo.vwLogTABLE AS vwLogTABLE_2
WHERE sampleID not in (SELECT SampleID FROM BadTbl) 
GROUP BY LEFT(SampleID, 12)



--10/3/11 this table stores the results of badtbl and avgstdDev and inserts into another table so we can get the first one
--I am creating a unique id so I can store that id in the xref table and then use one more update statement to set the value
--of repPixel based on that ID
truncate table GetRepPixelDS

insert into GetRepPixelDS (Coupon, Dot,ABS_VMinusAvgV)
select Coupon, Dot,ABS_VMinusAvgV from vwGetRepPixelDS where len(coupon) <=20 and len(dot) <=20

--now get the id and do update
update DataSummaryCouponXref SET  DateLastUpdated = getdate(),minIDRepPixelDS = (select isnull(min(id),0) from GetRepPixelDS where GetRepPixelDS.coupon = DataSummaryCouponXref.coupon)

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now