Function "never" returns
-
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
-
>>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 -
In this case the 100k return does not actually leave the server, it is used in a SP.
Improvement how? (he asked defensivly
) -
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 -
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 !!!** -
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 -
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
-
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="mailto
luginID=@iPluginID">PluginID=@iPluginID)AND
NOT CustomerID IS NULL) as t1
WHERE
t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE <A href="mailto
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 <A href="mailto
luginID=@iPluginID">PluginID=@iPluginID)AND
NOT CustomerID IS NULL) as t1
WHERE
t1.CustomerID IN (SELECT CustomerID FROM vSigmundCustomersPerPlugin WHERE <A href="mailto
luginID=@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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply