May 22, 2009 at 2:00 pm
This is a query sample. If I do not use user function, query run 3-4 mins since diffirence between enddade and begdate not greater then 7 days. If I use user function, query runs ~1 hour and scans through all cust table. What could cause this? If I replace user function with SQL function (f. e. upper()), query runs again 3-4 mins. User function uf_GetPN itself does not delay processing. I use SQL Server 2005.
SELECT c.custnum, c.firstname, c.lastname,
c.company, c.addr, IsNull(c.addr2, '') addr2,
c.city, c.state, c.zipcode,
c.email,
dhc.uf_GetPN(LTrim(RTrim(c.firstname))) sal
FROM dhc.cust c
JOIN
(SELECT TOP(100) PERCENT WITH TIES MAX(odr_date) last_order, custnum
FROM dhc.orders o3
AND odr_date >= @begDate
AND odr_date < @endDate
AND NOT EXISTS(SELECT * FROM dhc.orders b WHERE b.origodrnum = o3.ordernum
AND b.origodrnum > 0
GROUP BY custnum
ORDER BY custnum
) o1
ON c.custnum = o1.custnum
WHERE c.custtype IN ('O', 'R')
May 22, 2009 at 2:31 pm
By using the UDF SQL Server has to call the UDF for each row in returned, while the UPPER() function is optimized.
If you need to use the function you would probably be better of using a TVF and using CROSS APPLY.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 22, 2009 at 3:03 pm
CROSS APPLY is for table-valued functions. Function I use is scalar-valued. Problem is following:
query selects rows based join with orders table without user function; if user function is present, query scans though all cust table.
May 22, 2009 at 3:53 pm
ason (5/22/2009)
This is a query sample. If I do not use user function, query run 3-4 mins since diffirence between enddade and begdate not greater then 7 days. If I use user function, query runs ~1 hour and scans through all cust table. What could cause this? If I replace user function with SQL function (f. e. upper()), query runs again 3-4 mins. User function uf_GetPN itself does not delay processing. I use SQL Server 2005.SELECT c.custnum, c.firstname, c.lastname,
c.company, c.addr, IsNull(c.addr2, '') addr2,
c.city, c.state, c.zipcode,
c.email,
dhc.uf_GetPN(LTrim(RTrim(c.firstname))) sal
FROM dhc.cust c
JOIN
(SELECT TOP(100) PERCENT WITH TIES MAX(odr_date) last_order, custnum
FROM dhc.orders o3 -- is there a where clause missing here???
AND odr_date >= @begDate
AND odr_date 0
GROUP BY custnum
ORDER BY custnum
) o1
ON c.custnum = o1.custnum
WHERE c.custtype IN ('O', 'R')
See bolded comment above.
May 22, 2009 at 4:01 pm
Yes you are correct. Sorry I did not copy correctly.
SELECT c.custnum, c.firstname, c.lastname,
c.company, c.addr, IsNull(c.addr2, '') addr2,
c.city, c.state, c.zipcode,
c.email,
dhc.uf_GetPN(LTrim(RTrim(c.firstname))) sal
FROM dhc.cust c
JOIN
(SELECT TOP(100) PERCENT WITH TIES MAX(odr_date) last_order, custnum
FROM dhc.orders o3
WHERE odr_date >= @begDate -- is there a where clause missing here???
AND odr_date 0
GROUP BY custnum
ORDER BY custnum
) o1
ON c.custnum = o1.custnum
WHERE c.custtype IN ('O', 'R')
May 22, 2009 at 5:02 pm
ason (5/22/2009)
CROSS APPLY is for table-valued functions. Function I use is scalar-valued. Problem is following:query selects rows based join with orders table without user function; if user function is present, query scans though all cust table.
Exactly what I said. I said, you should use A TVF (table valued function) to replace the scalar function and then use CROSS APPLY.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply