Function "never" returns

Question

Function "never" returns

Henrik Litsne

SSC-Addicted

Points: 466
More actions
July 22, 2005 at 6:46 am

#167899

One of my functions does not return it just times out after a while. When I run the same query in Query Analyzer it returns after 19 s.
I'm pretty sure I use the same user so I do not think it is security related.
The function:
CREATE FUNCTION GetAllNewUnregisteredTransactions(@iPluginID int, @iCalculationID int, @iOperation int, @sPartitionName nvarchar(200))
RETURNS @tbSalesTransactions TABLE
(
TransactionID int
)
AS
BEGIN
-- @iOperation=1 -> Sales
-- @iOperation=2 -> SalesCommission
-- @iOperation=3 -> Price
-- @iOperation=4 -> PriceCommission
-- @iOperation=5 -> PriceCompensation
IF(@iOperation=1)
BEGIN
  INSERT INTO @tbSalesTransactions(TransactionID)
  SELECT t1.FinancialTransactionID FROM
   (SELECT CustomerID, FinancialTransactionID FROM tFinancialTransaction
   WHERE
    ArticleNumber IN
     (SELECT ArticleNumber FROM tArticle WHERE Product='0001' AND ArticleNumber IN
     (SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSales(@iCalculationID)))
    AND
    TransactionCode IN (SELECT TransactionTypeID FROM vSigmundTransactionGroupsByName WHERE GroupName = 'AfterDrawnSales')
    AND
    FinancialTransactionID NOT IN (SELECT TransactionID FROM tSigmundFTExtension WHERE luginID=@iPluginID">PluginID=@iPluginID)
    AND
    NOT CustomerID IS NULL) as t1
  WHERE
   t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE luginID=@iPluginID">PluginID=@iPluginID)
  UNION
  SELECT t1.FinancialTransactionID FROM
   (SELECT CustomerID, FinancialTransactionID FROM tFinancialTransaction
   WHERE
    ArticleNumber IN
     (SELECT ArticleNumber FROM tArticle WHERE Product<>'0001' AND ArticleNumber IN
     (SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSales(@iCalculationID)))
    AND
    TransactionCode IN (SELECT TransactionTypeID FROM vSigmundTransactionGroupsByName WHERE GroupName = 'PreDrawnSales')
    AND
    FinancialTransactionID NOT IN (SELECT TransactionID FROM tSigmundFTExtension WHERE luginID=@iPluginID">PluginID=@iPluginID)
    AND
    NOT CustomerID IS NULL) as t1
  WHERE
   t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE luginID=@iPluginID">PluginID=@iPluginID)
END
IF(@iOperation=2)
BEGIN
  INSERT INTO @tbSalesTransactions(TransactionID)
  SELECT t1.FinancialTransactionID FROM
   (SELECT CustomerID, FinancialTransactionID FROM tFinancialTransaction
   WHERE
    ArticleNumber IN
     (SELECT ArticleNumber FROM tArticle WHERE Product='0001' AND ArticleNumber IN
     (SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSalesCommission(@iCalculationID)))
    AND
    TransactionCode IN (SELECT TransactionTypeID FROM vSigmundTransactionGroupsByName WHERE GroupName = 'AfterDrawnSales')
    AND
    FinancialTransactionID NOT IN (SELECT TransactionID FROM tSigmundFTExtension WHERE luginID=@iPluginID">PluginID=@iPluginID)
    AND
    NOT CustomerID IS NULL) as t1
  WHERE
   t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE luginID=@iPluginID">PluginID=@iPluginID)
  UNION
  SELECT t1.FinancialTransactionID FROM
   (SELECT CustomerID, FinancialTransactionID FROM tFinancialTransaction
   WHERE
    ArticleNumber IN
     (SELECT ArticleNumber FROM tArticle WHERE Product<>'0001' AND ArticleNumber IN
     (SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSalesCommission(@iCalculationID)))
    AND
    TransactionCode IN (SELECT TransactionTypeID FROM vSigmundTransactionGroupsByName WHERE GroupName = 'PreDrawnSales')
    AND
    FinancialTransactionID NOT IN (SELECT TransactionID FROM tSigmundFTExtension WHERE luginID=@iPluginID">PluginID=@iPluginID)
    AND
    NOT CustomerID IS NULL) as t1
  WHERE
   t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE luginID=@iPluginID">PluginID=@iPluginID)
END
IF(@iOperation=3)
BEGIN
  INSERT INTO @tbSalesTransactions(TransactionID)
  SELECT t1.FinancialTransactionID FROM
   (SELECT CustomerID, FinancialTransactionID FROM tFinancialTransaction
   WHERE
    ArticleNumber IN
     (SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesPrice(@iCalculationID))
    AND
    TransactionCode IN (SELECT TransactionTypeID FROM vSigmundTransactionGroupsByName WHERE GroupName = 'Price')
    AND
    FinancialTransactionID NOT IN (SELECT TransactionID FROM tSigmundFTExtension WHERE luginID=@iPluginID">PluginID=@iPluginID)
    AND
    NOT CustomerID IS NULL) as t1
  WHERE
   t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE luginID=@iPluginID">PluginID=@iPluginID)
END
IF(@iOperation=4)
BEGIN
  INSERT INTO @tbSalesTransactions(TransactionID)
  SELECT t1.FinancialTransactionID FROM
   (SELECT CustomerID, FinancialTransactionID FROM tFinancialTransaction
   WHERE
    ArticleNumber IN
     (SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesPriceCommission(@iCalculationID))
    AND
    TransactionCode IN (SELECT TransactionTypeID FROM vSigmundTransactionGroupsByName WHERE GroupName = 'Price')
    AND
    FinancialTransactionID NOT IN (SELECT TransactionID FROM tSigmundFTExtension WHERE luginID=@iPluginID">PluginID=@iPluginID)
    AND
    NOT CustomerID IS NULL) as t1
  WHERE
   t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE luginID=@iPluginID">PluginID=@iPluginID)
END
IF(@iOperation=5)
BEGIN
  INSERT INTO @tbSalesTransactions(TransactionID)
  SELECT FinancialTransactionID FROM tFinancialTransaction
  WHERE
   ArticleNumber IN
    (SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesPriceCompensation(@iCalculationID))
   AND
   TransactionCode IN (SELECT TransactionTypeID FROM vSigmundTransactionGroupsByName WHERE GroupName = 'PriceCompensation')
   AND
   FinancialTransactionID NOT IN (SELECT TransactionID FROM tSigmundFTExtension WHERE luginID=@iPluginID">PluginID=@iPluginID)
   AND
   NOT CustomerID IS NULL
   AND
   CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE luginID=@iPluginID">PluginID=@iPluginID)
END
RETURN
END

I'm calling it with:
DECLARE @iCalculationID int
DECLARE @iPluginID int
DECLARE @iOperationID int
SET @iCalculationID = 1
SET @iPluginID = 32
SET @iOperationID = 2
  SELECT TransactionID, @iPluginID, 0, 1, 1, 1, 1
  FROM folkspel.dbo.GetAllNewUnregisteredTransactions(@iPluginID, @iCalculationID, @iOperationID,'')
The query returns 100k IDs when I run it.
Ideas anyone?
/HL

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

>>The query returns 100k IDs when I run it.<<

Well 100k down to the client sounds like a very bad idea. You may be running out of resources depending on the client (other than QA) that you are using.

BTW: I think there is room for improvement on your function queries

* Noel

Henrik Litsne SSC-Addicted Points: 466 More actions · Answer 2

In this case the 100k return does not actually leave the server, it is used in a SP.

Improvement how? (he asked defensivly )

noeld SSC Guru Points: 96590 More actions · Answer 3

Well if that is to be used in a stored procedure. I would

Divide those "operations" in to separate stored procedures (the caller determines which to call
Change many on those "IN" to joins or exists (depending on the DB realtionships ) to minimize scans
Make use of UNION ALL instead of UNION only (if appropriate, it usually is)
Try to decrase further the number of records affected (more filters?) because one normally should not have temp tables that big

Just my $0.02

.. he asked defensivly It is not my intention to criticize you we are all here lookong for help and knowledge and I am happy to share mi opinions with you

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

don't lie noel - YOU're not here looking for help and knowledge...I've yet to read a single post to which you have not responded with "been there, done that...." - one would think you were methuselah!

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 5

Well Some (not all ) of my knowledge come from some interesting chanllenges that everyonce in a while pop up in user forums like this one and I have learned from others!

But I have to admit that there is not substitue for hardwork

* Noel

Henrik Litsne SSC-Addicted Points: 466 More actions · Answer 6

Well, I solved my problem, but not THE problem.

Instead of a function that returns a table variable I used a SP that stored the data in a permanent table and returned a key to the data. This works well, but is not estetically pleasing.

As to improvements (noeld):

1) While I strongly agree with you in general this is the exception. If I hadn't used generic functions/stored procedures in this project the number might have risen from the current 50 to something like 500. The lines of T-SQL wouldn't have risen as much (of course), but it might've Increased by a multiple of 3 or something.

2) How would that work exactly?

3) Since these queries operate on disjunct (wholy separate) sets it does not matter in this case. It is however good advice to be certain of which kind of UNION one uses since this can cause "interresting" problems.

4) Normal does not enter into this

/HL

noeld SSC Guru Points: 96590 More actions · Answer 7

Henrik,

1) While I strongly agree with you in general this is the exception. If I hadn't used generic functions/stored procedures in this project the number might have risen from the current 50 to something like 500. The lines of T-SQL wouldn't have risen as much (of course), but it might've Increased by a multiple of 3 or something.

I differ here with the #of TSQL Lines being 3x with the divided # of procedures. It is very common to find systems with 1000's of procedures you still call your "generic" one therefore client side is completely transparent!!! and the "if" statements will probably be about the same the only thing that will definitly grow is the # of smaller procedures due to the granularity that is introduced!

2) How would that work exactly?

Well I am going to give you an example with whatever I understand from your query. But keep in mind I have no clue about your ER nor your table definitons:

ex for @iOperation = 1:

INSERT INTO @tbSalesTransactions(TransactionID)

SELECT t1.FinancialTransactionID FROM

(SELECT CustomerID, FinancialTransactionID FROM tFinancialTransaction

WHERE

ArticleNumber IN

(SELECT ArticleNumber FROM tArticle WHERE Product='0001' AND ArticleNumber IN

(SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSales(@iCalculationID)))

AND

TransactionCode IN (SELECT TransactionTypeID FROM vSigmundTransactionGroupsByName WHERE GroupName = 'AfterDrawnSales')

AND

FinancialTransactionID NOT IN (SELECT TransactionID FROM tSigmundFTExtension WHERE <A href="mailtoluginID=@iPluginID">PluginID=@iPluginID)

AND

NOT CustomerID IS NULL) as t1

WHERE

t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE <A href="mailtoluginID=@iPluginID">PluginID=@iPluginID)

UNION

SELECT t1.FinancialTransactionID FROM

(SELECT CustomerID, FinancialTransactionID FROM tFinancialTransaction

WHERE

ArticleNumber IN

(SELECT ArticleNumber FROM tArticle WHERE Product<>'0001' AND ArticleNumber IN

(SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSales(@iCalculationID)))

AND

TransactionCode IN (SELECT TransactionTypeID FROM vSigmundTransactionGroupsByName WHERE GroupName = 'PreDrawnSales')

AND

FinancialTransactionID NOT IN (SELECT TransactionID FROM tSigmundFTExtension WHERE <A href="mailtoluginID=@iPluginID">PluginID=@iPluginID)

AND

NOT CustomerID IS NULL) as t1

WHERE

t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE <A href="mailtoluginID=@iPluginID">PluginID=@iPluginID)

END

Could be expressed as something like this:

INSERT INTO @tbSalesTransactions(TransactionID)

SELECT t1.FinancialTransactionID

FROM

tFinancialTransaction FT

join

tArticle A on FT.ArticleNumber = A.ArticleNumber and A.Product='0001'

join

folkspel.dbo.GetSelectedArticlesSales(@iCalculationID) SAS on ASA.ArticleNumber = A.ArticleNumber

join

vSigmundTransactionGroupsByName TG on FT.TransactionCode = TG.TransactionTypeID and TG.GroupName = 'AfterDrawnSales'

left join

tSigmundFTExtension FTE on FT.FinancialTransactionID = FTE.TransactionID and FTE.PluginID=@iPluginID

join

vSigmundCustomersPerPlugin C on FT.CustomerID = C.CustomerID and C.PluginID=@iPluginID

WHERE

FTE.TransactionID IS NULL

FT.CustomerID IS NOT NULL

UNION ALL

SELECT t1.FinancialTransactionID

FROM

tFinancialTransaction FT

join

tArticle A on FT.ArticleNumber = A.ArticleNumber and A.Product<>'0001'

join

folkspel.dbo.GetSelectedArticlesSales(@iCalculationID) SAS on ASA.ArticleNumber = A.ArticleNumber

join

vSigmundTransactionGroupsByName TG on FT.TransactionCode = TG.TransactionTypeID and TG.GroupName = 'PreDrawnSales'

left join

tSigmundFTExtension FTE on FT.FinancialTransactionID = FTE.TransactionID and FTE.PluginID=@iPluginID

join

vSigmundCustomersPerPlugin C on FT.CustomerID = C.CustomerID and C.PluginID=@iPluginID

WHERE

FTE.TransactionID IS NULL

FT.CustomerID IS NOT NULL

3) Since these queries operate on disjunct (wholy separate) sets it does not matter in this case. It is however good advice to be certain of which kind of UNION one uses since this can cause "interresting" problems.

Well I strongly disagree on that one. Especially when you know that the sets are disjunct. SQL will still try to find duplicates and even if it does not finds any the operation has to be performed and if on top of that the set is as large as you said this could be *very* expensive operation

4) Normal does not enter into this

well, I am none to tell you if your environment is ABNORMAL just make sure you have exploited all the posibilities and it will eventually be up to you to decide that

Cheers!

* Noel